Lock Watching – What is Wrong with this SQL Statement?

3 06 2010

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.

« »


Actions

Information

13 responses

3 06 2010
niall litchfield (13:00:50) :

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.

Reply
3 06 2010
Charles Hooper (14:47:37) :

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

Reply
7 06 2010
Timur Akhmadeev (02:24:10) :

>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

SQL> select block, count(*) from v$lock group by block;
 
     BLOCK   COUNT(*)
---------- ----------
         2       2189
         1          1
         0         12
Reply
7 06 2010
Charles Hooper (05:20:43) :

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?

Reply
7 06 2010
Timur Akhmadeev (06:23:51) :

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

Reply
7 06 2010
Charles Hooper (07:45:10) :

Timur,

Thank you for performing the test on a RAC configuration.

Reply
4 06 2010
Arian (13:38:59) :

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?

Reply
4 06 2010
Charles Hooper (15:26:51) :

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.

SQL> DESC V$LOCK
 
 Name      Null?    Type
 --------- -------- -----------
 ADDR               RAW(4)
 KADDR              RAW(4)
 SID                NUMBER
 TYPE               VARCHAR2(2)
 ID1                NUMBER
 ID2                NUMBER
 LMODE              NUMBER
 REQUEST            NUMBER
 CTIME              NUMBER
 BLOCK              NUMBER
 
SQL> DESC V$LOCKED_OBJECT
 
 Name             Null?    Type
 ---------------- -------- -----------
 XIDUSN                    NUMBER
 XIDSLOT                   NUMBER
 XIDSQN                    NUMBER
 OBJECT_ID                 NUMBER
 SESSION_ID                NUMBER
 ORACLE_USERNAME           VARCHAR2(30)
 OS_USER_NAME              VARCHAR2(30)
 PROCESS                   VARCHAR2(24)
 LOCKED_MODE               NUMBER

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.

Reply
4 06 2010
Charles Hooper (21:38:32) :

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:

SQL> SELECT /*+ ORDERED */
  2  S.SID, S.USERNAME, S.PROGRAM, S.STATUS, SW.EVENT, SW.WAIT_TIME WT, SW.STATE,
  3  SW.SECONDS_IN_WAIT S_I_W, S.SQL_ID, S.SQL_ADDRESS, S.SQL_HASH_VALUE,
  4  S.SQL_CHILD_NUMBER, S.ROW_WAIT_OBJ# OBJ#, S.ROW_WAIT_FILE# FILE#,
  5  S.ROW_WAIT_BLOCK# BLOCK#, S.ROW_WAIT_ROW# ROW#, SW.P1, SW.P2, SW.P3
  6  FROM
  7  V$SESSION_WAIT SW, V$SESSION S
  8  WHERE
  9  S.USERNAME IS NOT NULL
 10  AND SW.SID=S.SID
 11  AND SW.EVENT NOT LIKE '%SQL*Net%'
 12  AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue',
 13  'wait for unread message on broadcast channel');
  
SID USERNAME PROGRAM     STATUS   EVENT                WT STATE   S_I_W SQL_ID        SQL_ADDRESS      SQL_HASH_VALUE SQL_CHILD_NUMBER OBJ# FILE# BLOCK# ROW#         P1     P2 P3
--- -------- ----------- -------- -------------------- -- ------- ----- ------------- ---------------- -------------- ---------------- ---- ----- ------ ---- ---------- ------ --
214 TESTUSER sqlplus.exe ACTIVE   enq: TM - contention  0 WAITING   213 dxbtvfguaa0xq 000007FFA479BDE0     4104455094                0   -1     0      0    0 1414332421 114337  0
217 TESTUSER sqlplus.exe ACTIVE   enq: TM - contention  0 WAITING    78 873s5kmm4fkfa 000007FFA5182400     3863431626                0   -1     0      0    0 1414332418 114337  0

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:

SQL> SELECT
  2  S.SID, S.USERNAME, S.PROGRAM, S.SQL_ID, S.SQL_ADDRESS, S.SQL_HASH_VALUE,
  3  S.SQL_CHILD_NUMBER CN, S.ROW_WAIT_OBJ#, S.ROW_WAIT_FILE#, S.ROW_WAIT_BLOCK#,
  4  S.ROW_WAIT_ROW#, L.LMODE, L.REQUEST, L.ID1, L.ID2, L.TYPE, L.BLOCK
  5  FROM
  6  V$LOCK L,
  7  V$SESSION S
  8  WHERE
  9  (L.ID1, L.ID2, L.TYPE) IN
 10  (SELECT
 11  ID1, ID2, TYPE
 12  FROM
 13  V$LOCK
 14  WHERE
 15  REQUEST > 0)
 16  AND L.SID=S.SID;
 
SID USERNAME PROGRAM     SQL_ID        SQL_ADDRESS      SQL_HASH_VALUE CN ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LMODE REQUEST    ID1 ID2 TY BLOCK
--- -------- ----------- ------------- ---------------- -------------- -- ------------- -------------- --------------- ------------- ----- ------- ------ --- -- -----
211 TESTUSER sqlplus.exe 8sx6uby71yy6x 000007FFA3E7B490     2384427229  0           107              1             810             0     3       0 114337   0 TM     1
214 TESTUSER sqlplus.exe dxbtvfguaa0xq 000007FFA479BDE0     4104455094  0            -1              0               0             0     3       5 114337   0 TM     0
217 TESTUSER sqlplus.exe 873s5kmm4fkfa 000007FFA5182400     3863431626  0            -1              0               0             0     0       2 114337   0 TM     0

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:

SQL> select s1.username blkg_user, s1.machine blkg_ws, s1.sid blkg_sid,
  2         s2.username wait_user, s2.machine wait_ws, s2.sid wait_sid,
  3         lo.object_id blkd_obj_id, do.owner, do.object_name
  4  from v$lock l1, v$session s1, v$lock l2, v$session s2,
  5       v$locked_object lo, dba_objects do
  6  where s1.sid = l1.sid
  7    and s2.sid = l2.sid
  8    and l1.id1 = l2.id1
  9    and s1.sid = lo.session_id
 10    and lo.object_id = do.object_id
 11    and l1.block = 1
 12    and l2.request > 0
 13  ;
 
BLKG_USER BLKG_WS         BLKG_SID WAIT_USER WAIT_WS         WAIT_SID BLKD_OBJ_ID OWNER    OBJECT_NAME
--------- --------------- -------- --------- --------------- -------- ----------- -------- -----------
TESTUSER  K-MM\AIRFORCE-1      211 TESTUSER  K-MM\AIRFORCE-1      217      114335 TESTUSER T5
TESTUSER  K-MM\AIRFORCE-1      211 TESTUSER  K-MM\AIRFORCE-1      214      114335 TESTUSER T5
TESTUSER  K-MM\AIRFORCE-1      211 TESTUSER  K-MM\AIRFORCE-1      217      114337 TESTUSER T6
TESTUSER  K-MM\AIRFORCE-1      211 TESTUSER  K-MM\AIRFORCE-1      214      114337 TESTUSER T6

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.

Reply
11 06 2010
Flado (06:40:16) :

I might be nitpicking here, but shouldn’t “do.object_id” actually be “do.data_object_id”?

Cheers,
Flado

Reply
11 06 2010
Charles Hooper (07:38:26) :

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”

SQL> DESC DBA_OBJECTS
 Name
 ------------------------
 OWNER
 OBJECT_NAME
 SUBOBJECT_NAME
 OBJECT_ID
 DATA_OBJECT_ID
 OBJECT_TYPE
 CREATED
 LAST_DDL_TIME
 TIMESTAMP
 STATUS
 TEMPORARY
 GENERATED
 SECONDARY

SQL> DESC V$LOCKED_OBJECT
 Name
 ------------------------
 XIDUSN
 XIDSLOT
 XIDSQN
 OBJECT_ID
 SESSION_ID
 ORACLE_USERNAME
 OS_USER_NAME
 PROCESS
 LOCKED_MODE

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.

Reply
11 06 2010
Flado (09:12:49) :

Definitely OBJECT_ID. This

drop table t;
create table t (id) 
partition by list (id)
(partition tid1 values(1), partition tjunk values (default))
as select 1 from dual;
alter table t move partition tid1;
select id from t where id=1 for update;
with lo as (select /*+ materialize */ object_id from v$locked_object)
select do.object_name, do.object_type, do.object_id, do.data_object_id, lo.object_id
from dba_objects do, lo
where do.object_id=lo.object_id
   or do.data_object_id=lo.object_id;

outputs this (in SQL Developer):

drop table t succeeded.
create table succeeded.
alter table t succeeded.
ID                     
------- 
1       

1 rows selected

OBJECT_NAME        OBJECT_TYPE         OBJECT_ID              DATA_OBJECT_ID         OBJECT_ID              
------------------ ------------------- ---------------------- ---------------------- ---------------------- 
T                  TABLE PARTITION     168031                 168033                 168031                 
T                  TABLE               168030                                        168030                 

2 rows selected

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

Reply
11 06 2010
Charles Hooper (09:31:34) :

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/

Reply

Leave a reply to Charles Hooper Cancel reply