Oracle 数据库发生等待事件：enq: TX - row lock contention ，排查思路

原创 Lucifer三思而后行 2021-08-21

6187

大家好，这里是 Lucifer三思而后行，专注于提升数据库运维效率。

前言

最近看 awr 报告时，经常会看到一些 enq: TX - row lock contention 的等待事件，所以简单研究一下如何排查，仅为个人所见，如有异议或者修正还请评论指出，谢谢！

通常，产生enq: TX - row lock contention事件的原因有以下几种可能：

不同的session更新或删除同一条记录；
唯一索引有重复索引；
位图索引同时被更新或同时并发的向位图索引字段上插入相同字段值；
并发的对同一个数据块上的数据进行update操作；
等待索引块完成分裂；

现象

应用反馈系统使用存在延时，需要排查情况。查看监控服务器，发现数据库存在 enq: TX - row lock contention 锁的情况。

排查

首先确认发生问题的时间段，然后抓取问题时间段的报告来分析。

AWR 报告

执行 sqlplus / as sysdba @?/rdbms/admin/awrrpt.sql 输入对应时间段的信息，获取 awr 报告。

Top 10 Foreground Events by Total Wait Time

也可通过 awrcrt sqlplus / as sysdba @awrcrt.sql 来获取多段性能指标信息：

Segments by Row Lock Waits

通过观察 awr 报告中段的统计信息章节 Segments by Row Lock Waits 项，可以发现发生锁的对象主要是两张表 A 和 B和 A 表的索引：

与应用确认后，发现其中一张表 A 为核心业务表，暂时怀疑另一张表可能存在问题，这里称之为表 B，所以 A 表暂且不考虑。

SQL ordered by Elapsed Time

通过 🔍 搜索关键字，查出 B 表对应的 UPDATE 语句，执行较为频繁，先记录待查看：

sql_id 为：2xb71ufa5wmrh。

ASH 报告

抓取对应时间段的 ash 报告，查看是否存在有用信息。

执行 sqlplus / as sysdba @?/rdbms/admin/ashrpt.sql 获取报告：

Top User Events

Top SQL with Top Events

Top Blocking Sessions

Top DB Objects

从以上信息，不难看出，与 awr 报告分析出的结果吻合，同样的 sql_id 和对象，并且获取到了 blocking sid。

ADDMRPT 报告

有时，也可以通过抓取 addmrpt 报告来辅助看一下问题，可能有奇效。

执行 sqlplus / as sysdba @?/rdbms/admin/addmrpt.sql 获取 addmrpt 报告：

Summary of Findings

Finding 2: Row Lock Waits

同样都指向了 B 表和 sql_id 为 2xb71ufa5wmrh 的这条语句。

应用确认

经过应用确认，该条 sql 是一张核心业务表的一个触发器发起的，业务表每次新增提交时，会去执行该 sql 更新数据。由于未确认该触发器具体作用，因此无法尝试禁用来观察。

写在最后

经过排查，大部分的阻塞都是因为 sql_id 为 2xb71ufa5wmrh 的语句导致，具体也可以通过以以下 sql语句 来进行查询：

select DISTINCT b.sql_id,c.blocked_sql_id
  from DBA_HIST_ACTIVE_SESS_HISTORY b,
       (select a.sql_id as blocked_sql_id,
       a.blocking_session,
               a.blocking_session_serial#,
               count(a.blocking_session)
          from DBA_HIST_ACTIVE_SESS_HISTORY a
         where event like '%enq: TX - row lock contention%'
           and snap_id between 18835 and 18836
         group by a.blocking_session, a.blocking_session_serial#,a.sql_id
        having count(a.blocking_session) > 100
         order by 3 desc) c
 where b.session_id = c.blocking_session
   and b.session_serial# = c.blocking_session_serial#
   and b.snap_id between 18835 and 18836;
复制

需自行替换对应的快照范围 snap_id 值，查询结果 sql_id 为被阻塞，blocked_sql_id 为阻塞 ID。

📢 如有问题，请及时指正，谢谢！

往期精彩文章

Oracle 一键巡检自动生成 Word 报告
 Oracle 一键安装合集
 Oracle一键安装脚本的 21 个疑问与解答
 Oracle一键巡检脚本的 21 个疑问与解答
 全网首发：Oracle 23ai 一键安装脚本（非 RPM）
Oracle 19C 最新 RU 补丁 19.24 ，一键安装！
Oracle Linux 7.9 一键安装 Oracle 19C
RedHat 9.4(aarch64) 一键安装 Oracle 19C
openEuler 22.03 LTS SP4 一键安装 Oracle 19C RAC
RHEL 7.9 一键安装 Oracle 19C 19.23 RAC
Oracle DataGuard GAP 修复手册
 优化 Oracle：最佳实践与开发规范
 DBA 必备：Linux 软件源配置全攻略
 Linux 一键配置时钟同步全攻略