v$lock中的id1,id2为锁定的对象标识,block为阻塞数目。脚本show_blocker.sql相关代码如下:
col block_msg for a80
from v$lock a,v$lock b,v$session c
where a.id1=b.id1
and a.id2=b.id2
and a.block>0
and a.sid<>b.sid
and a.sid=c.sid
;
模拟场景:开3个session,
session1做更新,不提交。
session2做相同行的更新,将会被阻塞。
session3执行脚本观察结果。
select">chennan@cwgl94>select * from t where a=1;
---------- ---------- --------------------
1 chennan
---------- ---------- --------------------
1 chennan my address
session2:
select">chennan@cwgl94>select * from t where a=1;
---------- ---------- --------------------
1 chennan
[ 一直阻塞 ...]
select">chennan@cwgl94>select sid,serial#,terminal from v$session;
---------- ---------- ----------------
1 1 CHENNAN
2 1 CHENNAN
3 1 CHENNAN
4 1 CHENNAN
5 1 CHENNAN
6 1 CHENNAN
7 1 CHENNAN
8 1 CHENNAN
9 204 CHENNAN
10 2590 CHENNAN
11 1480 CHENNAN
-----------------------------------------------------------
CHENNAN ('11,1480') is blocking 10