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 comp.databases.oracle.server 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

        N1
----------
         1

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';

SQL_ID        SQL_TEXT
------------- ----------------------------------------
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;

        N1
----------
         2

1 row selected.

SQL> rollback;

Rollback complete.

SQL> select * from t1;

        N1
----------
         1

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.

Footnote:

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):

7 Comments »

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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,528 other followers