Skip to Main Content
  • Questions
  • How to get ROWID for locked rows in oracle

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sujit.

Asked: March 01, 2017 - 1:12 pm UTC

Last updated: March 02, 2017 - 4:42 am UTC

Version: Oracle 12c

Viewed 10K+ times! This question is

You Asked

Hi,

I am carrying out an update on dept table with update query as - "update dept set dname = initcap(dname) where deptno=20;"
As, I have not committed, row lock will be placed on the row. I want to find out the rowid of dept table locked from V$LOCK. Currently, I am not able to figure out how to get it. Is there any other way?

and we said...

You cannot. The only way to see if a row is locked (via SQL) is to attempt to lock the row yourself. We don't maintain a "list of locked rows", which is why you can have an infinite number of locked rows with no server overhead.

You could perhaps make some inferences on what has been locked, for example:

Session 1

SQL> update dept set dname = 'x' where deptno = 20;

1 row updated.



Session 2

SQL> select * from dept for update skip locked;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


which "tells me" that 20 was perhaps already locked. But of course, I've now locked the rest of the table.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks a lot Connor for your quick response

Sujit, March 02, 2017 - 7:20 am UTC

Thanks a lot Connor for your quick response

Sujit, March 02, 2017 - 7:22 am UTC


Some additional points...

J. Laurindo Chiappa, March 02, 2017 - 4:09 pm UTC

Two additional points :

a) true, no list of locks exists but we can obtain a list of the locked rows that are Blocking other sessions, easily :

=> one session will lock a row doing some DML :

scott#1:SQL>select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

scott#1:SQL>update dept set loc='SP' where deptno=30;

=> another session tries to use the same row, becaming Blocked :

scott#2:SQL>update dept set loc='RJ' where deptno=30;


==> let´s check the sessions being blocked :

SYS:SQL>select sid, event from v$session where event like 'enq%';

SID EVENT
---------- ----------------------------------------------------------------
94 enq: TX - row lock contention

=> let´s find the name of the locked table AND the row number of the locked row causing Blocking :

SYS:SQL>select o.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
2 dbms_rowid.rowid_create ( 1, o.DATA_OBJECT_ID, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
3 from v$session s, dba_objects o where sid=&waiting_sid and s.ROW_WAIT_OBJ# = o.OBJECT_ID ;
Informe o valor para waiting_sid: 94
antigo 3: from v$session s, dba_objects o where sid=&waiting_sid and s.ROW_WAIT_OBJ# = o.OBJECT_ID
novo 3: from v$session s, dba_objects o where sid=94 and s.ROW_WAIT_OBJ# = o.OBJECT_ID

OBJECT_NAME
--------------------------------------------------------------------------------
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
------------- -------------- --------------- ------------- ------------------
DEPT
20063 4 333 2 AAAE5fAAEAAAAFNAAC

SYS:SQL>select * from scott.dept where rowid='AAAE5fAAEAAAAFNAAC';

DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO


SYS:SQL>

b) the lock information for each row is mainteined in the data block, so we could ask for a block dump to see it : the procedure would be to find the block querying V$LOCK and alike views, and then ask for a dump in the blocks, see the Documentation for V$LOCK/V$TRANSACTION refs, and https://jonathanlewis.wordpress.com/2009/05/21/row-directory/ , http://www.oaktable.net/category/tags/itl-quotinterested-transaction-listquot-locking-oracle-lock-locks-quotdumping-blockquo , http://wayne-lang.blogspot.com.br/2009/12/oracle-row-level-locking.html and https://richardfoote.wordpress.com/category/block-dumps/ for block dump refs/examples....

Regards,

J. Laurindo Chiappa