模拟试验之-外键未加索引导致死锁

session 1:

scott@TEST>create table p(x int primary key);
 
Table created.
 
scott@TEST>create  table c(x references p);
 
Table created.
 
scott@TEST>insert into p values(1);
 
1 row created.
 
scott@TEST>insert into p values(2);
 
1 row created.
 
scott@TEST>insert into p values(3);
 
1 row created.
 
scott@TEST>commit;
 
Commit complete.
 
scott@TEST>insert into c values(2);
 
1 row created.
 
scott@TEST>delete from p where x=1;
delete from p where x=1
            *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

 

session 2

scott@TEST>insert into c values(2);
 
1 row created.
 
scott@TEST>delete from p where x=3;

 

此时查看锁情况：

sys@TEST>l
  1  select addr,sid,type,id1,id2,lmode,request,block from v$lock where type in('TX','TM')
  2*
sys@TEST>/
 
ADDR            SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
-------- ---------- -- ---------- ---------- ---------- ---------- ----------
5B9E4430         12 TX     196609        286          6          0          0
5B9A8978         12 TM       6339          0          3          0          1
5B9A88F4         12 TM       6337          0          2          0          0
5B9E405C         17 TX     458760        483          6          0          0
5B9A8A80         17 TM       6339          0          3          5          0
5B9A89FC         17 TM       6337          0          3          0          0
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-214858/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-214858/