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




外键引起的阻塞：

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被阻塞了。


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