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

外键上没有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被阻塞了。

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