数据库版本10.2.0.4,我模拟出来一个这样的场景:
select * from v$lock where sid <=155 and type='TX' order by id1,id2,lmode desc ,ctime desc;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
000007FF1DB281A8 000007FF1DB281E0 148 TX 65580 240 6 0 1594 1
000007FF1E8DB5A8 000007FF1E8DB5C8 149 TX 65580 240 0 6 1522 0
000007FF1DADE8C8 000007FF1DADE900 149 TX 196638 252 6 0 1609 1
000007FF1E8DBB00 000007FF1E8DBB20 144 TX 196638 252 0 6 671 0
000007FF1DB030E8 000007FF1DB03120 146 TX 655370 239 6 0 1621 1
000007FF1E8DB640 000007FF1E8DB660 148 TX 655370 239 0 6 1513 0
000007FF1E8DB6D8 000007FF1E8DB6F8 152 TX 655370 239 0 6 740 0
SELECT inst_id,
DECODE(request, 0, 'Holder: ', 'Waiter: ') || sid sess,
id1,
id2,
lmode,
request,
type
FROM gV$lock
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$lock WHERE request > 0)
ORDER BY id1,id2,request;
INST_ID SESS ID1 ID2 LMODE REQUEST TYPE
1 Holder: 148 65580 240 6 0 TX
1 Waiter: 149 65580 240 0 6 TX
1 Holder: 149 196638 252 6 0 TX
1 Waiter: 144 196638 252 0 6 TX
1 Holder: 146 655370 239 6 0 TX
1 Waiter: 148 655370 239 0 6 TX
1 Waiter: 152 655370 239 0 6 TX
可以看到:
sid 148阻塞了149,149阻塞了144,而148受146的阻塞,同时146除了阻塞148以外,还阻塞152.
也就是说整个的enqueue chain或者等待关系是:
144=>149=>148=>146
152=>148=>146
也就是说此时146是源头。
我现在想的是有没有sql,可以得到一目了然的“等待关系”的结果,而不用我们再去做“二次识别”
?