带着这个疑问,分别查看了v$session_wait,v$lock两张表,发现v$session_wait里面大量的“SQL*Net message from client”等待事件,且wait_type为“IDLE”,也就是空间等待。查询v$lock表,通过CTIM排序也发现类似的情况,有几个 session持有TM类型的锁,事件都是几千秒,类似下面的图:
TM是表级的锁,LMODE均为3,也就是SX(表级共享行级排他锁),按理来说不会阻塞其他TM锁级别的会话。再次查看v$lock里面block为1 的SID,看看是哪个对象产生了阻塞,结果发现产生阻塞的对象是一张子表,而产生阻塞的SID是对其主表进行了DML操作(delete了一条记录但未提 交),此时问题基本上确定了,因为产生阻塞的SID正在执行一个存储过程,该存储过程涉及操作多张表,其中就有一张很关键的主业务表,在执行删除操作后, 下面的操作是一个大的查询赋值,此查询当天不知道为什么HANG住了,导致持有的锁一直未释放,从而阻塞了对其他子表进行操作的SESSION,话说回来 也是一个典型的子表缺失索引的主外键表操作问题。
PS:提供几条SQL用于快速定位问题
---查看哪些会话被阻塞
select c.terminal||' ('''||a.sid||','||c.serial#||''') is blocking '||b.sid
||','||d.serial# block_msg, a.block
from v$lock a,v$lock b,v$session c,v$session d
where a.id1=b.id1
and a.id2=b.id2
and a.block>0
and a.sid <>b.sid
and a.sid=c.sid
and b.sid=d.SID
;
--查看某个会话正在执行的SQL
select sql_text from v$sqltext_with_newlines where (hash_value,address)
in (select sql_hash_value,sql_address from
v$session where sid=145) order by address,piece;
另:对于外键表同时有2列外键到同一张主表的情况要特别注意,如果单独一列没有索引,那么在主表做DML操作未提交时,另外一个会话如果也对主表做DML操作,
此时会对子表请求一个lmode=5的锁,也就是事务排他锁,此时会HANG住。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22166274/viewspace-1130687/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22166274/viewspace-1130687/