http://www.orafaq.com/node/854
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.
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.
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.
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.
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
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.
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
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.
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/ .
