Waiting for row lock. But which row is locked?

By Franck Pachot

.
When you are waiting on a locked row, the locked resource is the transaction that has locked the row. We have no information about which row is locked. Here is how to get it from V$SESSION.

In a first session I lock a row in the SCOTT schema:

SQL> select * from SCOTT.SALGRADE where grade=1 for update ;
 
     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200

And in another session:

SQL> delete from SCOTT.SALGRADE;

Of course it’s waiting.

Let’s see the wait event:

SQL> select sid,state,event,p1raw,p1text from v$session where event like 'enq: %'
 
   SID STATE   EVENT                          P1RAW            P1TEXT
------ ------- ------------------------------ ---------------- ---------
    54 WAITING enq: TX - row lock contention  0000000054580006 name|mode

So I’ve a row lock contention.

Here is more information from V$SESSION about the row that is locked:

SQL> select row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row# from v$session where event like 'enq: %';
 
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
       116760              6             211             0

From there you can find which row is locked. It’s straightforward, but I’ve two warnings about it.

First warning is that the ROW_WAIT_OBJ# is not the OBJECT_ID but the DATA_OBJECT_ID.
You have to join with DBA_OBJECT and get the DATA_OBJECT_ID if you want to get the ROWID.

SQL> select owner,object_name,data_object_id from dba_objects where object_id=116760;
 
OWNER      OBJECT_NAM DATA_OBJECT_ID
---------- ---------- --------------
SCOTT      SALGRADE           116760

The ROW_WAIT_OBJ# is the OBJECT_ID but I need the DATA_OBJECT_ID to get the ROWID. Here it is the same, but just try to truncate the table (and insert back the data) and you will see that it is different.

The second warning is about the ROW_WAIT_FILE# which is the the absolute file number.
But you need the relative file number if you want to get the ROWID.

We have to go though DBA_DATA_FILES to get it.

SQL> select relative_fno from dba_data_files where file_id=6;
 
RELATIVE_FNO
------------
           6

It’s the same number here, but if you have transported datafiles (or have more than 1024 datafiles), then you will see different numbers.

Remember, the ROWID must change when a table is truncated (in order to be sure that we don’t read old data) and the ROWID must not change when we transport tablespaces (so that we don’t have to update all the blocks in the tablespace).

So now, I can get the ROWID with dbms_rowid and get the row that is locked:

SQL> select * from SCOTT.SALGRADE where rowid=dbms_rowid.rowid_create(1,116760,6,211,0);
 
     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200

This is the ideal case. You find the row that is locked.
For most of the mode 6 TX lock (exclusive) you will have that information because the session went to the row in order to see the lock, and that information was registered in V$SESSION.
If you have a mode 4 TX lock (share) that can be different. You find those when the lock is at higher level than the row. For example ITL wait, or unique index entry, or referential integrity (I’m talking about TX mode 4 here, not TM locks). Then the information gotten from V$SESSION is incomplete. There is no row number, it can be an index block, etc.

More information about locks at DOAG 2015.

As a summary, here is the query to get the ROWID for which sessions are waiting on TX locks:
(fixed thanks to comment below.)

select s.p1raw,o.owner,o.object_name,
dbms_rowid.rowid_create(1,o.data_object_id,f.relative_fno,s.row_wait_block#,s.row_wait_row#) row_id
from v$session s 
join dba_objects o on s.row_wait_obj#=o.object_id 
join dba_segments m on o.owner=m.owner and o.object_name=m.segment_name
join dba_data_files f on s.row_wait_file#=f.file_id and m.tablespace_name=f.tablespace_name
where s.event like 'enq: TX%'

From P1RAW, you have the lock type (5458 is TX in ascii) and the lock mode: 54580006 is mode 6 (exclusive) and 54580004 is mode 4 (share).