enq: TX - row lock contention等待事件，這個是資料庫裡面一個比較常見的等待事件。enq是enqueue的縮寫，它是一種保護共用資源的鎖定機制，一個排隊機制，先進先出(FIFO)。enq: TX - row lock contention等待事件，OACLE將其歸類為app... ...
enq: TX - row lock contention等待事件，這個是資料庫裡面一個比較常見的等待事件。enq是enqueue的縮寫，它是一種保護共用資源的鎖定機制，一個排隊機制，先進先出(FIFO)。enq: TX - row lock contention等待事件，OACLE將其歸類為application級別的等待事件。有些場景是因為應用邏輯設計不合理造成的。下麵我們看看enq: TX - row lock contention的英文介紹：
This wait indicates time spent waiting for a TX lock, typically due to waiting to gain access to a row in a table that is currently locked by that transaction. The TX lock waited on is "TX-P2RAW-P3RAW" and the object / row that triggered the wait can usually be found in the ROW_WAIT_* columns of V$SESSION for the waiting session.
A TX lock is acquired when a transaction initiates its first change and is held until the transaction does a COMMIT or ROLLBACK. It is used mainly as a queuing mechanism so that other sessions can wait for the transaction to complete. The lock name (ID1 and ID2) of the TX lock reflect the transaction ID of the active transaction.
下麵我們模擬一下enq: TX - row lock contention等待事件出現的場景，希望能對這個等待事件有較深的理解，主要參考了官方文檔 ID 62354.1
1： Waits due to Row being locked by an active Transaction
這個是因為不同的session同時更新或刪除同一個記錄。例如，會話1持有row level lock，會話2在等待這個鎖釋放。準備測試環境和數據
SQL> create table test
2 ( id number(10),
3 name varchar2(16)
4 ) ;
Table created.SQL> insert into test
2 values(1001, 'kk');
1 row created.SQL> insert into test
values(1002, 'tttt')
1 row created.SQL> commit;
Commit complete.SQL>
會話1（會話ID為75）更新某一行
SQL> select sid from v$mystat where rownum =1;
SID
---------- 75SQL> update test set name='ken' where id=1001;
1 row updated.SQL>
會話2（會話ID為200）也更新這一行（刪除亦可）
SQL> select sid from v$mystat where rownum=1;
SID
---------- 200SQL> update test set name='kerry' where id=1001; --一直被阻塞
在會話3中查看對應的會話、鎖以及等待相關信息，這些SQL各
SQL> col type for a32;
SQL> SELECT sid,
2 type,
3 id1,
4 id2,
5 lmode,
6 request
7 FROM v$lock 8 WHERE type = 'TX';
SID TYPE ID1 ID2 LMODE REQUEST
---------- -------------------------------- ---------- ---------- ---------- ---------- 200 TX 655385 2361 0 675 TX 655385 2361 6 0
SQL> COL event FOR a36;
SQL> SELECT sid,
2 Chr(Bitand(p1, -16777216) / 16777215)
3 || Chr(Bitand(p1, 16711680) / 65535) "name",
4 ( Bitand(p1, 65535) ) "mode",
5 event,
6 sql_id,
7 final_blocking_session
8 FROM v$session
9 WHERE event LIKE 'enq%';
SID name mode EVENT SQL_ID FINAL_BLOCKING_SESSION
---------- -------- ---------- ------------------------------------ ------------- ---------------------- 200 TX 6 enq: TX - row lock contention cz4tvs78skhus 75SQL> COL wait_class FOR A32;
SQL> SELECT inst_id,
2 blocking_session,
3 sid,
4 serial#,
5 wait_class,
6 seconds_in_wait
7 FROM gv$session
8 WHERE blocking_session IS NOT NULL
9 ORDER BY blocking_session;
INST_ID BLOCKING_SESSION SID SERIAL# WAIT_CLASS SECONDS_IN_WAIT
---------- ---------------- ---------- ---------- -------------------------------- --------------- 1 75 200 12230 Application 179SQL> COL TX FOR A24;
SQL> SELECT
2 sid, seq#, state, seconds_in_wait,3 'TX-'||lpad(ltrim(p2raw,'0'),8,'0')||'-'||lpad(ltrim(p3raw,'0'),8,'0') TX,
4 trunc(p2/65536) XIDUSN,
5 trunc(mod(p2,65536)) XIDSLOT,
6 p3 XIDSQN
7 FROM v$session_wait 8 WHERE event='enq: TX - row lock contention';
SID SEQ# STATE SECONDS_IN_WAIT TX XIDUSN XIDSLOT XIDSQN---------- ---------- ------------------- --------------- ------------------------ ---------- ---------- ---------- 200 46 WAITING 203 TX-000A0019-00000939 10 25 2361SQL> col event for a36
SQL> col username for a10
SQL> col sql_fulltext for a80
SQL> SELECT g.inst_id,
2 g.sid,
3 g.serial#,
4 g.event,
5 g.username,
6 g.sql_hash_value,
7 s.sql_fulltext
8 FROM gv$session g,
9 v$sql s 10 WHERE g.wait_class = 'Application'
11 AND g.sql_hash_value = s.hash_value;
