导航：首页 > 数据库 >

enq: TX – row lock contention的测试和案例分析

发表于：2024-11-28 作者：千家信息网编辑

千家信息网最后更新 2024年11月28日，参考：http://www.killdb.com/2015/07/13/%E5%85%B3%E4%BA%8Eenq-tx-row-lock-contention%E7%9A%84%E6%B5%8B%E

千家信息网最后更新 2024年11月28日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显示是当前状态,而非历史数据.

很赞哦！