Oracle Scratchpad

April 19, 2009

Locking SQL

Filed under: Locks,Oracle,Troubleshooting — Jonathan Lewis @ 6:11 pm BST Apr 19,2009

A question that comes up fairly regularly on OTN Database Forum and the newsgroup is one that asks some variant of:

“When a session has some rows locked in a table, how can I find the statement that locked those rows ?”

“I have several sessions waiting for a TX lock, and have the SID of the blocking session, how do I find the blocking SQL ?”

There are various approaches you can take to solving this problem – for example, you may be able to check v$open_cursor for the session and see something that looks like a suitable candidate – but whatever approach you take is at best intelligent guesswork, and it’s liable to error.

There are several possible associations between statements and sessions, sessions and locks, statement and locks, sessions and undo, and so on that might give you a reasonable clue if you happen to check them at the right moment; but there is no direct record of the link between a locked row and the SQL statement that locked it.

Here’s a little cut-n-paste from an SQL*PLus session to give you an idea of how easily you can be fooled if you try to track back from locked rows to an SQL statement:

SQL> select * from t1


1 row selected.

SQL> update t1 set n1 = 2;

1 row updated.

SQL> select sql_id, sql_text from V$sql where sql_text = 'update t1 set n1 = 2';

------------- ----------------------------------------
3ucn6r75k18nr update t1 set n1 = 2

1 row selected.

SQL> alter system flush shared_pool;

System altered.

SQL> select sql_id, sql_text from V$sql where sql_text = 'update t1 set n1 = 2';

no rows selected

SQL> select * from t1;


1 row selected.

SQL> rollback;

Rollback complete.

SQL> select * from t1;


1 row selected.

I start by updating the single row in the table – then force the update statement out of the library cache. The table is still locked, the row is updated but not committed – I did the final rollback just to make that point – there simply is no link between the locked table (let alone row) and the statement that did the locking.

SQL statements can disappear from memory remarkably quickly, and a locked table is no guarantee that the statement that did the locking is still in memory. It’s easy to see the statement that is waiting on “enq: TX – row lock contention”, it’s easy to find a session that it’s waiting behind (v$lock), as a general guideline it may not be possible to find out exactly what statement is the cause of the conflicting lock.


For an interesting read on locking and ASH (active session history), Doug Burns has a short series of investigative articles on his blog:

And there’s more (May 2009):

Update (Oct 2015)

Under my “rules of trust” – anything more than a few years old should be considered suspect until confirmed. The question has just appeared again on OTN – 21st Oct 2015 – and when I followed the chain of responses it brought me here, which prompted me to point out that this note is still valid.


  1. […] that there is no way of knowing the SQL statement that was holding a particular lock. I see that Jonathan Lewis has posted an illustration of this although let’s wait to see if Tanel Poder comes up with something I decided at this point to draw […]

    Pingback by Doug's Oracle Blog — April 20, 2009 @ 8:20 pm BST Apr 20,2009 | Reply

  2. […] für die Blockade sein. Zwar wird dies sicher häufig der Fall sein, aber wie Jonathan Lewis in seinem Blog beschreibt, lässt sich ein für ein Lock ursächliches SQL nicht mit endgültiger Sicherheit […]

    Pingback by Diagnose von Locks in Oracle-Datenbanken « Oraculix — April 22, 2009 @ 4:24 pm BST Apr 22,2009 | Reply

  3. Can we use flashback_transaction_query (undo_sql) to find out locking statement?

    Comment by Alexey — April 28, 2009 @ 9:50 am BST Apr 28,2009 | Reply

    • Alexey,
      No – there are various helpful details that you can get from log miner, flashback etc. but as far as statements are concerned, there is never any guarantee that you will find the thing that really drove the update.

      Comment by Jonathan Lewis — May 2, 2009 @ 11:07 am BST May 2,2009 | Reply

  4. Thank you for the good article, however assuming that the statement is still in the cache, how can we find it?

    Comment by Dmitry — December 17, 2013 @ 3:39 pm BST Dec 17,2013 | Reply

    • Dmitry,

      Without going back and checking I can’t be sure, but I think that Doug probably covers the options in hist series of articles.

      In outline: from v$lock you can get the blocking SID, and then check the serial# from v$session.

      You can query v$open_cursor to see if it holds any SQL for that sid that looks as if it could be the locking SQL, and you may be lucky enough to find some likely candidates.

      If v$open_cursor doesn’t give you anything it’s possible that you may find the combination of session and serial number in v$active_session_history (if you are licensed to use it) reporting SQL_IDs for the session – if you do find some SQL_IDs that weren’t in v$open_cursor then you can check to see if they’re still in v$sql (or v$sqlstats) and again look for likely candidates.

      Comment by Jonathan Lewis — December 17, 2013 @ 4:19 pm BST Dec 17,2013 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by