Oracle常见死锁发生的原因以及解决方法详解

一.删除和更新之间引起的死锁

造成死锁的原因就是多个线程或进程对同一个资源的争抢或相互依赖。这里列举一个对同一个资源的争抢造成死锁的实例。

CREATE TABLE testLock(  ID NUMBER, 

test VARCHAR(100)  ) 

COMMIT  

INSERT INTO testLock VALUES(1,'test1'); 

INSERT INTO testLock VALUES(2,'test2'); 

COMMIT; 

SELECT * FROM testLock 

        ID TEST  
---------- ----------------------------------  
         1 test1  
         2 test2 

死锁现象的重现：
1.在sql 窗口 执行：

SELECT * FROM testLock FOR UPDATE; -- 加行级锁 并对内容进行修改，不要提交

查询死锁：

select s.username,l.object_id, l.session_id,s.serial#, s.lockwait,s.status,s.machine,s.program from v$session s,v$locked_object l where s.sid = l.session_id;

字段说明：

Username：死锁语句所用的数据库用户；
SID: session identifier， session 标示符，session 是通信双方从开始通信到通信结束期间的一个上下文。
SERIAL#: sid 会重用，但是同一个sid被重用时，serial#会增加，不会重复。
Lockwait：可以通过这个字段查询出当前正在等待的锁的相关信息。
Status：用来判断session状态。Active：正执行SQL语句。Inactive：等待操作。Killed：被标注为删除。
Machine： 死锁语句所在的机器。
Program： 产生死锁的语句主要来自哪个应用程序。

此时有一行在命令在等待操作Inactive

查看引起死锁的语句：

SQL>  select sql_text from v$sql where hash_value in   (select sql_hash_value from v$session where sid in  (select session_id from v$locked_object));  

此时没有死锁的语句。

2.另开一个command窗口，执行：delete from testLock WHERE ID=1;

此时发生死锁（注意此时要另开一个窗口，不然会提示：POST THE CHANGE RECORD TO THE DATABASE. 点yes 后强制commit）：

死锁查看：

SQL>  select s.username,l.object_id, l.session_id,s.serial#, s.lockwait,s.status,s.machine,s.program from v$session s,v$locked_object l where s.sid = l.session_id;

查看引起死锁的语句：

SQL>  select sql_text from v$sql where hash_value in   (select sql_hash_value from v$session where sid in  (select session_id from v$locked_object));  

查出以下语句死锁：

delete from testLock where  ID = 1 

死锁的处理：alter system kill session 'session_id,serial#';

alter system kill session '301,16405'; 

再查看一下死锁，会发现已经没有stauts为active的记录了，发生死锁的语句已经被终止。

二.在外键上没有加索引引起的死锁

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

所有的删除操作都可以成功执行，关于两种情况下锁信息的不同这里就不深入分析了，重点就是在外键列上建立索引。

虽然有一些文章提到过，如果满足某些情况，可以不在外键列上建立的索引，但是我的观点一向是，既然创建了外键，就不要在乎再多一个索引，因为一个索引所增加的代价，与缺失这个索引所带来的问题相比，是微不足道的。