深入淺出SQL Server中的死鎖【轉】

簡介死鎖的本質是一種僵持狀態，是多個主體對於資源的爭用而導致的。理解死鎖首先需要對死鎖所涉及的相關觀念有一個理解。一些基礎知識要理解SQL Server中的死鎖，更好的方式是通過類比從更大的面理解死鎖。比如說一個經典的例子就是汽車（主體）對於道路(資源)的徵用，如圖1所示。圖1.對於死鎖的直 ...

簡介

死鎖的本質是一種僵持狀態，是多個主體對於資源的爭用而導致的。理解死鎖首先需要對死鎖所涉及的相關觀念有一個理解。

一些基礎知識

要理解SQL Server中的死鎖，更好的方式是通過類比從更大的面理解死鎖。比如說一個經典的例子就是汽車（主體）對於道路(資源)的徵用，如圖1所示。

圖1.對於死鎖的直觀理解

在圖1的例子中，每隊汽車都占有一條道路，但都需要另外一隊汽車所占有的另一條道路，因此互相阻塞，誰都無法前行，因此造成了死鎖。由這個簡單的例子可以看出，發生死鎖需要四個必要條件，如下:

1）互斥條件：

主體對於資源是獨占的，圖1中每條汽車道只能跑一隊汽車，不能跑第二隊。

2)請求和等待條件:

指主體已經保持至少一個資源，但又提出了新的資源請求，而該資源已被其它主體占有，此時請求主體阻塞，但又對自己已獲得的其它資源保持不放。在圖1中，每隊汽車已經占有了一條車道，又想獲得另一條由其它車隊占有的車道，造成阻塞。

3)不剝奪條件

指的是主體已經獲得的資源在完成其目標之前不能被釋放。在圖1中，目標指的是汽車可以通過車道，不剝奪指的是在完成這個目標之前，車隊並不會讓出其已占的車道。

4)環路等待條件

指在發生死鎖時，必然存在一個主體——資源的環形鏈，即主體集合{P0，P1，P2，···，Pn}中的P0正在等待一個P1占用的資源；P1正在等待P2占用的資源，……，Pn正在等待已被P0占用的資源。在圖1中可以看出，四條車道和四隊汽車正好符號環路等待的條件，車隊1希望獲得車隊2占有的車道，車隊2希望獲得車隊3占有的車道，車隊3希望獲得車隊4占有的車道,車隊4反過來又希望獲得車隊1占有的車道，形成一個環路。

死鎖在進程中的定義

下麵讓我們再縮小死鎖的範圍，回到電腦的世界。在電腦中，主體的這個抽象的詞被更具體的進程所替代，而資源縮小到電腦所使用的資源。在電腦中，死鎖是由阻塞所引起。因此在開始之間，我想簡單介紹一下進程的幾種狀態，如果有興趣，也可以參看我之前的一篇文章:http://www.cnblogs.com/CareySon/archive/2012/05/04/ProcessAndThread.html.

簡單來說，進程是組織資源的最小單位，多道操作系統中允許併發進行，每一道進程都像圖1所示的汽車那樣，需要前進，在前進的過程中，需要各種資源以及CPU，圖2是不考慮進行創建銷毀等狀態，簡單概述進程的幾種狀態。

圖2.進程的幾種狀態

很多資源是可以共用的，比如記憶體。但對於印表機等資源來說就需要獨占。圖2中的幾種狀態簡單理解是，當進程沒有所需的資源時，比如說等待IO，等待印表機，這時是阻塞狀態。而當進程獲得了這些資源時，就可以變為就緒狀態，在就緒狀態的進程再獲得CPU時，就變為執行狀態。而執行的過程中，CPU被剝奪了就繼續變為就緒狀態，或是當需要其它資源時，就會繼續變為阻塞狀態。以此往複。

在操作系統中，有些資源可以是不可剝奪資源，比如印表機，當印表機被一個進程占用時，另一個進程就會被阻塞。還有一類資源是要重點強調的,這類資源是臨時性資源，比如進程產生的信號量,消息，緩衝區內的消息，多個進程或線程訪問這類資源時更容易引起死鎖。在SQL Server中產生的死鎖其實就是由這類資源所造成的。

當兩個或多個進程既然有了當前的資源，又需要額外的資源時，滿足了上面所述死鎖的四個條件時，就會產生死鎖。

死鎖在SQL Server中的定義

在SQL Server中，阻塞更多的是產生於實現併發之間的隔離性。為了使得併發連接所做的操作之間的影響到達某一期望值而對資源人為的進行加鎖(鎖本質其實可以看作是一個標誌位)。當一個連接對特定的資源進行操作時，另一個連接同時對同樣的資源進行操作就會被阻塞(當然了，這和鎖之間的相容性有關，關於鎖更深入的討論超出了本文的範圍，關於這部分內容可以看我的另一篇文章:T-SQL查詢進階—理解SQL Server中的鎖)，阻塞是死鎖產生的必要條件。

下麵，我們通過一個簡單的例子來看死鎖。

首先，要出現死鎖，一定要滿足前面提到死鎖出現的四個必要條件，圖3中可以清楚的看到這兩個連接(SPID52和SPID55)是如何滿足這四個條件的。

圖3.一個死鎖示例

Lock Monitor

圖3中死鎖後可以看到，SQL Server並不會讓死鎖僵持下去，而是通過一個叫Lock Monitor的線程定期進行檢測（預設是5秒）。當發現死鎖後，會剝奪其中一個SPID占有的資源，好讓另一個SPID執行下去，具體剝奪哪一個SPID基於如下兩個因素:

1.死鎖的優先順序。

2.在死鎖優先順序相同的情況下，根據開銷，開銷小的事務將會被剝奪

下麵，還是根據圖3中的例子，我們設置死鎖優先順序，使得左邊的事務被剝奪回滾，如圖4所示。

圖4.設置死鎖優先順序後，優先順序低的SPID被剝奪

SQL Server中死鎖的檢測

首先要理解，在多併發的環境中，死鎖是不可避免的，只能儘量的通過合理的資料庫設計，良好的索引，適當的查詢語句以及隔離等級來儘量的減少。因此，檢測死鎖的目的是知道哪裡可能會產生死鎖，通過對檢測到的死鎖進行分析後，儘量的優化查詢/索引/隔離等級來降低死鎖發生的可能性。

查看死鎖有兩種方式，一種是通過服務端的Trace來做，另一種是通過SQL Profiler，首先讓我們來看通過Trace來抓死鎖。

通過Trace來看死鎖

當死鎖發生後，通過服務端的Trace就可以將死鎖信息傳到日誌。在SQL Server 2000時代，只能通過Trace flag 1204來開啟，由於Trace flag 1204並不能提供XML死鎖圖，在SQL Server 2005以及之後的版本被Trace flag 1222所取代。

為了在服務端針對所有的Session開啟Trace flag 1222。可以通過如代碼1所示。

DBCC TRACEON(1222,-1)

代碼1.針對所有Session開啟1222這個Trace Flag

除去代碼1之外，還可以通過在啟動SQL Server實例之前，對加啟動參數 –t1222。這裡就不再細說了。

此時，當發生死鎖後，就能從日誌看到相關的記錄，如圖5所示。

圖5.死鎖後的記錄

通過Profiler來查看死鎖

另一種方法是開啟Profiler來捕捉,Profiler捕捉到的圖示死鎖信息內容就更直觀了，Profiler的設置如圖6所示。

圖6.Profiler中抓死鎖圖的設置

所抓到的死鎖圖如圖7所示。

圖7.死鎖圖

通過這個死鎖圖，可以更直觀的看到死鎖產生的主體和資源，並且滑鼠移到主體上時，還可以顯示造成死鎖的語句。死鎖的犧牲品進程會被打X號。

上面的死鎖圖還可以看到造成死鎖的資源。

SQL Server中產生死鎖的一些情況

由書簽查找產生的死鎖

這類死鎖產生的原因是書簽查找和更新數據產生的僵持狀態。簡單來說,就是由於Update語句對基本表產生X鎖，然後需要對錶上的索引也進行更新，而表上的索引正好被另一個連接進行查找，加了S鎖，此時又產生書簽查找去基本表加了X鎖的數據進行書簽查找，此時形成死鎖，這個概念可以從圖8看到。

圖8.由書簽查找產生的死鎖

這種死鎖可以通過Include列來減少書簽查找，從而減少這種類型死鎖發生的概率。

由外鍵產生的死鎖

這類死鎖產生的原因來自外鍵約束。當主表(也就是主鍵是從表外鍵的那個表)更新數據時，需要查看從表，以確定從表的外鍵列滿足外鍵約束。此時會在主表上加X鎖，但這並不能阻止同一時間，另一個SPID向從表添加被修改的主表主鍵，為瞭解決這個問題，SQL Server在進行這類更新時，使用Range鎖，這種鎖是當隔離等級為序列化時才有的，因此在這時雖然隔離等級可能是預設的已提交讀，但是行為卻是序列化。這很可能就會導致死鎖。

解決辦法之一是向外鍵列添加索引，使得Range鎖加在索引上，而不是表本身。從而降低了死鎖發生的概率。

由於推進順序不當產生的死鎖

這也是圖3中死鎖的原因。在多個事務對資源的使用順序不當，形成死鎖環路而引發的。解決方法是儘量是資源的使用順序一致。這也是死鎖問題出現最多的一種情況。

如何減少死鎖

上面簡單講述了SQL Server中產生死鎖的一些情況。下麵我們從更寬泛的角度來看如何減少死鎖。

在操作系統中，進程併發減少死鎖的原理同樣可以套用到SQL Server中。在操作系統對於處理死鎖的辦法如下：

1) 預防死鎖。

這是一種較簡單和直觀的事先預防的方法。方法是通過設置某些限制條件，去破壞產生死鎖的四個必要條件中的一個或者幾個，來預防發生死鎖。預防死鎖是一種較易實現的方法，已被廣泛使用。但是由於所施加的限制條件往往太嚴格，可能會導致系統資源利用率和系統吞吐量降低。

2) 避免死鎖。

該方法同樣是屬於事先預防的策略，但它並不須事先採取各種限制措施去破壞產生死鎖的的四個必要條件，而是在資源的動態分配過程中，用某種方法去防止系統進入不安全狀態，從而避免發生死鎖。

3)檢測死鎖。

這種方法並不須事先採取任何限制性措施，也不必檢查系統是否已經進入不安全區，此方法允許系統在運行過程中發生死鎖。但可通過系統所設置的檢測機構，及時地檢測出死鎖的發生，並精確地確定與死鎖有關的進程和資源，然後採取適當措施，從系統中將已發生的死鎖清除掉。

4)解除死鎖。

這是與檢測死鎖相配套的一種措施。當檢測到系統中已發生死鎖時，須將進程從死鎖狀態中解脫出來。常用的實施方法是撤銷或掛起一些進程，以便回收一些資源，再將這些資源分配給已處於阻塞狀態的進程，使之轉為就緒狀態，以繼續運行。死鎖的檢測和解除措施，有可能使系統獲得較好的資源利用率和吞吐量，但在實現上難度也最大。

由上面4中處理死鎖的辦法看，其中檢測死鎖和解除死鎖是Lock Monitor的事，作為DBA或資料庫開發人員，處理死鎖要放在預防和避免死鎖上。

預防死鎖

預防死鎖就是破壞四個必要條件中的某一個和幾個，使其不能形成死鎖。有如下幾種辦法

1)破壞互斥條件

破壞互斥條件有比較嚴格的限制,在SQL Server中，如果業務邏輯上允許臟讀，則可以通過將隔離等級改為未提交讀或使用索引提示。這樣使得讀取不用加S鎖，從而避免了和其它查詢所加的與S鎖不相容的鎖互斥，進而減少了死鎖出現的概率。

2)破壞請求和等待條件

這點由於事務存在原子性，是不可破壞的，因為解決辦法是儘量的減少事務的長度，事務內執行的越快越好。這也可以減少死鎖出現的概率。

3)破壞不剝奪條件

由於事務的原子性和一致性，不剝奪條件同樣不可破壞。但我們可以通過增加資源和減少資源占用兩個角度來考慮。

增加資源：比如說通過建立非聚集索引，使得有了額外的資源，查詢很多時候就不再索要鎖基本表，轉而鎖非聚集索引，如果索引能夠“覆蓋(Cover)”查詢，那更好不過。因此索引Include列不僅僅減少書簽查找來提高性能，還能減少死鎖。增加資源還可以通過SQL Server 2005之後的行版本控制進行，但這種方式並不推薦，在此不再詳細討論。

減少資源占用:比如說查詢時，能用select col1,col2這種方式，就不要用select * .這有可能帶來不必要的書簽查找

避免死鎖

避免死鎖是在有限的資源下，使得主體爭用資源不形成環路。比如說典型的銀行家演算法，就是在資源有限的情況下，在不造成現金流斷裂的情況下，儘可能多的按一定順序分配資源。

因此避免死鎖的關鍵是“順序”。在SQL Server中，儘量使查詢對資源的使用順序保持一致。比如圖3就是一個典型的不按順序請求資源而導致的死鎖。假設圖3的順序改為圖9所示順序，那是形不成死鎖的，轉而，死鎖會變為等待。

圖9.按順序，死鎖轉為等待

SQL Server中死鎖的處理

那既然死鎖無法避免，在出現死鎖的時候要有一種處理機制。可以想象一下，如果你的程式是一個電子商務網站，由於死鎖造成用戶的生成的訂單被RollBack…

因此死鎖的處理在SQL Server可以放在兩個層面進行

在SQL Server層面處理死鎖

首先要知道，SQL Server中死鎖的錯誤代碼是1205，由於死鎖是由阻塞引起的，而阻塞的時間往往都不長，索引可以通過重試幾次來處理死鎖，典型的代碼如代碼2所示。

--重試次數
DECLARE @retry INT

SET @retry = 3

WHILE ( @retry > 0 ) 
    BEGIN
        BEGIN TRY
   --這裡是業務代碼
   
   --事務成功，將重試次數變為
            SET @retry = 0
        END TRY
   
        BEGIN CATCH
   --如果是死鎖，則重試
            IF ( ERROR_NUMBER() = 1205 ) 
                SET @retry = @retry
            ELSE 
                BEGIN
      --如果是其它錯誤，記錄到日誌等..
                END
      
        END CATCH
    END

代碼2.在SQl Server層面處理死鎖

在程式層處理死鎖

和SQL Server中處理死鎖的方式大同小異，也是通過錯誤代碼進行判斷，下麵是C#處理死鎖的方式如代碼3所示。

int retry = 3;
        while (retry > 0)
        {
            try
            {
                //執行sql語句的代碼
                //將重試次數變為0
                retry = 0;
            }
            catch(SqlException e)
            {
                //如果是死鎖的話,0.5S後重試
                if(e.Number==1205)
                {
                    System.Threading.Thread.Sleep(500);
                    retry--;
                }
                //其它錯誤....
                else
                {
                    throw;
                }
            }
        }

代碼3.死鎖處理的C#代碼

總結

本文講述了死鎖的概念，產生死鎖的四個必要條件，死鎖的處理方式和在SQL Server中如何檢測避免和處理死鎖。死鎖是由於阻塞引起的，瞭解這部分基本概念對於死鎖方面的排錯是非常必要的。

深入淺出SQL Server中的死鎖 【轉】

簡介