由于外键不建索引引起的锁

模拟环境 ：
在会话窗口1
SQL> create table t1 (id number primary key ,name varchar2(12));
Table created.
SQL> create table t2 (id references t1,addr varchar2(12));
Table created.
SQL>
SQL> insert into t1 values(‘1’,’a’);
1 row created.
SQL> insert into t1 values (‘2’,’b’);
1 row created.
SQL> commit
2 ;
Commit complete.
SQL>
环境准备完成
切换到会话窗口2往子表里面插入数据
SQL> insert into t2 values(1,’guangdong’);
1 row created.
SQL>
没有commit
在会话窗口1删除一条记录
SQL> delete from t1 where id =2;

会看到在窗口1出现阻塞的现象，因为会话1要删除t1表时会偿试去对t1表加一个全表锁，但是由于会话2插入数据的时候没有保存，t1表本来就存在了一个锁，所以在执行delete from t1 where id =2; 的时候就会出现阻塞的现象。

给外键加上索引，然后重复以上操作
SQL> create index ind_t2 on t2 (id);
Index created
在会话窗口2 执行：
SQL> insert into t2 values (1,’gd’);
1 row created.
SQL>
没有commit；
在会话窗口1 执行
SQL> delete from t1 where id =2;
1 row deleted.
SQL> commit;
Commit complete.
SQL>
可以看到在子表的外键上加上索引的时候，就不会出现这种阻塞，所以平时建子外键的时候，子表的外键一定要给它建个索引。

以下脚本是查询数据库中未建索引的主键
column columns format a30 word_wrapped;
column table_name format a15 word_wrapped;
column constraint_name format a15 word_wrapped;
select table_name ,constraint_name,
cname1 || nvl2(cname2,’,’||cname2,null)||
nvl2(cname3,’,’||cname3,null)||nvl2(cname4,’,’||cname4,null)||
nvl2(cname5,’,’||cname5,null)||nvl2(cname6,’,’||cname6,null)||
nvl2(cname7,’,’||cname7,null)||nvl2(cname8,’,’||cname8,null)
columns
from (select b.table_name,
b.constraint_name,
max(decode(position,1,column_name,null)) cname1,
max(decode(position,2,column_name,null)) cname2,
max(decode(position,3,column_name,null)) cname3,
max(decode(position,4,column_name,null)) cname4,
max(decode(position,5,column_name,null)) cname5,
max(decode(position,6,column_name,null)) cname6,
max(decode(position,7,column_name,null)) cname7,
max(decode(position,8,column_name,null)) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns)a,
user_constraints b
where a.constraint_name=b.constraint_name
and b.constraint_type=’R’
group by b.table_name,b.constraint_name
) cons
where col_cnt > ALL
(select count(*)
from user_ind_columns i,
user_indexes ui
where i.table_name=cons.table_name
and i.column_name in (cname1,cname2,cname3,cname4,
cname5,cname6,cname7,cname8)
and i.column_position <= cons.col_cnt
and ui.table_name=i.table_name
and ui.index_name = i.index_name
and ui.index_type IN (‘NORMAL’,’NORMAL/REV’)
group by i.index_name
);