概念描述

行锁，对应等待时间’enq: TX – row lock contention’。是应用环境中经常碰到的故障现象。当发生行锁时，往往意味着大量业务会话被阻塞。造成业务功能无法进行。因此需要尽快排查出问题源头及原因。采取有效的处理措施。

关于行锁等待事件enq: TX – row lock contention ，通常是Application级别的问题。常见的TX锁等待原因：
1 应用代码逻辑层有问题，导致同时修改相同数据引发锁等待。
2 应用代码逻辑层有问题，导致事务不提交引发锁等待。
3 主键或者唯一键冲突引发锁等待。
4 位图索引维护引发锁等待。
5 事务回滚导致的锁等待。
6 慢SQL导致的锁等待。

根据经验，大多数行锁的产生都来自于事务未能及时提交、SQL低效等原因。当发生行锁问题时，对应用的影响是很大的，应用会报出无法完成正常事务。就需要快速的排查问题原因，并通过相应手段避免行锁持续的影响。

行锁问题排查：

为了演示发生行锁问题时如果快速排查。设计了如下脚本：
实验脚本：

--会话1：对目标行做更新，但不提交update t1 set OBJECT_NAME=OBJECT_NAME||'1' where object_id=110;--会话2：模拟其他应用对相同行的更新begin  p_test_update;end; /--会话3：有用户视图编译该程序alter procedure p_test_update compile;--会话4：新的会话继续更新相同行update t1 set OBJECT_NAME=OBJECT_NAME||'BBB' where object_id=110;

通过上述脚本，模拟了两条阻塞链：
阻塞链1：由于会话1未提交导致的后续应用及编译动作均被阻塞；
阻塞链2：会话1未提交导致的其他会话相同行更新被阻塞。
如下展示如何快速的获得阻塞基本情况：

备份数据

由于行锁问题的影响较大，发生阻塞时为了降低对业务的影响。部分场景可能需要通过重启应用等方式来打破锁源头的持有。为了在事后能获得阻塞的相关信息。需要通过脚本，将容易丢失的会话数据备份到物理表中：

CREATE TABLE OPEN_TABLEBAK AS select * from gv$open_cursor;CREATE TABLE ASH_TABLEBAK AS select * from gv$active_session_history;

查询锁及当前SQL

当锁正在发生时，可以及时查看当前正在执行语句及锁定情况：
1.获取锁模式：

set linesize 400set pagesize 400col object_name for a40select a.inst_id,a.sid,a.type,a.id1,a.id2,b.owner,b.object_name,a.lmode,a.requestfrom gv$lock a,dba_objects bwhere a.id1=b.object_id(+) and a.type in ('TM','TX')order by a.inst_id,a.sid;

锁为6级排它锁。确定为更新相同数据导致。

2.查看阻塞对象及会话源头：

set lines 200 pages 40col SID for 9999col OWNER for a10col OBJECT_NAME for a20col event for a30col b_sid for 9999col f_b_sid for 9999SELECT T2.SID,       T2.SERIAL#,       T3.OWNER,       T3.OBJECT_NAME,       t2.event,blocking_session as b_sid,final_blocking_session as f_b_sid,       t2.p1,t2.p2,t2.p3,round(t2.wait_time_micro/1E6,4) waittime,       T2.LOGON_TIME,t1.LOCKED_MODE  FROM GV$LOCKED_OBJECT T1, GV$SESSION T2, DBA_OBJECTS T3 WHERE T1.SESSION_ID = T2.SID and t1.INST_ID=t2.INST_ID   AND T1.OBJECT_ID = T3.OBJECT_ID ORDER BY T2.LOGON_TIME;

会话141/17，均被会话12锁阻塞。

3.查看正在执行语句：获取对应会话当前SQL。

col username format a13col prog format a10 trunccol sql_text format a60 trunccol sid format a12col sql_id format a16col child for 99999col execs format 9999999col sqlprofile format a22col avg_ela for 999999.99col last_ela for 999999col event format a15select       sid||','||serial# sid,       substr(a.event,1,15) event,       b.sql_id||','||child_number sql_id,           a.inst_id,       plan_hash_value,       executions execs,       (elapsed_time/decode(nvl(executions,0),0,1,executions))/1000000 avg_ela,       last_call_et last_ela,       sql_textfrom gv$session a, gv$sql bwhere status = 'ACTIVE'and username is not nulland a.sql_id = b.sql_idand a.sql_child_number = b.child_numberand a.inst_id=b.INST_IDand sql_text not like '%from gv$session a, gv$sql b%'and a.program not like '%(P%)';

当前正在执行3条语句。更新T1表时发生行锁。阻塞源头会话为12。但会话12等待事件为SQL*Net message from client。怀疑当前会话为空闲状态。

查询阻塞链

1.当锁正在发生时，通过gv$session查看阻塞链：

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;

阻塞链情况：包含2条阻塞链
1节点的 12 会话，阻塞了141会话，141会话又阻塞了140会话。
1节点的 12 会话，阻塞链1节点17会话。

2.如果锁已经不存在，需要通过ASH视图/ASH备份表来查询阻塞链：

col SID_CHAIN for a30col SQL_CHAIN for a40col EVENT_CHAIN for a50with ash as (SELECT INST_ID,session_id,SQL_ID,event,blocking_session,program,to_char(sample_time,'yyyymmdd hh24:mi:ss') sample_time,sample_id,blocking_inst_idFROM gv$active_session_history WHERE  sample_time >= to_date('2022-06-10 22:10:52','yyyy-mm-dd hh24:mi:ss'))select a.*,ash.sql_id from (select sample_time,blocking_session final_block,sys_connect_by_path(session_id,',') SID_CHAIN,sys_connect_by_path(SQL_ID,',') sql_CHAIN,sys_connect_by_path(EVENT,',') EVENT_CHAIN FROM ASH START WITH sql_id='c5qgdg8hx991z' and session_id='17'CONNECT BY PRIOR BLOCKING_SESSION=SESSION_ID AND PRIOR INST_ID=blocking_inst_id AND SAMPLE_ID=PRIOR SAMPLE_ID)aleft join ash on a.final_block=ash.session_idwhere instr(SID_CHAIN,FINAL_BLOCK)=0--AND NOT EXISTS(SELECT 1 FROM ASH B WHERE A.FINAL_BLOCK=B.SESSION_ID AND B.BLOCKING_SESSION IS NOT NULL)ORDER BY a.SAMPLE_TIME;

带入发生行锁的会话或SQLID等。

但该脚本查出的数据会相对较多，需要翻到最后面确定阻塞链信息。最终与上述通过gv$session查到的阻塞链一致。

3.还可以借助WAIT_CHAINS脚本，进一步帮助确定问题源头：


可以看到影响最大的是两条阻塞链。源头均为会话12。与前述查询的阻塞链条一致。
SECONDS：阻塞持续了多少秒；
DISTINCT_SIDS：该会话阻塞了多少会话数。
%This：会话阻塞占比程度。

查询源头SQL

除了找到上述阻塞链情况，有时候还希望找到对应的源头会话在执行什么SQL，为什么会产生阻塞。则需要通过下面脚本去排查。但能否找出原始SQL受到源头会话的SQL执行情况等影响。不一定能找到。

1.通过未提交事务查SQL信息：带入会话ID
如果存在未提交的事务，查看该事务上的相关语句。分析是否存在未提交或部分低效语句导致整个事务未能提交。

col sql_text for a60col MODULE for a20 trunccol MACHINE for a10 trunc select distinct t1.SID,                 t1.SERIAL#,                 nvl(t2.SQL_text, t4.SQL_text) SQL_TEXT,                 t3.SQL_ID,t3.MODULE,t3.MACHINE, round(t2.ELAPSED_TIME/1E6,1) AS els_s,to_char(t3.sql_exec_start, 'mm-dd hh24:mi') as start_time   from gv$transaction            t,        gv$session                t1,        gv$sql                    t2,        gv$active_session_history t3,        dba_hist_sqltext         t4  where t.SES_ADDR = t1.SADDR and t.INST_ID=t1.INST_ID    and t1.SID = t3.session_id    and t1.SERIAL# = t3.session_serial# and t1.inst_id=t3.inst_id    and t3.SQL_ID = t2.SQL_ID(+)    and t3.SQL_ID = t4.SQL_ID(+)and t1.SID='12'order by start_time;


其中UPDATE为未提交语句，但同时后续还执行了多条查询类语句，其执行效率较低，也可能是整个事务未提交的原因。

2.通过游标找未提交SQL：带入会话ID

col user_name for a15col CURSOR_TYPE for a20 trunc SELECT inst_id,sid,user_name,sql_id,sql_text,last_sql_active_time,sql_exec_id,cursor_type FROM GV$OPEN_CURSOR WHERE SID='12';

如果是未提交语句，且通过方法1没有找到SQL语句，可以看下游标中是否有记录。如果有备份。直接查询游标的备份表。

第一次查询找到了未提交更新语句。

后续该会话又执行了其他语句，重复查询时，可能找不到源头语句。再查询备份表

col user_name for a15col CURSOR_TYPE for a20 trunc SELECT inst_id,sid,user_name,sql_id,sql_text,last_sql_active_time,sql_exec_id,cursor_type FROM OPEN_TABLEBAK WHERE SID='12';

通过备份表还是可以查到源头信息的，因此备份游标表这步还是比较关键的。

3.通过ASH视图尝试获取SQL信息：带入会话ID及查询时间段等

col SQL_TEXT for a75 trunccol MODULE for a13 trunccol MACHINE for a10 trunccol OBJECT for a10 trunccol start_time for a11col exec_time for a9col cnt for 9999col INST for 9select session_id SID,t1.MODULE,t1.MACHINE,cnt,T1.INST,SQ.SQL_ID,SQ.SQL_TEXT,ob.object_name OBJECT,start_time,exec_time from (select inst_id INST,session_id,SQL_ID,SQL_EXEC_ID,MODULE,MACHINE,CURRENT_OBJ#,count(*) AS cnt,to_char(sql_exec_start, 'mm-dd hh24:mi') as start_time,SUBSTR(max(sample_time) - sql_exec_start,11,9)  as exec_time from gv$active_session_history where session_id='12' and sample_time >= to_date('2022-06-10 23:00:00','yyyy-mm-dd hh24:mi:ss')group by inst_id,session_id,SQL_ID,SQL_EXEC_ID,MODULE,MACHINE,CURRENT_OBJ#,sql_exec_start) t1left join gv$sql SQON T1.SQL_ID=SQ.SQL_ID and t1.INST=SQ.inst_idleft join dba_objects obon T1.CURRENT_OBJ#=ob.object_idorder by start_time;


该会话上执行过的SQL会记录，但如果未提交语句执行较快，则不一定能找到。

知识总结

通过上述演示，模拟了源头未提交语句的行锁阻塞分析过程。实际场景中可能等待情况会更加复杂，但上述查询步骤仍然有效。需要利用好ASH视图及gv$open_cursor。