SQL?Server的死鎖是什么及怎么結束

本文小編為大家詳細介紹“SQL Server的死鎖是什么及怎么結束”，內容詳細，步驟清晰，細節處理妥當，希望這篇“SQL Server的死鎖是什么及怎么結束”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來學習新知識吧。

一、了解死鎖

死鎖是導致數據庫中的競爭性并發鎖，通常在多步驟事務中。
當兩個或多個任務永久相互阻止時，每個任務都鎖定了其他任務嘗試鎖定的資源，就會發生死鎖。例如：

• 事務 A 獲取第 1 行上的共享鎖。

• 事務 B 獲取第 2 行上的共享鎖。

• 事務 A 現在請求第 2 行上的獨占鎖，并被阻止，直到事務 B 完成并釋放第 2 行上的共享鎖。

• 事務 B 現在請求第 1 行上的獨占鎖，并被阻止，直到事務 A 完成并釋放它在第 1 行上的共享鎖。

事務 A 在事務 B 完成之前無法完成，但事務 B 被事務 A 阻止。此條件也稱為循環依賴關系：事務 A 依賴于事務 B，事務 B 通過對事務 A 的依賴來關閉循環。

死鎖中的兩個事務將永遠等待，除非死鎖被外部進程打破。SQL Server 數據庫引擎死鎖監視器定期檢查處于死鎖狀態的任務。如果監視器檢測到循環依賴關系，它將選擇其中一個任務作為受害者，并在出現錯誤的情況下終止其事務。這允許其他任務完成其事務。具有因錯誤而終止的事務的應用程序可以重試該事務，該事務通常在另一個死鎖事務完成后完成。

死鎖經常與正常阻塞混淆。當一個事務請求鎖定另一個事務鎖定的資源時，請求事務將等待，直到鎖定被釋放。默認情況下，除非設置了LOCK_TIMEOUT，否則 SQL Server 事務不會超時。請求事務被阻止，而不是死鎖，因為請求事務沒有執行任何操作來阻止擁有鎖的事務。最終，擁有事務將完成并釋放鎖，然后請求事務將被授予鎖定并繼續。死鎖幾乎可以立即解決，而阻塞理論上可以無限期地持續存在。僵局有時被稱為致命的擁抱。

死鎖是可能發生在具有多個線程的任何系統上的情況，而不僅僅是在關系數據庫管理系統上，并且可能發生在數據庫對象上的鎖以外的資源上。例如，多線程操作系統中的線程可能會獲取一個或多個資源，例如內存塊。如果要獲取的資源當前由另一個線程擁有，則第一個線程可能必須等待擁有該線程釋放目標資源。據說等待線程依賴于該特定資源的擁有線程。在 SQL Server 數據庫引擎實例中，會話在獲取非數據庫資源（如內存或線程）時可能會死鎖。

在圖中，事務 T1 依賴于表鎖資源的事務 T2。同樣，事務 T2 依賴于表鎖資源的事務 T1。由于這些依賴項形成一個循環，因此事務 T1 和 T2 之間存在死鎖。

當對表進行分區并將 的設置設置為 AUTO 時，也會發生死鎖。設置為 AUTO 時，通過允許 SQL Server 數據庫引擎在 HoBT 級別而不是表級別鎖定表分區，并發性會增加。但是，當單獨的事務在表中持有分區鎖并希望在其他事務分區上的某個位置使用鎖時，這會導致死鎖。這種類型的死鎖可以通過設置為 ;盡管此設置將通過強制對分區進行大量更新以等待表鎖定來降低并發性。

二、檢測并結束死鎖

當兩個或多個任務永久相互阻止時，每個任務都鎖定了其他任務嘗試鎖定的資源，就會發生死鎖。下圖顯示了死鎖狀態的高級視圖，其中：

• 任務 T1 鎖定了資源 R1（由從 R1 到 T1 的箭頭指示），并請求鎖定資源 R2（由從 T1 到 R2 的箭頭指示）。

• 任務 T2 鎖定了資源 R2（由從 R2 到 T2 的箭頭指示），并請求鎖定資源 R1（由從 T2 到 R1 的箭頭指示）。

• 由于在資源可用之前，這兩個任務都無法繼續，并且在任務繼續之前無法釋放這兩個資源，因此存在死鎖狀態。

SQL Server 數據庫引擎會自動檢測 SQL Server 中的死鎖周期。SQL Server 數據庫引擎選擇其中一個會話作為死鎖受害者，當前事務將終止，并顯示錯誤以打破死鎖。

2.1、可能死鎖的資源

每個用戶會話可能代表其運行一個或多個任務，其中每個任務可能獲取或等待獲取各種資源。以下類型的資源可能會導致阻塞，從而導致死鎖。

• 鎖。等待獲取資源（如對象、頁面、行、元數據和應用程序）的鎖可能會導致死鎖。例如，事務 T1 在行 r1 上具有共享 （S） 鎖，并且正在等待在 r2 上獲取獨占 （X） 鎖。事務 T2 在 r2 上具有共享 （S） 鎖，并且正在等待在行 r1 上獲取獨占 （X） 鎖。這會導致鎖定循環，其中 T1 和 T2 相互等待對方釋放鎖定的資源。

• 工作線程。等待可用工作線程的排隊任務可能會導致死鎖。如果排隊的任務擁有阻止所有工作線程的資源，則會導致死鎖。例如，會話 S1 啟動事務并在行 r1 上獲取共享 （S） 鎖，然后進入睡眠狀態。在所有可用工作線程上運行的活動會話正在嘗試獲取行 r1 上的獨占 （X） 鎖。由于會話 S1 無法獲取工作線程，因此它無法提交事務并釋放行 r1 上的鎖。這會導致死鎖。

• 內存。當并發請求正在等待可用內存無法滿足的內存授予時，可能會發生死鎖。例如，兩個并發查詢 Q1 和 Q2 作為用戶定義的函數執行，分別獲取 10 MB 和 20 MB 的內存。如果每個查詢需要 30 MB，總可用內存為 20 MB，則 Q1 和 Q2 必須等待對方釋放內存，這會導致死鎖。

• 與并行查詢執行相關的資源。與交換端口關聯的協調器、生產者或使用者線程可能會相互阻塞，從而導致死鎖，通常是在包含至少一個不屬于并行查詢一部分的其他進程時。此外，當并行查詢開始執行時，SQL Server 會根據當前工作負荷確定并行度或工作線程數。如果系統工作負荷意外更改（例如，新查詢開始在服務器上運行或系統工作線程不足），則可能會發生死鎖。

• 多個活動結果集 （MARS） 資源。用戶資源、會話互斥鎖、事務互斥鎖等這些資源用于控制 MARS 下多個活動請求的交錯。

為了使任務在 MARS 下運行，它必須獲取會話互斥鎖。如果任務在事務下運行，則必須獲取事務互斥鎖。這保證在給定會話和給定事務中一次只有一個任務處于活動狀態。獲取所需的互斥鎖后，任務就可以執行。當任務完成或在請求中間產生時，它將首先釋放事務互斥鎖，然后以相反的獲取順序釋放會話互斥鎖。但是，這些資源可能會發生死鎖。在以下偽代碼中，兩個任務（用戶請求 U1 和用戶請求 U2）在同一會話中運行。

從用戶請求 U1 執行的存儲過程已獲取會話互斥鎖。如果存儲過程需要很長時間才能執行，則 SQL Server 數據庫引擎假定存儲過程正在等待用戶的輸入。用戶請求 U2 正在等待會話互斥鎖，而用戶正在等待來自 U2 的結果集，U1 正在等待用戶資源。這是死鎖狀態，邏輯上說明為：

三、處理死鎖

當 SQL Server 數據庫引擎實例選擇某個事務作為死鎖受害者時，它將終止當前批處理，回滾該事務，并向應用程序返回錯誤消息 1205。

由于提交 Transact-SQL 查詢的任何應用程序都可以被選為死鎖受害者，因此應用程序應具有可以捕獲錯誤消息 1205 的錯誤處理程序。如果應用程序未捕獲錯誤，則應用程序可以在不知道其事務已回滾并可能發生錯誤的情況下繼續操作。

實現捕獲錯誤消息 1205 的錯誤處理程序允許應用程序處理死鎖情況并采取補救措施（例如，自動重新提交死鎖中涉及的查詢）。通過自動重新提交查詢，用戶無需知道發生了死鎖。

在重新提交查詢之前，應用程序應短暫暫停。這使死鎖中涉及的其他事務有機會完成并釋放構成死鎖循環一部分的鎖。這樣可以最大程度地降低在重新提交的查詢請求其鎖時再次發生死鎖的可能性。

四、最大限度地減少死鎖

盡管無法完全避免死鎖，但遵循某些編碼約定可以最大程度地減少生成死鎖的可能性。最小化死鎖可以提高事務吞吐量并減少系統開銷，因為事務較少：

• 回滾，撤消事務執行的所有工作。

• 由應用程序重新提交，因為它們在死鎖時已回滾。

要幫助最大程度地減少死鎖，請執行以下操作：

• 以相同的順序訪問對象。

• 避免事務中的用戶交互；保持交易簡短且批量。

• 使用較低的隔離級別。

• 使用基于行版本控制的隔離級別。將數據庫選項設置為啟用已提交的讀取事務以使用行版本控；使用快照隔離。

• 使用綁定連接。

4.1、以相同的順序訪問對象

如果所有并發事務都以相同的順序訪問對象，則不太可能發生死鎖。例如，如果兩個并發事務在表上獲取鎖，然后在表上獲得鎖，則一個事務在表上被阻止，直到另一個事務完成。第一個事務提交或回滾后，第二個事務將繼續，并且不會發生死鎖。對所有數據修改使用存儲過程可以標準化訪問對象的順序。

4.2、避免事務中的用戶交互

避免編寫包含用戶交互的事務，因為無需用戶干預即可運行的批處理的速度比用戶必須手動響應查詢（例如回復應用程序請求的參數提示）的速度快得多。例如，如果事務正在等待用戶輸入，并且用戶周末去吃午飯甚至回家，則用戶會延遲事務完成。這會降低系統吞吐量，因為事務持有的任何鎖僅在提交或回滾事務時釋放。即使沒有出現死鎖情況，訪問相同資源的其他事務也會在等待事務完成時被阻止。

4.3、保持交易簡短且在一個批次中

當多個長時間運行的事務在同一數據庫中同時執行時，通常會發生死鎖。事務時間越長，獨占鎖或更新鎖的持有時間就越長，從而阻止其他活動并導致可能的死鎖情況。

將事務保存在一個批次中可最大程度地減少事務期間的網絡往返，從而減少完成事務和釋放鎖定時可能出現的延遲。

4.4、使用較低的隔離級別

確定事務是否可以在較低的隔離級別運行。實現 read commit 允許事務讀取先前由另一個事務讀取（未修改）的數據，而無需等待第一個事務完成。與較高的隔離級別（如可序列化）相比，使用較低的隔離級別（如已提交的讀取）保留共享鎖的持續時間更短。這減少了鎖定爭用。

4.5、使用基于行版本控制的隔離級別

當數據庫選項設置為 ON 時，在讀取提交隔離級別下運行的事務在讀取操作期間使用行版本控制而不是共享鎖。
快照隔離還使用行版本控制，在讀取操作期間不使用共享鎖。在事務可以在快照隔離下運行之前，必須設置數據庫選項ALLOW_SNAPSHOT_ISOLATIONON。

實現這些隔離級別，以最大程度地減少讀取和寫入操作之間可能發生的死鎖。

4.6、使用綁定連接

使用綁定連接，同一應用程序打開的兩個或多個連接可以相互協作。輔助連接獲取的任何鎖都像由主連接獲取一樣保存，反之亦然。因此，它們不會相互阻塞。

4.7、停止事務

在死鎖方案中，受害事務會自動停止并回滾。在死鎖情況下無需停止事務。

讀到這里，這篇“SQL Server的死鎖是什么及怎么結束”文章已經介紹完畢，想要掌握這篇文章的知識點還需要大家自己動手實踐使用過才能領會，如果想了解更多相關內容的文章，歡迎關注億速云行業資訊頻道。