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

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

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

十余年的东昌网站建设经验，针对设计、前端、开发、售后、文案、推广等六对一服务，响应快，48小时及时工作处理。全网营销推广的优势是能够根据用户设备显示端的尺寸不同，自动调整东昌建站的显示方式，使网站能够适用不同显示终端，在浏览器中调整网站的宽度，无论在任何一种浏览器上浏览网站，都能展现优雅布局与设计，从而大程度地提升浏览体验。创新互联公司从事“东昌网站设计”,“东昌网站推广”以来，每个客户项目都认真落实执行。

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 

1.         ID TEST  

2. ---------- ----------------------------------   

3.          1 test1  

4.          2 test2 

死锁现象的重现：

1. 在sql 窗口 执行：SELECT * FROM testLock FOR UPDATE; -- 加行级锁 并对内容进行修改，不要提交

查看引起死锁的语句：

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