ITPub博客

首页 > 应用开发 > IT综合 > 和外键相关的阻塞和死锁问题总结

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

原创 IT综合 作者:warehouse 时间:2010-10-13 12:38:28 0 删除 编辑

外键上没有index操作主表数据时常会引起阻塞和deadlock问题

[@more@]

外键引起的阻塞:
session 1:
SQL> create table p(id int primary key ) tablespace users;

表已创建。

SQL> create table r(id int references p on delete cascade) tablespace users;

表已创建。

SQL> insert into p values(1);

已创建 1 行。

SQL> insert into p values(2);

已创建 1 行。

SQL> insert into p values(3);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select distinct sid from v$mystat;

SID
----------
159

SQL> select * from p;

ID
----------
1
2
3
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
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
159 TM 13017 0 3 0 9 0
159 TX 589854 449 6 0 9 0
159 TM 13020 0 3 0 9 0

SQL> col object_name format a10
SQL> select object_id,object_name from dba_objects where object_id in (13017,130
20);

OBJECT_ID OBJECT_NAM
---------- ----------
13017 P
13020 R
--=========================
很明显session 1中的语句delete from p where id=1;在字表r上加了锁,mode为3;
--=========================
session 2:
SQL> select distinct sid from v$mystat;

SID
----------
128

SQL> delete from p where id=2;
等待、被session 1阻塞了
--=========================
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 9 0
128 TM 13020 0 0 5 9 0
159 TX 589854 449 6 0 90 0
159 TM 13020 0 3 0 90 1
159 TM 13017 0 3 0 90 0

SQL>
很明显session 2中的语句delete from p where id=2;请求锁mode为5被
session 1中的语句delete from p where id=1加在r上的锁mode为3阻塞了;
由于mode 3对应的RX(row exclusive)和mode 5对应的SRX(share row exclusive)不能兼容,因此
session 2被阻塞了。
--=========================
外键引起的死锁:
rollback掉上面session 1和session 2中的sql
--=========================
session 1:
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 15 0
159 TX 393228 371 6 0 15 0
159 TM 13020 0 3 0 15 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 14 0
128 TX 262168 360 6 0 14 0
128 TM 13020 0 3 0 14 0
159 TX 393228 371 6 0 55 0
159 TM 13020 0 3 0 55 0
159 TM 13017 0 2 0 55 0

已选择6行。

SQL>
--=======================
当我们向子表r中分别通过session 1和2插入数据时,此时发现在r表上家了锁mode是3,在主表
p上加的锁mode是2(RS: row share),此时别没有阻塞,接下来操作主表...
session 1:
SQL> delete from p where id=1;
阻塞...
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 332 0
128 TX 262168 360 6 0 332 0
128 TM 13020 0 3 0 332 1
159 TX 393228 371 6 0 373 0
159 TM 13020 0 3 5 373 0
159 TM 13017 0 3 0 29 0

已选择6行。

SQL>
从最后一个字段block=1发现session 1(sid:159)请求的锁mode 5被session 2(sid:128)加在子表(r)
上的3锁阻塞...(上面已经说了mode 3和5不能兼容);
--=========================
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;
--====================
session 2:
SQL> delete from p where id=3;

已删除 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 3 0 30 0
128 TX 393219 372 6 0 541 0
128 TM 13020 0 3 0 541 0
159 TX 131078 387 6 0 643 0
159 TM 13020 0 3 0 643 0
159 TM 13017 0 3 0 447 0

已选择6行。

SQL>
同理r上有了index之后,session 2(sid:128)中的操作delete from p where id=3执行之后
加在主表p上的锁mode由原来的2变成了3,而且没有index之前请求的子表r上的锁mode是5,现在变成了3,
锁mode 3和session 1里面insert操作引起的锁mode 3在行级锁不发生冲突(因为2个session操作的r表里的数据不是同一行)
的情况下是可以兼容的,因此session 1(sid:159)不再阻塞session 2;没有了相互阻塞,死锁也就不会发生了,
因此在外键上创建index不仅会提高由于操作(比如delete主表数据)主表主键
数据而引起的扫描子表的效率而且也可以避免操作主表数据而引起的阻塞和死锁问题

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

上一篇: 难缠的ora-04031
请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    717
  • 访问量
    5098436