Oracle Application Technology

Many times I have seen either user or development team complaining of their DML statements running long or hung. And it is most of the time perceived as caused by slow database.

But every time, I don’t see any contention from database side. It is the database lock that has been causing the ‘hung’ behaviour. There are ways to investigate the database locks and the associated details.

I run below query on V$LOCK and see the following details

<pre>SQL> SELECT sid,
                                TYPE,
                                DECODE( block, 0, 'NO', 'YES' ) BLOCKER,
        DECODE( request, 0, 'NO', 'YES' ) WAITER,
        decode(LMODE,1,'    ',2,'RS',3,'RX',4,'S',5,'SRX',6,'X','NONE') lmode,
                                 decode(REQUEST,1,'    ',2,'RS',3,'RX',4,'S',5,'SRX',6,'X','NONE') request,
                                TRUNC(CTIME/60) MIN ,
                                ID1,
                                ID2,
        block
                        FROM  v$lock
      where request > 0 OR block =1;</pre>
       SID TY BLO WAI LMOD REQU        MIN        ID1        ID2      BLOCK
---------- -- --- --- ---- ---- ---------- ---------- ---------- ----------
       859 TX NO  YES NONE X             0    6094884    1244082          0
      1839 TX NO  YES NONE X             0    5242904    1939567          0
      1926 TX YES NO  X    NONE          1    5242904    1939567          1
      3082 TX YES NO  X    NONE          0    6094884    1244082          1

Any session with block=1 is the blocker. Here session with SID = 1926 is blocking other session as it has block=1. Now we need to find this blocker session is blocking which other sessions. To find this, we need to compare the ID1 and ID2 column value. Here we can see ID1 and ID2 for SID=1926 is matching the ID1 and ID2 of SID=1839 . Hence SID=1839 is the blocked session. Any session which is blocked will not be able to process their request and hence REQUEST column for those blocked session will have value > 0.

Let us describe V$LOCK to understand other interested columns.

SQL> desc v$lock
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ADDR                                                           RAW(8)
 KADDR                                                          RAW(8)
 SID                                                            NUMBER
 TYPE                                                           VARCHAR2(2)
 ID1                                                            NUMBER
 ID2                                                            NUMBER
 LMODE                                                          NUMBER
 REQUEST                                                        NUMBER
 CTIME                                                          NUMBER
 BLOCK                                                          NUMBER

ID1 and ID2 – These are rollback segment and transaction table entries respectively. When a blocker session acquired the lock, it posted an entry for rollback segment and transaction table. And when another session (blocked sessions) tried to read the same block, Oracle tried to generate the “consistent Read” image by reading the rollback segment but since there was an exclusive lock by the ‘blocker’ session, it has to wait. Therefore we same value of ID1 and ID2 for the blocker and the blocked session.

LMODE – This is the mode of lock acquired. Value ranges from 1 t0 6. 1 (null), 2 (row share) , 3 (row exclusive) , 4 (share) , 5 (share row exclusive) and 6 (exclusive). ‘Exclusive’ is the most restrictive kind of lock.

TYPE – This tells us the kind of lock put on the table where the data is locked. If a session has to change the data in a table, it has to acquire ‘transactional lock (TX)’

REQUEST – This column represents the lock mode requested by the blocking session. Any session which is being blocked will always some value updated against this column. In the query output shown above, we can see all the blocked sessions have a value of 5 against the REQUEST column. This means, all these sessions have requested for a lock mode 5 and waiting for the lock being held by the blocker session.

If the ‘Type’ of lock is ‘TM’ – which is DML level lock and generally acquired in row exclusive (LMODE = 3) mode. This will not let any DDL happening on the locked table. When the TYPE = TM, ID1 corresponds to the object ID. We can query dba_objects and find the object name and type against the given object ID.

We can find the row which is being blocked by the blocker session using v$session.

• First we need to find value of 4 columns from v$session – 1)ROW_WAIT_OBJ# 2)ROW_WAIT_FILE# 3)ROW_WAIT_BLOCK# & 4)ROW_WAIT_ROW#
• Then using these 4 values and using DBMS_ROWID packge, we can generate the ROWID of the blocked row
  • select dbms_rowid.rowid_create(1,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) where sid = <BLOCKED_SESSION>

Good understanding of database lock helps in troubleshooting and investigating the same. Hope it helps to you as well.

-Anand