Identify the blocking session

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
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
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
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
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.

How do you find out who is locking a specific row in a table?

1- Select the sid and other desired columns from v$session where lockwait is not null

2- Select * from v$lock where sid = the sid(s) from the prior step

3- Look at the ID1 column for the requested but not held lock

4- Select * from v$lock where ID1 = the ID1 value returned in the prior step

5- Select from v$session for the sid found in step 4 that has the lock

At this point you can also use the sid to query against v$sql or any of the other v$ tables that contains SQL statements to see the SQL being executed by the blocking session if the v$session table shows a status of ACTIVE for this session. SQL does not normally display for INACTIVE sessions or sessions executing DDL.

Here is a series of sample SQL scripts to perform items 1 – 5:

Find all lock waited sessions:

set echo off

rem
rem filename: session_locked.sql
rem SQL*Plus script to display selected sessions and related process infor-
rem mation for all Oracle sessions blocked from processing due to a lock
rem held by another session.
rem
rem 11/27/95 s3527 m d powell new script
rem 19991207 Mark D Powell Chg headings to reflect Oracle terminology
rem

set verify off
column machine format a08 heading "APPL.|MACHINE"
column sid format 99999
column serial# format 99999
column spid format 99999 heading "ORACLE|SERVER|PROCESS"
column process format 99999 heading "USER|APPL.|PROCESS"
column username format a12

rem

select
s.username, s.status, s.sid, s.serial#,
p.spid, s.machine, s.process, s.lockwait
from v$session s, v$process p
where s.lockwait is not null
and s.paddr = p.addr
/

Find lock information based on SID of lock waited session:

set echo off

rem
rem filename: lock_sid.sql
rem SQL*Plus script to display Oracle dml locks held by an Oracle session.
rem
rem 11/27/95 s3527 m d powell new script
rem

set verify off
set feedback on
column "Mode Held" format a9
column "Mode Req " format a9

REM
REM If type = TM then id1 = object id
REM TX rbs number and slot
REM id2 = wrap number
REM

select
sid, type, id1, id2,
decode(lmode,
0,'WAITING' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
to_char(lmode)
) "Mode Held",
decode(request,
0,'None' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
6,'Exclusive',
to_char(request)
) "Mode Req "
from v$lock
where sid = &session
/

Find holder and other waiters for a lock ID:

set echo off

rem
rem filename: lock_find.sql
rem SQL*Plus script to display all Oracle sessions holding or attempting
rem to obtain a particular lock (id1) on an object.
rem
rem 11/27/95 s3527 m d powell new script
rem 04/28/98 dbawrk m d powell + comments on value of id1 and id2
rem

set verify off
set feedback on
column "Mode Held" format a9
column "Mode Req " format a9

rem
rem If type = TM then id1 = object id
rem TX rbs number and slot
rem id2 = wrap number
rem

select
sid, type, id1, id2,
decode(lmode,
0,'WAITING' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
6,'Exclusive',
to_char(lmode)
) "Mode Held",
decode(request,
0,'None' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
6,'Exclusive',
to_char(request)
) "Mode Req "
from v$lock
where id1 = '&lockno'
/

Find the SQL for the sid

set echo off

rem
rem filename: sql_user.sql
rem SQL*Plus script to display the sql being executed for a particular
rem Oracle session.
rem
rem 11/27/95 s3527 m d powell new script
rem

set verify off

select
sid, username, command, lockwait, status,
osuser, sql_text
from v$session s, v$sqlarea q
where s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and s.sid = &usrsid
/

Just change the where clause in the first query from where lockwait is not null to where s.sid = &session_id and you have the code for session_sid.sql

Once you know the blocking session you now need to determine if the blocking session is a run away session, the user has gone to lunch or left for the day, or is a sign of a system problem. That task is beyond this article, which I hope will give you a basic introduction to lock blocker determination and tracking.