June 3, 2010
I came across an interesting SQL statement that is described as identifying blocking transactions:
select s1.username blkg_user, s1.machine blkg_ws, s1.sid blkg_sid, s2.username wait_user, s2.machine wait_ws, s2.sid wait_sid, lo.object_id blkd_obj_id, do.owner, do.object_name from v$lock l1, v$session s1, v$lock l2, v$session s2, v$locked_object lo, dba_objects do where s1.sid = l1.sid and s2.sid = l2.sid and l1.id1 = l2.id1 and s1.sid = lo.session_id and lo.object_id = do.object_id and l1.block = 1 and l2.request > 0;
The SQL statement is a bit different from the one that I typically use for determining enqueues. The documentation also includes a SQL statement for determining enqueues.
What, if anything, is wrong with the above SQL statement? If you need a test case, try the one found in this article. I suspect that there may be more than one answer.
Hi Charles, well the query isn’t RAC aware and doesn’t understand the serial# column in v$session either. The dba_waiters and dbs_blockers views seem to have passed the author by as well though there maybe good reason for this.
Niall,
It is interesting that you mention that the above SQL statement is not RAC aware – that is something that I had not considered at the time that I put this blog article together (that is one of the benefits of giving people the opportunity to add comments to articles – I might have thought about it some time in the past though). I recall reading a forum post some time ago where someone mentioned that the BLOCK column of V$LOCK was showing the number 2 rather than 1. I believe that it was Mark Bobak who mentioned that a 2 may appear in the BLOCK column in a RAC environment, even though the documentation suggests that only 0 or 1 may appear in that column. I could not find that particular forum thread response, but I did find this one that was posted by Yong Huang:
http://www.freelists.org/post/oracle-l/vlock-Blocker,2
I suspect that there is another very good reason why you state that the SQL statement is not RAC aware – something about a missing letter in the view name.
—
To all readers:
What else is wrong with the SQL statement? Another hint?
http://www.mi-oaug.org/Presentations/Understanding%20Locks%20and%20Enqueues.ppt
http://www.perfvision.com/papers/09_enqueues.ppt
>a 2 may appear in the BLOCK column in a RAC environment, even though the documentation suggests that only 0 or 1 may appear in that column
AFAIK it is almost always 2 in RAC
Timur,
Excellent. Now the question that I have for you is what happens when one session blocks another session. Do you see one of the BLOCK=2 rows become a BLOCK=1 row? For instance, if you try the test case found in this blog article:
https://hoopercharles.wordpress.com/2010/01/07/deadlock-on-oracle-11g-but-not-on-10g/
What do you see when both sessions 2 and 3 hang? Do you see something similar to my results that I posted a couple of days ago, further down this page?
>Now the question that I have for you is what happens when one session blocks another session. Do you see one of the BLOCK=2 rows become a BLOCK=1 row?
Yes – in case of sessions are on the same instance. It remains 2 when sessions are located on different nodes.
Timur,
Thank you for performing the test on a RAC configuration.
I don’t have any environment to test it now. But shouldn’t there be an extra join condition for the object_id between v$lock and v$locked_object?
I think that your answer is getting closer to one of the issues that I thought might happen, and the test case for this article did show a problem that indicated a missing join condition.
Unfortunately, there is no OBJECT_ID column in V$LOCK. For some types of enqueue entries that appear in V$LOCK, the OBJECT_ID appears in the ID1 column, but that is not consistent across all enqueue types.
Another clue, this is from Oracle Database 10.2.0.2 when sessions 2 and 3 lock up while executing the test script for this article:
From the above, sessions 2 and 3 are enqueing with a TM type enqueue, with both pointing at whatever is object ID 114337. V$SESSION.ROW_WAIT_OBJ# shows -1, but we are able to determine the object ID from the value of P2.
This is the second checking SQL statement, executed at the same point in the test case:
The above output shows that session 211 (session 1) is blocking sessions 2 and 3 – we are able to determine that because ID1, ID2, and TYPE all match.
Now, the SQL statement that appears at the start of this blog article:
Object ID 114337 appearing in the output is expected – my script that targeted V$SESSION_WAIT showed that object ID also, but why is OBJECT ID 114335 in the list?
Note that the above output could be a bit different with Oracle Database 10.2.0.4, and will be different for Oracle Database 11.1.0.6 or higher.
I might be nitpicking here, but shouldn’t “do.object_id” actually be “do.data_object_id”?
Cheers,
Flado
Good question. If you see something wrong, please feel free to point out the problem, even if it is nitpicking – this blog article is asking what is wrong with this SQL statement. Minor problems repeated frequently enough can become bigger problems at a later time.
I found a couple of interesting articles on Metalink related to locks:
Metalink Doc ID 202390.1 “Payables Invoice Processing Troubleshooting Guide” uses object_id
Metalink Doc ID 478027.1 “How to Troubleshoot Hangs on Unix” uses object_id
Metalink Doc ID 117316.1 “ORA-0054: When Dropping or Truncating Table, When Creating or Rebuilding Index” uses object_id
Metalink Doc ID 821691.1 “ORA-00054: resource busy and acquire with NOWAIT error while running Planning ODS Load” uses object_id
Metalink Doc ID 748144.1 “How To Troubleshoot And Resolve Service Request And Task Locking Issues Within CRM” uses object_id
Metalink Doc ID 198150.1 “How To Indentify The Row Which is Locked By an Other User’s Session” uses object_id – interesting, states “ROW_WAIT_ROW# in v$sesssion will only be populated when there is a deadlock
situation occurring.” – I wonder if I have a counter-example?
Metalink Doc ID 390207.1 “Unexpected Behaviour Of Lock Table And Fine Grained Access Control” uses object_id
Metalink Doc ID 15476.1 “FAQ about Detecting and Resolving Locking Conflicts” – does not show how to join the views, but an interesting document
Metalink Doc ID 398519.1 “Detecting Blocking Sessions in RAC and non-RAC (Enqueue Locks) Environments” – BLOCK “value is always 2 in RAC environments unless there is local conflict” – joins ID1 of V$LOCK to DBA_OBJECTS.OBJECT_ID
Metalink Doc ID 102925.1 “Tracing sessions: waiting on an enqueue” – “For the lock type TM (table lock), column ID1 is the object_id (or the data_object_id) for that table (could be joined with DBA_OBJECTS).” – seems to be unclear which column.
Metalink Doc ID 62354.1 “TX Transaction locks – Example wait scenarios” – avoids the question.
Metalink Doc ID 43724.1 “VIEW: ‘V$LOCKED_OBJECT’ Reference Note” – “This view lists all locks acquired by every transaction on the system.” – “OBJECT_ID – Object ID being locked”
It looks like the only option to join these two views is OBJECT_ID. Now maybe someone can help explain what kinds of problems that might cause.
Definitely OBJECT_ID. This
outputs this (in SQL Developer):
The version is 10.2.0.3, although it shouldn’t matter much.
ROW_WAIT_ROW# definitely gets populated for normal lock waits – I’ve been using scripts depending on it (to construct a ROWID and select the row, of course) for ages and haven’t noticed any wrong or missing results so far. Got to check what does ROW_WAIT_OBJ# contain, though: DBMS_ROWID.rowid_create() wants – and DBMS_ROWID.rowid_object() returns – a DATA_OBJECT_ID.
Thanks!
Flado
I did not know if anyone would catch that Metalink quote – more than one Metalink article repeated that mistake regarding ROW_WAIT_ROW#. Someone should file a bug report with Metalink. 🙂
My test case showing that ROW_WAIT_ROW# is populated even when there is no deadlock, and in some cases when ROW_WAIT_OBJ# is shown as -1, may be found in this blog article:
https://hoopercharles.wordpress.com/2009/12/06/enqueue-experimentations/