如何進行等待事件enq TX row lock contention的分析

158次閱讀

共計 10702 個字符，預計需要花費 27 分鐘才能閱讀完成。

本篇文章為大家展示了如何進行等待事件 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

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

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

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

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 掉

上述內容就是如何進行等待事件 enq TX row lock contention 的分析，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注丸趣 TV 行業資訊頻道。

正文完