鋒利的SQL2014：處理死結，sql2014

鋒利的SQL2014：處理死結，sql2014
 

在兩個或多個任務中，如果每個任務鎖定了其他任務試圖鎖定資源，此時會造成這些任務永久阻塞，從而出現死結。例如：

      事務A擷取了行1的共用鎖定。

      事務B擷取了行2的共用鎖定。

      現在，事務A請求行2的獨佔鎖定，但在事務B完成並釋放其對行2持有的共用鎖定之前被阻塞。

      現在，事務B請求行1的獨佔鎖定，但在事務A完成並釋放其對行1持有的共用鎖定之前被阻塞。

事務A必須在事務B完成之後才能完成，但事務B被事務A阻塞。這種情況也稱為循環相依性關係：事務A依賴於事務B，而事務B又依賴於事務A，從而形成了一個迴圈。

除非某個外部進程斷開死結，否則死結中的兩個事務都將無限期等待下去。資料庫引擎死結監視器定期檢查陷入死結的任務。如果檢測死結，將選擇其中一個任務作為犧牲品，然後終止其事務並提示錯誤。這樣，其他任務就可以完成其事務，從而解除死結。對於事務以錯誤終止的應用程式，它還可以重試該事務，但通常要等到與它一起陷入死結的其他事務完成後執行。

17.6.1  防止死結的方法1．按同一順序訪問對象

如果所有並發事務按同一順序訪問對象，則發生死結的可能性會降低。例如，如果兩個並發事務先擷取Supplier表上的鎖，然後擷取Part表上的鎖。在其中一個事務完成之前，另一個事務將在Supplier表上被阻塞。當第1個事務提交或復原之後，第2個事務將繼續執行，這樣就不會發生死結。如果使用預存程序進行資料修改，則可以使對象的訪問順序標準化。

2．避免事務中的使用者互動

避免編寫包含使用者互動的事務，因為沒有使用者幹預的批處理的運行速度遠快於使用者必須手動響應查詢時的速度。例如，如果事務正在等待使用者輸入，而使用者去吃午餐了，那麼使用者就耽誤了事務的完成。這將降低系統的輸送量，因為事務持有的任何鎖只有在事務提交或復原後才會釋放。即使不出現死結的情況，在佔用資源的事務完成之前，訪問同一資源的其他事務也會被阻塞。

3．保持事務簡短並處於一個批處理中

在同一資料庫中，並發執行多個需要長時間啟動並執行事務時通常會發生死結。事務的已耗用時間越長，它持有獨佔鎖定或更新鎖定的時間也就越長，從而會阻塞其他活動並可能導致死結。

4．使用較低的隔離等級

確定事務是否能在較低的隔離等級上運行。實現已提交讀允許事務讀取另一個事務已讀取（未修改）的資料，而不必等待第1個事務完成。使用較低的隔離等級（例如已提交讀）比使用較高的隔離等級（例如可序列化）持有共用鎖定的時間更短。這樣就減少了鎖爭用。

5．使用基於資料列版本設定的隔離等級

如果將READ_COMMITTED_SNAPSHOT資料庫選項設定為ON，則在已提交讀隔離等級下啟動並執行事務在讀操作期間將使用資料列版本設定而不是共用鎖定。

17.6.2  使用TRY...CATCH處理死結

在TRY...CATCH構造的CATCH塊可以捕獲1205死結錯誤，發生錯誤後，可以通過復原事務來解除鎖定。下面的語句建立了用於說明死結狀態的表和用於列印錯誤資訊的預存程序。

USE AdventureWorks;

GO

 

-- 驗證表是否已經存在

IF OBJECT_ID(N'my_sales',N'U') IS NOT NULL

    DROP TABLE my_sales;

GO

 

-- 建立表並插入資料

CREATE TABLE my_sales

    (

    Itemid      INT PRIMARY KEY,

    Sales       INT not null

    );

GO

 

INSERT my_sales (itemid,sales) VALUES (1, 1);

INSERT my_sales (itemid,sales) VALUES (2, 1);

GO

 

-- 驗證預存程序是否已經存在

IF OBJECT_ID(N'usp_MyErrorLog',N'P') IS NOT NULL

    DROP PROCEDURE usp_MyErrorLog;

GO

 

-- 建立預存程序，用於輸出錯誤訊息

CREATE PROCEDURE usp_MyErrorLog

AS

    PRINT

        N'錯誤 ' +CONVERT(VARCHAR(50), ERROR_NUMBER()) +

        N', 嚴重層級 ' +CONVERT(VARCHAR(5), ERROR_SEVERITY()) +

        N', 狀態 ' +CONVERT(VARCHAR(5), ERROR_STATE()) +

        N', 行 ' +CONVERT(VARCHAR(5), ERROR_LINE());

    PRINT

        ERROR_MESSAGE();

下面的會話1和會話2代碼指令碼在兩個單獨的SQL Server Management Studio串連下同時運行。兩個會話都嘗試更新表中的相同行。在第一次嘗試過程中，其中一個會話將成功完成更新操作，而另一個會話將被選擇為死結犧牲品。死結犧牲品錯誤將使執行跳至CATCH塊，事務將進入無法提交狀態。在CATCH塊中，死結犧牲品會復原事務並重試更新此表，直到更新成功或達到了重試限制。

 

會話1

會話2

USE AdventureWorks; GO   -- 定義並設定變數，指定嘗試提交更新的次數 DECLARE @retry INT; SET @retry = 5;   -- 如果被作為了死結犧牲品，保持嘗試更新 WHILE (@retry > 0) BEGIN     BEGIN TRY         BEGIN TRANSACTION;             UPDATE my_sales         SET sales = sales + 1         WHERE itemid = 1;           -- 延時等待，此時itemid為1和2的行         -- 在沒有提交前，都無法釋放鎖         WAITFOR DELAY '00:00:13';             UPDATE my_sales         SET sales = sales + 1         WHERE itemid = 2;           SET @retry = 0;           COMMIT TRANSACTION;     END TRY     BEGIN CATCH         -- 檢測錯誤編號，如果是死結犧牲品，         -- 則減少重新嘗試計數。如果是其他         -- 錯誤，則退出WHILE迴圈         IF (ERROR_NUMBER() = 1205)             SET @retry = @retry - 1;         ELSE             SET @retry = -1;           -- 輸出錯誤訊息         EXECUTE usp_MyErrorLog;           -- 會話中包含無法提交的事務         -- XACT_STATE將返回 -1         IF XACT_STATE() <> 0             ROLLBACK TRANSACTION;     END CATCH; END; -- 結束WHILE迴圈

USE AdventureWorks; GO   -- 定義並設定變數，指定嘗試提交更新的次數 DECLARE @retry INT; SET @retry = 5;   -- 如果被作為了死結犧牲品，保持嘗試更新 WHILE (@retry > 0) BEGIN     BEGIN TRY        BEGIN TRANSACTION;             UPDATE my_sales         SET sales = sales + 1         WHERE itemid = 2;           -- 延時等待，此時itemid為1和2的行         -- 在沒有提交前，都無法釋放鎖         WAITFOR DELAY '00:00:07';             UPDATE my_sales         SET sales = sales + 1         WHERE itemid = 1;           SET @retry = 0;           COMMIT TRANSACTION;     END TRY     BEGIN CATCH         -- 檢測錯誤編號，如果是死結犧牲品，         -- 則減少重新嘗試計數。如果是其他         -- 錯誤，則退出WHILE迴圈         IF (ERROR_NUMBER() = 1205)             SET @retry = @retry - 1;         ELSE             SET @retry = -1;           -- 輸出錯誤訊息         EXECUTE usp_MyErrorLog;           -- 會話中包含無法提交的事務         -- XACT_STATE將返回 -1         IF XACT_STATE() <> 0             ROLLBACK TRANSACTION;     END CATCH; END; -- 結束WHILE迴圈

下面是會話1中返回的訊息，表示兩行都已經被更新。

(1 行受影響) 

 

(1 行受影響)

下面是會話2中返回的訊息，會話2被作為了死結犧牲品。

(1 行受影響)                    -- 由於死結，會話2事務中只成功更新1行，這時會發生復原操作

錯誤 1205, 嚴重層級 13, 狀態 51, 行18   -- 由預存程序usp_MyErrorLog輸出的錯誤訊息

事務(進程 ID52)與另一個進程被死結在鎖資源上，並且已被選作死結犧牲品。請重新運行該事務。 -- SQL Server的提示

 

(1 行受影響)  -- 本行和下行訊息是重新嘗試更新後得到的提示訊息

 

(1 行受影響)