Oracle Scratchpad

April 19, 2009

Locking SQL

Filed under: Locks,Oracle,Troubleshooting — Jonathan Lewis @ 6:11 pm GMT 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):

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.

12 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT Dec 17,2013 | Reply

  5. Hi Jonathan,

    Recently I came across a scenario where an adhoc user process (a procedure call, run from SQL developer) was causing many sessions to wait on enq TX – Row Lock Contention. Most of the waiting sessions were running a simple update on a table. I tried to look for some clues what statement that session could have run that caused this but was not successful. One place where i looked was ASH samples but couldn’t find anything. Maybe that SQL was not sampled. User stopped his process and the locking was cleared. He ran it again for very few records and we traced that session. I looked at all the SQLs that were run but couldn’t find anything that could have caused enq TX – row lock contention. There was no DML being done on that table. No For updates. There was only one SELECT. You think there could be something that will not appear in the trace file but can still cause such a scenario ?

    Thank you !

    Comment by Amardeep Sidhu — November 5, 2018 @ 11:03 am GMT Nov 5,2018 | Reply

    • Amardeep,

      I don’t know of any reason why a simple select should cause any locking problems.

      You could try dumping the session state for a couple of the blocked sessions to see if that gives you any clues. You can, at least, see which table the blocked sessions are trying to modify so that gives you a pointer to what the block might be doing.

      As far as not seeing anything in a trace – I have come across a couple of cases in the past where Oracle packages cunningly disable tracing as their first step then re-enable it as the last: maybe something like this is hiding the cause of the blocking. I have also seen a couple of oddities in the dim and distant past where a data lock (TX or TM) is waiting on some sort of library cache lock or row cache latch. I haven’t seen one for a very long time, but that sort of thing has te potential to show up as a TX enqueue waiting on a select statement.

      Comment by Jonathan Lewis — November 5, 2018 @ 7:11 pm GMT Nov 5,2018 | Reply

      • Thank you for the response.

        >> You can, at least, see which table the blocked sessions are trying to modify so that gives you a pointer to what the block might be doing.

        This part was clear I think. All the waiting sessions were running an update on a table.

        >>As far as not seeing anything in a trace – I have come across a couple of cases in the past where Oracle packages cunningly disable tracing as their first step then re-enable it as the last: maybe something like this is hiding the cause of the blocking.

        You mean nothing at all appears in the trace ? That was not the case here. There were a lot of SQLs but none seemed like a possible candidate that could have caused the block.

        Initially i was wondering if it was something related to triggers but then if there were any triggers that executed any DML on the table, that should have been captured in the trace.

        Thank you.

        Comment by Amardeep Sidhu — November 6, 2018 @ 11:20 am GMT Nov 6,2018 | Reply

        • Amardeep,

          This part was clear I think. All the waiting sessions were running an update on a table.
          But did you also check that the blocking session was holding a TM lock on the same table, and of what type? And if you couldn’t see one did you check whether the table had table locks disabled (that can have strange side effects https://jonathanlewis.wordpress.com/2010/02/15/lock-horror/ ) You didn’t mention v$lock in your initial note.

          You mean nothing at all appears in the trace ?
          No, I’ve seen Oracle disable tracing as the first step of a procedure then re-enable it as the last step.

          if there were any triggers that executed any DML on the table, that should have been captured in the trace
          Technically that may not be true – for example (and this is probably not relevant to your case, but I mention it as one of those cases that no-one thinks about when they’re trying to track down an unexpected workload) the triggers that fire to maintain a materialized view log will execute SQL that will not appear in the trace file.

          Comment by Jonathan Lewis — November 6, 2018 @ 3:23 pm GMT Nov 6,2018

        • Thanks Jonathan.

          Unfortunately I missed checking the lock type and v$lock details. Since the system was almost stuck with hundreds of sessions waiting for the blocker; everyone around wanted to get rid of that session and get the system running.

          Comment by Amardeep Sidhu — November 14, 2018 @ 6:50 am GMT Nov 14,2018


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:

WordPress.com Logo

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