未加索引的外鍵導緻的死鎖模拟實驗以及問題

[email protected]> create table pk_t (x int primary key);  --建立主角

Table created.

[email protected]> insert into pk_t select rownum from dual connect by level <= 40;

40 rows created.

[email protected]> commit;

Commit complete.

[email protected]> create table fk_t (x int , y int);    --建立副表

Table created.

[email protected]> insert into fk_t select rownum, rownum/2 from dual connect by level <= 30;

30 rows created.

[email protected]> commit;

Commit complete.

[email protected]> alter table fk_t add constraint fk_fkt foreign key (x) references pk_t(x); --建立外鍵限制

Table altered.

[email protected]> select table_name, constraint_name,
  2  	    cname1 || nvl2(cname2,','||cname2,null) ||
  3  	    nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
  4  	    nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
  5  	    nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
  6  		   columns
  7  	 from ( select b.table_name,
  8  		       b.constraint_name,
  9  		       max(decode( position, 1, column_name, null )) cname1,
 10  		       max(decode( position, 2, column_name, null )) cname2,
 11  		       max(decode( position, 3, column_name, null )) cname3,
 12  		       max(decode( position, 4, column_name, null )) cname4,
 13  		       max(decode( position, 5, column_name, null )) cname5,
 14  		       max(decode( position, 6, column_name, null )) cname6,
 15  		       max(decode( position, 7, column_name, null )) cname7,
 16  		       max(decode( position, 8, column_name, null )) cname8,
 17  		       count(*) col_cnt
 18  		  from (select substr(table_name,1,30) table_name,
 19  			       substr(constraint_name,1,30) constraint_name,
 20  			       substr(column_name,1,30) column_name,
 21  			       position
 22  			  from user_cons_columns ) a,
 23  		       user_constraints b
 24  		 where a.constraint_name = b.constraint_name
 25  		   and b.constraint_type = 'R'
 26  		 group by b.table_name, b.constraint_name
 27  	      ) cons
 28  	where col_cnt > ALL
 29  		( select count(*)
 30  		    from user_ind_columns i,
 31  			 user_indexes	  ui
 32  		   where i.table_name = cons.table_name
 33  		     and i.column_name in (cname1, cname2, cname3, cname4,
 34  					   cname5, cname6, cname7, cname8 )
 35  		     and i.column_position <= cons.col_cnt
 36  		     and ui.table_name = i.table_name
 37  		     and ui.index_name = i.index_name
 38  		     and ui.index_type IN ('NORMAL','NORMAL/REV')
 39  		   group by i.index_name
 40  		)
 41  /
--查找出未加索引的外鍵
TABLE_NAME	CONSTRAINT_NAME COLUMNS
--------------- --------------- ------------------------------
FK_T		FK_FKT		X
……

--模拟死鎖
[email protected]> delete from fk_t where x=1;  --第一個會話中執行删除，子表加上SX鎖

1 row deleted.

[email protected]> delete from pk_t where x=1;  --第一個會話中執行删除，父表加上SX鎖

1 row deleted.

[email protected]> delete from fk_t where x=2;  --第二個會話中執行删除

1 row deleted.

[email protected]> delete from pk_t where x=2;  --第二個會話中執行删除，hang住

[email protected]> delete from pk_t where x=2;  --再次在第一個會話中執行删除

[email protected]> delete pk_t where x=2;
delete pk_t where x=2
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource   --第二個會話中立刻報錯形成死鎖

--再次模拟死鎖
[email protected]> create index idx_fk_x on fk_t(x) nologging;  --給外鍵增加索引

Index created.

[email protected]> delete from fk_t where x=1;  --第一個會話中執行删除，子表加上SX鎖

1 row deleted.

[email protected]> delete from pk_t where x=1;  --第一個會話中執行删除，父表加上SX鎖

1 row deleted.

[email protected]> delete from fk_t where x=2;  --第二個會話中執行删除

1 row deleted.

[email protected]> delete from pk_t where x=2;  --第二個會話中執行删除，均成功。

1 row deleted.
           

--未加索引的外鍵會存在的問題

1.引起全表鎖。

2.當使用了on delete cascade，而且沒有對子表加索引，要從父表中删除多行時，每删除一行就要掃描一次子表。

3.當你從父表查詢子表，你會發現沒有索引的話會使查詢變慢。

--什麼時候不需要對外鍵增加索引

1.不會從父表中删除行。

2.不會去更新父表的主鍵。

3.不會從父表連接配接到子表。

--參考來源《Oracle程式設計藝術深入了解資料庫體系結構（第三版）》