时间：2021-07-01 10:21:17 帮助过：29人阅读
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
---------- 75SQL> 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
---------- 200SQL> 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 675 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 75SQL> 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 179SQL> 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 2361SQL> 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=1001SQL> 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 23611 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 414 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$lock13 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 22131 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 414 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=1001SQL> 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$lock13 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 22111 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
---------- 75SQL> insert into department values(1000, ‘sales‘);
1 row created.
会话2：
SQL> select sid from v$mystat where rownum=1;
SID
---------- 200SQL> 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
---------- 73SQL> 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 23711 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 2371SQL>
另外遇到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 0SQL> set linesize 240;
SQL> select owner, object_name from dba_objects
2 where object_id=75389; OWNER OBJECT_NAME
------------------------------ ------------------TEST TESTSQL> 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_o