Deadlock的一些總結（死結分析及處理）

文章目錄

• 關於作者:

1.1.1 摘要

      在系統設計過程中，系統的穩定性、響應速度和讀寫速度至關重要，就像12306.cn那樣，當然我們可以通過提高系統並發能力來提高系統效能總體效能，但在並發作用下也會出現一些問題，例如死結。

     今天的博文將著重介紹死結的原因和解決方案。

1.1.2 本文

      定義：

      死結是由於並發進程只能按互斥方式訪問臨界資源等多種因素引起的，並且是一種與執行時間和速度密切相關的錯誤現象。

      死結的定義：若在一個進程集合中，每一個進程都在等待一個永遠不會發生的事件而形成一個永久的阻塞狀態，這種阻塞狀態就是死結。

      死結產生的必要條件：

      1.互斥mutual exclusion)：系統存在著臨界資源；

      2.佔有並等待(hold and wait)：已經得到某些資源的進程還可以申請其他新資源；

      3.不可剝奪(no preemption)：已經分配的資源在其宿主沒有釋放之前不允許被剝奪；

      4.迴圈等待(circular waiting)：系統中存在多個（大於2個）進程形成的封閉的進程鏈，鏈中的每個進程都在等待它的下一個進程所佔有的資源；

圖1死結產生條件

      我們知道哲學家就餐問題是在電腦科學中的一個經典問題（並發和死結），用來示範在並行計算中多線程同步(Synchronization)時產生的問題，其中一個問題就是存在死結風險。

圖2哲學家就餐問題（圖片源於wiki）

     而對應到資料庫中，當兩個或多個任務中，如果每個任務鎖定了其他任務試圖鎖定資源，此時會造成這些任務阻塞，從而出現死結；這些資源可能是：單行(RID，堆中的單行)、索引中的鍵(KEY，行鎖)、頁(PAG，8KB)、區結構(EXT，連續的8頁)、堆或B樹(HOBT) 、表(TAB，包括資料和索引)、檔案(File，資料庫檔案)、應用程式專用資源(APP)、中繼資料(METADATA)、配置單位(Allocation_Unit)、整個資料庫(DB)。

     假設我們定義兩個進程P1和P2，它們分別擁有資源R2和R1，但P1需要額外的資源R1恰好P2也需要R2資源，而且它們都不釋放自己擁有的資源，這時資源和進程之間形成了一個環從而形成死結。

 

圖3死結（圖片源於wiki）

SQL Server中死結排查：

      1.使用SQL Server中系統預存程序sp_who和sp_lock，可以查看當前資料庫中阻塞進程的情況；

      首先我們在資料庫中建立兩個表Who和Lock分別用來存放阻塞和鎖定的資料，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));

      接著我們要把阻塞和鎖定資料分別存放到Who和Lock表中，SQL代碼如下：

INSERT INTO #Who    -- Diagnose which process causing the block.    EXEC sp_who activeINSERT INTO #Lock    -- Check which source has been locked.    EXEC sp_lockDECLARE @DBName nvarchar(20);SET @DBName='LMS_RFD'SELECT Who.* FROM #Who who  WHERE dbname=@DBNameSELECT Lock.* FROM #Lock lock    JOIN #Who who        ON Who.spid=Lock.spid            AND dbname=@DBName;                        DECLARE crsr Cursor FOR    SELECT blk FROM #Who 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;-- Get the locked source.SELECT Who.spid,hostname,objid,[type],mode,object_name(objid) as objName FROM #Lock lock    JOIN #Who who        ON Who.spid=Lock.spid            AND dbname=@DBName    WHERE objid<>0;

      2.使用SQL Server Profiler分析死結，將Deadlock graph事件類別添加到跟蹤。此事件類別使用死結涉及到的進程和對象的XML資料填充跟蹤中的TextData資料列。SQL Server 事件探查器可以將XML文檔提取到死結XML(.xdl) 檔案中，以後可在SQL Server Management Studio中查看該檔案(下面將給出詳細介紹)。

死結的樣本和解決方案

      首先我們在資料庫tempdb中建立兩個表DlTable1和DlTable2，它們都包含兩個欄位分別是Id和Name，接著我們往這兩個表中插入資料，具體SQL代碼如下：

-- Note we use tempdb for testing.USE tempdb-- Create datatable in tempdb.CREATE TABLE DlTable1 (DL1Id INT, DL1Name VARCHAR(20))CREATE TABLE DlTable2 (DL2Id INT, DL2Name VARCHAR(20))-- Insert multiple data into DlTable1 and DlTable2 in SQL Server 2005.INSERT INTO DlTable1SELECT 1, 'Deadlock'UNION ALLSELECT 2, 'JKhuang'UNION ALLSELECT 3, 'Test'GOINSERT INTO DlTable2SELECT 1, 'Deadlock'UNION ALLSELECT 2, 'JacksonHuang'UNION ALLSELECT 3, 'Test'GO-- Insert multiple data into DlTable1 and DlTable2 in SQL Server 2008.INSERT INTO DlTable1 VALUES (1, 'Deadlock'), (2, 'JKhuang'), (3, 'Test')INSERT INTO DlTable2 VALUES (1, 'Deadlock'), (2, 'JacksonHuang'), (3, 'Test')

    現在我們執行以上SQL代碼成功建立了DlTable1和DlTable2並且插入了資料。

圖4插入資料到表中

    接著我們開啟兩個查詢時段分別建立兩個獨立的事務A和B如下：

-- In query window 1.USE tempdbGO-- Create transaction A.BEGIN     TRANSACTION            UPDATE DlTable1 SET DL1Name = 'Uplock' WHERE DL1Id = 2            -- Delay 23 second.            WAITFOR DELAY '00:00:23'            UPDATE DlTable2 SET DL2Name = 'Downlock' WHERE DL2Id = 2ROLLBACK TRANSACTION-- In query window 2.USE tempdbGO-- Create transaction B.BEGIN     TRANSACTION            UPDATE DlTable2 SET DL2Name = 'Downlock' WHERE DL2Id = 2            -- Delay 23 second.            WAITFOR DELAY '00:00:23'            UPDATE DlTable1 SET DL1Name = 'Uplock' WHERE DL1Id = 2ROLLBACK TRANSACTION

     上面我們定義了兩個獨立的事務A和B，為了測試死結這裡我們使用WAITFOR DELAY使事務執行產生延時。

圖5事務執行結果

      運行完上面的兩個查詢後，我們發現其中一個事務執行失敗，系統提示該進程和另一個進程發生死結，而另一個事務執行成功，這是由於SQL Server自動選擇一個事務作為死結犧牲品。

      既然發生了死結，那麼究竟是哪個資源被鎖定了呢？現在我們通過死結排除方法一來查看具體是哪個資源被鎖定。

      現在我們重新執行事務A和B，接著使用死結排除方法一查看更新事務具體使用到的鎖。

圖6更新操作使用的鎖

      通過我們知道，首先事務A給表DlTable1下了行獨佔鎖定（RID X），然後在下頁意向更新鎖定（PAG IX），最後給整個DlTable1表下了表意向更新鎖定（TAB IX）；事務B的使用的鎖也是一樣的。

      事務A擁有DL1Id = 2行獨佔鎖定（RID X）同時去請求DL2Id = 2的行獨佔鎖定（RID X），但我們知道事務B已經擁有DL2Id = 2的行獨佔鎖定（RID X），而且去請求DL1Id = 2行獨佔鎖定（RID X），由於行獨佔鎖定和行獨佔鎖定是衝突的所以導致死結。

圖7鎖的相容性

      前面我們介紹了使用sp_lock查看更新操作時SQL Server使用的鎖（行鎖、頁鎖和表鎖），現在我們在更新操作後查詢操作，SQL代碼如下：

      現在我們使用SQL Server Profiler分析死結

      在本節中，我們將看到如何使用SQL Server Profiler來捕獲死結跟蹤。

      1.啟動SQL Server事件探查器和串連所需的SQL Server執行個體

      2.建立一個新的跟蹤

      3.在事件選擇頁中，取消預設事件選項，我們選擇“死結圖形”事件、 “鎖定：死結”和“鎖定：死結鏈”如所示：

圖8事件選擇設定

     4. 啟動一個新的跟蹤

     5.在SSMS中，開兩個查詢時段#1和#2，我們重新執行前面兩個事務

     6.事務執行結束，一個執行成為，另一個發生死結錯誤

     7.我們開啟事件探查器，如所示：

圖9 Deadlock graph

     8.選擇Deadlock graph，我們可以直觀查看到兩個事務之間發生死結的原因

圖10 事務進程A

      的橢圓形有一個叉，表示事務A被SQL Server選擇為死結犧牲品，如果我們把滑鼠指標移動到橢圓中會出現一個提示。

圖11 事務進程B

      的橢圓形表示進程執行成功，我們把滑鼠指標移動到橢圓中也會出現一個提示。

      中間的兩個矩形框稱為資源節點，它們代表的資料庫物件，如表，行或索引。由於事務A和B在擁有各自資源時試圖獲得對方資源的一個獨佔鎖，使得進程相互等待對方釋放資源從而導致死結。

死結避免：

     現在讓我們回顧一下上了死結的四個必要條件：互斥,佔有並等待,不可剝奪和迴圈等待；我們只需破壞其中的一個或多個條件就可以避免死結發生，方法如下：

     (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完成之前，資料庫中決不能有其他開啟的串連。資料庫不必一定要處於單一使用者模式中。

     在資料庫中設定READ COMMITTED SNAPSHOT 或 ALLOW SNAPSHOT ISOLATIONON ON時，查詢資料時不再使用請求共用鎖定，如果請求的行正被鎖定(例如正在被更新)，SQL_Server會從行版本儲存區返回最早的關於該行的記錄(SQL_server會在更新時將之前的行資料在tempdb庫中形成一個連結清單。（詳細請點這裡和這裡）

ALTER Database DATABASENAME SET READ_COMMITTED_SNAPSHOT ON

     (6).使用綁定串連。(註：綁定會話有利於在同一台伺服器上的多個會話之間協調操作。綁定會話允許一個或多個會話共用相同的事務和鎖(但每個回話保留其自己的交易隔離等級)，並可以使用同一資料，而不會有鎖衝突。可以從同一個應用程式內的多個會話中建立綁定會話，也可以從包含不同會話的多個應用程式中建立綁定會話。在一個會話中開啟事務(begin tran)後，調用exec sp_getbindtoken @Token out;來取得Token，然後傳入另一個會話並執行EXEC sp_bindsession
@Token來進行綁定(最後的樣本中示範了綁定串連)。

解決死結

      這裡有幾個方法可以協助我們解決死結問題。

      最佳化查詢

      我們在寫查詢語句時，要考慮一下查詢是否Join了沒有必要的表？是否返回資料太多（太多的列或行）？查詢是否執行表掃描？是否能通過調整查詢次序來避免死結？是否應該使用Join的地方使用了Left Join？Not In語句是否考慮周到？

      我們在寫查詢語句可以根據以上準則來考慮查詢是否應該做出最佳化。

      慎用With(NoLock)

      預設情況下SELECT語句會對查詢到的資源加S鎖(共用鎖定)，由於S鎖與X鎖(獨佔鎖定)不相容，在加上With(NoLock)後，SELECT不對查詢到的資源加鎖(或者加Sch-S鎖，Sch-S鎖可以與任何鎖相容)；從而使得查詢語句可以更好和其他語句並發執行，適用於表資料更新不頻繁的情況。

     也許有些人會提出質疑With(NoLock)，可能會導致髒讀，首先我們要考慮查詢的表是否頻繁進行更新操作，而且是否要讀回來的資料會被修改，所以衡量是否使用With(NoLock)還是要根據具體實際出發。

     最佳化索引

     是否有任何缺失或多餘的索引？是否有任何重複的索引？

     處理死結

     我們不能時刻都觀察死結的發生，但我們可以通過日誌來記錄系統發生的死結，我們可以把系統的死結錯誤寫入到表中，從而方便分析死結原因。

     緩衝

     也許我們正在執行許多相同的查詢非常頻繁，如果我們把這些頻繁的操作都放到Cache中，執行查詢的次數將減少發生死結的機會。我們可以在資料庫的暫存資料表或表，或記憶體，或磁碟上應用Cache。

1.1.3 總結

      本文主要介紹了什麼是死結、怎樣導致了死結和死結的解決方案，正如我們可以看到，由於導致死結的原因很多，所以死結的解決方案不盡相同，首先我們必須明確死結發生的地方，例如進程為了爭奪哪類資源導致死結的，這時我們可以考慮使用Profiler工具進行跟蹤查詢；在清楚死結發生的地方後，我們要檢查一下查詢是否考慮周到了，可以根據以上的方法最佳化查詢語句。

參考

http://msdn.microsoft.com/zh-cn/library/ms174313.aspx

http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/

http://www.cnblogs.com/happyhippy/

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

轉載自：http://www.cnblogs.com/rush/archive/2012/02/19/2358209.html

關於作者: [作者]： JK_Rush從事.NET開發和熱衷於開源高效能系統設計，通過博文交流和分享經驗，歡迎轉載，請保留原文地址，謝謝。 [出處]：http://www.cnblogs.com/rush/ [本文基於]:署名-非商業性使用 3.0許可協議發布，歡迎轉載，演繹，但是必須保留本文的署名JK_Rush（包含連結），且不得用於商業目的。如您有任何疑問或者授權方面的協商，請與我聯絡。