Oracle-enq:TX-row-contention等待场景
前言：
产生TX锁等待的原因一般都是应用程序的逻辑问题，表，索引设计问题以及性能问题导致，本文主要讨论TX锁的等待产生场景。
等待场景:
1 应用代码逻辑层设计问题导致同时修改相同数据引发锁等待。
2 事务没有正常commit/rollback引发锁等待。
3 主键或者唯一键冲突引发锁等待。
4 位图索引引发锁等待。
5 大事务回滚导致的锁等待。
6 慢SQL导致的锁等待。
7 查询包含事务调用导致的锁等待。
表数据:
table_name:test.test_1 SQL> desc test.test_1; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(13) NAME1 VARCHAR2(30) NAME2 VARCHAR2(30) NAME3 VARCHAR2(30) STATUS VARCHAR2(2) primary key(id) bitmap index(status) ID NAME1NAME2 NAME3 STATUS---------- ------------------------------ ------------------------------ ------------------------------ -- 1 1aaaaaa 1bbbbbbbbb1ccccccccc 1 2 2aaaaaa 2bbbbbbbbb2ccccccccc 0 3 3aaaaaa 3bbbbbbbbb3ccccccccc 0 4 4aaaaaa 4bbbbbbbbb4ccccccccc 0 5 5aaaaaa 5bbbbbbbbb5ccccccccc 1场景一:应用代码逻辑层设计问题导致同时修改相同数据引发锁等待。
session:sid:32
#执行一条update，更新id=1的数据SQL> update test.test_1set name1='1bbbb'where id=1; 1 row updatedsession:sid:45
#执行一条update，更新id=1的数据#会话45执行被会话32堵塞，TX锁级别为6SQL> update test.test_1set name1='2bbbb'where id=1; ---->blockingBLOCKING_STATUS--------------------------------------------------------------------------------TEST@rac1 ( SID=32 ) is blocking SYS@rac1 ( SID=45 )有时还会引发死锁问题
session:sid:32
#执行一条update，更新id=2的数据SQL> update test.test_1set name1='2bbbb'where id=2; 1 row updated.session:sid:45
#执行一条update，更新id=1的数据SQL> update test.test_1set name1='1bbbb'where id=1;1 row updated.session:sid:32
#执行一条update，更新id=1的数据#会话32执行被会话45堵塞，TX锁级别为6SQL> update test.test_1set name1='1aaaa'where id=1;---->blockingBLOCKING_STATUS--------------------------------------------------------------------------------SYS@rac1 ( SID=45 ) is blocking TEST@rac1 ( SID=32 )session:sid:45
#执行一条update，更新id=2的数据#会话45执行被会话32堵塞，TX锁级别为6SQL> update test.test_1set name1='2bbbb'where id=2;---->blockingBLOCKING_STATUS--------------------------------------------------------------------------------TEST@rac1 ( SID=32 ) is blocking SYS@rac1 ( SID=45 )session:sid:32
#此时会话32报00060死锁错误，执行的sql被回滚但事务并没回滚SQL> update test.test_1set status=0where id=1; update test.test_1 *ERROR at line 1:ORA-00060: deadlock detected while waiting for resource场景二:事务没有正常commit/rollback引发锁等待。
1 会话出现超时导致死链接，导致事务无法正常提交。
2 代码逻辑设计出现问题导致没有完成commit/rollback流程，特别是在对一些异常捕获的流程处理。
3 数据库性能问题，bug导致的无法正常commit/rollback。
场景三:主键或者唯一键冲突引发锁等待。
session:sid:32
#会话32插入一条主键为6的数据insert into test.test_1 values(6,'6aaaaaa','6bbbbbbb','6ccccccc',0);1 row created.session:sid:45
#会话45也插入一条主键为6的数据#会话45执行被会话32堵塞，TX锁级别为4insert into test.test_1 values(6,'6aaaaaa','6bbbbbbb','6ccccccc',0);---->blockingBLOCKING_STATUS--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------TEST@rac1 ( SID=32 ) is blocking SYS@rac1 ( SID=45 ) INST_ID SID TY ID1 ID2 OWNER OBJECT_NAME LMODE REQUEST---------- ---------- -- ---------- ---------- -------------------- ---------------------------------------- ---------- ---------- 1 32 TX 720926 29326 0 1 32 TM 880600 TEST TEST_1 3 0 1 45 TX 983040 23396 0 1 45 TM 880600 TEST TEST_1 3 0 1 45 TX 720926 29320 4需要等到会话32提交了事务，会话45才会报唯一键冲突。
ERROR at line 1:ORA-00001: unique constraint (TEST.SYS_C0011112) violated场景四:位图索引引发锁等待。
session:sid:32
#会话32更新表test_1 id=1的数据SQL> update test.test_1 set status=2 where id=1; 1 row updated.session:sid:45
#会话45更新test_1 id=2的数据#会话45执行被会话32堵塞，TX锁级别为4SQL> update test.test_1 2 set status=3 3 where id=2;---->blockingBLOCKING_STATUS---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------TEST@rac1 ( SID=32 ) is blocking SYS@rac1 ( SID=45 ) INST_ID SID TY ID1 ID2 OWNER OBJECT_NAME LMODE REQUEST---------- ---------- -- ---------- ---------- -------------------- ---------------------------------------- ---------- ---------- 1 32 TX 1114133 4282 6 0 1 32 TM 880600 TEST TEST_1 3 0 1 45 TX 1179656 2547 6 0 1 45 TM 880600 TEST TEST_1 3 0 1 45 TX 1114133 4282由于位图索引使用位图来记录数据，不同的DML更新不同的行可能争用同一段位图段，所以会产生锁等待。
场景五:大事务回滚导致的锁等待。
session:sid:55
#会话55更新表test_1 id=2的2百万数据SQL> update test.test_1set name1='aaaaaaaaaaa'where id=2;#从操作系统kill了会话55，让会话55事务进行回滚kill -9 15699ERROR at line 1:ORA-03135: connection lost contactProcess ID: 15699Session ID: 55 Serial number: 3session:sid:29
#会话29更新表test_1 id=2数据#会话55执行被会话29堵塞，TX锁级别为6update test.test_1set name1='xxx'where id=2 and rownumblockingBLOCKING_STATUS---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------TEST@rac1 ( SID=55 ) is blocking SYS@rac1 ( SID=29 )#堵塞的会话正是当前正在回滚的事务INST_ID ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_TIME START_SCNB START_SCNW START_UEXT START_UBAFIL START_UBABLK START_UBASQN START_UBAREC SES_ADDR FLAG SPA REC NOU PTX------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- ---------- ---------- ---------- ------------ ------------ ------------ ------------ ---------------- ---------- --- --- --- ---NAME PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN PTX_XIDSLT PTX_XIDSQN DSCN-B DSCN-W USED_UBLK USED_UREC LOG_IO PHY_IO CR_GET---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- CR_CHANGE START_DAT DSCN_BASE DSCN_WRAP START_SCN DEPENDENT_SCN XID PRV_XID PTX_XID---------- --------- ---------- ---------- ---------- ------------- ---------------- ---------------- ---------------- 1 000000007E907E60 17 144285 5 49614 885 66 ACTIVE 05/19/22 18:28:085967266 0 0 5 226 751 40 00000000827AABC8 7683 NO NO NO NO 00 0 0 0 0 0 0 43346 4194304 12749349 73104 2185496 2092747 19-MAY-22 0 0 5967266 0 11000E00BD100000 0000000000000000 0000000000000000场景六:慢SQL导致的锁等待。
1 事务里面包含多个执行sql或者存在慢SQL导致提交时间延长。
#执行一条update，更新id=1的数据,并执行数据查询#这时候数据查询的速度会直接影响事务的提交速度，如果查询过慢，就可能出现锁等待的情况SQL> update test.test_1set name1='1bbbb'where id=1;SQL> select count(*) from test.test_2 where object_id=1;2 自身DML操作语句执行效率过低，如全表扫描，或者多表关联操作。
场景七:查询包含事务调用导致的锁等待。
#创建一个带匿名事务的函数create or replace function fun_test return varchar2aspragma autonomous_transaction;begin update test.test_1 set name1='aaa' where id='6'; commit; return 1;end;/session:sid:52
#会话52执行带函数的查询select fun_test from dual;session:sid:29
#会话29执行update id=6#会话29执行被会话52堵塞，TX锁级别为6,直到会话52执行语句结束update test.test_1set name1='xxx'where id=6 and rownumblockingBLOCKING_STATUS--------------------------------------------------------------------------------TEST@rac1 ( SID=52 ) is blocking SYS@rac1 ( SID=29 )
