未加索引的外键导致的死锁模拟实验以及问题

EODA@PROD1> create table pk_t (x int primary key);  --建立主角

Table created.

EODA@PROD1> insert into pk_t select rownum from dual connect by level <= 40;

40 rows created.

EODA@PROD1> commit;

Commit complete.

EODA@PROD1> create table fk_t (x int , y int);    --建立副表

Table created.

EODA@PROD1> insert into fk_t select rownum, rownum/2 from dual connect by level <= 30;

30 rows created.

EODA@PROD1> commit;

Commit complete.

EODA@PROD1> alter table fk_t add constraint fk_fkt foreign key (x) references pk_t(x); --创建外键约束

Table altered.

EODA@PROD1> 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
……

--模拟死锁
EODA@PROD1> delete from fk_t where x=1;  --第一个会话中执行删除，子表加上SX锁

1 row deleted.

EODA@PROD1> delete from pk_t where x=1;  --第一个会话中执行删除，父表加上SX锁

1 row deleted.

EODA@PROD1> delete from fk_t where x=2;  --第二个会话中执行删除

1 row deleted.

EODA@PROD1> delete from pk_t where x=2;  --第二个会话中执行删除，hang住

EODA@PROD1> delete from pk_t where x=2;  --再次在第一个会话中执行删除

EODA@PROD1> delete pk_t where x=2;
delete pk_t where x=2
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource   --第二个会话中立刻报错形成死锁

--再次模拟死锁
EODA@PROD1> create index idx_fk_x on fk_t(x) nologging;  --给外键增加索引

Index created.

EODA@PROD1> delete from fk_t where x=1;  --第一个会话中执行删除，子表加上SX锁

1 row deleted.

EODA@PROD1> delete from pk_t where x=1;  --第一个会话中执行删除，父表加上SX锁

1 row deleted.

EODA@PROD1> delete from fk_t where x=2;  --第二个会话中执行删除

1 row deleted.

EODA@PROD1> delete from pk_t where x=2;  --第二个会话中执行删除，均成功。

1 row deleted.

--未加索引的外键会存在的问题
1.引起全表锁。
2.当使用了on delete cascade，而且没有对子表加索引，要从父表中删除多行时，每删除一行就要扫描一次子表。
3.当你从父表查询子表，你会发现没有索引的话会使查询变慢。


--什么时候不需要对外键增加索引
1.不会从父表中删除行。
2.不会去更新父表的主键。
3.不会从父表连接到子表。

--参考来源《Oracle编程艺术深入理解数据库体系结构（第三版）》