参考文档:
https://docs.oracle.com/cd/E11882_01/server.112/e41573/instance_tune.htm#PFGRF024
https://docs.oracle.com/cd/E11882_01/server.112/e41573/instance_tune.htm#PFGRF024
10.3.6.1 Finding Locks and Lock Holders
-- 模拟deadlock -略
查看v$Lock表
SYS@test>select * from v$Lock where request>0
2 ;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------------------- -------------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0000000090D902C8 0000000090D90320 45 TX 65567 1267 0 6 778 0
SYS@test>
查看谁阻止了谁
SYS@test>SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
ORDER BY id1, request; 2 3 4 5
SESS ID1 ID2 LMODE REQUEST TY
---------- ---------- ---------- ---------- ---------- --
Holder: 1 65567 1267 6 0 TX
Waiter: 45 65567 1267 0 6 TX
SYS@test>
End