Oracle性能优化之'enq: TX - row lock contention'队列等待事件

Oracle性能优化之'enq: TX - row lock contention'队列等待事件

问题背景：
客户反馈系统反应缓慢，协助排查优化

1> 查看问题时段的AWR报告



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

对应的sql

DELETE tablename WHERE ID=:B1

关于等待事件enq: TX - row lock contention ：

通常是Application级别的问题。

Oracle数据库的等待事件enq: TX - row lock contention会在下列三种情况下会出现。

1.第一种情况，是真正的业务逻辑上的行锁冲突

如一条记录被多个人同时修改。

这种锁对应的请求模式是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. ）

2.第二种情况，是唯一键冲突（In mode 4,唯一索引）

如主键字段相同的多条记录同时插入。这种锁对应的请求模式是4。

这也是应用逻辑问题。表上存在唯一索引，A会话插入一个值（未提交），B会话随后也插入同样的值;A会话提交后，enq: TX - row lock contention消失。

3.第三种情况，是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索引中一个块中的记录，等于某一个记录被同时更新，自然就会出现行锁等待。插入并发量越大，等待越严重。

4.其他原因
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列中得到。

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

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

AWR报告中sql是个delete语句，查看一下执行计划，是否有索引

SQL_ID 4ggjbjszghd7x
--------------------
DELETE SHAREINNERDOC WHERE SOURCEID=:B1




Plan hash value: 2749040791
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 9 (100)| |
| 1 | DELETE | SHAREINNERDOC | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SHAREINNERDOC | 11 | 506 | 9 (0)| 00:00:01 |
| 3 | INDEX RANGE SCAN | DSHAREINDEXDOC_SOURCEID | 11 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

SQL走的是INDEX RANGE SCAN,索引名称DSHAREINDEXDOC_SOURCEID
查看索引的类型为FUNCTION-BASED NORMAL

判断客户队列等待的原因为第二种原因，调整应用逻辑及时commit ，避免A会话删除一个值（未提交），B会话随后也删除同样的值;A会话提交后，enq: TX - row lock contention消失。