MSSQL死鎖產生原因及解決方法

一、    什麼是死鎖

死鎖是指兩個或兩個以上的進程在執行過程中,因爭奪資源而造成的一種互相等待的現象,若無外力作用,它們都將無法推進下去.此時稱系統處於死鎖狀態或系統產生了死鎖,這些永遠在互相等的進程稱為死鎖進程.

二、    死鎖產生的四個必要條件

• 互斥條件：指進程對所分配到的資源進行排它性使用，即在一段時間內某資源只由一個進程占用。如果此時還有其它進程請求資源，則請求者只能等待，直至占有資源的進程用畢釋放
• 請求和保持條件：指進程已經保持至少一個資源，但又提出了新的資源請求，而該資源已被其它進程占有，此時請求進程阻塞，但又對自己已獲得的其它資源保持不放
• 不剝奪條件：指進程已獲得的資源，在未使用完之前，不能被剝奪，只能在使用完時由自己釋放
• 環路等待條件：指在發生死鎖時，必然存在一個進程——資源的環形鏈，即進程集合{P0，P1，P2，···，Pn}中的P0正在等待一個P1占用的資源；P1正在等待P2占用的資源，……，Pn正在等待已被P0占用的資源

這四個條件是死鎖的必要條件，只要系統發生死鎖，這些條件必然成立，而只要上述條件之一不滿足，就不會發生死鎖。

三、     如何處理死鎖

1)   鎖模式

1. 共用鎖（S）

由讀操作創建的鎖，防止在讀取數據的過程中，其它事務對數據進行更新；其它事務可以併發讀取數據。共用鎖可以加在表、頁、索引鍵或者數據行上。在SQL SERVER預設隔離級別下數據讀取完畢後就會釋放共用鎖，但可以通過鎖提示或設置更高的事務隔離級別改變共用鎖的釋放時間。

     2.獨占鎖(X)

對資源獨占的鎖，一個進程獨占地鎖定了請求的數據源，那麼別的進程無法在此數據源上獲得任何類型的鎖。獨占鎖一致持有到事務結束。

     3.更新鎖(U)

更新鎖實際上並不是一種獨立的鎖，而是共用鎖與獨占鎖的混合。當SQL SERVER執行數據修改操作卻首先需要搜索表以找到需要修改的資源時，會獲得更新鎖。

更新鎖與共用鎖相容，但只有一個進程可以獲取當前數據源上的更新鎖，

其它進程無法獲取該資源的更新鎖或獨占鎖，更新鎖的作用就好像一個序列化閥門（serialization gate），將後續申請獨占鎖的請求壓入隊列中。持有更新鎖的進程能夠將其轉換成該資源上的獨占鎖。更新鎖不足以用於更新數據—實際的數據修改仍需要用到獨占鎖。對於獨占鎖的序列化訪問可以避免轉換死鎖的發生，更新鎖會保留到事務結束或者當它們轉換成獨占鎖時為止。

      4. 意向鎖(IX,IU,IS)

意向鎖並不是獨立的鎖定模式，而是一種指出哪些資源已經被鎖定的機制。

如果一個表頁上存在獨占鎖，那麼另一個進程就無法獲得該表上的共用表鎖，這種層次關係是用意向鎖來實現的。進程要獲得獨占頁鎖、更新頁鎖或意向獨占頁鎖，首先必須獲得該表上的意向獨占鎖。同理，進程要獲得共用行鎖，必須首先獲得該表的意向共用鎖，以防止別的進程獲得獨占表鎖。

      5. 特殊鎖模式(Sch_s,Sch_m,BU)

SQL SERVER提供3種額外的鎖模式：架構穩定鎖、架構修改鎖、大容量更新鎖。

      6.轉換鎖(SIX,SIU,UIX)

轉換鎖不會由SQL SERVER 直接請求，而是從一種模式轉換到另一種模式所造成的。SQL SERVER 2008支持3種類型的轉換鎖：SIX、SIU、UIX.其中最常見的是SIX鎖，如果事務持有一個資源上的共用鎖（S），然後又需要一個IX鎖，此時就會出現SIX。

      7.鍵範圍鎖

鍵範圍鎖是在可序列化隔離級別中鎖定一定範圍內數據的鎖。保證在查詢數據的鍵範圍內不允許插入數據。

2)   鎖粒度

SQL SERVER 可以在表、頁、行等級別鎖定用戶的數據資源即非系統資源（系統資源是用閂鎖來保護的）。此外SQL SERVER 還可以鎖定索引鍵和索引鍵範圍。

通過sys.dm_tran_locks視圖可以查看誰被鎖定了（如行，鍵，頁）、鎖的模式以及特定資源的標誌符。基於sys.dm_tran_locks視圖創建如下視圖用於查看鎖定的資源以及鎖模式（通過這個視圖可以查看事務鎖定的表、頁、行以及加在數據資源上的鎖類型）。

CREATE VIEW dblocks AS  
SELECT request_session_id AS spid, 
       DB_NAME(resource_database_id) AS dbname, 
       CASE WHEN resource_type='object'  
                 THEN OBJECT_NAME(resource_associated_entity_id)  
            WHEN resource_associated_entity_id=0 THEN 'n/a' 
            ELSE OBJECT_NAME(p.object_id) END AS entity_name, 
       index_id, 
       resource_type AS RESOURCE, 
       resource_description AS DESCRIPTION, 
       request_mode AS mode, 
       request_status AS STATUS  
FROM sys.dm_tran_locks t LEFT JOIN sys.partitions p ON p.partition_id=t.resource_associated_entity_id 
WHERE resource_database_id=DB_ID()

3)   如何跟蹤死鎖

通過選擇sql server profiler 事件中的如下選項就可以跟蹤到死鎖產生的相關語句。

4)   死鎖案例分析

在該案例中process65db88, process1d0045948為語句1的進程，process629dc8 為語句2的進程； 語句2獲取了1689766頁上的更新鎖，在等待1686247頁上的更新鎖；而語句1則獲取了1686247頁上的更新鎖在等待1689766頁上的更新鎖，兩個語句等待的資源形成了一個環路，造成死鎖。

5)   如何解決死鎖

針對如上死鎖案例，分析其對應語句執行計劃如下：

          通過執行計劃可以看出，在查找需要更新的數據時使用的是索引掃描，比較耗費性能，這樣就造成鎖定資源時間過長，增加了語句併發執行時產生死鎖的概率。

 處理方式：

1． 在表上建立一個聚集索引。

2． 對語句更新的相關欄位建立包含索引。

優化後該語句執行計劃如下：

            優化後的執行計劃使用了索引查找，將大幅提升該查詢語句的性能，降低了鎖定資源的時間，同時也減少了鎖定資源的範圍，這樣就降低了鎖資源迴圈等待事件發生的概率，對於預防死鎖的發生會有一定的作用。

            死鎖是無法完全避免的，但如果應用程式適當處理死鎖，對涉及的任何用戶及系統其餘部分的影響可降至最低（適當處理是指發生錯誤1205時，應用程式重新提交批處理，第二次嘗試大多能成功。一個進程被殺死，它的事務被取消，它的鎖被釋放，死鎖中涉及到的另一個進程就可以完成它的工作並釋放鎖，所以就不具備產生另一個死鎖的條件了。）

四、     如何預防死鎖

    阻止死鎖的途徑就是避免滿足死鎖條件的情況發生，為此我們在開發的過程中需要遵循如下原則：

1. 儘量避免併發的執行涉及到修改數據的語句。
2. 要求每一個事務一次就將所有要使用到的數據全部加鎖，否則就不允許執行。
3. 預先規定一個加鎖順序，所有的事務都必須按照這個順序對數據執行封鎖。如不同的過程在事務內部對對象的更新執行順序應儘量保證一致。
4. 每個事務的執行時間不可太長，對程式段的事務可考慮將其分割為幾個事務。在事務中不要求輸入，應該在事務之前得到輸入，然後快速執行事務。
5. 使用儘可能低的隔離級別。
6. 數據存儲空間離散法。該方法是指採用各種手段，將邏輯上在一個表中的數據分散的若幹離散的空間上去，以便改善對錶的訪問性能。主要通過將大表按行或者列分解為若幹小表，或者按照不同的用戶群兩種方法實現。
7. 編寫應用程式，讓進程持有鎖的時間儘可能短，這樣其它進程就不必花太長的時間等待鎖被釋放。