死鎖存在的兩個必要條件，一個是多工工作的併發，另一個是共享資源的獨佔性需求。只要一個系統（廣義系統）中存在這兩個前提，我們就認為可能出現死鎖的情況。

死鎖描述的是一種狀態。當兩個或兩個以上的任務單元在執行過程中，因為請求資源出現等待，因資源永遠不能獲得而相互等待的狀態。如果沒有外力的作用，死鎖狀態是會一直持續下去。死鎖是伴隨著多工、並行操作產生的，在單任務情況下，一個任務單元可以使用並且獨佔所有資源，不存在資源等待的情況，所以也沒有死鎖的情況。在進入多工系統環境下，多個任務之間存在資源共享和獨佔的需求，才可能出現死鎖。

死鎖最簡單的例子：任務A，B，資源1，2。任務A獨佔了資源1，任務B獨佔了資源2。此時，任務A要資源2，向任務B提出請求並等待。任務B要求資源1，並且也等待。AB兩者均不釋放所佔有的資源，就造成了死鎖。

Oracle的鎖機制是建立在行鎖一級，在插入、更新行一級資訊的時候，會加入獨佔鎖內容。死鎖是資料庫經常發生的問題，資料庫一般不會無緣無故產生死鎖，死鎖通常都是由於我們應用程式的設計本身造成的。下面透過實驗模擬一個死鎖現象：兩個session分別更新兩條記錄，在一個事務裡再嘗試更新對方記錄

建立一張測試表tb_1

SCOTT@seiang11g>create table tb_1 as select * from emp;

Table created.

SCOTT@seiang11g>select * from tb_1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

分別在兩個不同的會話進行更新操作

Session1：

SCOTT@seiang11g>update tb_1 set ename='WJQ' where empno=7369;

1 row updated.

Session2：

SCOTT@seiang11g>update tb_1 set ename='SEIANG' where empno=7499;

1 row updated.

Session1：

再執行empno=7499欄位的更新，此時語句已經hang住，需要等待會話2發出commit或rollback動作，表現為停住操作，不斷的輪詢資源

SCOTT@seiang11g>update tb_1 set ename='SEIANG_WJQ' where empno=7499;

update tb_1 set ename='SEIANG_WJQ' where empno=7499

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

注意：Oracle此處的處理：只是駁回了一方的請求，並沒有回滾該請求，也沒有將另一方的hange狀態解除。

Session2：

一旦執行更新，Session1就會馬上報錯ORA-00060，當前session被hang住

SCOTT@seiang11g>update tb_1 set ename='WJQORA' where empno=7369;

思考：
顯然，這個過程中是Oracle內部的防護機制起了作用，防止了系統中死鎖的發生。在Oracle中，存在某種輪詢的機制，隨時檢查系統中出現的多會話被hange住的情況，一旦發生，就將一個session被hange住的請求退回，丟擲ORA-00060錯誤。

在兩個session互鎖的情況下，Oracle死鎖檢測程式可以起作用。那麼，如果死鎖結構複雜的時候，是不是Oracle的檢測機制會失效。在使用4-5個session進行檢查，雖然檢測起效的時間有長有短，但最後都是將死鎖的狀態加以解除。

有一點需要注意，Oracle解決死鎖的方式只是將請求拒絕，並不是將事務回滾。所以，在解決死鎖之後，其他被hange住的session依然還是被hange住。所以，從應用程式和PL/SQL的角度，如果接受到了ORA-00060錯誤，應該做的工作就是回滾當前事務，解決整體的資源爭用現象。

告警日誌中的相關資訊：

[oracle@seiang11g trace]$ tail -f alert_seiang11g.log

Tue Sep 19 06:06:01 2017

Archived Log entry 20 added for thread 1 sequence 24 ID 0xc94cf059 dest 1:

Tue Sep 19 14:51:24 2017

ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/oradb11g/seiang11g/trace/seiang11g_ora_12360.trc.

分析Trace日誌檔案中記錄的相關資訊，可以找到相關的死鎖語句：

[oracle@seiang11g ~]$ more /u01/app/oracle/diag/rdbms/oradb11g/seiang11g/trace/seiang11g_ora_12360.trc

Trace file /u01/app/oracle/diag/rdbms/oradb11g/seiang11g/trace/seiang11g_ora_12360.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

System name: Linux

Node name: seiang11g.comsys.com

Release: 3.10.0-514.el7.x86_64

Version: #1 SMP Tue Nov 22 16:42:41 UTC 2016

Machine: x86_64

VM name: VMWare Version: 6

Instance name: seiang11g

Redo thread mounted by this instance: 1

Oracle process number: 19

Unix process pid: 12360, image: oracle@seiang11g.comsys.com (TNS V1-V3)

*** 2017-09-19 14:51:24.355

*** SESSION ID:(52.3879) 2017-09-19 14:51:24.355

*** CLIENT ID:() 2017-09-19 14:51:24.355

*** SERVICE NAME:(SYS$USERS) 2017-09-19 14:51:24.355

*** MODULE NAME:(SQL*Plus) 2017-09-19 14:51:24.355

*** ACTION NAME:() 2017-09-19 14:51:24.355

*** 2017-09-19 14:51:24.355

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

Deadlock graph:

---------Blocker(s)-------- ---------Waiter(s)---------

Resource Name process session holds waits process session holds waits

TX-00090016-000004ec 19 52 X 30 53 X

TX-0005001f-0000050a 30 53 X 19 52 X

session 52: DID 0001-0013-000032A7 session 53: DID 0001-001E-00000A06

session 53: DID 0001-001E-00000A06 session 52: DID 0001-0013-000032A7

Rows waited on:

Session 52: obj - rowid = 00015A9A - AAAVqaAAEAAAAITAAB

(dictionary objn - 88730, file - 4, block - 531, slot - 1)

Session 53: obj - rowid = 00015A9A - AAAVqaAAEAAAAITAAA

(dictionary objn - 88730, file - 4, block - 531, slot - 0)

----- Information for the OTHER waiting sessions -----

Session 53:

sid: 53 ser: 4307 audsid: 155632 user: 83/SCOTT

flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

flags2: (0x40009) -/-/INC

pid: 30 O/S info: user: oracle, term: UNKNOWN, ospid: 12721

image: oracle@seiang11g.comsys.com (TNS V1-V3)

client details:

O/S info: user: oracle, term: pts/5, ospid: 12703

machine: seiang11g.comsys.com program: sqlplus@seiang11g.comsys.com (TNS V1-V3)

application name: SQL*Plus, hash value=3669949024

current SQL:

update tb_1 set ename='WJQORA' where empno=7369

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=g3gy243v5tj1c) -----

update tb_1 set ename='SEIANG_WJQ' where empno=7499

根據trace資訊，查詢：

SYS@seiang11g> select addr,pid,spid,username,serial# from v$process t where t.PID in (19,30);

ADDR PID SPID USERNAME SERIAL#

---------------- ---------- ------------------------ --------------- ----------

00000000AA51A628 19 12360 oracle 98

00000000AA525E10 30 12721 oracle 27

透過dba_blockers表中的HOLDING_SESSION欄位可以查詢到hang住會話的ID
SYS@seiang11g>select * from dba_blockers;

HOLDING_SESSION

---------------

52

Oracle死鎖發生

1、 Oracle對查詢不加鎖。Oracle本身支援多版本一致讀，如果當前的資料塊正在被修改（獨佔）而並未提交，Oracle會根據SCN查詢日誌和Undo機制，找到合適SCN的版本返回結果。所以，在查詢的時候，是不需要加鎖的。
2、 Oracle資料操作使用行級鎖（本質上是事務鎖），實現最小粒度的獨佔範圍。Oracle在DML的時候，只會對操作的行進行獨佔鎖定，而不是過大的資料單元（如頁page）。這樣，就保證了不會引起過多的獨佔資源。

同時，Oracle本身也提供了死鎖監視程式功能，能及時發現死鎖狀態，並自動的進行解鎖。在這些機制下，Oracle認為死鎖發生的機率很低（起碼自身不會引起死鎖）。只有一種情況會引發死鎖，就是開發人員手工提高加鎖的級別。

Oracle對於SQL的擴充中，有一部分是顯示物件加鎖。從lock table XXX到select XXX for update。這些操作都會引起對物件鎖級別的提升，這些都可能引發死鎖的發生。

◆ 定位死鎖語句：

1）執行下面SQL，先檢視哪些表被鎖住了:

SYS@seiang11g>select c.owner, c.object_name, c.object_type, b.sid,

2 b.serial#, b.lockwait, b.status, b.osuser, b.machine, b.process, b.program

3 from v$locked_object a ,

4 v$session b,

5 dba_objects c

6 where b.sid = a.session_id

7 and a.object_id = c.object_id;

OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL# LOCKWAIT STATUS OSUSER MACHINE PROCESS PROGRAM

---------- --------------- ------------------- ---------- ---------- ---------------- -------- --------------- -------------------- ------------------------ -------------------------

SCOTT TB_1 TABLE 53 4307 00000000AAD39888 ACTIVE oracle seiang11g.comsys.com 12703 sqlplus@seiang11g.comsys.

com (TNS V1-V3)

SCOTT TB_1 TABLE 52 3879 INACTIVE oracle seiang11g.comsys.com 12359 sqlplus@seiang11g.comsys.

com (TNS V1-V3)

如果有輸出的結果，則說明有死鎖，且能看到死鎖的機器是哪一臺。欄位說明：

OWNER：死鎖語句所用的資料庫使用者，這裡是HR使用者。
OBJECT_NAME：產生死鎖的物件，這裡是TEST表。
OBJECT_TYPE：產生死鎖的物件型別，這裡是TABLE。
SID：SESSION標識，常用於連線其它列。
SERIAL#：SID有可能會重複，當兩個session的SID重複時，SERIAL#用來區別session
LOCKWAIT：可以透過這個欄位查詢出當前正在等待的鎖的相關資訊，如果有內容表示被死鎖或者有鎖等待事件。
STATUS：用來判斷session狀態。ACTIVE:正執行。INACTIVE:等待操作。KILLED:被標註為殺死。
OSUSER：客戶端作業系統使用者名稱。
MACHINE：客戶端作業系統的機器名。
PROCESS：客戶端程式的ID。
PROGRAM：客戶端執行的應用程式。

2）檢視引起死鎖的會話

SYS@seiang11g>select b.username,b.sid,b.serial#,logon_time

2 from v$locked_object a,v$session b

3 where a.session_id = b.sid order by b.logon_time;

USERNAME SID SERIAL# LOGON_TIM

------------------------------ ---------- ---------- ---------

SCOTT 52 3879 19-SEP-17

SCOTT 53 4307 19-SEP-17

2）用dba使用者執行以下語句，可以檢視到被死鎖的語句。

SYS@seiang11g>select sql_text from v$sql where (address,hash_value) in

2 (select sql_address,sql_hash_value from v$session where sid in

3 (select session_id from v$locked_object));

SQL_TEXT

----------------------------------------------------------------------------------------------------

update tb_1 set ename='WJQORA' where empno=7369

select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.se

ssion_id = b.sid order by b.logon_time

或者是：

SYS@seiang11g>select sql_text from v$sql where (address,hash_value) in

2 (select sql_address,sql_hash_value from v$session where lockwait is not null);

SQL_TEXT

----------------------------------------------------------------------------------------------------

update tb_1 set ename='WJQORA' where empno=7369

這裡查出來出現死鎖的語句和之前的測試結果一致。

3）檢視被阻塞的會話

SYS@seiang11g>select * from dba_waiters;

WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2

--------------- --------------- ------------ ---------- --------------- ---------- ----------

53 52 Transaction Exclusive Exclusive 589846 1260

那麼，如果出現死鎖的情況，我們如何處理？

◆ 解決死鎖問題

方法一：提交或回滾死鎖的會話

這裡Session1出現死鎖，只要執行commit或者rollback就可以解除死鎖，只不過事務中第一個SQL執行成功，第二個SQL執行失敗。

Session1：

SCOTT@seiang11g>commit;:

Commit complete.

此時，死鎖狀態解除：

SYS@seiang11g>select sid, serial#, username, command, lockwait, osuser from v$session where lockwait is not null;

no rows selected

方式二：重新啟動資料庫

方式三：殺掉阻塞的會話

（1）查詢死鎖的程式：

SYS@seiang11g>select sid, serial#, username, command, lockwait, osuser from v$session where lockwait is not null;

SID SERIAL# USERNAME COMMAND LOCKWAIT OSUSER

---------- ---------- ------------------------------ ---------- ---------------- ---------------

53 4307 SCOTT 6 00000000AAD39888 oracle

2）kill掉這個死鎖的程式：

SYS@seiang11g>alter system kill session '53,4307';

◆ 總結：

原因分析：

首先死鎖是怎麼發生的:
簡單說，兩個或多個併發事務相互等待，互補想讓，沒有外力就無法繼續下去，這就製造了死鎖。資料庫檢測到死鎖時，就會將死鎖的各個事務回滾，並丟擲ORA-00060異常。所以上面報錯出現的情況極少，將死鎖解除後又可以正常執行。

解決思路：

死鎖是無法根除的，特別在高併發的系統中。只有儘可能最佳化速度，減少互相等待的機會。原則為：執行速度越快越好，訪問資源時鎖的範圍越小越好。根據這個原則就可以最佳化我們的sql，將負責的sql拆分，若果業務允許的情況下。還有事務越小越好。

解決技巧：

1、出現死鎖異常後，手工將死鎖解開。
2、找出造成死鎖的sql：
（1）直接看日誌：程式中日誌做的很詳細的話，是能夠找到具體哪個sql報的錯，操作的哪個表，還有別的模組也操作這個表，執行緒，併發的程式也會引起。
（2）透過oracle的後臺v$session表和 v$sql 的分析找到。
3、對sql進行最佳化。

參考連結：

http://blog.itpub.net/17203031/viewspace-682115/

http://blog.itpub.net/31394774/viewspace-2144941/

作者：SEian.G（苦練七十二變，笑對八十一難）

【死鎖】ORA-00060: deadlock detected while waiting for resource

相關文章