What's blocking my lock?

Natalka Roshak's picture
articles: 

If you've ever gotten a phone call from an annoyed user whose transaction just won't go through, or from a developer who can't understand why her application sessions are blocking each other, you know how useful it can be to identify not just whose lock is doing the blocking, but what object is locked. Even better, you can identify the exact row that a session is waiting to lock.

Create a blocking lock

To begin, create a situation where one user is actively blocking another. Open two sessions. Issue the following commands in Session 1 to build the test table:

SQL> create table tstlock (foo varchar2(1), bar varchar2(1));

Table created.

SQL> insert into tstlock values (1,'a'); 

1 row created.

SQL> insert into tstlock values (2, 'b');

1 row created.

SQL> select * from tstlock ;

FOO BAR
--- ---
1   a
2   b

2 rows selected.

SQL> commit ;

Commit complete.

Now grab a lock on the whole table, still in Session 1:

SQL> select * from tstlock for update ;

And in Session 2, try to update a row:

SQL> update tstlock set bar=
  2  'a' where bar='a' ;

This statement will hang, blocked by the lock that Session 1 is holding on the entire table.

Identify the blocking session

Oracle provides a view, DBA_BLOCKERS, which lists the SIDs of all blocking sessions. But this view is often, in my experience, a good bit slower than simply querying V$LOCK, and it doesn't offer any information beyond the SIDs of any sessions that are blocking other sessions. The V$LOCK view is faster to query, makes it easy to identify the blocking session, and has a lot more information.

SQL> select * from v$lock ;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
AF9E2C4C AF9E2C60        479 TX     131078      16739          0          6        685          0
ADDF7EC8 ADDF7EE0        422 TM      88519          0          3          0        697          0
ADDF7F74 ADDF7F8C        479 TM      88519          0          3          0        685          0
ADEBEA20 ADEBEB3C        422 TX     131078      16739          6          0        697          1
....     ....            ... ...      ....       ....       ....       ....        ....      ....

Note the BLOCK column. If a session holds a lock that's blocking another session, BLOCK=1. Further, you can tell which session is being blocked by comparing the values in ID1 and ID2. The blocked session will have the same values in ID1 and ID2 as the blocking session, and, since it is requesting a lock it's unable to get, it will have REQUEST > 0.

In the query above, we can see that SID 422 is blocking SID 479. SID 422 corresponds to Session 1 in our example, and SID 479 is our blocked Session 2.

To avoid having to stare at the table and cross-compare ID1's and ID2's, put this in a query:

SQL> select l1.sid, ' IS BLOCKING ', l2.sid
  2  from v$lock l1, v$lock l2
  3  where l1.block =1 and l2.request > 0
  4  and l1.id1=l2.id1
  5  and l1.id2=l2.id2
SQL> /

       SID 'ISBLOCKING'         SID
---------- ------------- ----------
       422  IS BLOCKING         479

1 row selected.

Even better, if we throw a little v$session into the mix, the results are highly readable:

SQL> select s1.username || '@' || s1.machine
  2  || ' ( SID=' || s1.sid || ' )  is blocking '
  3  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  4  from v$lock l1, v$session s1, v$lock l2, v$session s2
  5  where s1.sid=l1.sid and s2.sid=l2.sid
  6  and l1.BLOCK=1 and l2.request > 0
  7  and l1.id1 = l2.id1
  8  and l2.id2 = l2.id2 ;


BLOCKING_STATUS
----------------------------------------------------------------------------------------------------
BULKLOAD@yttrium ( SID=422 )  is blocking BULKLOAD@yttrium ( SID=479 )

1 row selected.

There's still more information in the v$lock table, but in order to read that information, we need to understand a bit more about lock types and the cryptically-named ID1 and ID2 columns.

Lock type and the ID1 / ID2 columns

In this case, we already know that the blocking lock is an exclusive DML lock, since we're the ones who issued the locking statement. But most of the time, you won't be so lucky. Fortunately, you can read this information from the v$lock table with little effort.

The first place to look is the TYPE column. There are dozens of lock types, but the vast majority are system types. System locks are normally only held for a very brief amount of time, and it's not generally helpful to try to tune your library cache, undo logs, etc. by looking in v$lock! (See the V$LOCK chapter in the Oracle Database Reference for a list of system lock types.)

There are only three types of user locks, TX, TM and UL. UL is a user-defined lock -- a lock defined with the DBMS_LOCK package. The TX lock is a row transaction lock; it's acquired once for every transaction that changes data, no matter how many objects you change in that transaction. The ID1 and ID2 columns point to the rollback segment and transaction table entries for that transaction.

The TM lock is a DML lock. It's acquired once for each object that's being changed. The ID1 column identifies the object being modified.

Lock Modes

You can see more information on TM and TX locks just by looking at the lock modes. The LMODE and REQUEST columns both use the same numbering for lock modes, in order of increasing exclusivity: from 0 for no lock, to 6 for exclusive lock. A session must obtain an exclusive TX lock in order to change data; LMODE will be 6. If it can't obtain an exclusive lock because some of the rows it wants to change are locked by another session, then it will request a TX in exclusive mode; LMODE will be 0 since it does not have the lock, and REQUEST will be 6. You can see this interaction in the rows we selected earlier from v$lock:

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
AF9E2C4C AF9E2C60        479 TX     131078      16739          0          6        685          0
ADEBEA20 ADEBEB3C        422 TX     131078      16739          6          0        697          1

Note that ID1 and ID2 in Session 2, which is requesting the TX lock (LMODE=0, REQUEST=6), point back to the rollback and transaction entries for Session 1. That's what lets us determine the blocking session for Session 2.

You may also see TX locks in mode 4, Shared mode. If a block containing rows to be changed doesn't have any interested transaction list (ITL) entries left, then the session acquires a TX lock in mode 4 while waiting for an ITL entry. If you see contention for TX-4 locks on an object, you probably need to increase INITRANS for the object.

TM locks are generally requested and acquired in modes 3, aka Shared-Row Exclusive, and 6. DDL requires a TM Exclusive lock. (Note that CREATE TABLE doesn't require a TM lock -- it doesn't need to lock any objects, because the object in question doesn't exist yet!) DML requires a Shared-Row Exclusive lock. So, in the rows we selected earlier from v$lock, you can see from the TM locking levels that these are DML locks:

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
ADDF7EC8 ADDF7EE0        422 TM      88519          0          3          0        697          0
ADDF7F74 ADDF7F8C        479 TM      88519          0          3          0        685          0

Identifying the locked object

Now that we know that each TM row points to a locked object, we can use ID1 to identify the object.

SQL> select object_name from dba_objects where object_id=88519 ;

OBJECT_NAME
--------------
TSTLOCK

Sometimes just knowing the object is enough information; but we can dig even deeper. We can identify not just the object, but the block and even the row in the block that Session 2 is waiting on.

Identifying the locked row

We can get this information from v$session by looking at the v$session entry for the blocked session:

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

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
        88519             16          171309             0

This gives us the object ID, the relative file number, the block in the datafile, and the row in the block that the session is waiting on. If that list of data sounds familiar, it's because those are the four components of an extended ROWID. We can build the row's actual extended ROWID from these components using the DBMS_ROWID package. The ROWID_CREATE function takes these arguments and returns the ROWID:

SQL> select do.object_name,
  2  row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
  3  dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
  4  from v$session s, dba_objects do
  5  where sid=543
  6  and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

OBJECT_NAME     ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
--------------- ------------- -------------- --------------- ------------- ------------------
TSTLOCK                 88519             16          171309             0 AAAVnHAAQAAAp0tAAA

And, of course, this lets us inspect the row directly.

SQL> select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;

FOO BAR
--- ---
1   a

Conclusion

We've seen how to identify a blocking session, and how to inspect the very row that the waiting session is waiting for. And, I hope, learned a bit about v$lock in the process.

About the author

Natalka Roshak is a senior Oracle and Sybase database administrator, analyst, and architect. She is based in Kingston, Ontario, and consults across North America. More of her scripts and tips can be found in her online DBA toolkit at http://toolkit.rdbms-insight.com/ .

Comments

Hi Natalka / Orafaq team,

You are both just doing a great deal of service by educating us on Oracle topics in a easy way to comprehend and put in practise.

Thanks again.
Sunil

This article was extremely helpful in understanding my current "DB reorg parallel processes blocking each other" problem.

Thank you so much for sharing,

Norma Jean

I have never seen such a clear explanation like this before. Great work! Hope to find similar articles with more internal details about the Oracle database in future.

Keep up the good work...

Use this query for RAC databases:

column sess format A20
SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,12) sess,
       id1, id2, lmode, request, type, inst_id
 FROM GV$LOCK
WHERE (id1, id2, type) IN
   (SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
     ORDER BY id1, request;
SESS                        ID1        ID2      LMODE    REQUEST TY    INST_ID
-------------------- ---------- ---------- ---------- ---------- -- ----------
Holder: 2173           34078745     437269          6          0 TX          2
Waiter: 2165           34078745     437269          0          6 TX          1

Hi Natalka,

This article indeed sets a platform to debug and analyze frequently occuring locking issues in the Oracle database. Few of my long awaited queries have been answered with this. I truly appreciate the efforts that you've put in here.

Best Regards,
Mahesh

This is very informative. Can you further trace down the blocking SQL? This information will be very useful for tuning the application to avoid or reduce future clocking.

cbruhn2's picture

If you got the sid it should be easy by using the following sql :

select s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = &sid
order by piece;

just insert the sid that is blocking when asked for it.

Dear Natalka/ Orafaq team

Hey guys you rock!!! Thanks for putting up such a wonderful explanation. It really helped me solve my issues. Thanks once again. Hope to find many more solutions here, keep the good work going.

Regards
Zubin

"Never Say Die"

Natalka,

You are my guru.... I love you...

Cheers,
Ameya

I'm not a DBA expert but I was finding information about oracle locks for a script to check them with other oracle things and this article is written really well and it's really simple to understand !! :D
Thanks :D

Yeah, really really well explained, sure, i liked it a lot, but... I still have no idea about how to unlock my locked objects. They are just 2 tables, both TM locks on Mode 3 and on the same session. I tried to kill the session but it sets the session to "to be killed", it tells me that it will kill it when it finishes the uninterruptible things it is doing, so, not very helpful. I know that the interruptible things is just a http request via the dbms_http. I know it cant finish the request because i forgot to enable the proxy in the procedure, so it is trying an http_request via a no proxy, so i guess is still waiting for the response.. funny innit? So any help would be apreciated.

Thanx in advance

This is the best article i found about locks after searching for a long time thanks a lot Natalka Roshak.

cheers,
sandeep

I agree with everyone above. I read a lot of Oracle information (Jonathan Lewis books and website, Ask Tom, etc.) but everything on your site is right to the point, easy to follow and I can't wait to try what I've read on my systems. I'm new to your site so I have more exploring to do. Hopefully I can find some straight forward tuning, dbms_stats, and optimizer -CBO tips. Thanks

It's really a great article on lock. It explains everything so clearly. Thanks a lot Natalka Roshak.

I agree. This is the best and most to the point explanation of locks.

Potkin Nemat
http://potkoracle.blogspot.com/

Hi Potkin,

Could you please grant me the access to see your blog " http://potkoracle.blogspot.com/ ".

Thanks,
Syed

Natalka,
That was an excellent article. You have structured the explanation so well and in a step by step manner so that it is very clear and easy to understand , and therefore easy to remember. Good job ! And thanks for the effort put in.

Cheers

Wonderful! It really helped me. I was having a similar reported issue and was able to figure out the problem using your steps.

Thanks
Sram


Dear Natalka,

your effort to explain in such way is greatly appriciated.

Thanks,
Mujahid.

Good example......got good idea after looking this example.

Thanks
Ashok M

Hi .. your examples are very clear...
and very helpful ....

Thanx & plz keep posting these knoledge sharing posts...

Recently, There was a concern that locks were causing slow response/appln hangs in the Prod environment ,
Then developed a query based on this article, scheduled thru cron to report the blocking locks very 10 minutes..
and proved that there were really no blocking locks and no issues with the DB side.. Thank you Natalka!

Good explanation with proper example.

Thanks a lot.

A very very clear explanation for those i have seen so far.
I'm 101% hot for accessing your blog.. please invite me if you can
http://potkoracle.blogspot.com/

Great post!, it really helped me to understand the basics of dealing with locks.

I've found what I think might be a mistake in the post, though I'm not really sure. The correct SQL query in the "Identifying the locked row" section that may be used to get the extended rowid is:

select do.object_name,
row_wait_obj#, do.data_object_id, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, do.data_object_id, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=543
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

That is, the data_object_id should be used instead of the object_id in the rowid_create function.

Thank you!

This is an excellent article !!
but I wonder what happens when a session locks multiple rows. how can we check all the rows locked?

EXCELLENT ARTICLE, very clear and comprehendable

I'm running 10g, and I have a blocked developer.

I can use your code before "Identifying a locked object" but then something isn't working.

my id1 in v$lock is 458753, but when I query dba_objects where object_id = 458753, I don't get any rows returned.

Has that part changed for 10g, or is there something else that I'm missing?

ID1 values depends on lock type. If the lock type is TX then ID1 is NOT the object id.

The article is great. Just one doubt. I could not understand from where the SID=543 came from in the query

select do.object_name,
row_wait_obj#, do.data_object_id, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, do.data_object_id, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=543
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

Hi Natalka,

In my case, I have two session. From these two session I fired same query.

ses 1 :
MLXWTIBSLDB (IBSLDB1) SQL >select rowid,CUSTINFO_CUSTOMERID from ptest where CUSTINFO_CUSTOMERID=404591 for update;

ROWID CUSTINFO_CUSTOMERID
------------------ -------------------
AAAfbiAABAAAMOCAAA 404591

ses 2 :

MLXWTIBSLDB (IBSLDB1) SQL >select rowid,CUSTINFO_CUSTOMERID from ptest where CUSTINFO_CUSTOMERID=404591 for update;

ses 3 :

When I checked for v$session it's showing me different block number for these sessions.

SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ------------- -------------- --------------- -------------
904 128738 1 50050 0
2852 128738 1 51925 0

Also these is no block exists with number 51925 for file 1.

MLXWTIBSLDB (IBSLDB1) SQL >select SEGMENT_NAME,FILE_ID,BLOCK_ID from dba_extents where SEGMENT_NAME ='PTEST';

SEGMENT_NAME FILE_ID BLOCK_ID
----------------------------- ----------
PTEST 1 51849
PTEST 1 51721
PTEST 1 51593
PTEST 1 51465
PTEST 1 51337
PTEST 1 51209
PTEST 1 51081
PTEST 1 50953
PTEST 1 50825
PTEST 1 50697
PTEST 1 50569
PTEST 1 50457
PTEST 1 50449
PTEST 1 50441
PTEST 1 50049

Below are the rowid generation for ses1 and ses2:

MLXWTIBSLDB (IBSLDB1) SQL >select do.object_name,
2 row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) from v$session s, dba_objects do where sid=&a and s.ROW_WAIT_OBJ# = do.OBJECT_ID
3 /
Enter value for a: 904
OBJECT_NAME ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
-------------------------------------------------------------------------------------------------------------------------------- ------------- -------------- --------------- -------------
DBMS_ROWID.ROWID_C
------------------
PTEST 128738 1 50050 0
AAAfbiAABAAAMOCAAA

MLXWTIBSLDB (IBSLDB1) SQL >select do.object_name,
2 row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) from v$session s, dba_objects do where sid=&a and s.ROW_WAIT_OBJ# = do.OBJECT_ID
3 /
Enter value for a:2852
OBJECT_NAME ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
-------------------------------------------------------------------------------------------------------------------------------- ------------- -------------- --------------- -------------
DBMS_ROWID.ROWID_C
------------------
PTEST 128738 1 51925 0
AAAfbiAABAAAMrVAAA

Can you please clarify me why am getting different block id for ses1 and ses2 as I am tring to update same record.

Thanks in advance.

is it possible for us connect oracle 8i database with oracle 10g, am using vista operating system i tried to install oracle 9i client but its giving error can any one help me with the possible solutions

Hi,

This article is great and really useful. Just wants to know this is still applicable to RAC. Because, just for an example, if the blocked sessions obtain on two different nodes will it works the same way?

Thanks

Hi,

I came upon your article that is excellent.
However, there is a small mistake about dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ):

ROW_WAIT_OBJ# is the object_id but dbms_rowid.rowid_create expects the data_object_id
ROW_WAIT_FILE# is the absolute file_id but dbms_rowid.rowid_create expects a relative file number

So that will be wrong when object_id<>data_object_id. You can test that after truncating the table - as data_object_id will change
And it will be wrong as well when file_id<>relative_fno. You will have that case when having a big number of datafiles.

That means that you need to join with dba_objects to get the data_object_id and with dba_data_files to get the absolute file_id.

Regards,
Franck.

Thanks so much for making such great contributions. I found this information to be very thorough and helpful.

I'm facing real production issues, randomly.

What do I do when the locking points to index object and rowid in the index?

-- converted from this excellent article. 
select to_char(sysdate,'yyyymmdd:hh24:mi:ss'), do.object_name,
    row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
   dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
   from v\$session s, dba_objects do
   where sid
 in
( select s.sid
 from v\$lock l, v\$session s, v\$process p
 where (l.id1, l.id2, l.type) in (select id1, id2, type from v\$lock where request>0)
    and l.sid=s.sid
    and s.paddr=p.addr
 and l.request = 0
)
   and s.ROW_WAIT_OBJ# = do.OBJECT_ID;

Output is like:

INDX_SOFT_EMAIL
       138737             20          380738             0 AAAh3xAAUAABc9CAAA

I was hoping I will find a table and then I can find the actual row.

How do I find the row being locked basedon a rowid of a index?

Nice article to explain the locking property of Oracle. I would just take the pain to mention about the lock modes. The lock mode, which is defined by LMODE in V$LOCK metadata view, is a number which is generally either 3 or 6. Below is a complete list of lock modes, that Oracle allows :

Lock Mode Abbreviation
======= =========
Row Shared SS
Row Exclusive RX
Share S
Share Row Exclusive SRX/SSX
Exclusive X
No lock NULL/''

I just found this post, and it is very nicely explained. Thanks
Foued

--moderator addition: I have published this comment, because it does bring a useful article to the top of the "recent comments" listing, and others might find it interesting. But in future when commenting on a blog post, try to add something: perhaps why you found it helpful (or not) and how you used the information, or extend upon the scripts to deliver more value.