Deadlock的产生与分析

参考文档：
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