Oracle常見死鎖發(fā)生的原因以及解決方法

一.刪除和更新之間引起的死鎖

造成死鎖的原因就是多個(gè)線程或進(jìn)程對(duì)同一個(gè)資源的爭搶或相互依賴。這里列舉一個(gè)對(duì)同一個(gè)資源的爭搶造成死鎖的實(shí)例。

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 

死鎖現(xiàn)象的重現(xiàn)：

1. 在sql 窗口 執(zhí)行：SELECT * FROM testLock FOR UPDATE; -- 加行級(jí)鎖 并對(duì)內(nèi)容進(jìn)行修改，不要提交

查詢死鎖：

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：死鎖語句所用的數(shù)據(jù)庫用戶；
SID: session identifier， session 標(biāo)示符，session 是通信雙方從開始通信到通信結(jié)束期間的一個(gè)上下文。
SERIAL#: sid 會(huì)重用，但是同一個(gè)sid被重用時(shí)，serial#會(huì)增加，不會(huì)重復(fù)。
Lockwait：可以通過這個(gè)字段查詢出當(dāng)前正在等待的鎖的相關(guān)信息。
Status：用來判斷session狀態(tài)。Active：正執(zhí)行SQL語句。Inactive：等待操作。Killed：被標(biāo)注為刪除。
Machine： 死鎖語句所在的機(jī)器。
Program： 產(chǎn)生死鎖的語句主要來自哪個(gè)應(yīng)用程序。

此時(shí)有一行在命令在等待操作 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));  

此時(shí)沒有死鎖的語句。

2.另開一個(gè)command窗口，執(zhí)行：delete from testLock WHERE ID=1;

此時(shí)發(fā)生死鎖（注意此時(shí)要另開一個(gè)窗口，不然會(huì)提示：POST THE CHANGE RECORD TO THE DATABASE. 點(diǎn)yes 后強(qiáng)制commit）：

死鎖查看：

1. 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'; 

再查看一下死鎖，會(huì)發(fā)現(xiàn)已經(jīng)沒有stauts為active的記錄了， 發(fā)生死鎖的語句已經(jīng)被終止。

二.在外鍵上沒有加索引引起的死鎖

客戶的10.2.0.4 RAC for AIX環(huán)境頻繁出現(xiàn)ORA-60死鎖問題，導(dǎo)致應(yīng)用程序無法順利執(zhí)行。 
經(jīng)過一系列的診斷，發(fā)現(xiàn)最終問題是由于外鍵上沒有建立索引所致，由于程序在主子表上刪除數(shù)據(jù)，缺少索引導(dǎo)致行級(jí)鎖升級(jí)為表級(jí)鎖，最終導(dǎo)致大量的鎖等待和死鎖。 
下面通過一個(gè)例子簡單模擬一下問題： 
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. 
這時(shí)在會(huì)話2同樣對(duì)子表進(jìn)行刪除： 
SQL2> delete t_f where fid = 1; 
1 row deleted. 
回到會(huì)話1執(zhí)行主表的刪除： 
SQL> delete t_p where id = 2; 
會(huì)話被鎖，回到會(huì)話2執(zhí)行主表的刪除： 
SQL2> delete t_p where id = 1; 
會(huì)話同樣被鎖，這時(shí)會(huì)話1的語句被回滾，出現(xiàn)ORA-60死鎖錯(cuò)誤： 
delete t_p where id = 2 
* 
ERROR at line 1: 
ORA-00060: deadlock detected while waiting for resource 
SQL> rollback; 
Rollback complete. 
將會(huì)話1操作回滾，會(huì)話2同樣回滾并建立外鍵列上的索引： 
1 row deleted. 
SQL2> rollback; 
Rollback complete. 
SQL2> create index ind_t_f_fid on t_f(fid); 
Index created. 
重復(fù)上面的步驟會(huì)話1刪除子表記錄： 
SQL> delete t_f where fid = 2; 
1 row deleted. 
會(huì)話2刪除子表記錄： 
SQL2> delete t_f where fid = 1; 
1 row deleted. 
會(huì)話1刪除主表記錄： 
SQL> delete t_p where id = 2; 
1 row deleted. 
會(huì)話2刪除主表記錄： 
SQL> delete t_p where id = 1; 
1 row deleted. 
所有的刪除操作都可以成功執(zhí)行，關(guān)于兩種情況下鎖信息的不同這里就不深入分析了，重點(diǎn)就是在外鍵列上建立索引。 
雖然有一些文章提到過，如果滿足某些情況，可以不在外鍵列上建立的索引，但是我的觀點(diǎn)一向是，既然創(chuàng)建了外鍵，就不要在乎再多一個(gè)索引，因?yàn)橐粋€(gè)索引所增加的代價(jià)，與缺失這個(gè)索引所帶來的問題相比，是微不足道的。