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;



oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html