队列等待之enq: TX - row lock contention

article/2025/8/28 12:02:57

【性能优化】队列等待之enq: TX - row lock contention
问题背景：
客户反映某条sql DELETE SHAREINNERDOC WHERE SOURCEID=:B1<br/>这个执行时间太长

问题解决
1>
查看awr报告：

有队列等待之enq: TX - row lock contention，对应的sql也是客户提供的sql

enq: TX - row lock contention 通常是Application级别的问题。通常情况下，Oracle数据库的等待事件enq: TX - row lock contention会在下列三种情况下会出现。
（一）第一种情况，是真正的业务逻辑上的行锁冲突，如一条记录被多个人同时修改。这种锁对应的请求模式是6（Waits for TX in mode 6 ：A 会话持有row level lock，B会话等待这个lock释放。）。不同的session更新或删除同一个记录。（This occurs when one application is updating or deleting a row that another session is also trying to update or delete. ）
（二）第二种情况，是唯一键冲突（In mode 4,唯一索引），如主键字段相同的多条记录同时插入。这种锁对应的请求模式是4。这也是应用逻辑问题。表上存在唯一索引，A会话插入一个值（未提交），B会话随后也插入同样的值;A会话提交后，enq: TX - row lock contention消失。
（三）第三种情况，是bitmap索引的更新冲突（in mode 4 :bitmap），就是多个会话同时更新bitmap索引的同一个数据块。源于bitmap的特性：位图索引的一个键值，会指向多行记录，所以更新一行就会把该键值指向的所有行锁定。此时会话请求锁的对应的请求模式是4。bitmap索引的物理结构和普通索引一样，也是 B-tree 结构,但它存储的数据记录的逻辑结构为"key_value,start_rowid,end_rowid,bitmap"。
其内容类似这样："‘8088’,00000000000,10000034441,1001000100001111000"
Bitmap是一个二进制，表示 START_ROWID 到 END_ROWID 的记录，1 表示等于 key_value即‘8088’的 ROWID 记录， 0 则表示不是这个记录。
在了解bitmap索引的结构之后，我们就能理解同时插入多条记录到拥有bitmap索引的表时，就会同时更新bitmap索引中一个块中的记录，等于某一个记录被同时更新，自然就会出现行锁等待。插入并发量越大，等待越严重。
（四）其他原因
It could be a primary key problem; a trigger firing attempting to insert, delete, or update a row; a problem with initrans; waiting for an index split to complete; problems with bitmap indexes;updating a row already updated by another session; or something else.

如果数据库一出现enq: TX - row lock contention等待，可以去看v$session和v$session_wait等视图。在v$session和v$session_wait中，如果看到的event列是enq: TX - row lock contention的，就表示这个会话正处于行锁等待。该等待事件的请求模式可以从v$session和v$session_wait的p1列中得到。

1 select sid,
2 chr(bitand(p1, -16777216) / 16777215) ||
3 chr(bitand(p1, 16711680) / 65535) "Name",
4 (bitand(p1, 65535)) "Mode"
5 from v$session_wait
6 where event like 'enq%';

通过这个SQL可以将p1转换为易阅读的文字。

2> sql是个delete语句，查看一下执行计划，是否有索引

 1 SQL_ID 4ggjbjszghd7x2 --------------------3 DELETE SHAREINNERDOC WHERE SOURCEID=:B14 5 Plan hash value: 27490407916 ----------------------------------------------------------------------------------------------------------------7 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |8 ----------------------------------------------------------------------------------------------------------------9 | 0 | DELETE STATEMENT | | | | 9 (100)| |
10 | 1 | DELETE | SHAREINNERDOC | | | | |
11 | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SHAREINNERDOC | 11 | 506 | 9 (0)| 00:00:01 |
12 | 3 | INDEX RANGE SCAN | DSHAREINDEXDOC_SOURCEID | 11 | | 3 (0)| 00:00:01 |
13 ----------------------------------------------------------------------------------------------------------------

可以看到SQL走的是INDEX RANGE SCAN,索引名称DSHAREINDEXDOC_SOURCEID
查看索引的类型为FUNCTION-BASED NORMAL
函数索引（Function-Based Indexes，FBI），它基于对表中列进行计算后的结果创建索引。函数索引在不修改应用程序的逻辑基础上提高了查询性能。如果没有函数索引，那么任何在列上执行了函数的查询都不能使用这个列的索引。当在查询中包含该函数时，数据库才会使用该函数索引。函数索引可以是一个B-Tree索引或位图索引。
用于生成索引的函数可以是算术表达式，也可以是一个包含SQL函数、用户定义PL/SQL函数、包函数，或C调用的表达式。当数据库处理INSERT和UPDATE语句时，它仍然必须计算函数才能完成对语句的处理。
对于函数索引的索引列的函数查询可以通过视图DBA_IND_EXPRESSIONS来实现，通过如下的SQL语句可以查询所有的函数索引：
SELECT * FROM DBA_INDEXES D WHERE D.INDEX_TYPE LIKE 'FUNCTION-BASED%';

查看这个索引是系统自动创建的，

而条件中引用的SOURCEID没有合理的索引，只有一个组合索引，两千万的数据没有合理的索引。。
为SOURCEID字段创建索引并收集统计信息，sql开始引用新的执行计划，等待事件enq: TX - row lock contention也没了