关于外键列上的索引

1、 关于外键列上的索引yangtingkun在“外键列上是否需要索引”这篇文章里介绍了对于在外键列上建索引的必要性，这篇文章里有提到：这时会话被锁住，因为缺少了外键索引后，主表删除或更新记录会导致子表整个表被锁，而这会导致严重的系统并发问题。这里怎样理解“主表删除或更新记录会导致子表整个表被锁”？其实他这里的含义就是lock the entire table in shared mode。从我随后的测试中可以看到，这个是不一定的，在外键列上没有索引的情况下，是否锁子表跟子表上是否有mode为3的TM enqueue有关。我们来做一个测试，我这里沿用他文章里的建表语句：SQL> CREATE

2、 TABLE T_P (ID NUMBER, NAME VARCHAR2(30);Table createdSQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);Table alteredSQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30);Table createdSQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID);Table alteredSQL> ins

3、ert into t_p values(1,'A');1 row insertedSQL> insert into t_c values(1,1,'A');1 row insertedSQL> commit;Commit complete现在我们开两个session，分别是session 1和session 2。Session 1：SQL> select object_id from dba_objects where object_name='T_P' OBJECT_ID- 86351SQL> select sid fr

4、om v$mystat where rownum<2; SID- 20SQL> insert into t_p values(2,'A');1 row insertedSQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid=20; SID TYPE ID1 ID2 LMODE REQUEST BLOCK- - - - - - - 20 TX 131113 101077 6 0 0 20 TM 86353 0 2 0 0 20 TM 86351 0 3 0 0SQL> d

5、elete from t_p where id=2;1 row deletedSQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid=20; SID TYPE ID1 ID2 LMODE REQUEST BLOCK- - - - - - - 20 TX 131113 101077 6 0 0 20 TM 86353 0 2 0 0 20 TM 86351 0 3 0 0执行完上述语句后我们转到session 2：Session 2：SQL> select object_id from dba_ob

6、jects where object_name='T_C' OBJECT_ID- 86353SQL> select sid from v$mystat where rownum<2; SID- 18SQL> delete from t_c where id=1;1 row deletedSQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (18,20); SID TYPE ID1 ID2 LMODE REQUEST BLOCK- - - - - - - 18

7、TX 65543 67674 6 0 0 18 TM 86353 0 3 0 0 18 TM 86351 0 2 0 0 20 TX 131113 101077 6 0 0 20 TM 86353 0 2 0 0 20 TM 86351 0 3 0 06 rows selected这里所有的操作都顺畅的执行，因为在enqueue mode里，2和3是兼容的。我们现在来看yangtingkun提到的那个堵塞的例子：在上述session中分别执行rollback，然后我们这次先转到session 2：Session 2：SQL> delete from t_c where id=1;1 ro

8、w deletedSQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (18,20); SID TYPE ID1 ID2 LMODE REQUEST BLOCK- - - - - - - 18 TX 458759 99739 6 0 0 18 TM 86353 0 3 0 0 18 TM 86351 0 2 0 0再转到session 1：Session 1：SQL> insert into t_p values(2,'A');1 row insertedSQL>

9、select sid,type,id1,id2,lmode,request,block from v$lock where sid in (18,20); SID TYPE ID1 ID2 LMODE REQUEST BLOCK- - - - - - - 18 TX 458759 99739 6 0 0 18 TM 86353 0 3 0 0 18 TM 86351 0 2 0 0 20 TX 65551 68357 6 0 0 20 TM 86353 0 2 0 0 20 TM 86351 0 3 0 06 rows selectedSQL> delete from t_p where

10、 id=2;执行到这里就hang住了我们现在再次转到session 2：Session 2：SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (18,20); SID TYPE ID1 ID2 LMODE REQUEST BLOCK- - - - - - - 18 TX 458759 99739 6 0 0 18 TM 86353 0 3 0 1 18 TM 86351 0 2 0 0 20 TX 65551 68357 6 0 0 20 TM 86353 0 2 4 0 20 TM 863

11、51 0 3 0 0从结果里我们可以清晰的看到，session 1在执行delete from t_p where id=2时会被hang住的原因是因为现在oracle想把原来子表上的TM enqueue由2变成4，而t_c上已经有了3，enqueue mode里3和4不兼容，所以就hang住了。DSI405这样解释oracle为啥要convert：Instead of releasing the SS lock and acquiring the S one, the lock is converted because the session cannot release the SS lo

12、ck even momentarily in case there is need to roll back.如法炮制，你就可以很容易的观察到子表的外键列上建了索引后以及是否执行alter table XXX disable table lock对上述TM enqueue的影响。Session 1：SQL> CREATE INDEX IND_T_C_FID ON T_C (FID);Index createdSQL> insert into t_p values(2,'A');1 row insertedSQL> select sid,type,id1,id2

13、,lmode,request,block from v$lock where sid in (18,20); SID TYPE ID1 ID2 LMODE REQUEST BLOCK- - - - - - - 20 TX 196619 77630 6 0 0 20 TM 86353 0 2 0 0 20 TM 86351 0 3 0 0SQL> delete from t_p where id=2;1 row deletedSQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (18,20

14、); SID TYPE ID1 ID2 LMODE REQUEST BLOCK- - - - - - - 20 TX 196619 77630 6 0 0 20 TM 86353 0 2 0 0 20 TM 86351 0 3 0 0转到session 2：Session 2：SQL> delete from t_c where id=1;1 row deletedSQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (18,20); SID TYPE ID1 ID2 LMODE REQU

15、EST BLOCK- - - - - - - 18 TX 524303 53353 6 0 0 18 TM 86353 0 3 0 0 18 TM 86351 0 2 0 0 20 TX 196619 77630 6 0 0 20 TM 86353 0 2 0 0 20 TM 86351 0 3 0 06 rows selected从结果里看到，这种模式下建了索引跟没建没有区别。全部rollback后先转到session 2：Session 2：SQL> delete from t_c where id=1;1 row deletedSQL> select sid,type,id1

16、,id2,lmode,request,block from v$lock where sid in (18,20); SID TYPE ID1 ID2 LMODE REQUEST BLOCK- - - - - - - 18 TX 327682 113645 6 0 0 18 TM 86353 0 3 0 0 18 TM 86351 0 2 0 0再转到session 1：Session 1：SQL> insert into t_p values(2,'A');1 row insertedSQL> select sid,type,id1,id2,lmode,reque

17、st,block from v$lock where sid in (18,20); SID TYPE ID1 ID2 LMODE REQUEST BLOCK- - - - - - - 18 TX 327682 113645 6 0 0 18 TM 86353 0 3 0 0 18 TM 86351 0 2 0 0 20 TX 458764 98619 6 0 0 20 TM 86353 0 2 0 0 20 TM 86351 0 3 0 06 rows selectedSQL> delete from t_p where id=2;1 row deletedSQL> select

18、 sid,type,id1,id2,lmode,request,block from v$lock where sid in (18,20); SID TYPE ID1 ID2 LMODE REQUEST BLOCK- - - - - - - 18 TX 327682 113645 6 0 0 18 TM 86353 0 3 0 0 18 TM 86351 0 2 0 0 20 TX 458764 98619 6 0 0 20 TM 86353 0 2 0 0 20 TM 86351 0 3 0 06 rows selected从结果里我们可以清晰的看到，session 1在执行delete

19、from t_p where id=2时没有hang住，因为现在oracle在子表的外键列上有索引的情况下并没有想把原来的TM enqueue由2变成4，oracle这里还是保持了2不变。全部rollback后我们现在来测试alter table XXX disable table lock对上述TM enqueue的影响：先转到session 2：Session 2：SQL> drop index IND_T_C_FID;Index droppedSQL> alter table t_c disable table lock;Table alteredSQL> delete

20、 from t_c where id=1;1 row deletedSQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (18,20); SID TYPE ID1 ID2 LMODE REQUEST BLOCK- - - - - - - 18 TX 524323 53706 6 0 0 18 TM 86351 0 2 0 0从结果里我们可以看到现在子表T_C上没有TM enqueue了。再转到session 1：Session 1：SQL> insert into t_p values(

21、2,'A');1 row insertedSQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (18,20); SID TYPE ID1 ID2 LMODE REQUEST BLOCK- - - - - - - 18 TX 524323 53706 6 0 0 18 TM 86351 0 2 0 0 20 TX 655386 48160 6 0 0SQL> delete from t_p where id=2;delete from t_p where id=2ORA-0

22、0069: cannot acquire lock - table locks disabled for T_C从结果里我们可以看到，对子表做disable table lock是不行的。全部rollback后我们再次转到session 2：Session 2：SQL> alter table t_c enable table lock;Table alteredSQL> alter table t_p disable table lock;Table alteredSQL> delete from t_c where id=1;1 row deletedSQL> se

23、lect sid,type,id1,id2,lmode,request,block from v$lock where sid in (18,20); SID TYPE ID1 ID2 LMODE REQUEST BLOCK- - - - - - - 18 TX 65546 65739 6 0 0 18 TM 86353 0 3 0 0再转到session 1：Session 1：SQL> insert into t_p values(2,'A');1 row insertedSQL> select sid,type,id1,id2,lmode,request,bl

24、ock from v$lock where sid in (18,20); SID TYPE ID1 ID2 LMODE REQUEST BLOCK- - - - - - - 18 TX 65546 65739 6 0 0 18 TM 86353 0 3 0 0 20 TX 196632 77830 6 0 0 20 TM 86353 0 2 0 0SQL> delete from t_p where id=2;执行到这里就hang住了我们现在再次转到session 2：Session 2：SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (18,20); SID TYPE ID1 ID2 LMODE REQUEST BLOCK- - - - - - - 18 TX 65546 65739 6 0 0