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
----------
75
SQL> 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
----------
200
SQL> 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 6
75 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 75
SQL> 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 179
SQL> 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 2361
SQL> 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;
INST_ID SID SERIAL# EVENT USERNAME SQL_HASH_VALUE SQL_FULLTEXT
---------- ---------- ---------- ----------------------------- ----------- ------------- ---------------------------------
1 200 12230 enq: TX - row lock contention TEST 3515433816 update test set name='kerry' where id=1001
SQL> col type for a12;
SQL> select /*+rule*/
2 inst_id,
3 decode(request, 0, 'holder', 'waiter') role,
4 sid,
5 type,
6 request,
7 lmode,
8 block,
9 ctime,
10 id1,
11 id2
12 from gv$lock
13 where (id1, id2, type) in
14 (select id1, id2, type from gv$lock where request >0)
15 order by ctime desc ,role;
INST_ID ROLE SID TYPE REQUEST LMODE BLOCK CTIME ID1 ID2
---------- ------ ---------- ------------ ---------- ---------- ---------- ---------- ---------- ----------
1 holder 75 TX 0 6 1 255 655385 2361
1 waiter 200 TX 6 0 0 245 655385 2361
此時只能等待持有鎖的會話commit或者rollback。 通常為會話1在某行上執行 update/delete 未提交，會話2對同一行數據進行 update/delete，或其它原因（例如SQL性能差）造成的鎖釋放速度緩慢或網絡問題，都會造成後續的會話進入隊列等待。
2：Waits due to Unique or Primary Key Constraint Enforcement
表上存在主鍵或唯一索引，會話1插入一個值（未提交），會話2同時或隨後也插入同樣的值;會話提交後1，enq: TX - row lock contention消失。
SQL> drop table test purge;
Table dropped.
SQL> create table test
2 (
3 id number(10),
4 name varchar(16),
5 constraint pk_test primary key(id)
6 );
Table created.
會話1（會話ID為8）
SQL> insert into test values(1000, 'kerry');
1 row created.
會話2（會話ID為14）
SQL> insert into test values(1000,'jimmy');
會話3 在會話3中查看對應的會話、鎖、以及等待信息
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
---------- -------------------------------- ---------- ---------- ---------- ----------
14 TX 589835 2213 0 4
14 TX 393232 2160 6 0
8 TX 589835 2213 6 0
SQL> col event for a40
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;
INST_ID SID SERIAL# EVENT USERNAME SQL_HASH_VALUE SQL_FULLTEXT
---------- ---------- ---------- ----------------------------- ---------- -------------- ----------------------
1 14 13392 enq: TX - row lock contention TEST 874051616 insert into test values(1000,'jimmy')
SQL> col type for a12;
SQL> select /*+rule*/
2 inst_id,
3 decode(request, 0, 'holder', 'waiter') role,
4 sid,
5 type,
6 request,
7 lmode,
8 block,
9 ctime,
10 id1,
11 id2
12 from gv$lock
13 where (id1, id2, type) in
14 (select id1, id2, type from gv$lock where request >0)
15 order by ctime desc ,role;
INST_ID ROLE SID TYPE REQUEST LMODE BLOCK CTIME ID1 ID2
---------- ------------ ---------- ------------ ---------- ---------- ---------- ---------- ---------- ----------
1 holder 8 TX 0 6 1 92 589835 2213
1 waiter 14 TX 4 0 0 70 589835 2213
會話1（會話ID為8）提交事務後
SQL> insert into test values(1000, 'kerry');
1 row created.
SQL> commit;
會話2（會話ID為14）遇到ORA-00001錯誤提示
SQL> insert into test values(1000,'jimmy');
insert into test values(1000,'jimmy')
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.PK_TEST) violated
這個在ORACLE 10g以及以上版本都無法測試（Oracle 9i可以測試），因為ORACLE 10g中，對於單個數據塊，Oracle缺省最大支持255個並發，MAXTRANS參數在ORACLE 10g以及以上版本被廢棄了，即使你使用下面SQL指定了maxtrans為1， 但是你查看表的定義，你會發現maxtrans依然為255。
SQL> drop table test purge;
Table dropped.
SQL> create table test
2 (
3 id number(10),
4 name varchar(16)
5 ) initrans 1 maxtrans 1;
Table created.
所以這個場景只會發生在ORACLE 9i的版本中或是並發非常高的系統當中。
Waits due to rows being covered by the same BITMAP index fragment
這個源於位圖索引的特性，更新位圖索引的一個鍵值，會指向多行記錄，所以更新一行就會把該鍵值指向的所有行鎖定
SQL> create table employee
2 (
3 employee_id number(10),
4 employee_name nvarchar2(24),
5 sex varchar2(6)
6 );
Table created.
SQL> create bitmap index idx_employee_bitmap on employee(sex);
Index created.
SQL> insert into employee
2 select 1000, 'kerry', 'female' from dual union all
3 select 1001, 'jimmy', 'female' from dual;
2 rows created.
SQL> commit;
Commit complete.
SQL>
會話1：
SQL> update employee set sex='male' where employee_id=1000;
1 row updated.
會話2：
SQL> update employee set sex='male' where employee_id=1001;
會話3：
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
---------- -------------------------------- ---------- ---------- ---------- ----------
14 TX 589836 2211 0 4
14 TX 131096 2204 6 0
8 TX 589836 2211 6 0
SQL> col event for a40
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;
INST_ID SID SERIAL# EVENT USERNAME SQL_HASH_VALUE SQL_FULLTEXT
---------- ---------- ---------- ------------------------------ ---------- -------------- ---------------------------------
1 14 13392 enq: TX - row lock contention EST 2418349426 update employee set sex='male' where employee_id=1001
SQL> col type for a12;
SQL> select /*+rule*/
2 inst_id,
3 decode(request, 0, 'holder', 'waiter') role,
4 sid,
5 type,
6 request,
7 lmode,
8 block,
9 ctime,
10 id1,
11 id2
12 from gv$lock
13 where (id1, id2, type) in
14 (select id1, id2, type from gv$lock where request >0)
15 order by ctime desc ,role;
INST_ID ROLE SID TYPE REQUEST LMODE BLOCK CTIME ID1 ID2
---------- ------------ ---------- ------------ ---------- ---------- ---------- ---------- ---------- ----------
1 holder 8 TX 0 6 1 116 589836 2211
1 waiter 14 TX 4 0 0 76 589836 2211
SQL>
其它場景
There are other wait scenarios which can result in a SHARE mode wait for a TX lock but these are rare compared to the examples given above.
Example:
If a session wants to read a row locked by a transaction in a PREPARED state then it will wait on the relevant TX lock in SHARE mode (REQUEST=4). As a PREPARED transaction should COMMIT , ROLLBACK or go to an in-doubt state very soon after the prepare this is not generally noticeable.
例如，表存在主外鍵讀情況,主表不提交,子表那麼必須進行等待.
初始化測試表
SQL> create table employee( employee_id number, employee_name varchar(12), depart_id number);
Table created.
SQL> create table department(depart_id number primary key, depart_name varchar2(24));
Table created.
會話1：
SQL> select sid from v$mystat where rownum=1;
SID
----------
75
SQL> insert into department values(1000, 'sales');
1 row created.
會話2：
SQL> select sid from v$mystat where rownum=1;
SID
----------
200
SQL> insert into employee values(1024, 'kerry', 1000); --一直掛起，直到會話1提交
會話3
SQL> show user;
USER is "SYS"
SQL> select sid from v$mystat where rownum=1;
SID
----------
73
SQL> col type for a12;
SQL> select /*+rule*/
2 inst_id,
3 decode(request, 0, 'holder', 'waiter') role,
4 sid,
5 type,
6 request,
7 lmode,
8 block,
9 ctime,
10 id1,
11 id2
12 from gv$lock
13 where (id1, id2, type) in
14 (select id1, id2, type from gv$lock where request >0)
15 order by ctime desc ,role;
INST_ID ROLE SID TYPE REQUEST LMODE BLOCK CTIME ID1 ID2
---------- ------ ---------- ------------ ---------- ---------- ---------- ---------- ---------- ----------
1 holder 75 TX 0 6 1 197 458758 2371
1 waiter 200 TX 4 0 0 97 458758 2371
SQL> 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 108 WAITING 145 TX-00070006-00000943 7 6 2371
SQL>
另外遇到enq: TX - row lock contention等待事件，單實例與RAC是否有所區別呢，如果是RAC，需要注意識別實例，否則很容易誤殺其它會話？如果你查到了blocker，是不是應該直接kill掉呢？ 這個必須要先征詢客戶的意見，確認之後才可以殺掉。不能因為外在壓力和自己的急躁而擅自Kill會話。
在WAITEVENT: "enq: TX - row lock contention" Reference Note (文檔 ID 1966048.1)中，也有一些比較有意思的SQL，可以參考一下
SQL> SELECT row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
2 FROM v$session
3 WHERE event='enq: TX - row lock contention'
4 AND state='WAITING';
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
75389 4 211 0
SQL> set linesize 240;
SQL> select owner, object_name from dba_objects
2 where object_id=75389;
OWNER OBJECT_NAME
------------------------------ ------------------
TEST TEST
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'
9 ;
SID SEQ# SECONDS_IN_WAIT TX XIDUSN XIDSLOT XIDSQN
---------- ---------- --------- --------------- --------------------------- ---------- ---------- ----------
137 27 WAITING 245 TX-00040012-000007E6 4 18 2022
在TX - row lock contention 的一些場景 這篇文章裡面介紹了出現enq: TX - row lock contention等待的案例場景，網絡問題、執行計劃問題、應用問題等。在我遇到的實際案例當中，網絡問題造成的'enq: TX - row lock contention'較多，因為現在大多數是無線網絡，有些應用程序出現問題或網絡出現問題過後，導致數據庫中的進程依然在，但是對於的UPDATE等DML操作沒有及時提交。從而出現較嚴重的enq: TX - row lock contention
診斷定位enq: TX - row lock contention等待事件
在官方文檔 ID 62354.1裡面，提供了一個根據AWR 快照ID查找那些段出現row lock 等待較多的SQL，這個也有一定的參考意義。
SELECT P.snap_id,
P.begin_interval_time,
O.owner,
O.object_name,
O.subobject_name,
O.object_type,
S.row_lock_waits_delta
FROM dba_hist_seg_stat S,
dba_hist_seg_stat_obj O,
dba_hist_snapshot P
WHERE S.dbid =O.dbid
AND S.ts# =O.ts#
AND S.obj# =O.obj#
AND S.dataobj# =O.dataobj#
AND S.snap_id =P.snap_id
AND S.dbid =P.dbid
AND S.instance_number =P.instance_number
AND S.row_lock_waits_delta > 0
AND P.snap_id BETWEEN <begin_snap> AND <end_snap>
ORDER BY 1,3,4;
一般最常用的還是AWR報告結合ASH報告來診斷、定位enq: TX - row lock contention等待事件，另外，在TX - row lock contention 的一些場景這篇分享文章中，對如何減少enq: TX - row lock contention等待做了一些總結，具體如下：
在一些事務頻繁，並發較高的環境下，為了盡可能減少 TX - row lock contention 等待事件的發生，應當從應用設計到數據庫多個層面進行考慮。
應用層面：
1、約束通常是為了保證數據完整性，在並發場景下，應充分考慮事務進行的邏輯順序，避免多個會話事務交叉進行，觸發約束沖突在事務級別發生競爭；
2、要提高並發效率，應當盡可能拆分大事務為小事務，提高 tx enqueue 鎖的獲取釋放速度；
3、如果要使用悲觀鎖（for update），應盡可能減少鎖定的行范圍；
數據庫層面：
1、在 dml 頻繁的表上建立適當的索引，提高 SQL 執行的效率，減少 tx enqueue 鎖持有的時間；避免全表掃描這種，容易造成 IO 開銷巨大，熱塊競爭，會話堆積的訪問方式。
2、在 dml 頻繁的表上不應使用位圖索引；
3、對於 dml 頻繁的表，不應使用 IOT 表，物化視圖等；
4、RAC 環境下，對於批量的 dml 操作，盡可能固定在單一節點，盡量降低網絡開銷、集群競爭、一致性塊獲取和日志刷盤等帶來的影響。
參考資料：
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=521417992978367&id=62354.1&displayIndex=1&_afrWindowMode=0&_adf.ctrl-state=1d01vq0378_227
http://mp.weixin.qq.com/s?__biz=MjM5MzExMTU2OQ==&mid=2650603515&idx=1&sn=275956ad38d26168e44027336644e5a0&scene=2&srcid=0711qxhIykeqO278x7VZFx5k&from=timeline&isappinstalled=0#wechat_redirect
http://www.dbform.com/html/2015/2317.html
http://www.killdb.com/2015/07/13/%E5%85%B3%E4%BA%8Eenq-tx-row-lock-contention%E7%9A%84%E6%B5%8B%E8%AF%95%E5%92%8C%E6%A1%88%E4%BE%8B%E5%88%86%E6%9E%90.html
http://blog.chinaunix.net/uid-23284114-id-3390180.html
http://yunlongzheng.blog.51cto.com/788996/411205
