如何找出引起enq:TX - row lock contention的記錄

V$SESSION中有如下4個列，用來記錄當發生enq:TX-row lock contention的時候， 導緻挂起的行。

 ROW_WAIT_OBJ#                                      NUMBER  --包含該記錄的OBJECT_ID  ROW_WAIT_FILE#                                     NUMBER  --該記錄所在的相對檔案号  ROW_WAIT_BLOCK#                                    NUMBER  --該記錄所在的BLOCK号  ROW_WAIT_ROW#                                      NUMBER  --該記錄所在塊中的行号

大部分人采用如下的SQL來查詢引起堵塞的行：

select c.OBJECT_NAME,a.ROW_WAIT_OBJ#,a.ROW_WAIT_FILE#,a.ROW_WAIT_BLOCK#,a.ROW_WAIT_ROW#, dbms_rowid.rowid_create(1,c.OBJECT_ID,a.ROW_WAIT_FILE#,a.ROW_WAIT_BLOCK#,a.ROW_WAIT_ROW#) rid  from v$session a , v$enqueue_lock b, dba_objects c where a.sid=b.SID and b.type='TX' and a.ROW_WAIT_OBJ#=object_id(+)

而且一般情況下是沒問題的。 如下：

SESSION 1：

SQL> select rowid from test where rownum=1;

ROWID ------------------ AAp/YEAAFAACmXkAAA

SQL> delete from test where rownum=1;

1 row deleted.

SQL> 

SESSION 2：

SQL> update test set object_name=object_name where rownum=1;

SESSION 2将會被HANG住。

通過另外的SESSION執行如下的查詢可以看到引起堵塞的記錄是 TEST表的ROWID=‘AAp/YEAAFAACmXkAAA’的記錄。

SQL> select c.OBJECT_NAME,a.ROW_WAIT_OBJ#,a.ROW_WAIT_FILE#,a.ROW_WAIT_BLOCK#,a.ROW_WAIT_ROW#,   2  dbms_rowid.rowid_create(1,c.OBJECT_ID,a.ROW_WAIT_FILE#,a.ROW_WAIT_BLOCK#,a.ROW_WAIT_ROW#) rid    3  from v$session a , v$enqueue_lock b, dba_objects c   4  where a.sid=b.SID and b.type='TX' and a.ROW_WAIT_OBJ#=object_id(+);

OBJECT_NAM ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# RID ---------- ------------- -------------- --------------- ------------- ------------------------------------ TEST            11007492              5          681444             0 AAp/YEAAFAACmXkAAA

SQL> select rowid,object_id from scott.test where rowid='AAp/YEAAFAACmXkAAA';

ROWID               OBJECT_ID ------------------ ---------- AAp/YEAAFAACmXkAAA        258

SQL> select rowid,object_id from scott.test where rowid='AAp/YEAAFAACmXkAAA' for update skip locked;

no rows selected

但是這是在TEST表的OBJECT_ID和DATA_OBJECT_ID一緻的情況下才行。

SQL> select object_id,data_object_id from user_objects where object_name='TEST';

 OBJECT_ID DATA_OBJECT_ID ---------- --------------   11007492       11007492      一旦OBJECT_ID和DATA_OBJECT_ID不一樣，那麼查詢結果将是錯誤的。      因為V$SESSION.ROW_WAIT_OBJ#記錄的是對象的OBJECT_ID   而DBMS_ROWID.CREATE_ROWID需要的是對象的DATA_OBJECT_ID     如下:    SESSION 1:   SQL> alter table test move;

Table altered.

SQL> select object_id,data_object_id from user_objects where object_name='TEST';

 OBJECT_ID DATA_OBJECT_ID ---------- --------------   11007492       11007579

SQL> select rowid from test where rownum=1;

ROWID ------------------ AAp/ZbAAFAACmjsAAA

SQL> delete from test where rownum=1;

1 row deleted.

SQL> 

SESSION 2:

SQL> update test set object_name=object_name where rownum=1;

将會HANG住。

SESSION 3:

SQL> select c.OBJECT_NAME,a.ROW_WAIT_OBJ#,a.ROW_WAIT_FILE#,a.ROW_WAIT_BLOCK#,a.ROW_WAIT_ROW#,   2  dbms_rowid.rowid_create(1,c.OBJECT_ID,a.ROW_WAIT_FILE#,a.ROW_WAIT_BLOCK#,a.ROW_WAIT_ROW#) rid    3  from v$session a , v$enqueue_lock b, dba_objects c   4  where a.sid=b.SID and b.type='TX' and a.ROW_WAIT_OBJ#=object_id(+)   5  /

OBJECT_NAM ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# RID ---------- ------------- -------------- --------------- ------------- ------------------------------------ TEST            11007492              5          682220             0 AAp/YEAAFAACmjsAAA

SQL> SELECT rowid from scott.test where rowid='AAp/YEAAFAACmjsAAA'; SELECT rowid from scott.test where rowid='AAp/YEAAFAACmjsAAA'                         * ERROR at line 1: ORA-01410: invalid ROWID

ROWID_CREATE中的C.ROW_WAIT_OBJ#需要改為DATA_OBJECT_ID才行。

SQL> select c.OBJECT_NAME,a.ROW_WAIT_OBJ#,a.ROW_WAIT_FILE#,a.ROW_WAIT_BLOCK#,a.ROW_WAIT_ROW#,   2  dbms_rowid.rowid_create(1,c.DATA_OBJECT_ID,a.ROW_WAIT_FILE#,a.ROW_WAIT_BLOCK#,a.ROW_WAIT_ROW#) rid    3  from v$session a , v$enqueue_lock b, dba_objects c   4  where a.sid=b.SID and b.type='TX' and a.ROW_WAIT_OBJ#=object_id(+);

OBJECT_NAM ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# RID ---------- ------------- -------------- --------------- ------------- ------------------------------------ TEST            11007492              5          682220             0 AAp/ZbAAFAACmjsAAA

SQL> SELECT rowid from scott.test where rowid='AAp/ZbAAFAACmjsAAA';

ROWID ------------------ AAp/ZbAAFAACmjsAAA