【原理】一步步教你如何排查 ORACLE中行锁问题
概念描述
行锁，对应等待时间’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 400
set pagesize 400
col object_name for a40
select a.inst_id,a.sid,a.type,a.id1,a.id2,b.owner,b.object_name,a.lmode,a.request
from gv$lock a,dba_objects b
where a.id1=b.object_id(+) and a.type in ('TM','TX')
order by a.inst_id,a.sid;
锁为6级排它锁。确定为更新相同数据导致。
2.查看阻塞对象及会话源头：
set lines 200 pages 40
col SID for 9999
col OWNER for a10
col OBJECT_NAME for a20
col event for a30
col b_sid for 9999
col f_b_sid for 9999
SELECT 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 a13
col prog format a10 trunc
col sql_text format a60 trunc
col sid format a12
col sql_id format a16
col child for 99999
col execs format 9999999
col sqlprofile format a22
col avg_ela for 999999.99
col last_ela for 999999
col event format a15
select
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_text
from gv$session a, gv$sql b
where status = 'ACTIVE'
and username is not null
and a.sql_id = b.sql_id
and a.sql_child_number = b.child_number
and a.inst_id=b.INST_ID
and 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 a30
col SQL_CHAIN for a40
col EVENT_CHAIN for a50
with 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_id
FROM 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)a
left join ash on a.final_block=ash.session_id
where 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 a60
col MODULE for a20 trunc
col 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 a15
col 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 a15
col 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 trunc
col MODULE for a13 trunc
col MACHINE for a10 trunc
col OBJECT for a10 trunc
col start_time for a11
col exec_time for a9
col cnt for 9999
col INST for 9
select 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) t1
left join gv$sql SQ
ON T1.SQL_ID=SQ.SQL_ID and t1.INST=SQ.inst_id
left join dba_objects ob
on T1.CURRENT_OBJ#=ob.object_id
order by start_time;
该会话上执行过的SQL会记录，但如果未提交语句执行较快，则不一定能找到。
知识总结
通过上述演示，模拟了源头未提交语句的行锁阻塞分析过程。实际场景中可能等待情况会更加复杂，但上述查询步骤仍然有效。需要利用好ASH视图及gv$open_cursor。
