深入淺出SQL Server中的死鎖

簡介

    死鎖的本質(zhì)是一種僵持狀態(tài)，是多個主體對于資源的爭用而導(dǎo)致的。理解死鎖首先需要對死鎖所涉及的相關(guān)觀念有一個理解。

一些基礎(chǔ)知識

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

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

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

1）互斥條件：

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

2)請求和等待條件:

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

3)不剝奪條件

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

4)環(huán)路等待條件

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

死鎖在進程中的定義

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

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

    圖2.進程的幾種狀態(tài)

    很多資源是可以共享的，比如內(nèi)存。但對于打印機等資源來說就需要獨占。圖2中的幾種狀態(tài)簡單理解是，當(dāng)進程沒有所需的資源時，比如說等待IO，等待打印機，這時是阻塞狀態(tài)。而當(dāng)進程獲得了這些資源時，就可以變?yōu)榫途w狀態(tài)，在就緒狀態(tài)的進程再獲得CPU時，就變?yōu)閳?zhí)行狀態(tài)。而執(zhí)行的過程中，CPU被剝奪了就繼續(xù)變?yōu)榫途w狀態(tài)，或是當(dāng)需要其它資源時，就會繼續(xù)變?yōu)樽枞麪顟B(tài)。以此往復(fù)。

    在操作系統(tǒng)中，有些資源可以是不可剝奪資源，比如打印機，當(dāng)打印機被一個進程占用時，另一個進程就會被阻塞。還有一類資源是要重點強調(diào)的,這類資源是臨時性資源，比如進程產(chǎn)生的信號量,消息，緩沖區(qū)內(nèi)的消息，多個進程或線程訪問這類資源時更容易引起死鎖。在SQL Server中產(chǎn)生的死鎖其實就是由這類資源所造成的。

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

死鎖在SQL Server中的定義

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

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

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

    圖3.一個死鎖示例

Lock Monitor

    圖3中死鎖后可以看到，SQL Server并不會讓死鎖僵持下去，而是通過一個叫Lock Monitor的線程定期進行檢測（默認(rèn)是5秒）。當(dāng)發(fā)現(xiàn)死鎖后，會剝奪其中一個SPID占有的資源，好讓另一個SPID執(zhí)行下去，具體剝奪哪一個SPID基于如下兩個因素:

    1.死鎖的優(yōu)先級。

    2.在死鎖優(yōu)先級相同的情況下，根據(jù)開銷，開銷小的事務(wù)將會被剝奪

    下面，還是根據(jù)圖3中的例子，我們設(shè)置死鎖優(yōu)先級，使得左邊的事務(wù)被剝奪回滾，如圖4所示。

    圖4.設(shè)置死鎖優(yōu)先級后，優(yōu)先級低的SPID被剝奪

SQL Server中死鎖的檢測

    首先要理解，在多并發(fā)的環(huán)境中，死鎖是不可避免的，只能盡量的通過合理的數(shù)據(jù)庫設(shè)計，良好的索引，適當(dāng)?shù)牟樵冋Z句以及隔離等級來盡量的減少。因此，檢測死鎖的目的是知道哪里可能會產(chǎn)生死鎖，通過對檢測到的死鎖進行分析后，盡量的優(yōu)化查詢/索引/隔離等級來降低死鎖發(fā)生的可能性。

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

通過Trace來看死鎖

    當(dāng)死鎖發(fā)生后，通過服務(wù)端的Trace就可以將死鎖信息傳到日志。在SQL Server 2000時代，只能通過Trace flag 1204來開啟，由于Trace flag 1204并不能提供XML死鎖圖，在SQL Server 2005以及之后的版本被Trace flag 1222所取代。

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

DBCC TRACEON(1222,-1)

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

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

    此時，當(dāng)發(fā)生死鎖后，就能從日志看到相關(guān)的記錄，如圖5所示。

   
    圖5.死鎖后的記錄

通過Profiler來查看死鎖   

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

    圖6.Profiler中抓死鎖圖的設(shè)置

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

    圖7.死鎖圖

    通過這個死鎖圖，可以更直觀的看到死鎖產(chǎn)生的主體和資源，并且鼠標(biāo)移到主體上時，還可以顯示造成死鎖的語句。死鎖的犧牲品進程會被打X號。

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

SQL Server中產(chǎn)生死鎖的一些情況

由書簽查找產(chǎn)生的死鎖

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

    圖8.由書簽查找產(chǎn)生的死鎖

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

由外鍵產(chǎn)生的死鎖

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

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

由于推進順序不當(dāng)產(chǎn)生的死鎖

    這也是圖3中死鎖的原因。在多個事務(wù)對資源的使用順序不當(dāng)，形成死鎖環(huán)路而引發(fā)的。解決方法是盡量是資源的使用順序一致。這也是死鎖問題出現(xiàn)最多的一種情況。

如何減少死鎖

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

    在操作系統(tǒng)中，進程并發(fā)減少死鎖的原理同樣可以套用到SQL Server中。在操作系統(tǒng)對于處理死鎖的辦法如下：

1) 預(yù)防死鎖。

這是一種較簡單和直觀的事先預(yù)防的方法。方法是通過設(shè)置某些限制條件，去破壞產(chǎn)生死鎖的四個必要條件中的一個或者幾個，來預(yù)防發(fā)生死鎖。預(yù)防死鎖是一種較易實現(xiàn)的方法，已被廣泛使用。但是由于所施加的限制條件往往太嚴(yán)格，可能會導(dǎo)致系統(tǒng)資源利用率和系統(tǒng)吞吐量降低。

2) 避免死鎖。

該方法同樣是屬于事先預(yù)防的策略，但它并不須事先采取各種限制措施去破壞產(chǎn)生死鎖的的四個必要條件，而是在資源的動態(tài)分配過程中，用某種方法去防止系統(tǒng)進入不安全狀態(tài)，從而避免發(fā)生死鎖。

3)檢測死鎖。

這種方法并不須事先采取任何限制性措施，也不必檢查系統(tǒng)是否已經(jīng)進入不安全區(qū)，此方法允許系統(tǒng)在運行過程中發(fā)生死鎖。但可通過系統(tǒng)所設(shè)置的檢測機構(gòu)，及時地檢測出死鎖的發(fā)生，并精確地確定與死鎖有關(guān)的進程和資源，然后采取適當(dāng)措施，從系統(tǒng)中將已發(fā)生的死鎖清除掉。

4)解除死鎖。

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

    由上面4中處理死鎖的辦法看，其中檢測死鎖和解除死鎖是Lock Monitor的事，作為DBA或數(shù)據(jù)庫開發(fā)人員，處理死鎖要放在預(yù)防和避免死鎖上。

預(yù)防死鎖

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

1)破壞互斥條件

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

2)破壞請求和等待條件

    這點由于事務(wù)存在原子性，是不可破壞的，因為解決辦法是盡量的減少事務(wù)的長度，事務(wù)內(nèi)執(zhí)行的越快越好。這也可以減少死鎖出現(xiàn)的概率。

3)破壞不剝奪條件

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

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

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

避免死鎖

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

    因此避免死鎖的關(guān)鍵是“順序”。在SQL Server中，盡量使查詢對資源的使用順序保持一致。比如圖3就是一個典型的不按順序請求資源而導(dǎo)致的死鎖。假設(shè)圖3的順序改為圖9所示順序，那是形不成死鎖的，轉(zhuǎn)而，死鎖會變?yōu)榈却?/font>。

--重試次數(shù)
DECLARE @retry INT

SET @retry = 3

WHILE ( @retry > 0 ) 
    BEGIN
        BEGIN TRY
   --這里是業(yè)務(wù)代碼
   
   --事務(wù)成功，將重試次數(shù)變?yōu)?/span>
            SET @retry = 0
        END TRY
   
        BEGIN CATCH
   --如果是死鎖，則重試
            IF ( ERROR_NUMBER() = 1205 ) 
                SET @retry = @retry
            ELSE 
                BEGIN
      --如果是其它錯誤，記錄到日志等..
                END
      
        END CATCH
    END

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