enq: TX – row lock contention的测试和案例分析
参考：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
关于enq: TX – row lock contention的测试和案例分析
1、主键或唯一index
session 1：
SQL> select sid from v$mystat where rownum=1;
SID
----------
74
SQL> create table t1_tx(id number primary key,name varchar2(20));
Table created.
SQL> insert into t1_tx values(1,'wang');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t1_tx values(2,'xxoo');
1 row created.
未提交。。。。。。。。。。。。
session 2：
SQL> select sid from v$mystat where rownum=1;
SID
----------
45
SQL> insert into t1_tx values(2,'xxoo');
hang。。。。。。。。。。。。。。。
session 3:
SQL> set lines 200
SQL> col event for a30
SQL> select inst_id,
2 sid,
3 chr(bitand(p1, -16777216) / 16777215) ||
4 chr(bitand(p1, 16711680) / 65535) "Name",
5 (bitand(p1, 65535)) "Mode",
6 event,
7 sql_id,
8 blocking_session,
9 FINAL_BLOCKING_SESSION
10 from gv$session
11 where event like 'enq%';
INST_ID SID Name Mode EVENT SQL_ID BLOCKING_SESSION FINAL_BLOCKING_SESSION
---------- ---------- ---- ---------- ------------------------------ ------------- ---------------- ----------------------
1 45 TX 4 enq: TX - row lock contention 4s99cmp3khb1b 74 74
SQL>
SQL> select sid,serial#,username,sql_id,status from v$session where sid=74;
SID SERIAL# USERNAME SQL_ID STATUS
---------- ---------- ------------------------------ ------------- --------
74 23 HR INACTIVE
sql_id为空说明为非活动会话，会话等该提交或者回滚。
SQL> select * from v$Lock where block=1;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000008E9E80C0 000000008E9E8138 74 TX 524298 9978 6 0 221 1
block为1,阻塞会话。
可以看出,对于表存在主键或者 unique index 时,一个会话操作主键不提交时,其他会话如果也操作相同的主键时，那么必须进行等待，而其持有的mode＝4；而阻塞blocker的持有mode＝6.
2、Bitmap INDEX
session 1：
SQL> select * from t1_tx;
ID NAME
---------- --------------------
1 wang
2 wang
3 xxoo
4 xxoo
SQL>
SQL> select sid from v$mystat where rownum=1;
SID
----------
74
SQL> create bitmap index idx_bitmap_name on t1_tx(name);
Index created.
SQL> update t1_tx set name='tx' where id=3;
1 row updated.
未提交。。。。。。。。。。。
session 2:
SQL> select sid from v$mystat where rownum=1;
SID
----------
45
SQL> update t1_tx set name='bitmap' where id=4;
hang。。。。。。。。。。。。。
session 3：
SQL> col event for a30
SQL> select inst_id,
2 sid,
3 chr(bitand(p1, -16777216) / 16777215) ||
4 chr(bitand(p1, 16711680) / 65535) "Name",
5 (bitand(p1, 65535)) "Mode",
6 event,
7 sql_id,
8 blocking_session,
9 FINAL_BLOCKING_SESSION
10 from gv$session
11 where event like 'enq%';
INST_ID SID Name Mode EVENT SQL_ID BLOCKING_SESSION FINAL_BLOCKING_SESSION
---------- ---------- ---- ---------- ------------------------------ ------------- ---------------- ----------------------
1 45 TX 4 enq: TX - row lock contention 7wanaturqndn1 74 74
SQL>
SQL> set lines 200 pagesize 200
SQL> select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'));
Enter value for amp: 7wanaturqndn1
old 1: select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'))
new 1: select * from table(dbms_xplan.display_cursor('7wanaturqndn1;sql_id', NULL, 'ALL'))
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID 7wanaturqndn1, child number 0
-------------------------------------
update t1_tx set name='bitmap' where id=4
Plan hash value: 1842098942
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 1 (100)| |
| 1 | UPDATE | T1_TX | | | | |
|* 2 | INDEX UNIQUE SCAN| SYS_C0010951 | 1 | 25 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
SQL> select * from v$Lock where block=1;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000008E9E80C0 000000008E9E8138 74 TX 262171 2920 6 0 264 1
SQL>
SQL> select sid,serial#,username,sql_id,event from v$session where sid=74;
SID SERIAL# USERNAME SQL_ID EVENT
---------- ---------- ------------------------------ ------------- ----------------------------------------------------------------
74 23 HR SQL*Net message from client
SQL> select owner,index_name,index_type from dba_indexes where table_name='T1_TX';
OWNER INDEX_NAME INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
HR IDX_BITMAP_NAME BITMAP
HR SYS_C0012427 NORMAL
我们可以看到，如果表上存在位图index，那么在update时,多个会话同时进行更新，必然出现tx 等待。
此时waiter申请持有的tx 锁mode＝4，而blocker持有的mode＝6,而且通过v$session试图还无法查询到blocker会话到sql_id.
3、数据位于同一block
session 3：
SQL> conn hr/hr;
Connected.
SQL> select dbms_rowid.rowid_object(rowid) obj#,
2 dbms_rowid.rowid_relative_fno(rowid) rfile#,
3 dbms_rowid.rowid_block_number(rowid) block#,
4 dbms_rowid.rowid_row_number(rowid) row#
5 from t1_tx
6 order by 4;
OBJ# RFILE# BLOCK# ROW#
---------- ---------- ---------- ----------
90536 4 4087 0
90536 4 4087 1
90536 4 4087 2
90536 4 4087 3
SQL>
session 1：
SQL> select sid from v$mystat where rownum=1;
SID
----------
45
SQL> select * from t1_tx;
ID NAME
---------- --------------------
1 wang
2 wang
3 tx
4 bitmap
SQL> update t1_tx set name='enmotech' where id=2;
1 row updated.
SQL> commit;
Commit complete.
SQL>
session 2：
SQL> select sid from v$mystat where rownum=1;
SID
----------
74
SQL> update t1_tx set name='xyz'where id=4;
1 row updated.
SQL> commit;
Commit complete.
SQL>
即使我分别开2个会话执行100w次，也不会出现tx锁
session 1:
SQL> declare
2 c number;
3 begin
4 for i in 1 .. 1000000 loop
5 update t1_tx set name = 'shit1' where id = 2;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
session 2:
SQL> declare c number;
2 begin
3 for i in 1 .. 1000000 loop
4 update t1_tx set name = 't-shit' where id = 3;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
session 3:
SQL> set lines 200 pages 999
SQL> col event for a60
SQL> select inst_id,event,count(*) from gv$session where status='ACTIVE' and (wait_class<>'Idle' or event not like 'SQL*Net%') group by inst_id,event order by 1,3;
INST_ID EVENT COUNT(*)
---------- ------------------------------------------------------------ ----------
1 smon timer 1
1 Streams AQ: waiting for time management or cleanup tasks 1
1 Streams AQ: qmn slave idle wait 1
1 Space Manager: slave idle wait 1
1 SQL*Net message to client 1
1 VKTM Logical Idle Wait 1
1 pmon timer 1
1 Streams AQ: qmn coordinator idle wait 1
1 DIAG idle wait 2
1 rdbms ipc message 17
10 rows selected.
SQL> /
INST_ID EVENT COUNT(*)
---------- ------------------------------------------------------------ ----------
1 log file parallel write 1
1 smon timer 1
1 Streams AQ: waiting for time management or cleanup tasks 1
1 Streams AQ: qmn slave idle wait 1
1 buffer busy waits 1
1 Streams AQ: qmn coordinator idle wait 1
1 SQL*Net message to client 1
1 VKTM Logical Idle Wait 1
1 pmon timer 1
1 log buffer space 1
1 Disk file operations I/O 1
1 Space Manager: slave idle wait 1
1 DIAG idle wait 2
1 rdbms ipc message 15
14 rows selected.
SQL>
我们可以看到,不同会话更新同一block中到不同行，不会存在等待，假设更新同一行，那么不提交到情况执行，必然存在等待,这里不再累述。
4、外键
session 1:
SQL> select sid from v$mystat where rownum=1;
SID
----------
74
SQL>
SQL> create table t1 (id number ,name varchar2(20),product_id number);
Table created.
SQL> create table t2 (id number primary key,name varchar2(20));
Table created.
SQL> alter table t1 add constraint FK_PRODUCTID foreign key (PRODUCT_id) references t2 (ID);
Table altered.
SQL> select index_name,table_name from user_indexes where table_name='T1';
no rows selected
SQL> insert into t2 values(1,'aa');
1 row created.
SQL> insert into t2 values(2,'dd');
1 row created.
SQL> insert into t2 values(3,'cc');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t2 values(5,'cc');
1 row created.
未提交。。。。。。。。。。。
session 2：
SQL> select sid from v$mystat where rownum=1;
SID
----------
45
SQL>
SQL> insert into t1 values(1,'xx',5);
hang.......................子表操作会一直挂起
session 3:
SQL> l
1 select inst_id,
2 sid,
3 chr(bitand(p1, -16777216) / 16777215) ||
4 chr(bitand(p1, 16711680) / 65535) "Name",
5 (bitand(p1, 65535)) "Mode",
6 event,
7 sql_id,
8 blocking_session,
9 FINAL_BLOCKING_SESSION
10 from gv$session
11* where event like 'enq%'
SQL> /
INST_ID SID Name Mode EVENT SQL_ID BLOCKING_SESSION FINAL_BLOCKING_SESSION
---------- ---------- ---- ---------- ------------------------------ ------------- ---------------- ----------------------
1 45 TX 4 enq: TX - row lock contention btxh61ngubrv8 74 74
SQL> select sql_text from v$sql where sql_id='btxh61ngubrv8';
SQL_TEXT
--------------------------------------------------------------------------------------------------
insert into t1 values(1,'xx',5)
SQL> select sid,serial#,username,sql_id,status from v$session where sid=74;
SID SERIAL# USERNAME SQL_ID STATUS
---------- ---------- ------------------------------ ------------- --------
74 23 HR INACTIVE
实际上我们可以发现，无论子表有没有主键约束，都会存在这种情况，只有主表操作不提交.
1. 其原因一般有如下几种:
1) 表上存在主键或唯一性约束,多个会话操作同一条记录
2) 表存在主外键读情况,主表不提交,子表那么必须进行等待.
3) 表上存在位图Index,这跟uniqeue index中存在重复值是一样的道理,其中一个会话操作，其他会话必须等待.
4) 表进行自我外键关联,前面的事务不提交，那么会导致后面的会话一直等待.
2. 对于网上说的enq: TX – row lock contention也有可能是在等待index block分裂的情况,从理论上来讲,如果是在等待index block分裂，那么应该还伴有enq: TX – index contention等待事件产生.
3. 对于enq: TX – row lock contention,通过v$session视图查询时，等待会话带lock mode通常为4，而blocker会话带lock mode通常为6，并且一般查询blocker会话的sql_id都为空。这是正常现象，v$session显示是当前状态,而非历史数据.
关于enq: TX – row lock contention的测试和案例分析
1、主键或唯一index
session 1：
SQL> select sid from v$mystat where rownum=1;
SID
----------
74
SQL> create table t1_tx(id number primary key,name varchar2(20));
Table created.
SQL> insert into t1_tx values(1,'wang');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t1_tx values(2,'xxoo');
1 row created.
未提交。。。。。。。。。。。。
session 2：
SQL> select sid from v$mystat where rownum=1;
SID
----------
45
SQL> insert into t1_tx values(2,'xxoo');
hang。。。。。。。。。。。。。。。
session 3:
SQL> set lines 200
SQL> col event for a30
SQL> select inst_id,
2 sid,
3 chr(bitand(p1, -16777216) / 16777215) ||
4 chr(bitand(p1, 16711680) / 65535) "Name",
5 (bitand(p1, 65535)) "Mode",
6 event,
7 sql_id,
8 blocking_session,
9 FINAL_BLOCKING_SESSION
10 from gv$session
11 where event like 'enq%';
INST_ID SID Name Mode EVENT SQL_ID BLOCKING_SESSION FINAL_BLOCKING_SESSION
---------- ---------- ---- ---------- ------------------------------ ------------- ---------------- ----------------------
1 45 TX 4 enq: TX - row lock contention 4s99cmp3khb1b 74 74
SQL>
SQL> select sid,serial#,username,sql_id,status from v$session where sid=74;
SID SERIAL# USERNAME SQL_ID STATUS
---------- ---------- ------------------------------ ------------- --------
74 23 HR INACTIVE
sql_id为空说明为非活动会话，会话等该提交或者回滚。
SQL> select * from v$Lock where block=1;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000008E9E80C0 000000008E9E8138 74 TX 524298 9978 6 0 221 1
block为1,阻塞会话。
可以看出,对于表存在主键或者 unique index 时,一个会话操作主键不提交时,其他会话如果也操作相同的主键时，那么必须进行等待，而其持有的mode＝4；而阻塞blocker的持有mode＝6.
2、Bitmap INDEX
session 1：
SQL> select * from t1_tx;
ID NAME
---------- --------------------
1 wang
2 wang
3 xxoo
4 xxoo
SQL>
SQL> select sid from v$mystat where rownum=1;
SID
----------
74
SQL> create bitmap index idx_bitmap_name on t1_tx(name);
Index created.
SQL> update t1_tx set name='tx' where id=3;
1 row updated.
未提交。。。。。。。。。。。
session 2:
SQL> select sid from v$mystat where rownum=1;
SID
----------
45
SQL> update t1_tx set name='bitmap' where id=4;
hang。。。。。。。。。。。。。
session 3：
SQL> col event for a30
SQL> select inst_id,
2 sid,
3 chr(bitand(p1, -16777216) / 16777215) ||
4 chr(bitand(p1, 16711680) / 65535) "Name",
5 (bitand(p1, 65535)) "Mode",
6 event,
7 sql_id,
8 blocking_session,
9 FINAL_BLOCKING_SESSION
10 from gv$session
11 where event like 'enq%';
INST_ID SID Name Mode EVENT SQL_ID BLOCKING_SESSION FINAL_BLOCKING_SESSION
---------- ---------- ---- ---------- ------------------------------ ------------- ---------------- ----------------------
1 45 TX 4 enq: TX - row lock contention 7wanaturqndn1 74 74
SQL>
SQL> set lines 200 pagesize 200
SQL> select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'));
Enter value for amp: 7wanaturqndn1
old 1: select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'))
new 1: select * from table(dbms_xplan.display_cursor('7wanaturqndn1;sql_id', NULL, 'ALL'))
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID 7wanaturqndn1, child number 0
-------------------------------------
update t1_tx set name='bitmap' where id=4
Plan hash value: 1842098942
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 1 (100)| |
| 1 | UPDATE | T1_TX | | | | |
|* 2 | INDEX UNIQUE SCAN| SYS_C0010951 | 1 | 25 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
SQL> select * from v$Lock where block=1;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000008E9E80C0 000000008E9E8138 74 TX 262171 2920 6 0 264 1
SQL>
SQL> select sid,serial#,username,sql_id,event from v$session where sid=74;
SID SERIAL# USERNAME SQL_ID EVENT
---------- ---------- ------------------------------ ------------- ----------------------------------------------------------------
74 23 HR SQL*Net message from client
SQL> select owner,index_name,index_type from dba_indexes where table_name='T1_TX';
OWNER INDEX_NAME INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
HR IDX_BITMAP_NAME BITMAP
HR SYS_C0012427 NORMAL
我们可以看到，如果表上存在位图index，那么在update时,多个会话同时进行更新，必然出现tx 等待。
此时waiter申请持有的tx 锁mode＝4，而blocker持有的mode＝6,而且通过v$session试图还无法查询到blocker会话到sql_id.
3、数据位于同一block
session 3：
SQL> conn hr/hr;
Connected.
SQL> select dbms_rowid.rowid_object(rowid) obj#,
2 dbms_rowid.rowid_relative_fno(rowid) rfile#,
3 dbms_rowid.rowid_block_number(rowid) block#,
4 dbms_rowid.rowid_row_number(rowid) row#
5 from t1_tx
6 order by 4;
OBJ# RFILE# BLOCK# ROW#
---------- ---------- ---------- ----------
90536 4 4087 0
90536 4 4087 1
90536 4 4087 2
90536 4 4087 3
SQL>
session 1：
SQL> select sid from v$mystat where rownum=1;
SID
----------
45
SQL> select * from t1_tx;
ID NAME
---------- --------------------
1 wang
2 wang
3 tx
4 bitmap
SQL> update t1_tx set name='enmotech' where id=2;
1 row updated.
SQL> commit;
Commit complete.
SQL>
session 2：
SQL> select sid from v$mystat where rownum=1;
SID
----------
74
SQL> update t1_tx set name='xyz'where id=4;
1 row updated.
SQL> commit;
Commit complete.
SQL>
即使我分别开2个会话执行100w次，也不会出现tx锁
session 1:
SQL> declare
2 c number;
3 begin
4 for i in 1 .. 1000000 loop
5 update t1_tx set name = 'shit1' where id = 2;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
session 2:
SQL> declare c number;
2 begin
3 for i in 1 .. 1000000 loop
4 update t1_tx set name = 't-shit' where id = 3;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
session 3:
SQL> set lines 200 pages 999
SQL> col event for a60
SQL> select inst_id,event,count(*) from gv$session where status='ACTIVE' and (wait_class<>'Idle' or event not like 'SQL*Net%') group by inst_id,event order by 1,3;
INST_ID EVENT COUNT(*)
---------- ------------------------------------------------------------ ----------
1 smon timer 1
1 Streams AQ: waiting for time management or cleanup tasks 1
1 Streams AQ: qmn slave idle wait 1
1 Space Manager: slave idle wait 1
1 SQL*Net message to client 1
1 VKTM Logical Idle Wait 1
1 pmon timer 1
1 Streams AQ: qmn coordinator idle wait 1
1 DIAG idle wait 2
1 rdbms ipc message 17
10 rows selected.
SQL> /
INST_ID EVENT COUNT(*)
---------- ------------------------------------------------------------ ----------
1 log file parallel write 1
1 smon timer 1
1 Streams AQ: waiting for time management or cleanup tasks 1
1 Streams AQ: qmn slave idle wait 1
1 buffer busy waits 1
1 Streams AQ: qmn coordinator idle wait 1
1 SQL*Net message to client 1
1 VKTM Logical Idle Wait 1
1 pmon timer 1
1 log buffer space 1
1 Disk file operations I/O 1
1 Space Manager: slave idle wait 1
1 DIAG idle wait 2
1 rdbms ipc message 15
14 rows selected.
SQL>
我们可以看到,不同会话更新同一block中到不同行，不会存在等待，假设更新同一行，那么不提交到情况执行，必然存在等待,这里不再累述。
4、外键
session 1:
SQL> select sid from v$mystat where rownum=1;
SID
----------
74
SQL>
SQL> create table t1 (id number ,name varchar2(20),product_id number);
Table created.
SQL> create table t2 (id number primary key,name varchar2(20));
Table created.
SQL> alter table t1 add constraint FK_PRODUCTID foreign key (PRODUCT_id) references t2 (ID);
Table altered.
SQL> select index_name,table_name from user_indexes where table_name='T1';
no rows selected
SQL> insert into t2 values(1,'aa');
1 row created.
SQL> insert into t2 values(2,'dd');
1 row created.
SQL> insert into t2 values(3,'cc');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t2 values(5,'cc');
1 row created.
未提交。。。。。。。。。。。
session 2：
SQL> select sid from v$mystat where rownum=1;
SID
----------
45
SQL>
SQL> insert into t1 values(1,'xx',5);
hang.......................子表操作会一直挂起
session 3:
SQL> l
1 select inst_id,
2 sid,
3 chr(bitand(p1, -16777216) / 16777215) ||
4 chr(bitand(p1, 16711680) / 65535) "Name",
5 (bitand(p1, 65535)) "Mode",
6 event,
7 sql_id,
8 blocking_session,
9 FINAL_BLOCKING_SESSION
10 from gv$session
11* where event like 'enq%'
SQL> /
INST_ID SID Name Mode EVENT SQL_ID BLOCKING_SESSION FINAL_BLOCKING_SESSION
---------- ---------- ---- ---------- ------------------------------ ------------- ---------------- ----------------------
1 45 TX 4 enq: TX - row lock contention btxh61ngubrv8 74 74
SQL> select sql_text from v$sql where sql_id='btxh61ngubrv8';
SQL_TEXT
--------------------------------------------------------------------------------------------------
insert into t1 values(1,'xx',5)
SQL> select sid,serial#,username,sql_id,status from v$session where sid=74;
SID SERIAL# USERNAME SQL_ID STATUS
---------- ---------- ------------------------------ ------------- --------
74 23 HR INACTIVE
实际上我们可以发现，无论子表有没有主键约束，都会存在这种情况，只有主表操作不提交.
1. 其原因一般有如下几种:
1) 表上存在主键或唯一性约束,多个会话操作同一条记录
2) 表存在主外键读情况,主表不提交,子表那么必须进行等待.
3) 表上存在位图Index,这跟uniqeue index中存在重复值是一样的道理,其中一个会话操作，其他会话必须等待.
4) 表进行自我外键关联,前面的事务不提交，那么会导致后面的会话一直等待.
2. 对于网上说的enq: TX – row lock contention也有可能是在等待index block分裂的情况,从理论上来讲,如果是在等待index block分裂，那么应该还伴有enq: TX – index contention等待事件产生.
3. 对于enq: TX – row lock contention,通过v$session视图查询时，等待会话带lock mode通常为4，而blocker会话带lock mode通常为6，并且一般查询blocker会话的sql_id都为空。这是正常现象，v$session显示是当前状态,而非历史数据.
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
