你闭嘴！应用不改谁来改？都是DBA的错...

每逢到年底，就是各种故障频发，对于我从业15年的一个DBA老兵，已经是一种无法逃避的规律了，今年做了很多业务的迁移升级，数据库也提升到了19C的版本，这不最近业务频发的Oracle-enq: TX - row lock contention让人头疼，DBA说并发高，业务逻辑的问题，开发说程序跑了好几年了，一直很稳定，不会出现这种问题！

那么遇到类似的问题，如何找到堵塞链源头呢？我们该彻底的排查那些问题呢？

1.row lock contention解读

'enq: TX - row lock contention'，行锁争用等待; 当需要加锁的行数据上已经存在锁时，会产生该等待事件，直到已经获得锁的对象释放（会话结束、提交、回滚等），后续对象才能加上锁。

在这里要特别解读下ASH报告里P1、P2、P3参数

记住不同的等待事件，3个值各不相同哦
P1：name|mode
name：TYPE，mode：LMODE
P2：usn<<16 | slot
usn：v$transaction.XIDUSN 回滚段编号，
slot：v$transaction.XIDSLOT 事务槽号
P3：sequence
sequence：v$transaction.XIDSQN 序列号
--通过以下数据字典获取
select name,wait_class,PARAMETER1,PARAMETER2,PARAMETER3 
from v$event_name where name='enq: TX - row lock contention';
SQL > select SID||','||SERIAL# sid#,saddr,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#,p1,p2,p3 from v$session where event like 'enq: TX - row lock contention';
select chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1, 16711680)/65535) "Lock",bitand(p1, 65535) "Mode",TRUNC(P2 / POWER(2, 16)) AS XIDUSN,BITAND(P2, TO_NUMBER('FFFF', 'XXXX')) + 0 AS XIDSLOT,P3 XIDSQN from v$session where event = 'enq: TX - row lock contention';
select ADDR,XIDUSN,XIDSLOT,XIDSQN from v$transaction;

2.行锁发生的原因

常见的TX锁等待原因:

1 应用代码逻辑层有问题，导致同时修改相同数据引发锁等待。
2 应用代码逻辑层有问题，导致事务不提交引发锁等待。
3 主键或者唯一键冲突引发锁等待。
4 位图索引维护引发锁等待。
5 事务回滚导致的锁等待。
6 慢SQL导致的锁等待。

3.用哪些报告分析

结合awr,ash或者addm综合分析

@?/rdbms/admin/awrrpt
@?/rdbms/admin/ashrpt
@?/rdbms/admin/addmrpt

4.报告综合分析

通过报告综合分析，确定根因，寻找“犯罪嫌疑人”

4.1 AWR报告解读

从Top Event等待查看enq:TX的DBTIME 占比以及平均等待wait avg从Row Lock Waits去确认发生锁等待的对象

4.2 ASH报告解读

从Top SQL with Top Events确认产生锁等待的SQL_TEXT

从Top Blocking Sessions确认主要的堵塞会话信息。

4.3 ADDM报告解读

addm可以从里面发现等待的对象，堵塞源以及等待的SQL

5.等待事件信息

#查看等待事件的信息
set linesize 400
set pagesize 400
col sample_time for 40
col sql_text for a60
col event for a40
select to_char(a.SAMPLE_TIME,'yyyymmdd hh24:mi:ss'),a.SESSION_ID,a.SESSION_SERIAL#,a.event,a.p1,a.p2,a.p3,a.sql_id,a.BLOCKING_SESSION,a.BLOCKING_SESSION_SERIAL#,b.sql_text
from DBA_HIST_ACTIVE_SESS_HISTORY a,dba_hist_sqltext b                                                                                                  
where a.SAMPLE_TIME between to_date('2024-05-16 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2024-05-17 00:00:00','yyyy-mm-dd hh24:mi:ss')  and a.sql_id=b.sql_id                                                              
AND D.EVENT = 'enq: TX - row lock contention'
order by to_char(a.SAMPLE_TIME,'yyyymmdd hh24:mi:ss') ;
#查看争用对象       
SELECT  D.SESSION_ID,
      D.SESSION_SERIAL#,
      D.current_obj#,
      D.current_file#,
      D.current_block#,
      D.current_row#,D.EVENT,
      D.P1TEXT,
      D.P1,
      D.P2TEXT,
      D.P2,
      CHR(BITAND(P1, -16777216) / 16777215) ||
      CHR(BITAND(P1, 16711680) / 65535) "Lock",
      BITAND(P1, 65535) "Mode",
      D.BLOCKING_SESSION,
      D.BLOCKING_SESSION_STATUS,
      D.BLOCKING_SESSION_SERIAL#,
      D.SQL_ID,
      TO_CHAR(D.SAMPLE_TIME, 'YYYYMMDDHH24MISS') SAMPLE_TIME
 FROM DBA_HIST_ACTIVE_SESS_HISTORY D
WHERE D.SAMPLE_TIME BETWEEN TO_DATE('2024-05-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
     TO_DATE('2024-05-17 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
  AND D.EVENT = 'enq: TX - row lock contention'
order by D.BLOCKING_SESSION;
  SELECT * FROM dba_objects D WHERE D.object_id=87620;

6.查询堵塞链

如果要追踪历史的记录，以下查询

可以通过以以下 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 48835 and 48836
         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 48835 and 48836;

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

如果你的行锁是当前发生的，以下语句追踪

select *
  from (select a.inst_id, a.sid, a.serial#,
               a.sql_id,
               a.event,
               a.status,
               connect_by_isleaf as isleaf,
               sys_connect_by_path(a.SID||'@'||a.inst_id, ' <- ') tree,
               level as tree_level
          from gv$session a
         start with a.blocking_session is not null
        connect by (a.sid||'@'||a.inst_id) = prior (a.blocking_session||'@'||a.blocking_instance))
 where isleaf = 1
 order by tree_level asc;

总结

出现了enq: TX - row lock contention，现在痛点是业务上不想改逻辑，在分析时，要收集等待的对象，操作的SQL，堵塞的上下游，等待对象TX锁的持有级别，请求级别以及SQL的性能来确认TX锁等待的原因。