[转帖]模拟enq: TX - row lock contention争用

转载

jinanxiaolaohu6 2024-01-11 17:04:32

文章标签 SQL sql SYS 文章分类 JavaScript 前端开发

enq: TX - row lock contention它表示一个事务正在等待另一个事务释放被锁定的行。这种等待事件通常发生在并发访问数据库时，多个事务试图同时修改同一行数据时会发生行级锁争用。

以下是可能导致 “enq: TX - row lock contention” 等待事件的一些常见原因：
1、并发性高：当多个事务同时访问同一行数据时，就会发生锁争用。这种情况通常在高并发应用中发生。
2、事务时间较长：如果一个事务长时间占用某一行或表资源，其他事务就会等待该资源的释放，这也会导致锁争用。
3、索引设计：如果表没有恰当的索引或索引设计不合理，就会导致查询时扫描大量的数据行，从而增加锁争用的可能性。
4、数据库性能问题：如果数据库的性能出现问题，比如CPU使用率过高，I/O等待时间过长等，就会导致锁争用等待事件的发生。

1、当多个session改变同一行时

session 1
SQL> create table test_table (
2 id number primary key,
3 name varchar2(50),
4 value number
5 );

Table created.

SQL> insert into test_table values (1, 'test', 100);

1 row created.

SQL> commit
2 ;

Commit complete.

SQL> update test_table set value = 200 where id = 1;

1 row updated.

session 2

SQL> update test_table set value = 300 where id = 1;

session 3

SQL> @swc sid||':'||serial#||':'||sql_id||':'||event||':'||p1||':'||p2 1=1
old 5: REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ')
new 5: REPLACE(SYS_CONNECT_BY_PATH(sid||':'||serial#||':'||sql_id||':'||event||':'||p1||':'||p2, '->'), '->', ' -> ')
old 34: START WITH (ses.state='WAITING' AND ses.wait_class!='Idle') AND &2
new 34: START WITH (ses.state='WAITING' AND ses.wait_class!='Idle') AND 1=1

SESSIONS PATH
---------- --------------------------------------------------------------------------------------------------------------------------------------------
1 -> 254:11896:6f702u1hk7xgg:enq: TX - row lock contention:1415053318:393216 -> 237:20405::SQL*Net message from client:1650815232:1
1 -> 254:11896:6f702u1hk7xgg:enq: TX - row lock contention:1415053318:393216

SQL> select * from v$lock where type in ('TM','TX');

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ---------- ----------
000000007D49C910 000000007D49C940 254 TX 196618 2441 0 6 198 0 0
00007F2437305AC8 00007F2437305AF0 237 TM 76931 0 3 0 203 0 0
00007F2437305AC8 00007F2437305AF0 254 TM 76931 0 3 0 198 0 0
00000000790CD360 00000000790CD398 237 TX 196618 2441 6 0 203 1 0

SQL> select * from v$locked_object;

XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE CON_ID
---------- ---------- ---------- ---------- ---------- ------------------------------ -------------------- --------------- ----------- ----------
3 10 2441 76931 237 SYS oracle 8108 3 0
0 0 0 76931 254 SYS oracle 7650 3 0

237和254同时在76931对象上加了3级共享锁，237号会话持有事务

2、当多个session导致唯一键冲突

session 1

SQL> CREATE TABLE test_table (
2 id NUMBER PRIMARY KEY,
3 name VARCHAR2(50) UNIQUE
4 );

Table created.

SQL> INSERT INTO test_table (id, name) VALUES (1, 'test');

1 row created.

session 2

SQL> INSERT INTO test_table (id, name) VALUES (1, 'test');

session 3

SQL> @swc sid||':'||serial#||':'||sql_id||':'||event||':'||p1||':'||p2 1=1
old 5: REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ')
new 5: REPLACE(SYS_CONNECT_BY_PATH(sid||':'||serial#||':'||sql_id||':'||event||':'||p1||':'||p2, '->'), '->', ' -> ')
old 34: START WITH (ses.state='WAITING' AND ses.wait_class!='Idle') AND &2
new 34: START WITH (ses.state='WAITING' AND ses.wait_class!='Idle') AND 1=1

SESSIONS PATH
---------- --------------------------------------------------------------------------------------------------------------------------------------------
1 -> 254:11896:5usmba4xn6bwc:enq: TX - row lock contention:1415053316:524291 -> 237:4551::SQL*Net message from client:1650815232:1
1 -> 254:11896:5usmba4xn6bwc:enq: TX - row lock contention:1415053316:524291


SQL> select * from v$lock where type in ('TM','TX') order by sid;

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ---------- ----------
000000007CC26AE8 000000007CC26B20 237 TX 524291 2425 6 0 86 1 0
00007F2437B866F0 00007F2437B86718 237 TM 76933 0 3 0 86 0 0
000000007EF5B3A8 000000007EF5B3E0 254 TX 589855 2470 6 0 79 0 0
000000007D49C4E8 000000007D49C518 254 TX 524291 2425 0 4 79 0 0
00007F2437B866F0 00007F2437B86718 254 TM 76933 0 3 0 79 0 0

237和254同时在对象76933加了共享锁,也同时获得了6级锁，但是254需要申请4级锁导致阻塞

session 1

SQL> commit;

Commit complete.

session 2

SQL> INSERT INTO test_table (id, name) VALUES (1, 'test');
INSERT INTO test_table (id, name) VALUES (1, 'test')
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C007696) violated

区别：如果update导致的enq: TX - row lock contention，为数据行上锁导致，与程序设置有关。
如果insert导致enq: TX - row lock contention，则为唯一约束导致的，好的解决方案是使用序列生成唯一键。

session 1

SQL> CREATE SEQUENCE my_sequence
2 START WITH 1
3 INCREMENT BY 1
4 MAXVALUE 999999999
5 CYCLE;

Sequence created.

SQL> INSERT INTO test_table (id, name) VALUES (my_sequence.nextval, 'c');

1 row created.

session 2

SQL> INSERT INTO test_table (id, name) VALUES (my_sequence.nextval, 'd');

1 row created.

session 3

SQL> @swc sid||':'||serial#||':'||sql_id||':'||event||':'||p1||':'||p2 1=1
old 5: REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ')
new 5: REPLACE(SYS_CONNECT_BY_PATH(sid||':'||serial#||':'||sql_id||':'||event||':'||p1||':'||p2, '->'), '->', ' -> ')
old 34: START WITH (ses.state='WAITING' AND ses.wait_class!='Idle') AND &2
new 34: START WITH (ses.state='WAITING' AND ses.wait_class!='Idle') AND 1=1

no rows selected

session 1 提交

SQL> commit;

Commit complete.

session 2 提交

SQL> commit;

Commit complete.

SQL> select * from SQL> commit;
select * from SQL> commit
*
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL>
SQL> Commit complete.
2
SQL>
SQL> select * from test_table;

ID NAME
---------- ----------------------------------------------------------------------------------------------------
6 c
7 d

测试2

SQL> CREATE TABLE test_table (
2 id NUMBER PRIMARY KEY,
3 name VARCHAR2(50)
4 );

Table created.

SQL>
SQL> CREATE BITMAP INDEX test_index ON test_table(name);

Index created.

SQL> INSERT INTO test_table
2 SELECT LEVEL, 'Name' || MOD(LEVEL, 5) FROM DUAL CONNECT BY LEVEL <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

session 1

SQL> UPDATE test_table SET name = 'NewName' WHERE name = 'Name1';

session 2

SQL> UPDATE test_table SET name = 'NewName' WHERE name = 'Name2';

session 3

SQL> SELECT * FROM test_table WHERE name = 'Name1' for update;

session 4

SQL> sELECT * FROM test_table WHERE name = 'Name2' for update;

等待

EVENT SID
------------------------------ ----------
enq: TX - row lock contention 35
enq: TX - row lock contention 41
resmgr:cpu quantum 237
resmgr:cpu quantum 254



EVENT SID
------------------------------ ----------
enq: TX - row lock contention 35
enq: TX - row lock contention 41
SQL*Net message from client 237
enq: TX - row lock contention 254

SQL> @swc sid||':'||serial#||':'||sql_id||':'||event||':'||p1||':'||p2 1=1
old 5: REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ')
new 5: REPLACE(SYS_CONNECT_BY_PATH(sid||':'||serial#||':'||sql_id||':'||event||':'||p1||':'||p2, '->'), '->', ' -> ')
old 34: START WITH (ses.state='WAITING' AND ses.wait_class!='Idle') AND &2
new 34: START WITH (ses.state='WAITING' AND ses.wait_class!='Idle') AND 1=1

SESSIONS PATH
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 -> 254:11896:asyw0r1gaxchh:enq: TX - row lock contention:1415053316:196634
1 -> 41:1567:g9gsass4u3d7z:enq: TX - row lock contention:1415053318:262175
1 -> 41:1567:g9gsass4u3d7z:enq: TX - row lock contention:1415053318:262175 -> 254:11896:asyw0r1gaxchh:enq: TX - row lock contention:1415053316:196634 -> 237:4551:f1dckkc3aj43j:SQL*Net message from client:1650815232:1
1 -> 41:1567:g9gsass4u3d7z:enq: TX - row lock contention:1415053318:262175 -> 254:11896:asyw0r1gaxchh:enq: TX - row lock contention:1415053316:196634
1 -> 254:11896:asyw0r1gaxchh:enq: TX - row lock contention:1415053316:196634 -> 237:4551:f1dckkc3aj43j:SQL*Net message from client:1650815232:1
1 -> 35:61574:c93b0u0y4m43y:enq: TX - row lock contention:1415053318:196634 -> 237:4551:f1dckkc3aj43j:SQL*Net message from client:1650815232:1
1 -> 35:61574:c93b0u0y4m43y:enq: TX - row lock contention:1415053318:196634

7 rows selected.

SQL> select sql_id,sql_text from v$sql where sql_id in ('f1dckkc3aj43j','asyw0r1gaxchh','g9gsass4u3d7z','f1dckkc3aj43j','c93b0u0y4m43y');

SQL_ID SQL_TEXT
-------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
f1dckkc3aj43j UPDATE test_table SET name = 'NewName' WHERE name = 'Name1'
asyw0r1gaxchh UPDATE test_table SET name = 'NewName' WHERE name = 'Name2'
c93b0u0y4m43y SELECT * FROM test_table WHERE name = 'Name1' for update
g9gsass4u3d7z sELECT * FROM test_table WHERE name = 'Name2' for update

SQL> select * from v$lock where type in ('TM','TX') order by type;

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ---------- ----------
00007FF0C6CE16E0 00007FF0C6CE1708 41 TM 76943 0 3 0 431 0 0
00007FF0C6CE16E0 00007FF0C6CE1708 254 TM 76943 0 3 0 441 0 0
00007FF0C6CE16E0 00007FF0C6CE1708 237 TM 76943 0 3 0 443 0 0
00007FF0C6CE16E0 00007FF0C6CE1708 35 TM 76943 0 3 0 440 0 0
000000007EF5B3A8 000000007EF5B3E0 254 TX 262175 2020 6 0 441 1 0
000000007EF39868 000000007EF398A0 237 TX 196634 2448 6 0 443 1 0
000000007D49C0D8 000000007D49C108 41 TX 262175 2020 0 6 431 0 0
000000007D49BF38 000000007D49BF68 254 TX 196634 2448 0 4 382 0 0
000000007D49BCC8 000000007D49BCF8 35 TX 196634 2448 0 6 440 0 0

9 rows selected.

SQL> select * from v$locked_object;

XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE CON_ID
---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ---------- ----------- ----------
0 0 0 76943 35 SYS oracle 7843 3 0
0 0 0 76943 41 SYS oracle 11893 3 0
3 26 2448 76943 237 SYS oracle 8108 3 0
4 31 2020 76943 254 SYS oracle 7650 3 0

锁定的对象还是76943，这次由237阻塞254，254阻塞41，237还阻塞35，
也就是g9gsass4u3d7z–>asyw0r1gaxchh–>f1dckkc3aj43j
四个会话都在76943对象上加了共享锁，254，237加了6级事物锁，237申请4级锁，35、41申请6级锁。
由于每次一行变化都要计算位图值，导致DML本身的性能下降，多个会话同时执行DML时会出现过度的TX锁争用。

commit 237号会话

SQL> @swc sid||':'||serial#||':'||sql_id||':'||event||':'||p1||':'||p2 1=1
old 5: REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ')
new 5: REPLACE(SYS_CONNECT_BY_PATH(sid||':'||serial#||':'||sql_id||':'||event||':'||p1||':'||p2, '->'), '->', ' -> ')
old 34: START WITH (ses.state='WAITING' AND ses.wait_class!='Idle') AND &2
new 34: START WITH (ses.state='WAITING' AND ses.wait_class!='Idle') AND 1=1

SESSIONS PATH
---------- -----------------------------------------------------------------------
1 -> 41:1567:g9gsass4u3d7z:enq: TX - row lock contention:1415053318:262175 -> 254:11896:asyw0r1gaxchh:SQL*Net message from client:1650815232:1
1 -> 41:1567:g9gsass4u3d7z:enq: TX - row lock contention:1415053318:262175