Locking SQL

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 it 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 (mode 3), the row is updated but not committed (hence still locked) – 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 (using 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:

• Part 1
• Part 2
• Part 3
• Part 4
• Part 5

And there’s more (May 2009):

• Part 6 – you could start here as it lists parts 1 to 5
• Part 7
• Part 8
• Part 9
• The end ?

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.