SQL Server死鎖總結教程

 

1. 死鎖原理

    根據操作系統中的定義：死鎖是指在一組進程中的各個進程均占有不會釋放的資源，但因互相申請被其他進程所站用不會釋放的資源而處於的一種永久等待狀態。

    死鎖的四個必要條件：
互斥條件 (Mutual exclusion) ：資源不能被共享，只能由一個進程使用。
請求與保持條件 (Hold and wait) ：已經得到資源的進程可以再次申請新的資源。
非剝奪條件 (No pre-emption) ：已經分配的資源不能從相應的進程中被強制地剝奪。
循環等待條件 (Circular wait) ：系統中若干進程組成環路，該環路中每個進程都在等待相鄰進程正占用的資源。

 

對應到 SQL Server 中，當在兩個或多個任務中，如果每個任務鎖定了其他任務試圖鎖定的資源，此時會造成這些任務永久阻塞，從而出現死鎖；這些資源可能是：單行 (RID ，堆中的單行 ) 、索引中的鍵 (KEY ，行鎖 ) 、頁 (PAG ， 8KB) 、區結構 (EXT ，連續的 8 頁 ) 、堆或 B 樹 (HOBT) 、表 (TAB ，包括數據和索引 ) 、文件 (File ，數據庫文件 ) 、應用程序專用資源 (APP) 、元數據 (METADATA) 、分配單元 (Allocation_Unit) 、整個數據庫 (DB) 。 一個死鎖示例如下圖所示：

    說明： T1 、 T2 表示兩個任務； R1 和 R2 表示兩個資源；由資源指向任務的箭頭 ( 如 R1->T1 ， R2->T2) 表示該資源被改任務所持有；由任務指向資源的箭頭 ( 如 T1->S2 ， T2->S1) 表示該任務正在請求對應目標資源；
    其滿足上面死鎖的四個必要條件：
(1). 互斥：資源 S1 和 S2 不能被共享，同一時間只能由一個任務使用；
(2). 請求與保持條件： T1 持有 S1 的同時，請求 S2 ； T2 持有 S2 的同時請求 S1 ；
(3). 非剝奪條件： T1 無法從 T2 上剝奪 S2 ， T2 也無法從 T1 上剝奪 S1 ；
(4). 循環等待條件：上圖中的箭頭構成環路，存在循環等待。

 

2. 死鎖排查

(1). 使用 SQL Server 的系統存儲過程 sp_who 和 sp_lock ，可以查看當前數據庫中的鎖情況；進而根據 objectID(@objID)(SQL Server 2005)/ object_name(@objID)(Sql Server 2000) 可以查看哪個資源被鎖，用 dbcc ld(@blk) ，可以查看最後一條發生給 SQL Server 的 Sql 語句；

CREATE   Table  #Who(spid  int ,
    ecid  int ,
    status  nvarchar ( 50 ),
    loginname  nvarchar ( 50 ),
    hostname  nvarchar ( 50 ),
    blk  int ,
    dbname  nvarchar ( 50 ),
    cmd  nvarchar ( 50 ),
    request_ID  int );

CREATE   Table  #Lock(spid  int ,
    dpid  int ,
    objid  int ,
    indld  int ,
     [ Type ]   nvarchar ( 20 ),
    Resource  nvarchar ( 50 ),
    Mode  nvarchar ( 10 ),
    Status  nvarchar ( 10 )
);

INSERT   INTO  #Who
     EXEC  sp_who active   -- 看哪個引起的阻塞，blk 
INSERT   INTO  #Lock
     EXEC  sp_lock   -- 看鎖住了那個資源id，objid 

DECLARE   @DBName   nvarchar ( 20 );
SET   @DBName = ' NameOfDataBase '

SELECT  #Who. *   FROM  #Who  WHERE  dbname = @DBName
SELECT  #Lock. *   FROM  #Lock
     JOIN  #Who
         ON  #Who.spid = #Lock.spid
             AND  dbname = @DBName ;

-- 最後發送到SQL Server的語句
DECLARE  crsr  Cursor   FOR
     SELECT  blk  FROM  #Who  WHERE  dbname = @DBName   AND  blk <> 0 ;
DECLARE   @blk   int ;
open  crsr;
FETCH   NEXT   FROM  crsr  INTO   @blk ;
WHILE  ( @@FETCH_STATUS   =   0 )
BEGIN ;
     dbcc  inputbuffer( @blk );
     FETCH   NEXT   FROM  crsr  INTO   @blk ;
END ;
close  crsr;
DEALLOCATE  crsr;

-- 鎖定的資源
SELECT  #Who.spid,hostname,objid, [ type ] ,mode, object_name (objid)  as  objName  FROM  #Lock
     JOIN  #Who
         ON  #Who.spid = #Lock.spid
             AND  dbname = @DBName
     WHERE  objid <> 0 ;

DROP   Table  #Who;
DROP   Table  #Lock;

(2). 使用 SQL Server Profiler 分析死鎖 : 將 Deadlock graph 事件類添加到跟蹤。此事件類使用死鎖涉及到的進程和對象的 XML 數據填充跟蹤中的 TextData 數據列。 SQL Server 事件探查器   可以將 XML 文檔提取到死鎖 XML (.xdl) 文件中，以後可在 SQL Server Management Studio 中查看該文件。

 

3. 避免死鎖

    上面 1 中列出了死鎖的四個必要條件，我們只要想辦法破其中的任意一個或多個條件，就可以避免死鎖發生，一般有以下幾種方法 (FROM Sql Server 2005 聯機叢書 ) ：
(1). 按同一順序訪問對象。 ( 注：避免出現循環 )
(2). 避免事務中的用戶交互。 ( 注：減少持有資源的時間，較少鎖競爭 )
(3). 保持事務簡短並處於一個批處理中。 ( 注：同 (2) ，減少持有資源的時間 )
(4). 使用較低的隔離級別。 ( 注：使用較低的隔離級別（例如已提交讀）比使用較高的隔離級別（例如可序列化）持有共享鎖的時間更短，減少鎖競爭 )
(5). 使用基於行版本控制的隔離級別 ： 2005 中支持快照事務隔離和指定 READ_COMMITTED 隔離級別的事務使用行版本控制，可以將讀與寫操作之間發生的死鎖幾率降至最低：
SET ALLOW_SNAPSHOT_ISOLATION ON -- 事務可以指定 SNAPSHOT 事務隔離級別 ;
SET READ_COMMITTED_SNAPSHOT ON  -- 指定 READ_COMMITTED 隔離級別的事務將使用行版本控制而不是鎖定。默認情況下 ( 沒有開啟此選項，沒有加 with nolock 提示 ) ， SELECT 語句會對請求的資源加 S 鎖 ( 共享鎖 ) ；而開啟了此選項後， SELECT 不會對請求的資源加 S 鎖。
注意： 設置 READ_COMMITTED_SNAPSHOT 選項時，數據庫中只允許存在執行 ALTER DATABASE 命令的連接。在 ALTER DATABASE 完成之前，數據庫中決不能有其他打開的連接。數據庫不必一定要處於單用戶模式中。
(6). 使用綁定連接 。 ( 注：綁定會話有利於在同一台服務器上的多個會話之間協調操作。綁定會話允許一個或多個會話共享相同的事務和鎖 ( 但每個回話保留其自己的事務隔離級別 ) ，並可以使用同一數據，而不會有鎖沖突。可以從同一個應用程序內的多個會話中創建綁定會話，也可以從包含不同會話的多個應用程序中創建綁定會話。在一個會話中開啟事務 (begin tran) 後，調用 exec sp_getbindtoken @Token out; 來取得 Token ，然後傳入另一個會話並執行 EXEC sp_bindsession @Token 來進行綁定 ( 最後的示例中演示了綁定連接 ) 。

 

4. 死鎖處理方法：

(1). 根據 2 中提供的 sql ，查看那個 spid 處於 wait 狀態，然後用 kill spid 來干掉 ( 即破壞死鎖的第四個必要條件 : 循環等待 ) ；當然這只是一種臨時解決方案，我們總不能在遇到死鎖就在用戶的生產環境上排查死鎖、 Kill sp ，我們應該考慮如何去避免死鎖。

(2). 使用 SET LOCK_TIMEOUT timeout_period( 單位為毫秒 ) 來設定鎖請求超時 。默認情況下，數據庫沒有超時期限 (timeout_period 值為 -1 ，可以用 SELECT @@LOCK_TIMEOUT 來查看該值，即無限期等待 ) 。當請求鎖超過 timeout_period 時，將返回錯誤。 timeout_period 值為 0 時表示根本不等待，一遇到鎖就返回消息。設置鎖請求超時，破環了死鎖的第二個必要條件 ( 請求與保持條件 ) 。

服務器: 消息  1222 ，級別  16 ，狀態  50 ，行  1
已超過了鎖請求超時時段。

 

 

(3). SQL Server 內部有一個鎖監視器線程執行死鎖檢查 ，鎖監視器對特定線程啟動死鎖搜索時，會標識線程正在等待的資源；然後查找特定資源的所有者，並遞歸地繼續執行對那些線程的死鎖搜索，直到找到一個構成死鎖條件的循環。檢測到死鎖後，數據庫引擎   選擇運行回滾開銷最小的事務的會話作為死鎖犧牲品，返回 1205 錯誤，回滾死鎖犧牲品的事務並釋放該事務持有的所有鎖，使其他線程的事務可以請求資源並繼續運行。

 

5. 兩個死鎖示例及解決方法

5.1 SQL 死鎖

(1). 測試用的基礎數據：

CREATE   TABLE  Lock1(C1  int   default ( 0 ));
CREATE   TABLE  Lock2(C1  int   default ( 0 ));
INSERT   INTO  Lock1  VALUES ( 1 );
INSERT   INTO  Lock2  VALUES ( 1 );

 

 

(2). 開兩個查詢窗口，分別執行下面兩段 sql

-- Query 1
Begin   Tran
   Update  Lock1  Set  C1 = C1 + 1 ;
   WaitFor  Delay  ' 00:01:00 ' ;
   SELECT   *   FROM  Lock2
Rollback   Tran ;

 

 

-- Query 2
Begin   Tran
   Update  Lock2  Set  C1 = C1 + 1 ;
   WaitFor  Delay  ' 00:01:00 ' ;
   SELECT   *   FROM  Lock1
Rollback   Tran ;

 

上面的 SQL 中有一句 WaitFor Delay '00:01:00' ，用於等待 1 分鐘，以方便查看鎖的情況。