客户的10.2.0.4 RAC for AIX环境频繁出现ORA-60死锁问题，导致应用程序无法顺利执行。
经过一系列的诊断，发现最终问题是由于外键上没有建立索引所致，由于程序在主子表上删除数据，缺少索引导致行级锁升级为表级锁，最终导致大量的锁等待和死锁。
下面通过一个例子简单模拟一下问题：
SQL> CREATE TABLE t_p (id NUMBER PRIMARY KEY, name varchar2(30)); TABLE created. SQL> CREATE TABLE t_f (fid NUMBER, f_name varchar2(30), FOREIGN KEY (fid) REFERENCES t_p); TABLE created. SQL> INSERT INTO t_p VALUES (1, 'a'); 1 ROW created. SQL> INSERT INTO t_f VALUES (1, 'a'); 1 ROW created. SQL> INSERT INTO t_p VALUES (2, 'b'); 1 ROW created. SQL> INSERT INTO t_f VALUES (2, 'c'); 1 ROW created. SQL> commit; Commit complete. SQL> DELETE t_f WHERE fid = 2; 1 ROW deleted. |
这时在会话2同样对子表进行删除：
SQL2> DELETE t_f WHERE fid = 1; 1 ROW deleted. |
回到会话1执行主表的删除：
SQL> DELETE t_p WHERE id = 2; 会话被锁，回到会话2执行主表的删除： SQL2> DELETE t_p WHERE id = 1; |
会话同样被锁，这时会话1的语句被回滚，出现ORA-60死锁错误：
DELETE t_p WHERE id = 2 * ERROR at line 1: ORA-00060: deadlock detected while waiting FOR resource SQL> ROLLBACK; ROLLBACK complete. |
将会话1操作回滚，会话2同样回滚并建立外键列上的索引：
1 ROW deleted. SQL2> ROLLBACK; ROLLBACK complete. SQL2> CREATE INDEX ind_t_f_fid ON t_f(fid); INDEX created. |
重复上面的步骤会话1删除子表记录：
SQL> DELETE t_f WHERE fid = 2; 1 ROW deleted. |
会话2删除子表记录：
SQL2> DELETE t_f WHERE fid = 1; 1 ROW deleted. |
会话1删除主表记录：
SQL> DELETE t_p WHERE id = 2; 1 ROW deleted. |
会话2删除主表记录：
SQL> DELETE t_p WHERE id = 1; 1 ROW deleted. |
所有的删除操作都可以成功执行，关于两种情况下锁信息的不同这里就不深入分析了，重点就是在外键列上建立索引。
虽然有一些文章提到过，如果满足某些情况，可以不在外键列上建立的索引，但是我的观点一向是，既然创建了外键，就不要在乎再多一个索引，因为一个索引所增加的代价，与缺失这个索引所带来的问题相比，是微不足道的。