和外键相关的阻塞和死锁问题总结（三）

session 2:
SQL> delete from p where id=3;
阻塞...
session 3:
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in
2 (159,128) order by sid;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
128 TM 13017 0 3 0 30 0
128 TX 262168 360 6 0 569 0
128 TM 13020 0 3 5 569 0
159 TX 393228 371 6 0 610 0
159 TM 13020 0 3 0 610 1
159 TM 13017 0 2 0 30 0

已选择6行。

SQL>
从最后一个字段block=1发现session 2(sid:128)请求的锁mode 5被session 1(sid:159)加在子表(r)
上的3锁阻塞...(上面已经说了mode 3和5不能兼容)；这样session 1和session 2相互阻塞最终形成了死锁
，当然死锁oracle会自动侦测并且解除，于是session 1中出现了deadlock被解除的提示信息：
session 1：
SQL> delete from p where id=1;
delete from p where id=1
*
第 1 行出现错误:
ORA-00060: 等待资源时检测到死锁

SQL>
--=========================
如何避免使用外键而引起的死锁：
rollback掉上面session 1和session 2中的sql：
session 1：
SQL> select * from p;

ID
----------
1
2
3

SQL> select * from r;

未选定行

SQL> create index idx_r on r(id) tablespace users;

索引已创建。

SQL> insert into r values(2);

已创建 1 行。

SQL>
--========================
session 3：
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in
2 (159,128) order by sid;

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
159 TM 13017 0 2 0 78 0
159 TX 131078 387 6 0 78 0
159 TM 13020 0 3 0 78 0

SQL>
--=======================
session 2:
SQL> insert into r values(2);

已创建 1 行。

SQL>
--========================
session 3:
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in
2 (159,128) order by sid;

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
128 TM 13017 0 2 0 22 0
128 TX 393219 372 6 0 22 0
128 TM 13020 0 3 0 22 0
159 TX 131078 387 6 0 124 0
159 TM 13020 0 3 0 124 0
159 TM 13017 0 2 0 124 0

已选择6行。

SQL>
--==========================
session 1:
SQL> delete from p where id=1;

已删除 1 行。

SQL>
--========================
session 3:
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in
2 (159,128) order by sid;

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
128 TM 13017 0 2 0 121 0
128 TX 393219 372 6 0 121 0
128 TM 13020 0 3 0 121 0
159 TX 131078 387 6 0 223 0
159 TM 13020 0 3 0 223 0
159 TM 13017 0 3 0 27 0

已选择6行。

SQL>
--=======================
我们发现在子表r上创建了index之后，session 1(sid:159)中的操作delete from p where id=1执行之后
加在主表p上的锁mode由原来的2变成了3，而且没有index之前请求的子表r上的锁mode是5，现在变成了3，
锁mode 3和session 2里面insert操作引起的锁mode 3在行级锁不发生冲突(因为2个session操作的r表里的数据不是同一行)
的情况下是可以兼容的，因此session 2(sid:128)不在阻塞session 1;

（以上内容摘于网络，如有侵权，请告之，将第一时间删除）