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: January 22, 2025 - 1:48 am UTC

Version: Oracle 12c

Viewed 50K+ 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 Connor 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

  (7 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

Diagnosing enq row lock contention

Oj, January 08, 2025 - 8:21 am UTC

If i lock a row with select t where pk=:1 for update wait 30 and i get a resource busy exception, is there a way in the exception handler i can diagnose which session has that locked row?

Now you stated oracle does not keep record of locked rows, but in this case i know exactly the row that is locked, but i want to find (in the exception handler) as much information as possible who/what session has it locked (of course assuming by the time i get to the exception handler, its still locked after 30 seconds wait)
Connor McDonald
January 13, 2025 - 4:24 am UTC

Not directly, but you could do something like this:

select ...
from v$active_session_history
where session_id = sys_context('USERENV','SID')
and session_serial# = 
  ( select serial# from v$session where sid = sys_context('USERENV','SID') )
and event = 'enq: TX - row lock contention';
and sample_time > sysdate - n/86400


where 'n' is the number of seconds of interest.

That gives you things like

BLOCKING_SESSION_STATUS
BLOCKING_SESSION
BLOCKING_SESSION_SERIAL#
BLOCKING_INST_ID
BLOCKING_HANGCHAIN_INFO

Diagnosing enq row lock contention

Oj, January 14, 2025 - 10:25 am UTC

Thanks Connor, thats given us a good pointer to the blocking session.

To delve deeper, i think i need to look at v$open_cursor for that blocking session, it looks like i can do this;

Select *
From v$open_cursor oc
Where sid = :blocking_session

...but i have a suspicion i need a few more predicates, like on SADDR maybe? But i dont see a blocking saddr on v$active_session_history?
Connor McDonald
January 16, 2025 - 7:15 am UTC

You could only need SID but the question is ... does it really matter?

If a session is blocking you, they might be active, they might be idle, they might have run that statement an hour ago...

All that matters is - they are blocking.

If they are active, then fine - they are doing work. If they are idle (for a long time) then perhaps its time to look at killing them off.

A nice coverage of the options here



Diagnosing enq row lock contention

Oj, January 16, 2025 - 4:17 pm UTC

Thanks Connor. It matters in the sense that once we find the blocking session, we want to know what they were doing to have caused the block, what sql statement they were running. Once we get the statement, we will hunt for its origin in the app and then hopefully we can work out the circumstances when it causes blocking intermittently.

In the exception handler, im trying to grab all the info i can get for the blocking session, regardless of whether its active or inactive, and also grab all the open cursors info for that blocking session

It looks like based on the BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#, i can join back to v$session to get the SADDR ? And if so, i can then get to v$open_cursors using sid + saadr?

Although you did say i can only use SID - but i thought that might pick up rows belonging to other serial#? Maybe my understanding is lacking on what a unique session is and/or the link between sessions and open cursors...


Connor McDonald
January 22, 2025 - 1:46 am UTC

The serial# would bump up if it was a new session, but if it was a new session than any locks it had will be gone.

Find ROWID-s for blocked/waiting sessions

Iudith Mentzel, January 17, 2025 - 4:50 pm UTC

Hello All,

If you are interested to see the ROWID-s for ONLY the rows that some session is waiting to be released by a blocking session,
then you can use the following columns in V$SESSION:

ROW_WAIT_OBJ#
ROW_WAIT_FILE#
ROW_WAIT_BLOCK#
ROW_WAIT_ROW#

You can check the following

https://www.dbi-services.com/blog/oracle-locks-identifiying-blocking-sessions/

to see how you can use DBMS_ROWID.ROWID_CREATE for creating a ROWID using these columns.

Cheers & Best Regards,
Iudith Mentzel

Connor McDonald
January 22, 2025 - 1:48 am UTC

nice input