如何找出引起enq:TX-rowlockcontention的记录V$SESSION中有如下4个列，用来记录当发生enq:TX-rowlockcontention的时候，导致挂起的行。www.2cto.comROW_WAIT_OBJ#...SyntaxHighlighter.al
如何找出引起enq:TX - row lock contention的记录
V$SESSION中有如下4个列，用来记录当发生enq:TX-row lock contention的时候，
导致挂起的行。 www.2cto.com
ROW_WAIT_OBJ# NUMBER --包含该记录的OBJECT_ID
ROW_WAIT_FILE# NUMBER --该记录所在的相对文件号
ROW_WAIT_BLOCK# NUMBER --该记录所在的BLOCK号
ROW_WAIT_ROW# NUMBER --该记录所在块中的行号
www.2cto.com
大部分人采用如下的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=&＃39;TX&＃39; 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=&＃39;TX&＃39; 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=&＃39;AAp/YEAAFAACmXkAAA&＃39;;
ROWID OBJECT_ID
------------------ ----------
AAp/YEAAFAACmXkAAA 258
SQL> select rowid,object_id from scott.test where rowid=&＃39;AAp/YEAAFAACmXkAAA&＃39; 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=&＃39;TEST&＃39;;
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=&＃39;TEST&＃39;;
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=&＃39;TX&＃39; 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=&＃39;AAp/YEAAFAACmjsAAA&＃39;;
SELECT rowid from scott.test where rowid=&＃39;AAp/YEAAFAACmjsAAA&＃39;
*
ERROR at line 1:
ORA-01410: invalid ROWID
ROWID_CREATE中的C.ROW_WAIT_OBJ#需要改为DATA_OBJECT_ID才行。
www.2cto.com
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=&＃39;TX&＃39; 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=&＃39;AAp/ZbAAFAACmjsAAA&＃39;;
ROWID
------------------
AAp/ZbAAFAACmjsAAA
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有