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

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_NAMECONSTRAINT_NAME COLUMNS--------------- --------------- ------------------------------FK_TFK_FKTX……--模拟死锁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编程艺术深入理解数据库体系结构（第三版）》