概念描述
行锁，对应等待事件’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;
/
存储过程中的代码如下：
create or replace procedure szt.p_test_update
as
l_cnt number;
begin
update 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;
锁为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 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_history
where 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_sess
from ash
--start with event like 'enq: TX - row lock contention%'
start with blocking_session is not null
connect by nocycle
prior blocking_inst_id=inst_id
and prior blocking_session=session_id
and 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_seen
from ash2
where 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+\)') THEN
REGEXP_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_name
FROM
gv$active_session_history a
, dba_users u
, (SELECT
object_id,data_object_id,owner,object_name,subobject_name,object_type
, owner||'.'||object_name obj
, owner||'.'||object_name||' ['||object_type||']' objt
FROM dba_objects) o
WHERE
a.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.*
FROM
ash_samples s
, ash_data d
WHERE
s.sample_id = d.sample_id
AND d.sample_time BETWEEN sysdate-1/24 AND sysdate
CONNECT BY NOCYCLE
( PRIOR d.blocking_session = d.session_id
AND PRIOR d.blocking_inst_id = d.inst_id
AND PRIOR s.sample_id = d.sample_id
)
START WITH session_type='FOREGROUND'
)
SELECT * FROM (
SELECT
LPAD(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_seen
FROM
chains
WHERE
isleaf = 1
GROUP BY
username||':'||program2||event2
, path
ORDER BY
COUNT(*) DESC
)
WHERE
ROWNUM <= 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 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。
转载阅读的原文地址：https://www.modb.pro/db/437865 如有侵权，请联系删除
标签：POWER,中行,SQL,col,排查,session,time,ORACLE,id From： https://www.cnblogs.com/hgboy/p/18386749