目錄

• 一、了解死鎖
• 二、檢測(cè)并結(jié)束死鎖
• 2.1、可能死鎖的資源
• 三、處理死鎖
• 四、最大限度地減少死鎖
• 4.1、以相同的順序訪問(wèn)對(duì)象
• 4.2、避免事務(wù)中的用戶交互
• 4.3、保持交易簡(jiǎn)短且在一個(gè)批次中
• 4.4、使用較低的隔離級(jí)別
• 4.5、使用基于行版本控制的隔離級(jí)別
• 4.6、使用綁定連接
• 4.7、停止事務(wù)
• 總結(jié)

一、了解死鎖

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

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

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

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

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

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

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

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

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

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

• 任務(wù) T1 鎖定了資源 R1（由從 R1 到 T1 的箭頭指示），并請(qǐng)求鎖定資源 R2（由從 T1 到 R2 的箭頭指示）。
• 任務(wù) T2 鎖定了資源 R2（由從 R2 到 T2 的箭頭指示），并請(qǐng)求鎖定資源 R1（由從 T2 到 R1 的箭頭指示）。
• 由于在資源可用之前，這兩個(gè)任務(wù)都無(wú)法繼續(xù)，并且在任務(wù)繼續(xù)之前無(wú)法釋放這兩個(gè)資源，因此存在死鎖狀態(tài)。

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

2.1、可能死鎖的資源

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

• 鎖。等待獲取資源（如對(duì)象、頁(yè)面、行、元數(shù)據(jù)和應(yīng)用程序）的鎖可能會(huì)導(dǎo)致死鎖。例如，事務(wù) T1 在行 r1 上具有共享 （S） 鎖，并且正在等待在 r2 上獲取獨(dú)占 （X） 鎖。事務(wù) T2 在 r2 上具有共享 （S） 鎖，并且正在等待在行 r1 上獲取獨(dú)占 （X） 鎖。這會(huì)導(dǎo)致鎖定循環(huán)，其中 T1 和 T2 相互等待對(duì)方釋放鎖定的資源。
• 工作線程。等待可用工作線程的排隊(duì)任務(wù)可能會(huì)導(dǎo)致死鎖。如果排隊(duì)的任務(wù)擁有阻止所有工作線程的資源，則會(huì)導(dǎo)致死鎖。例如，會(huì)話 S1 啟動(dòng)事務(wù)并在行 r1 上獲取共享 （S） 鎖，然后進(jìn)入睡眠狀態(tài)。在所有可用工作線程上運(yùn)行的活動(dòng)會(huì)話正在嘗試獲取行 r1 上的獨(dú)占 （X） 鎖。由于會(huì)話 S1 無(wú)法獲取工作線程，因此它無(wú)法提交事務(wù)并釋放行 r1 上的鎖。這會(huì)導(dǎo)致死鎖。
• 內(nèi)存。當(dāng)并發(fā)請(qǐng)求正在等待可用內(nèi)存無(wú)法滿足的內(nèi)存授予時(shí)，可能會(huì)發(fā)生死鎖。例如，兩個(gè)并發(fā)查詢 Q1 和 Q2 作為用戶定義的函數(shù)執(zhí)行，分別獲取 10 MB 和 20 MB 的內(nèi)存。如果每個(gè)查詢需要 30 MB，總可用內(nèi)存為 20 MB，則 Q1 和 Q2 必須等待對(duì)方釋放內(nèi)存，這會(huì)導(dǎo)致死鎖。
• 與并行查詢執(zhí)行相關(guān)的資源。與交換端口關(guān)聯(lián)的協(xié)調(diào)器、生產(chǎn)者或使用者線程可能會(huì)相互阻塞，從而導(dǎo)致死鎖，通常是在包含至少一個(gè)不屬于并行查詢一部分的其他進(jìn)程時(shí)。此外，當(dāng)并行查詢開始執(zhí)行時(shí)，SQL Server 會(huì)根據(jù)當(dāng)前工作負(fù)荷確定并行度或工作線程數(shù)。如果系統(tǒng)工作負(fù)荷意外更改（例如，新查詢開始在服務(wù)器上運(yùn)行或系統(tǒng)工作線程不足），則可能會(huì)發(fā)生死鎖。
• 多個(gè)活動(dòng)結(jié)果集 （MARS） 資源。用戶資源、會(huì)話互斥鎖、事務(wù)互斥鎖等這些資源用于控制 MARS 下多個(gè)活動(dòng)請(qǐng)求的交錯(cuò)。

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

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

三、處理死鎖

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

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

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

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

四、最大限度地減少死鎖

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

• 回滾，撤消事務(wù)執(zhí)行的所有工作。
• 由應(yīng)用程序重新提交，因?yàn)樗鼈冊(cè)谒梨i時(shí)已回滾。

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

• 以相同的順序訪問(wèn)對(duì)象。
• 避免事務(wù)中的用戶交互；保持交易簡(jiǎn)短且批量。
• 使用較低的隔離級(jí)別。
• 使用基于行版本控制的隔離級(jí)別。將數(shù)據(jù)庫(kù)選項(xiàng)設(shè)置為啟用已提交的讀取事務(wù)以使用行版本控；使用快照隔離。
• 使用綁定連接。

4.1、以相同的順序訪問(wèn)對(duì)象

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

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

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

4.3、保持交易簡(jiǎn)短且在一個(gè)批次中

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

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

4.4、使用較低的隔離級(jí)別

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

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

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

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

4.6、使用綁定連接

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

4.7、停止事務(wù)

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

總結(jié)

有些應(yīng)用程序依賴于讀取提交隔離的鎖定和阻止行為。對(duì)于這些應(yīng)用程序，在啟用使用基于行版本控制的隔離級(jí)別之前需要進(jìn)行一些更改。

以上就是SQL Server的死鎖說(shuō)明的詳細(xì)內(nèi)容，更多關(guān)于SQL Server死鎖的資料請(qǐng)關(guān)注其它相關(guān)文章！