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

The Oracle (tm) Users' Co-Operative FAQ

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

The lockwait column of v$session will be non-null when a session is waiting on a lock. If the session is waiting on a specific row then the following four columns will contain the rowid being waited upon: row_wait_obj#, row_wait_file#, row_wait_block#, and row_wait_row#. However, it is my experience that most lock waited conditions stem from resource contention rather than row contention. But in either case the following tools are available for your use.

Oracle provides the following dictionary views created by the $ORACLE_HOME/rdbms/admin/catblock script that will allow you to see sessions blocking other users and sessions waiting on other users:

The dictionary views are documented in the Reference manual starting with version 7.3. DBA_WAITERS shows the holding session; however, it is my experience that for versions 7 – 8.1 that these scripts will prove too slow to be useful on a busy system experiencing lock waiting for resources. Because of this many DBAs prefer to investigate lock waiting problems using the dynamic performance tables: v$session, v$lock, v$sql, and v$lock_object being the main ones.

Oracle provides a script, utllockt, in the $ORACLE_HOME/rdbms/admin directory that will print a wait for indented tree graph of blocking and waiting sessions, but it is often beneficial to view this information directly from the v$ views a bit at a time particularly in the case where only one or two sessions are waiting. The basic process is:

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.

– Mark D Powell –

Further reading: N/A