模拟enq: tx -m6米乐安卓版下载

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，则为唯一约束导致的，好的m6米乐安卓版下载的解决方案是使用序列生成唯一键。

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