Identifying Locks on Objects using V$LOCKS

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 !!

Advertisements

12 thoughts on “Identifying Locks on Objects using V$LOCKS

  1. I’m using oracle 8.1.6.
    For what reason column ROW_WAIT_OBJ# has -1 value and the other 3 columns has 0 value?
    Thanks.

  2. Very usefull, but I had to transfer the object_id into data_object_id first before using in the dbms_rowid.rowid_create (tested with Oracle 10.2.0.2), till then you get correct rowid!

    select dbms_rowid.rowid_create (1, (SELECT data_object_id FROM user_objects WHERE object_id = ROW_WAIT_OBJ#), ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)

  3. THumbs upp for the article! This is very useful when we forgot to install Toad 🙂
    This’ll make us smarter..!

  4. Hmm is anyone else encountering problems with the images on this blog loading?

    I’m trying to find out if its a problem on my end or if it’s the blog.
    Any responses would be greatly appreciated.

  5. “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.”

    I don;t think this is accurate statement. Even if another session holds exclusive locks on rows of a block, Oracle can build Consistent Read Image for Select puposes. While updating and locking , the block is obtained in CUrrent mode. It if ITL (Interested Transaction List in the block header) Oracle points to the details of transaction entry such as unod segment and header etc.

    1. Hi Sri,

      what you are saying is correct. I should change that.
      So if we have select query, it should be able to see correct data by generating consistent image from existnig block by applying undo.
      What I meant in this context is that, 2nd session was doing DML and so it will not be able to do DML on same row by creating consistent image from UNDO.

      I hope that clarifies.

      Regards,

      Advait Deo

  6. Is there a way to scan Undo/Rollback segment to check if a copy of transaction which blocking Session has made hanges and original copy of the transaction in Undo/rollback segment

    1. You mean you want to see the changed block and original block for a transaction?
      
      I think we can see that in v$bh view. 
      Lets say I have an object T1 with one column COL1 and single row
      
      SQL> select * from T1;
      
            COL1
      ----------
      	 5
      
      DATA_OBJECT_ID for this object is 32426.
      When I first run above select, I can see that block is fetched in buffer cache in xcur mode. But I see 2 blocks being created in buffer cache. One for header and other for data block
      
      SQL> select FILE#, BLOCK#, status, OBJD, DIRTY from v$bh where OBJD = 32426;
      
           FILE#     BLOCK# STATUS	       OBJD D
      ---------- ---------- ---------- ---------- -
      	 1     237137 xcur	      32426 N
      	 1     237138 xcur	      32426 N
      
      Note that dirty column is showing "N"
      
      Let say, I update the value of column from 5 to 10
      
      SQL> update T1 set col1 = 10;
      
      1 row updated.
      
      Oracle immediately creates a CR copy of that block
      
      SQL> select FILE#, BLOCK#, status, OBJD, DIRTY from v$bh where OBJD = 32426;
      
           FILE#     BLOCK# STATUS	       OBJD D
      ---------- ---------- ---------- ---------- -
      	 1     237137 xcur	      32426 N
      	 1     237138 xcur	      32426 Y
      	 1     237138 cr	      32426 N
      
      You can also see that dirty = Y for one of the block in xcur state
      
      As I connect through more and more sessions, Oracle creates new blocks in CR mode from existing xcur dirty block
      
      SQL> select FILE#, BLOCK#, status, OBJD, DIRTY from v$bh where OBJD = 32426;
      
           FILE#     BLOCK# STATUS	       OBJD D
      ---------- ---------- ---------- ---------- -
      	 1     237137 xcur	      32426 N
      	 1     237138 cr	      32426 N
      	 1     237138 cr	      32426 N
      	 1     237138 xcur	      32426 Y
      	 1     237138 cr	      32426 N
      
      added few more sessions
      
      SQL> select FILE#, BLOCK#, status, OBJD, DIRTY from v$bh where OBJD = 32426;
      
           FILE#     BLOCK# STATUS	       OBJD D
      ---------- ---------- ---------- ---------- -
      	 1     237137 xcur	      32426 N
      	 1     237138 cr	      32426 N
      	 1     237138 cr	      32426 N
      	 1     237138 cr	      32426 N
      	 1     237138 cr	      32426 N
      	 1     237138 cr	      32426 N
      	 1     237138 cr	      32426 N
      	 1     237138 xcur	      32426 Y
      
      8 rows selected.
      
      This continues until it reaches to _db_block_max_cr_dba (which has value of 6 in my database). So not matter how many further sessions needs CR blocks max we will see only 6.
      
      Let me know if this answers your question.
      

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s