關(guān)于外鍵列上的索引

1、 關(guān)于外鍵列上的索引yangtingkun在“外鍵列上是否需要索引”這篇文章里介紹了對(duì)于在外鍵列上建索引的必要性，這篇文章里有提到：這時(shí)會(huì)話(huà)被鎖住，因?yàn)槿鄙倭送怄I索引后，主表刪除或更新記錄會(huì)導(dǎo)致子表整個(gè)表被鎖，而這會(huì)導(dǎo)致嚴(yán)重的系統(tǒng)并發(fā)問(wèn)題。這里怎樣理解“主表刪除或更新記錄會(huì)導(dǎo)致子表整個(gè)表被鎖”？其實(shí)他這里的含義就是lock the entire table in shared mode。從我隨后的測(cè)試中可以看到，這個(gè)是不一定的，在外鍵列上沒(méi)有索引的情況下，是否鎖子表跟子表上是否有mode為3的TM enqueue有關(guān)。我們來(lái)做一個(gè)測(cè)試，我這里沿用他文章里的建表語(yǔ)句：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現(xiàn)在我們開(kāi)兩個(gè)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執(zhí)行完上述語(yǔ)句后我們轉(zhuǎn)到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這里所有的操作都順暢的執(zhí)行，因?yàn)樵趀nqueue mode里，2和3是兼容的。我們現(xiàn)在來(lái)看yangtingkun提到的那個(gè)堵塞的例子：在上述session中分別執(zhí)行rollback，然后我們這次先轉(zhuǎn)到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再轉(zhuǎn)到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;執(zhí)行到這里就hang住了我們現(xiàn)在再次轉(zhuǎn)到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從結(jié)果里我們可以清晰的看到，session 1在執(zhí)行delete from t_p where id=2時(shí)會(huì)被hang住的原因是因?yàn)楝F(xiàn)在oracle想把原來(lái)子表上的TM enqueue由2變成4，而t_c上已經(jīng)有了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.如法炮制，你就可以很容易的觀察到子表的外鍵列上建了索引后以及是否執(zhí)行alter table XXX disable table lock對(duì)上述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轉(zhuǎn)到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從結(jié)果里看到，這種模式下建了索引跟沒(méi)建沒(méi)有區(qū)別。全部rollback后先轉(zhuǎn)到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再轉(zhuǎn)到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從結(jié)果里我們可以清晰的看到，session 1在執(zhí)行delete

19、from t_p where id=2時(shí)沒(méi)有hang住，因?yàn)楝F(xiàn)在oracle在子表的外鍵列上有索引的情況下并沒(méi)有想把原來(lái)的TM enqueue由2變成4，oracle這里還是保持了2不變。全部rollback后我們現(xiàn)在來(lái)測(cè)試alter table XXX disable table lock對(duì)上述TM enqueue的影響：先轉(zhuǎn)到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從結(jié)果里我們可以看到現(xiàn)在子表T_C上沒(méi)有TM enqueue了。再轉(zhuǎn)到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從結(jié)果里我們可以看到，對(duì)子表做disable table lock是不行的。全部rollback后我們?cè)俅无D(zhuǎn)到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再轉(zhuǎn)到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;執(zhí)行到這里就hang住了我們現(xiàn)在再次轉(zhuǎn)到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