外键没有索引起阻塞

create table test_f(a int,b int);
create table test1(a int,b int);

alter table test_f add constraint PK_test_f primary key (a);
alter table test1 add constraint F_test12  foreign key (b) references test_f(a) ;


DECLARE
i int;
BEGIN
for i in 1 .. 10000 LOOP
INSERT INTO test_f(a,b) values(i,i);
COMMIT;
END LOOP;
END;

DECLARE
i int;
BEGIN
for i in 1 .. 1000 LOOP
INSERT INTO test1(a,b) values(i,i);
COMMIT;
END LOOP;
END;

   
session1:
delete test1 where a=104;

session2:(挂起)
delete test_f where a=1009;

SQL> r
  1  SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
  2            sid sess, id1, id2, lmode, request, type
  3     FROM V$LOCK
  4   WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
  5*    ORDER BY id1, request

SESS                                  ID1        ID2      LMODE    REQUEST TY
------------------------------ ---------- ---------- ---------- ---------- --
Holder: 15                          24795          0          3          0 TM
Waiter: 125                         24795          0          0          4 TM

session3:(挂起)
INSERT INTO test_F values(10005,10005);

SQL> r
  1  SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
  2            sid sess, id1, id2, lmode, request, type
  3     FROM V$LOCK
  4   WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
  5*    ORDER BY id1, request

SESS                                  ID1        ID2      LMODE    REQUEST TY
------------------------------ ---------- ---------- ---------- ---------- --
Holder: 15                          24795          0          3          0 TM
Waiter: 146                         24795          0          0          3 TM
Waiter: 125                         24795          0          0          4 TM




结论：如果没有索引时,对父表的操作,会级联加一个TM S锁(level 4)到子表上;
如果有索引时,对父表的操作,会级联加一个TM RS锁(level 2)到子表上;
这时如果子表上本身就有个TM RX锁(这种锁很容易产生,insert update delete都会产生这种锁),
TM S锁和TM RX锁是互斥的, TM RS锁和TM RX锁是相容的.

阻塞关系汇总（0表示阻塞，1表示不阻塞）：
        2Rs        3Rx     4s      5srx       6x
2rs       1        1        1        1        0
3rx       1        1        0        0        0
4s        1        0        1        0        0
5srx      1        0        0        0        0
6x        0        0        0        0        0

参考:
http://space.itpub.net/7299296/viewspace-555687