SQL?Server的死鎖是什么及怎么結(jié)束

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

一、了解死鎖

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

• 事務(wù) A 獲取第 1 行上的共享鎖。

• 事務(wù) B 獲取第 2 行上的共享鎖。

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

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

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

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

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

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

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

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

二、檢測并結(jié)束死鎖

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

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

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

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

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

2.1、可能死鎖的資源

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

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

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

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

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

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

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

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

三、處理死鎖

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

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

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

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

四、最大限度地減少死鎖

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

• 回滾，撤消事務(wù)執(zhí)行的所有工作。

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

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

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

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

• 使用較低的隔離級別。

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

• 使用綁定連接。

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

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

4.2、避免事務(wù)中的用戶交互

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

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

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

將事務(wù)保存在一個批次中可最大程度地減少事務(wù)期間的網(wǎng)絡(luò)往返，從而減少完成事務(wù)和釋放鎖定時可能出現(xiàn)的延遲。

4.4、使用較低的隔離級別

確定事務(wù)是否可以在較低的隔離級別運行。實現(xiàn) read commit 允許事務(wù)讀取先前由另一個事務(wù)讀?。ㄎ葱薷模┑臄?shù)據(jù)，而無需等待第一個事務(wù)完成。與較高的隔離級別（如可序列化）相比，使用較低的隔離級別（如已提交的讀?。┍Ａ艄蚕礞i的持續(xù)時間更短。這減少了鎖定爭用。

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

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

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

4.6、使用綁定連接

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

4.7、停止事務(wù)

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

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