Ever wondered when a user comes to you saying that he/she cannot run a DML statement, which one should be able to run with in a second.
We suspect 2 reason for the same
1) Database is terribely slow (Which cannot be the case for processing a simple update)
2) Some one is holding an exclusive lock on object which user is trying to update (quite possible).

There can be many more reasons we can find upon further investigations. In this post we will see how to investigate reason 2 – If some other user is holding lock on the object which this user is trying to modify.

lets take a simple scenario.

session 1:

SQL> create table test (col1 number, col2 varchar2(5));

Table created.

SQL> insert into test values (1,’a’);

1 row created.

SQL> insert into test values (2,’b’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test for update;

COL1 COL2
———- —–
1 a
2 b

Session 2:

SQL> update test set col2=’a’ where col1 = 1;

The above session 2 will hang !!!

SQL> select * from v$lock;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000003BFD5D868 00000003BFD5D888         54 CF          0          0          2          0     669613          0
00000003BFD5D900 00000003BFD5D920         54 XR          4          0          1          0     669625          0
00000003BFD5DA30 00000003BFD5DA50         54 RS         25          1          2          0     669613          0
00000003BFD5DAC8 00000003BFD5DAE8         35 TX     196652        882          0          6        344          0
00000003BFD5DB60 00000003BFD5DB80         53 TS          3          1          3          0     669599          0
00000003BFD5DBF8 00000003BFD5DC18         55 RT          1          0          6          0     669613          0
00000003BFD5DDC0 00000003BFD5DDE0         57 MR          1          0          4          0     669601          0
00000003BFD5DE58 00000003BFD5DE78         57 MR          2          0          4          0     669601          0
00000003BFD5DEF0 00000003BFD5DF10         57 MR          3          0          4          0     669601          0
00000003BFD5DF88 00000003BFD5DFA8         57 MR          4          0          4          0     669601          0
00000003BFD5E020 00000003BFD5E040         57 PW          1          0          3          0     669599          0

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000003BFD5E3C8 00000003BFD5E3E8         57 MR         81          0          4          0     669593          0
00000003BE50B9B8 00000003BE50B9E0         49 TM      21837          0          3          0        374          0
00000003BE50BAB8 00000003BE50BAE0         35 TM      21837          0          3          0        344          0
00000003BDC81138 00000003BDC812C0         49 TX     196652        882          6          0        374          1

15 rows selected.

If a session is blocking another session, you will see block = 1 for that session. So in out case SID=49 is blocking some other session. We can also find the session which got blocked because of SID=49.
There are 2 columns ID1 and ID2. The values of ID1 and ID2 for SID=49 will match with some other SID in v$lock table. If you see carefully in our case its matching with SID=35. So SID 35 is the session which got blocked because of SID=49. Also the session which gets blocked will not be able to get its request processed. So you will see REQUEST column will have a value > 0.
So from the above analysis we can say that SID 49 is blocking SID 35.

We can directly write a query which will give the required output.

SQL> select a.SID “Blocking Session”, b.SID “Blocked Session”
2  from v$lock a, v$lock b
3  where a.SID != b.SID
4  and a.ID1 = b.ID1
5  and a.ID2 = b.ID2
6  and b.request > 0
7  and a.block = 1;

Blocking Session Blocked Session
—————- —————
49              35

Lets understand rest of the columns in v$lock tables here.

ID1 and ID2 -> There represents the rollback segment and transaction table entries for that transaction. So when session 1 acquired the lock it got rollback segment and transaction table entry. When another session requested the same block, Oracle tried to generate a CR “Consistent read” image for the same by applying the rollback segment. But since there was exclusive lock it was not able to proceed. Unless first session relievs the lock, second session cannot proceed. So for second session its showing the value of ID1 and ID2 same as session 1 as session 2 was using the same values from rollback segment to make a CR copy.

TYPE -> This column gives the type of lock obtained on that table in which the data was locked. For more information on lock type check defination of v$lock table in Oracle references. For a session to change the data in a table it has to acquire a TX lock. This is the transaction enqueue lock.

LMODE -> This is the mode of lock. The is having values from 0 to 6, 6 being the most restrictive and 0 being least restrictive. When a session obtains lock in mode 6 that mean it has obtained exclusive lock and no other session is allowed to update the data. So for SID=49 we can see here that lock mode is exclusive (6). For more information on lock mode, you can check the v$lock table definition in Oracle references

REQUEST -> This column represent the lock mode requested by a blocking session. The value in this column gets updated only when the session is blocked. For example in our case SID=35 is being blocked so LMODE column shows a value of 0, but REQUEST column shows a value of 6. That means that SID 35 has requested lock mode 6 which has not yet assigned to it.

Some time if a session request for exclusive lock (mode 6), it might not get it because there are no Interested Transaction List (ITL) available in the block in which a user want to change data. For this user session will wait on mode 4 (shared mode) for some time and as soon as ITL is available, it will obtain the lock in mode 6.

Also in the above rows, you can see that for SID 49 and 35, there are 2 more rows and the TYPE column shows ‘TM’. There are the DML level enqueues and are acquired in lock mode 3 (Shared Row Exclusive). The lock will prevent any DDL activity on this table.
We can find the object name by getting the ID1 column value from these rows containing TM lock. 21837 in our case.

SQL> select object_name from dba_objects where object_id=21837;

OBJECT_NAME
————–
TEST

We can even get the row which is being blocked by transaction using v$session.

SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
2  from v$session where sid=35;

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
————- ————– ————— ————-
21837              1           45082             0

The above 4 components are the components of ROWID and we can generate ROWID number from there components using DBMS_ROWID package.

SQL> select dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
2  from v$session where sid=35;

DBMS_ROWID.ROWID_C
——————
AAAFVNAABAAALAaAAA

Now we can check if this was the row which blocking session was trying to update

SQL> select * from test where rowid = ‘AAAFVNAABAAALAaAAA’;

COL1 COL2
———- —–
1 a

this was the row blocking session was trying to update.

Hope this helps !!