深入淺出SQL Server中的死結

簡介

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

 

一些基礎知識

    要理解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 INTSET @retry = 3WHILE ( @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中如何檢測避免和處理死結。死結是由於阻塞引起的，瞭解這部分基本概念對於死結方面的排錯是非常必要的。

轉自：http://www.cnblogs.com/CareySon/archive/2012/09/19/2693555.html