This discussion is archived
12 Replies Latest reply: Jan 21, 2013 8:24 AM by Nikolay Savvinov RSS

how to troubleshoot contention

user13312943 Newbie
Currently Being Moderated
Hi,
I am addressing following situation

Session 1:-

10:00 AM EST statement 1: Acquire lock on table 1
10:01 AM EST statement 2: select * from table2;

Session 2:

10:02 AM EST statement 1: Trying to acquire lock on table 1 and hangs

Under this situation, I would like to find out which sql statement (In our case Session 1, Statement 1)
was actually holding the lock along with the program name, osuser, username
and which statement is waiting for the lock to be released (session 2, statement 1)

Is it possible to find this information? Oracle database version : 10.2.0.4 and above

Thank you

Sarayu
  • 1. Re: how to troubleshoot contention
    asahide Expert
    Currently Being Moderated
    Hi,

    V$LOCK(LMODE & REQUEST)?

    Regards,
  • 2. Re: how to troubleshoot contention
    Girish Sharma Guru
    Currently Being Moderated
    Is it possible to find this information? Oracle database version : 10.2.0.4 and above
    Yes. Try to run below SQL and see who is blocking whom :
    column blocker format a25
    column blockee format a25
    column sid_serial format a10
    
    select
     (select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
     a.sid || ', ' ||
     (select serial# from v$session where sid=a.sid) sid_serial,
     ' is blocking ',
     (select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
     b.sid || ', ' ||
     (select serial# from v$session where sid=b.sid) sid_serial
     from v$lock a, v$lock b
     where a.block = 1
       and b.request > 0
       and a.id1 = b.id1
       and a.id2 = b.id2;
    Regards
    Girish Sharma
  • 3. Re: how to troubleshoot contention
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi Girish,

    that's not quite what the OP is looking for. If I understand correctly, he wants to know the statement responsible for the lock, and the statement requesting the lock. Getting latter is straightforward, because it's always the current sql_id for the blocked session, getting former is not (I'm not even sure if it's possible at all).

    Best regards,
    Nikolay
  • 4. Re: how to troubleshoot contention
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    if you have the Diagnostic Pack License, then you can use V$ACTIVE_SESSION_HISTORY to browse through recent statements run by the blocking session (which you can easily identify using DBA_BLOCKERS and/or V$LOCK, e.g. http://avdeo.com/2008/06/21/identifying-locks-on-objects-using-vlocks/). For less recent statements you can use DBA_HIST_ACTIVE_SESS_HISTORY or log miner.

    Best regards,
    Nikolay
  • 5. Re: how to troubleshoot contention
    Girish Sharma Guru
    Currently Being Moderated
    Hi Nikolay,

    Yes, OP is looking for Locking SQL and I got clue from below link : (v$open_cursor)
    http://jonathanlewis.wordpress.com/2009/04/19/locking-sql/

    See this demo :
    Session 1: User Scott :
    select * from emp where empno=7369 for update;
    
    Session 2: User Scott :
    select * from emp where empno=7369 for update;
    
    ... Its blocked.
    
    Session 3:
    I ran my above query and got blocker's SID=5 and then said :
    SQL> select sql_id,sql_text from v$open_cursor where sid=5 and sql_text like '%emp%';
    
    SQL_ID        SQL_TEXT
    ------------- ------------------------------------------------------------
    2fwkw323b9d5q select * from emp where empno=7369 for update
    
    SQL>
    
    Because I know that blockee is going to acquire lock on emp table, so I said sql_text like '%emp%'.
    Regards
    Girish Sharma
  • 6. Re: how to troubleshoot contention
    Nikolay Savvinov Guru
    Currently Being Moderated
    Girish,

    thanks for the link, but as Jonathan says himself in the post: "whatever approach you take is at best intelligent guesswork, and it’s liable to error".

    Also, your demo takes the easiest case possible: session 1 executes DML and holds the lock, session 2 requests the DML and cannot obtain the lock, and session 3 runs SQL to see who's locking whom. That's not what the OP is after. As he showed in his original post, he's mostly interested in scenarios when session 1 obtains a lock, and then runs other statements (maybe hundreds of them, maybe even obtaining other locks) while still holding the original lock. Then at some moment session 2 tries to acquire the lock and has to wait -- and your task is to determine the statement responsible (which can easily be 1000 statements before the last one).


    Best regards,
    Nikolay
  • 7. Re: how to troubleshoot contention
    Girish Sharma Guru
    Currently Being Moderated
    Nikolay,

    Session 1: SID=5
    SQL> update emp set ename='ALLEN' where empno=7499;
    
    1 row updated.
    
    SQL> update emp set ename='SMITH' where empno=7369;
    
    1 row updated.
    Session 2:
    SQL> update emp set ename='ALLEN' where empno=7499;
    ....hang
    Session 3:
    SQL> select sql_id,sql_text from v$open_cursor where sid=5 and sql_text like '%emp%';
    
    SQL_ID        SQL_TEXT
    ------------- ------------------------------------------------------------
    5458cjv7wsccz update emp set ename='SMITH' where empno=7369
    
    SQL>
    But, I am not getting in what business need it is requires that which Session 1's sql is blocking Session 2. When it is identified that Session 1(SID=5) is blocking Session2 (SID=70) in mine example; then simple either ask blocker to end the transaction or kill the session. I am not getting what business need is going to suffer to know whether it is :
    update emp set ename='SMITH' where empno=7369
    or
    update emp set ename='ALLEN' where empno=7499
    or
    any PL/SQL
    or
    ...whatever you are doing your session is blocking to X user, please end the transaction or check your code.

    I have learnt that if something which is not possible or tough to do in Oracle, then I think its business need is good candidate for question.

    Regards
    Girish Sharma
  • 8. Re: how to troubleshoot contention
    Nikolay Savvinov Guru
    Currently Being Moderated
    Girish,

    >
    I am not getting what business need is going to suffer to know whether it is :
    update emp set ename='SMITH' where empno=7369
    or
    update emp set ename='ALLEN' where empno=7499
    or
    any PL/SQL
    or
    ...whatever you are doing your session is blocking to X user, please end the transaction or check your code.
    A database may contain millions of lines of code -- which one needs to be checked?

    Best regards,
    Nikolay
  • 9. Re: how to troubleshoot contention
    Girish Sharma Guru
    Currently Being Moderated
    A database may contain millions of lines of code -- which one needs to be checked?
    When we are able to find the blocker's session ID then I think what ever code or PL/SQL he/she is executing, don't you think that this user needs to learn basics of DBMS because his/her code is blocking someone; whether it is single line or millions of line ? Upto what extend Oracle will keep track idiot programmer's flaws and why ? In above example, I just executed two transactions... first transaction is running and I executed second without ending first one. This is bad programming and Oracle teaches how to write database code.

    Smart technology makes people smart too... :)

    Regards
    Girish Sharma
  • 10. Re: how to troubleshoot contention
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,
    Girish Sharma wrote:
    A database may contain millions of lines of code -- which one needs to be checked?
    When we are able to find the blocker's session ID then I think what ever code or PL/SQL he/she is executing,
    or was executing 1 minute ago. Or 10 minutes ago. Or an hour ago. In a real-world application, when everything is run through a connection pool, how are you going to determine that?
    don't you think that this user needs to learn basics of DBMS because his/her code is blocking someone;
    No, I don't think that. In fact, I'm quite sure of the opposite. A user doesn't even have to know what "DBMS", "session" or "lock" means -- in 9 cases out of 10, this has nothing to do with his job. He just presses buttons and clicks on controls. He lives in a different world and operates with different concepts (e.g. "open a new document" instead of "open a new transaction by inserting into DOCUMENT table").
    whether it is single line or millions of line ? Upto what extend Oracle will keep track idiot programmer's flaws and why ?
    So that's your answer -- you're going to call the developer an idiot and claim that the problem is solved? But the application is still hanging, and it's not clear whether or not the holding session can be safely killed (because you have no idea what it was doing), you cannot make any reasonable assumption regarding chances of the application closing the transaction naturally within reasonable time, and you cannot tell the "idiot" developer which part of the codebase he has to work on to avoid the situation in the future.
    In above example, I just executed two transactions... first transaction is running and I executed second without ending first one. This is bad programming and Oracle teaches how to write database code.
    No one means for the transaction to hang about unclosed for hours -- but the code could have gone down some unexpected path or get trapped inside an infinite loop. As a DBA, it is still your job to help resolve the situation.

    Best regards,
    Nikolay
  • 11. Re: how to troubleshoot contention
    Girish Sharma Guru
    Currently Being Moderated
    There are three persons, Application user, developer and DBA. When I said "don't you think that this user needs to learn basics of DBMS because his/her code is blocking someone;" it means, I was pointing to developer who write database code; one who always forgets to close the cursor or don't commit/rollback the transaction.
    So that's your answer -- you're going to call the developer an idiot and claim that the problem is solved? But the application is still hanging
    So DBA is telling that this user (database user) is blocker. This user did not closed the transaction by above sql please.
    which part of the codebase he has to work on to avoid the situation in the future.
    very simple either commit or rollback and forgot to close the cursor.

    I am not sure, on which point we are not going to agree, but my simple assertions is idiot developers write database code and either DBA or Oracle stands culprit, while Oracle/DBA is victim here. Application user says its DBA's mistake, developers says its DBA's mistake, while I says neither I (DBA) developed application nor I written PL/SQL. As a DBA, when I (DBA) am saying that this user is blocking other session then as a DBA, Am I not doing my task please ?

    Regards
    Girish Sharma
  • 12. Re: how to troubleshoot contention
    Nikolay Savvinov Guru
    Currently Being Moderated
    Girish Sharma wrote:
    There are three persons, Application user, developer and DBA. When I said "don't you think that this user needs to learn basics of DBMS because his/her code is blocking someone;" it means, I was pointing to developer who write database code; one who always forgets to close the cursor or don't commit/rollback the transaction.
    So that's your answer -- you're going to call the developer an idiot and claim that the problem is solved? But the application is still hanging
    So DBA is telling that this user (database user) is blocker. This user did not closed the transaction by above sql please.
    Allowing users to access the database directly is a bad practice. Normally, users access the database via an application layer. And normally, the application is using a connection pool -- i.e. there are tens or hundreds of sessions with the same username open. So in real life, you're going to see something like this in your session browser:
    SID        SERIAL#         USERNAME    STATUS  MACHINE                      SQL_ID        SQL_TEXT
    
    1101         15612         APP_USER    INACTIVE  grid55.mycompany.com     axy56aj5idc      select blah from blah where blah   --- this is the offending session
    56           910           APP_USER    ACTIVE    grid50.mycompany.com     asyq63a51d0      begin pkg_data_load.blah   
    <100 more entries like that>
    503          815           APP_USER    INACTIVE  grid32.mycompany.com     frpj02jsz0as     select something from somewhere
    How will you proceed?
    which part of the codebase he has to work on to avoid the situation in the future.
    very simple either commit or rollback and forgot to close the cursor.
    No, it's not very simple. Good database development practices forbid developer to commit before the business transaction is completed (the atomicity principle). And the logic for the business transaction can be quite complex. And somewhere along the way, the code can get stuck through no fault of the developer. For example, I saw a case when the database session used to hang while trying to parse a statement (because of an internal optimizer bug). But before it got to this place, it could have acquired many locks, intending to release them soon. As result, the session hangs itself, and also hangs a bunch of other sessions competing for the held resources. When something like this happen, the DBA must provide as much information as possible to help pinpoint the exact place where the problem occurred, rather than give general advice like "always commit or roll back".


    Best regards,
    Nikolay

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points