等待事件enq TX row lock contention分析
通常情況下，Oracle資料庫的等待事件enq: TX - row lock contention會在下列三種情況下會出現。
第一種情況，是真正的業務邏輯上的行鎖衝突，如一條記錄被多個人同時修改。這種鎖對應的請求模式是6。
第二種情況，是唯一鍵衝突，如主鍵欄位相同的多條記錄同時插入。這種鎖對應的請求模式是4。這也是應用邏輯問題。
第三種情況，是bitmap索引的更新衝突，就是多個會話同時更新bitmap索引的同一個資料塊。此時會話請求鎖的對應的請求模式是4。
bitmap索引的物理結構和普通索引一樣，也是 B-tree 結構。但它儲存的資料記錄的邏輯結構為"key_value,start_rowid,end_rowid,bitmap"。
其內容類似這樣：
"‘8088’,00000000000,10000034441,1001000100001111000"
Bitmap是一個二進位制，表示 START_ROWID 到 END_ROWID 的記錄， 1 表示等於 key_value 即‘8088’的 ROWID 記錄， 0 則表示不是這個記錄。
在瞭解bitmap索引的結構之後，我們就能理解同時插入多條記錄到擁有bitmap索引的表時，就會同時更新bitmap索引中一個塊中的記錄，等於某一個記錄被同時更新，自然就會出現行鎖等待。插入併發量越大，等待越嚴重。
等待事件enq: TX - row lock contention中的enq是enquence的簡寫。enquence是協調訪問資料庫資源的內部鎖。
所有以“enq:”打頭的等待事件都表示這個會話正在等待另一個會話持有的內部鎖釋放，它的名稱格式是enq:enqueue_type - related_details。這裡的enqueue_type是TX，related_details是row lock contention。資料庫動態效能檢視v$event_name提供所有以“enq:”開頭的等待事件的列表。
雖然在awrrpt中看到大量enq: TX - row lock contention的等待，但這些是事後看到的資訊。根據AWRRPT，我們無法只能該等待事件的請求模式是什麼，是6還是4。
如果資料庫一出現enq: TX - row lock contention等待，可以去看v$session和v$session_wait等檢視。
在v$session和v$session_wait中，如果看到的event列是enq: TX - row lock contention的，就表示這個會話正處於行鎖等待。該等待事件的請求模式可以從v$session和v$session_wait的p1列中得到。
select sid,
chr(bitand(p1, -16777216) / 16777215) ||
chr(bitand(p1, 16711680) / 65535) "Name",
(bitand(p1, 65535)) "Mode"
from v$session_wait
where event like 'enq%';
透過這個SQL可以將p1轉換為易閱讀的文字。
針對這三種情況，分別進行測試：
首先，我準備一下測試表和資料。
--建立測試表和資料
SQL> create table t_all_objs as select owner,object_id,object_name from all_objects where 0=1;
Table created.
SQL> alter table T_ALL_OBJS add constraint pk_t_all_objs primary key (OBJECT_ID);
Table altered.
SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011701,'test1');
1 row created.
SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011702,'test2');
1 row created.
SQL> commit;
Commit complete.
第一種情況，不同會話同時更新同一條記錄
session1:
SQL> select sid from v$mystat where rownum<2;
SID
----------
46
SQL> select * from t_all_objs;
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST 2013011701 test1
TEST 2013011702 test2
SQL> update t_all_objs set object_name='test11' where object_id=2013011701;
1 row updated.
未commit
session 2:
SQL> select sid from v$mystat where rownum<2;
SID
----------
52
SQL> update t_all_objs set object_name='test101' where object_id=2013011701;
一直等待。。。。。。。。。
session 3：查詢
SQL> select sid,sql_id,blocking_session,event,p1text,p1,p2text,p2,p3text,p3,wait_class,state from v$session where sid in (46, 52);
SID SQL_ID BLOCKING_SESSION EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 WAIT_CLASS STATE
----- ------------- ---------------- ------------------------------ ---------- ---------- --------------- ---------- ---------- ---------- --------------- ----------
46 SQL*Net message from client driver id 1650815232 #bytes 1 0 Idle WAITING
52 c53uad8st2u8t 46 enq: TX - row lock contention name|mode 1415053318 usn<<16 | slot 65556 sequence 13548 Application WAITING
--得到sql_id值，查詢出SQL資訊
SQL> select s.sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = 'c53uad8st2u8t';
SQL_TEXT EXECUTIONS S.ELAPSED_TIME/1000000
---------------------------------------------------------------------- ---------- ----------------------
update t_all_objs set object_name='test101' where object_id=2013011701 0 501.178747
--再查詢鎖資訊：
SQL> select * from v$lock where sid in (46,52) order by sid, type;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
000000009398FE58 000000009398FEB0 46 AE 100 0 4 0 3706 0
00007FABEA622FC0 00007FABEA623020 46 TM 87875 0 3 0 597 0
0000000091E37248 0000000091E372C0 46 TX 65556 13548 6 0 597 1
000000009398F820 000000009398F878 52 AE 100 0 4 0 573 0
00007FABEA622FC0 00007FABEA623020 52 TM 87875 0 3 0 543 0
000000009398FBB8 000000009398FC10 52 TX 65556 13548 0 6 543 0
6 rows selected.
查詢得到未46的session最終阻塞了會話，是根源。
第二種情況，不同會話中同時插入主鍵欄位相同的記錄
session 1；
SQL> select sid from v$mystat where rownum=1;
SID
----------
43
SQL> select * from t_all_objs;
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST 2013011701 test1
TEST 2013011702 test2
SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test1');
1 row created.
未commit
session 2:
SQL> select sid from v$mystat where rownum=1;
SID
----------
55
SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test11');
一直等待。。。。。。。
session 3：查詢
SQL> select sid,sql_id,blocking_session,event,p1text,p1,p2text,p2,p3text,p3,wait_class,state from v$session where sid in (43, 55);
SID SQL_ID BLOCKING_SESSION EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 WAIT_CLASS STATE
----- ------------- ---------------- ------------------------------ ---------- ---------- --------------- ---------- ---------- ---------- --------------- ----------
43 SQL*Net message from client driver id 1650815232 #bytes 1 0 Idle WAITING
55 bsddu35jkskbz 43 enq: TX - row lock contention name|mode 1415053316 usn<<16 | slot 262149 sequence 13576 Application WAITING
SQL> select s.sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = 'bsddu35jkskbz';
SQL_TEXT EXECUTIONS S.ELAPSED_TIME/1000000
---------------------------------------------------------------------------------------------------- ---------- ----------------------
insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test11') 0 213.079739
SQL> select * from v$lock where sid in (43, 55) order by sid, type;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
000000009398F660 000000009398F6B8 43 AE 100 0 4 0 376 0
00007FABEA621F88 00007FABEA621FE8 43 TM 87875 0 3 0 266 0
0000000091E292E0 0000000091E29358 43 TX 262149 13576 6 0 266 1
000000009398F040 000000009398F098 55 AE 100 0 4 0 371 0
00007FABEA621F88 00007FABEA621FE8 55 TM 87875 0 3 0 256 0
0000000091DDB308 0000000091DDB380 55 TX 327688 13773 6 0 256 0
000000009398F900 000000009398F958 55 TX 262149 13576 0 4 256 0
7 rows selected.
最後查詢得是會話43阻塞了別的會話，是根源。
第三種情況，不同會話中同時bitmap索引列值相同的記錄
session 1:
SQL> select sid from v$mystat where rownum=1;
SID
----------
44
SQL> select * from T_ALL_OBJS ;
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST 2013011701 test1
TEST 2013011702 test2
SQL> create bitmap index ind_T_ALL_OBJS on T_ALL_OBJS (owner);
Index created.
SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test11');
1 row created.
未commit
session 2：
SQL> select sid from v$mystat where rownum=1;
SID
----------
40
SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test12');
一直等待。。。。。。。。。。
session 3：查詢
SQL> select sid,sql_id,blocking_session,event,p1text,p1,p2text,p2,p3text,p3,wait_class,state from v$session where sid in (44, 40);
SID SQL_ID BLOCKING_SESSION EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 WAIT_CLASS STATE
----- ------------- ---------------- ------------------------------ ---------- ---------- --------------- ---------- ---------- ---------- --------------- ----------
40 8s2tzhjpgx1nc 44 enq: TX - row lock contention name|mode 1415053316 usn<<16 | slot 655390 sequence 13564 Application WAITING
44 SQL*Net message from client driver id 1650815232 #bytes 1 0 Idle WAITING
SQL> select s.sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = '8s2tzhjpgx1nc';
SQL_TEXT EXECUTIONS S.ELAPSED_TIME/1000000
---------------------------------------------------------------------------------------------------- ---------- ----------------------
insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test12') 0 96.034408
SQL> select * from v$lock where sid in (44, 40) order by sid, type;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
000000009398E688 000000009398E6E0 40 AE 100 0 4 0 415 0
00007FABEA622FC0 00007FABEA623020 40 TM 87875 0 3 0 110 0
000000009398FBB8 000000009398FC10 40 TX 655390 13564 0 4 110 0
0000000091E54F48 0000000091E54FC0 40 TX 589844 13794 6 0 110 0
000000009398F3C0 000000009398F418 44 AE 100 0 4 0 410 0
00007FABEA622FC0 00007FABEA623020 44 TM 87875 0 3 0 126 0
0000000091E18128 0000000091E181A0 44 TX 655390 13564 6 0 126 1
7 rows selected.
最終查詢得會話44是阻塞根源。
模擬故障：
session 1：
SQL> select sid from v$mystat where rownum<2;
SID
----------
46
SQL> select * from t_all_objs;
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST 2013011701 test1
TEST 2013011702 test2
--插入資料，不提交
SQL> update t_all_objs set object_name='test11' where object_id=2013011701;
1 row updated.
session 2：
SQL> select sid from v$mystat where rownum<2;
SID
----------
52
SQL> select * from t_all_objs;
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST 2013011701 test1
TEST 2013011702 test2
--對同一行資料進行更新，不提交
SQL> update t_all_objs set object_name='test101' where object_id=2013011701;
等待ing.....................
session 3:
--查詢tx鎖會話sid,row_wait_object#資訊
select sid,sql_id,status,blocking_session, ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where event='enq: TX - row lock contention';
SID SQL_ID STATUS BLOCKING_SESSION ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
----- ------------- -------- ---------------- ------------- -------------- --------------- -------------
52 c53uad8st2u8t ACTIVE 46 87875 1 143649 0
--查詢被鎖的物件資訊：
select object_name from dba_objects where object_id in (87875);
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
T_ALL_OBJS
select OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID, OBJECT_TYPE from all_objects where object_name='T_ALL_OBJS';
OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ---------- -------------- -------------------
SYS T_ALL_OBJS 87875 87875 TABLE
--查詢被鎖的sesson所執行的sql
select sql_text from v$sql where sql_id in (select sql_id from v$session where sid=52);
SQL_TEXT
----------------------------------------------------------------------------------------------------
update t_all_objs set object_name='test101' where object_id=2013011701
--最後查詢 V$lock：
select SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from V$lock where block=1 or request<>0;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
----- -- ---------- ---------- ---------- ---------- ---------- ----------
46 TX 524304 13916 6 0 296 1
52 TX 524304 13916 0 6 284 0
或者透過如下SQL查詢會話之間鎖等待的關係：
select a.sid hold_sid, b.sid wait_sid, a.type, a.id1, a.id2, a.ctime
from v$lock a, v$lock b
where a.id1 = b.id1
and a.id2 = b.id2
and a.block = 1
and b.block = 0;
HOLD_SID WAIT_SID TY ID1 ID2 CTIME
---------- ---------- -- ---------- ---------- ----------
46 52 TX 524304 13916 2717
或者如下
select decode(request,0,'holder: ','waiter: ') ||
sid session_id, id1, id2, lmode, request, type
from v$lock
where (id1, id2, type) in (select id1, id2, type from v$lock where request > 0)
order by id1, request;
SESSION_ID ID1 ID2 LMODE REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
holder: 46 524304 13916 6 0 TX
waiter: 52 524304 13916 0 6 TX
最後可知sid為46的會話是阻塞根源。解決聯絡應用是不是會話為提交，或者kill掉
第一種情況，是真正的業務邏輯上的行鎖衝突，如一條記錄被多個人同時修改。這種鎖對應的請求模式是6。
第二種情況，是唯一鍵衝突，如主鍵欄位相同的多條記錄同時插入。這種鎖對應的請求模式是4。這也是應用邏輯問題。
第三種情況，是bitmap索引的更新衝突，就是多個會話同時更新bitmap索引的同一個資料塊。此時會話請求鎖的對應的請求模式是4。
bitmap索引的物理結構和普通索引一樣，也是 B-tree 結構。但它儲存的資料記錄的邏輯結構為"key_value,start_rowid,end_rowid,bitmap"。
其內容類似這樣：
"‘8088’,00000000000,10000034441,1001000100001111000"
Bitmap是一個二進位制，表示 START_ROWID 到 END_ROWID 的記錄， 1 表示等於 key_value 即‘8088’的 ROWID 記錄， 0 則表示不是這個記錄。
在瞭解bitmap索引的結構之後，我們就能理解同時插入多條記錄到擁有bitmap索引的表時，就會同時更新bitmap索引中一個塊中的記錄，等於某一個記錄被同時更新，自然就會出現行鎖等待。插入併發量越大，等待越嚴重。
等待事件enq: TX - row lock contention中的enq是enquence的簡寫。enquence是協調訪問資料庫資源的內部鎖。
所有以“enq:”打頭的等待事件都表示這個會話正在等待另一個會話持有的內部鎖釋放，它的名稱格式是enq:enqueue_type - related_details。這裡的enqueue_type是TX，related_details是row lock contention。資料庫動態效能檢視v$event_name提供所有以“enq:”開頭的等待事件的列表。
雖然在awrrpt中看到大量enq: TX - row lock contention的等待，但這些是事後看到的資訊。根據AWRRPT，我們無法只能該等待事件的請求模式是什麼，是6還是4。
如果資料庫一出現enq: TX - row lock contention等待，可以去看v$session和v$session_wait等檢視。
在v$session和v$session_wait中，如果看到的event列是enq: TX - row lock contention的，就表示這個會話正處於行鎖等待。該等待事件的請求模式可以從v$session和v$session_wait的p1列中得到。
select sid,
chr(bitand(p1, -16777216) / 16777215) ||
chr(bitand(p1, 16711680) / 65535) "Name",
(bitand(p1, 65535)) "Mode"
from v$session_wait
where event like 'enq%';
透過這個SQL可以將p1轉換為易閱讀的文字。
針對這三種情況，分別進行測試：
首先，我準備一下測試表和資料。
--建立測試表和資料
SQL> create table t_all_objs as select owner,object_id,object_name from all_objects where 0=1;
Table created.
SQL> alter table T_ALL_OBJS add constraint pk_t_all_objs primary key (OBJECT_ID);
Table altered.
SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011701,'test1');
1 row created.
SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011702,'test2');
1 row created.
SQL> commit;
Commit complete.
第一種情況，不同會話同時更新同一條記錄
session1:
SQL> select sid from v$mystat where rownum<2;
SID
----------
46
SQL> select * from t_all_objs;
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST 2013011701 test1
TEST 2013011702 test2
SQL> update t_all_objs set object_name='test11' where object_id=2013011701;
1 row updated.
未commit
session 2:
SQL> select sid from v$mystat where rownum<2;
SID
----------
52
SQL> update t_all_objs set object_name='test101' where object_id=2013011701;
一直等待。。。。。。。。。
session 3：查詢
SQL> select sid,sql_id,blocking_session,event,p1text,p1,p2text,p2,p3text,p3,wait_class,state from v$session where sid in (46, 52);
SID SQL_ID BLOCKING_SESSION EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 WAIT_CLASS STATE
----- ------------- ---------------- ------------------------------ ---------- ---------- --------------- ---------- ---------- ---------- --------------- ----------
46 SQL*Net message from client driver id 1650815232 #bytes 1 0 Idle WAITING
52 c53uad8st2u8t 46 enq: TX - row lock contention name|mode 1415053318 usn<<16 | slot 65556 sequence 13548 Application WAITING
--得到sql_id值，查詢出SQL資訊
SQL> select s.sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = 'c53uad8st2u8t';
SQL_TEXT EXECUTIONS S.ELAPSED_TIME/1000000
---------------------------------------------------------------------- ---------- ----------------------
update t_all_objs set object_name='test101' where object_id=2013011701 0 501.178747
--再查詢鎖資訊：
SQL> select * from v$lock where sid in (46,52) order by sid, type;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
000000009398FE58 000000009398FEB0 46 AE 100 0 4 0 3706 0
00007FABEA622FC0 00007FABEA623020 46 TM 87875 0 3 0 597 0
0000000091E37248 0000000091E372C0 46 TX 65556 13548 6 0 597 1
000000009398F820 000000009398F878 52 AE 100 0 4 0 573 0
00007FABEA622FC0 00007FABEA623020 52 TM 87875 0 3 0 543 0
000000009398FBB8 000000009398FC10 52 TX 65556 13548 0 6 543 0
6 rows selected.
查詢得到未46的session最終阻塞了會話，是根源。
第二種情況，不同會話中同時插入主鍵欄位相同的記錄
session 1；
SQL> select sid from v$mystat where rownum=1;
SID
----------
43
SQL> select * from t_all_objs;
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST 2013011701 test1
TEST 2013011702 test2
SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test1');
1 row created.
未commit
session 2:
SQL> select sid from v$mystat where rownum=1;
SID
----------
55
SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test11');
一直等待。。。。。。。
session 3：查詢
SQL> select sid,sql_id,blocking_session,event,p1text,p1,p2text,p2,p3text,p3,wait_class,state from v$session where sid in (43, 55);
SID SQL_ID BLOCKING_SESSION EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 WAIT_CLASS STATE
----- ------------- ---------------- ------------------------------ ---------- ---------- --------------- ---------- ---------- ---------- --------------- ----------
43 SQL*Net message from client driver id 1650815232 #bytes 1 0 Idle WAITING
55 bsddu35jkskbz 43 enq: TX - row lock contention name|mode 1415053316 usn<<16 | slot 262149 sequence 13576 Application WAITING
SQL> select s.sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = 'bsddu35jkskbz';
SQL_TEXT EXECUTIONS S.ELAPSED_TIME/1000000
---------------------------------------------------------------------------------------------------- ---------- ----------------------
insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test11') 0 213.079739
SQL> select * from v$lock where sid in (43, 55) order by sid, type;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
000000009398F660 000000009398F6B8 43 AE 100 0 4 0 376 0
00007FABEA621F88 00007FABEA621FE8 43 TM 87875 0 3 0 266 0
0000000091E292E0 0000000091E29358 43 TX 262149 13576 6 0 266 1
000000009398F040 000000009398F098 55 AE 100 0 4 0 371 0
00007FABEA621F88 00007FABEA621FE8 55 TM 87875 0 3 0 256 0
0000000091DDB308 0000000091DDB380 55 TX 327688 13773 6 0 256 0
000000009398F900 000000009398F958 55 TX 262149 13576 0 4 256 0
7 rows selected.
最後查詢得是會話43阻塞了別的會話，是根源。
第三種情況，不同會話中同時bitmap索引列值相同的記錄
session 1:
SQL> select sid from v$mystat where rownum=1;
SID
----------
44
SQL> select * from T_ALL_OBJS ;
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST 2013011701 test1
TEST 2013011702 test2
SQL> create bitmap index ind_T_ALL_OBJS on T_ALL_OBJS (owner);
Index created.
SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test11');
1 row created.
未commit
session 2：
SQL> select sid from v$mystat where rownum=1;
SID
----------
40
SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test12');
一直等待。。。。。。。。。。
session 3：查詢
SQL> select sid,sql_id,blocking_session,event,p1text,p1,p2text,p2,p3text,p3,wait_class,state from v$session where sid in (44, 40);
SID SQL_ID BLOCKING_SESSION EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 WAIT_CLASS STATE
----- ------------- ---------------- ------------------------------ ---------- ---------- --------------- ---------- ---------- ---------- --------------- ----------
40 8s2tzhjpgx1nc 44 enq: TX - row lock contention name|mode 1415053316 usn<<16 | slot 655390 sequence 13564 Application WAITING
44 SQL*Net message from client driver id 1650815232 #bytes 1 0 Idle WAITING
SQL> select s.sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = '8s2tzhjpgx1nc';
SQL_TEXT EXECUTIONS S.ELAPSED_TIME/1000000
---------------------------------------------------------------------------------------------------- ---------- ----------------------
insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test12') 0 96.034408
SQL> select * from v$lock where sid in (44, 40) order by sid, type;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
000000009398E688 000000009398E6E0 40 AE 100 0 4 0 415 0
00007FABEA622FC0 00007FABEA623020 40 TM 87875 0 3 0 110 0
000000009398FBB8 000000009398FC10 40 TX 655390 13564 0 4 110 0
0000000091E54F48 0000000091E54FC0 40 TX 589844 13794 6 0 110 0
000000009398F3C0 000000009398F418 44 AE 100 0 4 0 410 0
00007FABEA622FC0 00007FABEA623020 44 TM 87875 0 3 0 126 0
0000000091E18128 0000000091E181A0 44 TX 655390 13564 6 0 126 1
7 rows selected.
最終查詢得會話44是阻塞根源。
模擬故障：
session 1：
SQL> select sid from v$mystat where rownum<2;
SID
----------
46
SQL> select * from t_all_objs;
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST 2013011701 test1
TEST 2013011702 test2
--插入資料，不提交
SQL> update t_all_objs set object_name='test11' where object_id=2013011701;
1 row updated.
session 2：
SQL> select sid from v$mystat where rownum<2;
SID
----------
52
SQL> select * from t_all_objs;
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST 2013011701 test1
TEST 2013011702 test2
--對同一行資料進行更新，不提交
SQL> update t_all_objs set object_name='test101' where object_id=2013011701;
等待ing.....................
session 3:
--查詢tx鎖會話sid,row_wait_object#資訊
select sid,sql_id,status,blocking_session, ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where event='enq: TX - row lock contention';
SID SQL_ID STATUS BLOCKING_SESSION ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
----- ------------- -------- ---------------- ------------- -------------- --------------- -------------
52 c53uad8st2u8t ACTIVE 46 87875 1 143649 0
--查詢被鎖的物件資訊：
select object_name from dba_objects where object_id in (87875);
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
T_ALL_OBJS
select OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID, OBJECT_TYPE from all_objects where object_name='T_ALL_OBJS';
OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ---------- -------------- -------------------
SYS T_ALL_OBJS 87875 87875 TABLE
--查詢被鎖的sesson所執行的sql
select sql_text from v$sql where sql_id in (select sql_id from v$session where sid=52);
SQL_TEXT
----------------------------------------------------------------------------------------------------
update t_all_objs set object_name='test101' where object_id=2013011701
--最後查詢 V$lock：
select SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from V$lock where block=1 or request<>0;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
----- -- ---------- ---------- ---------- ---------- ---------- ----------
46 TX 524304 13916 6 0 296 1
52 TX 524304 13916 0 6 284 0
或者透過如下SQL查詢會話之間鎖等待的關係：
select a.sid hold_sid, b.sid wait_sid, a.type, a.id1, a.id2, a.ctime
from v$lock a, v$lock b
where a.id1 = b.id1
and a.id2 = b.id2
and a.block = 1
and b.block = 0;
HOLD_SID WAIT_SID TY ID1 ID2 CTIME
---------- ---------- -- ---------- ---------- ----------
46 52 TX 524304 13916 2717
或者如下
select decode(request,0,'holder: ','waiter: ') ||
sid session_id, id1, id2, lmode, request, type
from v$lock
where (id1, id2, type) in (select id1, id2, type from v$lock where request > 0)
order by id1, request;
SESSION_ID ID1 ID2 LMODE REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
holder: 46 524304 13916 6 0 TX
waiter: 52 524304 13916 0 6 TX
最後可知sid為46的會話是阻塞根源。解決聯絡應用是不是會話為提交，或者kill掉
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2144367/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 等待事件enq: TX - row lock contention事件ENQ
- 【等待事件】-enq: TX - row lock contention事件ENQ
- enq: TX - row lock contention等待事件處理ENQ事件
- enq: TX - row lock contentionENQ
- AWR實戰分析之----enq: TX - row lock contentionENQ
- 奇異的enq: TX - row lock contentionENQ
- enq: TX – row lock contention的測試和案例分析ENQ
- 故障排除 | enq：TX - index contention等待事件ENQIndex事件
- zt_Oracle enq: TX contention 和 enq: TM contention 等待事件OracleENQ事件
- Oracle Enqueues Wait Events 三 enq: TX - row lock contentionOracleENQAI
- 無關的表引起的enq: TX - row lock contentionENQ
- enq: TX - index contention等待ENQIndex
- 關於enq: TX - row lock contention行鎖的總結ENQ
- 20161208理解enq TX - row lock contentionENQ
- ORACLE 歸檔空間滿導致的enq: TX - row lock contentionOracleENQ
- 使用oradebug dump processstate 來診斷enq: TX - row lock contentionENQ
- enq: TX - allocate ITL entry等待事件分析ENQ事件
- enq: WF - contention等待事件ENQ事件
- enq: CF - contention 等待事件ENQ事件
- enq: TS - contention 等待事件ENQ事件
- 等待事件之enq: HW - contention事件ENQ
- enq:TM-contention事件等待ENQ事件
- 消除 enq: DX - contention 等待事件ENQ事件
- 一次資料庫相關操作卡住的排查--enq: TX - row lock contention資料庫ENQ
- enq:TX - index contentionENQIndex
- enq: TX - index contentionENQIndex
- Oracle等待事件之enq: TM – contentionOracle事件ENQ
- oracle等待事件之enq: CF – contentionOracle事件ENQ
- 等待事件之Row Cache Lock事件
- 如何診斷等待事件 enq: HW - contention事件ENQ
- 【效能調整】等待事件 enq: SQ - contention事件ENQ
- enq：Library cache lock/pin等待事件ENQ事件
- 關於enq: TX - allocate ITL entry等待事件ENQ事件
- 事務上的等待事件 —— enq: UL - contention事件ENQ
- enq: SQ - contention 等待事件處理辦法ENQ事件
- 關於enq: TX - index contention 等待的探討與測試ENQIndex
- 主外來鍵約束之主表插入未提交導致外來鍵表插入hang住的等待事件 TX-row lock contention事件
- oracle 11.2.0.4 rac叢集等待事件enq: TM - contentionOracle事件ENQ