外键上没有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被阻塞了。
--=========================
外键引起的死锁：
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
返回栏目页：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/