外键缺少索引引发的死锁

客户的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.

所有的删除操作都可以成功执行,关于两种情况下锁信息的不同这里就不深入分析了,重点就是在外键列上建立索引。
虽然有一些文章提到过,如果满足某些情况,可以不在外键列上建立的索引,但是我的观点一向是,既然创建了外键,就不要在乎再多一个索引,因为一个索引所增加的代价,与缺失这个索引所带来的问题相比,是微不足道的。

This entry was posted in ORACLE and tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *