一 模拟锁阻塞
#阻塞1
一个会话正在往一个大表写入大量数据的时候,另一个会话加字段:
#会话1
#会话2
会话2被阻塞了。
#阻塞2
模拟一个会话update一条记录,没提交。
另一个会话也update这一条记录:
会话2被阻塞了。
二 简单查看阻塞链
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(SID, '<-') tree,level as tree_levelfrom gv$session astart with a.blocking_session is not nullconnect by nocycle a.sid = prior a.blocking_session)where isleaf = 1order by tree_level asc;
输出示例:
TREE这里可以看到阻塞链(从右往左看),这里是:
978阻塞了729;
372阻塞了616。
三 查看具体谁阻塞了谁
SELECT DISTINCT s1.inst_id as blocking_inst_id,s1.username as blocking_username,s1.machine as blocking_machine,s1.module as blocking_module,s1.sid as blocking_sid,s1.audsid as blocking_audsid,s1.serial# as blocking_serial#,c1.sql_text as blocking_sql_text,s1.status as blocking_staus,s1.event as blocking_event,
s2.inst_id as waiting_inst_id,s2.username as waiting_username,s2.machine waiting_machine,s2.module as waiting_module,s2.sid as waiting_sid,s2.audsid as waiting_audsid,s2.serial# as waiting_serial#,c2.sql_text as waiting_sql_text,s2.status as blocking_staus,s2.event as waiting_eventFROM gv$lock l1,gv$session s1,gv$lock l2,gv$session s2,gv$sqlarea c1,gv$sqlarea c2,gv$process b1,gv$process b2WHERE s1.sid = l1.sidAND s2.sid = l2.sidAND s1.inst_id = l1.inst_idAND s2.inst_id = l2.inst_idAND s1.paddr = b1.addrAND s2.paddr = b2.addrAND c1.SQL_ID=s1.PREV_SQL_ID AND s2.sql_hash_value = c2.hash_valueAND l1.block > 0AND l2.request > 0AND l1.id1 = l2.id1AND l1.id2 = l2.id2
UNION
SELECT DISTINCT s1.inst_id as blocking_inst_id,s1.username as blocking_username,s1.machine as blocking_machine,s1.module as blocking_module,s1.sid as blocking_sid,s1.audsid as blocking_audsid,s1.serial# as blocking_serial#,c1.sql_text as blocking_sql_text,s1.status as blocking_staus,s1.event as blocking_event,
s2.inst_id as waiting_inst_id,s2.username as waiting_username,s2.machine waiting_machine,s2.module as waiting_module,s2.sid as waiting_sid,s2.audsid as waiting_audsid,s2.serial# as waiting_serial#,c2.sql_text as waiting_sql_text,s2.status as blocking_staus,s2.event as waiting_eventFROM gv$lock l1,gv$session s1,gv$lock l2,gv$session s2,gv$sqlarea c1,gv$sqlarea c2,gv$process b1,gv$process b2WHERE s1.sid = l1.sidAND s2.sid = l2.sidAND s1.inst_id = l1.inst_idAND s2.inst_id = l2.inst_idAND s1.paddr = b1.addrAND s2.paddr = b2.addrAND c1.hash_value=s1.sql_hash_valueAND c1.address=s1.sql_addressAND s2.sql_hash_value = c2.hash_valueAND l1.block > 0AND l2.request > 0AND l1.id1 = l2.id1AND l1.id2 = l2.id2
#备注
union前的sql可以查看源端执行过的sql(在一个事务里执行了,但是还没提交)。
union后的sql可以查看当前源端正在执行的sql。
一次性展示不全,这里分三个截图展示:
在每条记录里可以看到是谁阻塞了谁。
但是假如源端执行了这个事务后,又执行了其他事务,这时候查看源端阻塞sql就不准确了,这里只能查到源端执行的事务里的最后一条sql。
示例:
源端执行了一个事务,然后有执行了一个其他的sql,这时查看源端的阻塞sql是这样的:
这时结果是不准确的。假如想进一步排查,则可以对这个表进行下审计,审计阻塞方会话对这个表都做了什么操作,详细可参考
https://blog.csdn.net/YABIGNSHI/article/details/143970533