ORACLE中行锁问题排查手段seo优化

ORACLE中行锁问题排查手段

news/2024/10/8 6:22:39

概念描述
行锁，对应等待事件’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：模拟其他应用对相同行的更新
beginp_test_update;
end; 
/
存储过程中的代码如下：
create or replace procedure szt.p_test_update
asl_cnt number;
beginupdate t1 set OBJECT_NAME=OBJECT_NAME||'AAA' where object_id=110;commit;
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.获取锁模式及相关对象：

col OWNER for a10
col OBJECT_NAME for a20
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,gv$locked_object c
where a.type in ('TX')and a.inst_id=c.inst_id(+)and a.sid=c.SESSION_ID(+)and c.object_id=b.object_id(+)
order by a.inst_id,a.sid;
image.png

锁为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_MODEFROM GV$LOCKED_OBJECT T1, GV$SESSION T2, DBA_OBJECTS T3WHERE T1.SESSION_ID = T2.SID and t1.INST_ID=t2.INST_IDAND T1.OBJECT_ID = T3.OBJECT_IDORDER 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
selectsid||','||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_levelfrom gv$session astart with a.blocking_session is not nullconnect by (a.sid||'@'||a.inst_id) = prior (a.blocking_session||'@'||a.blocking_instance))where isleaf = 1order by tree_level asc;

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

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

col lock_chain for a75
col EVENT_CHAIN for a50
col first_seen for a18
col last_seen for a18
col BLOCKING_HEADER for a10
with ash as (
select *from gv$active_session_historywhere sample_time>=to_date('2022-07-30 10:00:52','yyyy-mm-dd hh24:mi:ss')and sample_time< to_date('2022-07-31 11:30:52','yyyy-mm-dd hh24:mi:ss')),
ash2 as (
select sample_time,inst_id,session_id,session_serial#,sql_id,sql_opname,event,blocking_inst_id,blocking_session,blocking_session_serial#,level lv,connect_by_isleaf isleaf,sys_connect_by_path(inst_id||'_'||session_id||'_'||session_serial#||':'||sql_id||':'||sql_opname,'->') lock_chain,sys_connect_by_path(EVENT,',') EVENT_CHAIN ,connect_by_root(inst_id||'_'||session_id||'_'||session_serial#) root_sessfrom ash--start with event like 'enq: TX - row lock contention%'start with blocking_session is not nullconnect by nocycle prior blocking_inst_id=inst_idand prior blocking_session=session_idand prior blocking_session_serial#=session_serial#and prior sample_id=sample_id)
select lock_chain lock_chain,case when blocking_session is not null then blocking_inst_id||'_'||blocking_session||'_'||blocking_session_serial# else inst_id||'_'||session_id||'_'||session_serial# end blocking_header, EVENT_CHAIN,count(*) cnt,TO_CHAR(min(sample_time),'YYYYMMDD HH24:MI:ss') first_seen,TO_CHAR(max(sample_time),'YYYYMMDD HH24:MI:ss') last_seenfrom ash2where isleaf=1
group by lock_chain,EVENT_CHAIN,case when blocking_session is not null then blocking_inst_id||'_'||blocking_session||'_'||blocking_session_serial# else inst_id||'_'||session_id||'_'||session_serial# end
having count(*)>1
order by first_seen, cnt desc;

带入发生行锁的时间段。

1节点的12会话，阻塞了141会话，141会话又阻塞了140会话。
1节点12会话，阻塞链1节点17会话。
除此之外还能看到对应的等待事件、SQL语句及其类型。

3.还可以借助WAIT_CHAINS脚本，进一步帮助确定问题源头：
使用方法：1）将脚本放入当前目录；2）@wait_chains.sql

col "%This" for a10
col WAIT_CHAIN for a55
col FIRST_SEEN for a20
col last_seen for a20
WITH 
bclass AS (SELECT /*+ INLINE */ class, ROWNUM r from v$waitstat),
ash AS (SELECT /*+ INLINE QB_NAME(ash) LEADING(a) USE_HASH(u) SWAP_JOIN_INPUTS(u) */a.*, o.*, u.username, CASE WHEN a.session_type = 'BACKGROUND' AND a.program LIKE '%(DBW%)' THEN'(DBWn)'WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THENREGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')ELSE'('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'END || ' ' program2, NVL(a.event||CASE WHEN event like 'enq%' AND session_state = 'WAITING'THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#')THEN ' ['||NVL((SELECT class FROM bclass WHERE r = a.p3),'undo @bclass '||a.p3)||']' ELSE null END,'ON CPU') || ' ' event2, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex, CASE WHEN BITAND(time_model, POWER(2, 01)) = POWER(2, 01) THEN 'DBTIME '  END||CASE WHEN BITAND(time_model, POWER(2, 02)) = POWER(2, 02) THEN 'BACKGROUND '  END||CASE WHEN BITAND(time_model, POWER(2, 03)) = POWER(2, 03) THEN 'CONNECTION_MGMT '  END||CASE WHEN BITAND(time_model, POWER(2, 04)) = POWER(2, 04) THEN 'PARSE '  END||CASE WHEN BITAND(time_model, POWER(2, 05)) = POWER(2, 05) THEN 'FAILED_PARSE '  END||CASE WHEN BITAND(time_model, POWER(2, 06)) = POWER(2, 06) THEN 'NOMEM_PARSE '  END||CASE WHEN BITAND(time_model, POWER(2, 07)) = POWER(2, 07) THEN 'HARD_PARSE '  END||CASE WHEN BITAND(time_model, POWER(2, 08)) = POWER(2, 08) THEN 'NO_SHARERS_PARSE '  END||CASE WHEN BITAND(time_model, POWER(2, 09)) = POWER(2, 09) THEN 'BIND_MISMATCH_PARSE '  END||CASE WHEN BITAND(time_model, POWER(2, 10)) = POWER(2, 10) THEN 'SQL_EXECUTION '  END||CASE WHEN BITAND(time_model, POWER(2, 11)) = POWER(2, 11) THEN 'PLSQL_EXECUTION '  END||CASE WHEN BITAND(time_model, POWER(2, 12)) = POWER(2, 12) THEN 'PLSQL_RPC '  END||CASE WHEN BITAND(time_model, POWER(2, 13)) = POWER(2, 13) THEN 'PLSQL_COMPILATION '  END||CASE WHEN BITAND(time_model, POWER(2, 14)) = POWER(2, 14) THEN 'JAVA_EXECUTION '  END||CASE WHEN BITAND(time_model, POWER(2, 15)) = POWER(2, 15) THEN 'BIND '  END||CASE WHEN BITAND(time_model, POWER(2, 16)) = POWER(2, 16) THEN 'CURSOR_CLOSE '  END||CASE WHEN BITAND(time_model, POWER(2, 17)) = POWER(2, 17) THEN 'SEQUENCE_LOAD '  END||CASE WHEN BITAND(time_model, POWER(2, 18)) = POWER(2, 18) THEN 'INMEMORY_QUERY '  END||CASE WHEN BITAND(time_model, POWER(2, 19)) = POWER(2, 19) THEN 'INMEMORY_POPULATE '  END||CASE WHEN BITAND(time_model, POWER(2, 20)) = POWER(2, 20) THEN 'INMEMORY_PREPOPULATE '  END||CASE WHEN BITAND(time_model, POWER(2, 21)) = POWER(2, 21) THEN 'INMEMORY_REPOPULATE '  END||CASE WHEN BITAND(time_model, POWER(2, 22)) = POWER(2, 22) THEN 'INMEMORY_TREPOPULATE '  END||CASE WHEN BITAND(time_model, POWER(2, 23)) = POWER(2, 23) THEN 'TABLESPACE_ENCRYPTION ' END time_model_nameFROM gv$active_session_history a, dba_users u, (SELECTobject_id,data_object_id,owner,object_name,subobject_name,object_type, owner||'.'||object_name obj, owner||'.'||object_name||' ['||object_type||']' objtFROM dba_objects) oWHEREa.user_id = u.user_id (+)AND a.current_obj# = o.object_id(+)AND sample_time BETWEEN sysdate-1/24 AND sysdate),
ash_samples AS (SELECT /*+ INLINE */ DISTINCT sample_id FROM ash),
ash_data AS (SELECT /*+ INLINE */ * FROM ash),
chains AS (SELECT /*+ INLINE */ sample_time ts, level lvl, session_id sid, REPLACE(SYS_CONNECT_BY_PATH(username||':'||program2||event2, '->'), '->', ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 AND d.blocking_session IS NOT NULL THEN ' -> [idle blocker '||d.blocking_inst_id||','||d.blocking_session||','||d.blocking_session_serial#||(SELECT ' ('||s.program||')' FROM gv$session s WHERE (s.inst_id, s.sid , s.serial#) = ((d.blocking_inst_id,d.blocking_session,d.blocking_session_serial#)))||']' ELSE NULL END path -- there's a reason why I'm doing this , CASE WHEN CONNECT_BY_ISLEAF = 1 THEN d.session_id ELSE NULL END sids, CONNECT_BY_ISLEAF isleaf, CONNECT_BY_ISCYCLE iscycle, d.*FROMash_samples s, ash_data dWHEREs.sample_id = d.sample_id AND d.sample_time BETWEEN sysdate-1/24 AND sysdateCONNECT BY NOCYCLE(    PRIOR d.blocking_session = d.session_idAND PRIOR d.blocking_inst_id = d.inst_idAND PRIOR s.sample_id = d.sample_id)START WITH session_type='FOREGROUND'
)
SELECT * FROM (SELECTLPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This", COUNT(*) seconds/* , ROUND(COUNT(*) / ((CAST(sysdate AS DATE) - CAST(sysdate-1/24 AS DATE)) * 86400), 1) AAS */, COUNT(DISTINCT sids) distinct_sids, path wait_chain, TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen, TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seenFROMchainsWHEREisleaf = 1GROUP BYusername||':'||program2||event2, pathORDER BYCOUNT(*) DESC)
WHEREROWNUM <= 30;

可以看到影响最大的是两条阻塞链。源头均为会话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 truncselect 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_timefrom gv$transaction            t,gv$session                t1,gv$sql                    t2,gv$active_session_history t3,dba_hist_sqltext         t4where t.SES_ADDR = t1.SADDR and t.INST_ID=t1.INST_IDand t1.SID = t3.session_idand t1.SERIAL# = t3.session_serial# and t1.inst_id=t3.inst_idand 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_timefrom 
(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_timefrom 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;