您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2008

SQL Server 2008 死結分析與處理：Profiler、追蹤旗標 1222、死結優先權

編輯：SQL Server 2008

前言

死結發生於兩個工作階段互相持有對方所需資源的鎖定，皆無法繼續。SQL Server 會選擇一個工作階段作為「死結犧牲者」終止並回滾。**死結不同於阻塞** (單向等待)。

常見成因

應用程式以**不同順序**存取資料表 (如 Session 1 先改客戶再改訂單，Session 2 反向)。
**長時間交易** 更新大量列/表，增加鎖定「表面積」。
鎖定升級：列鎖 → 頁鎖 → 表鎖，若兩會話同頁面升級易發生死結。

方法一：SQL Server Profiler 分析

建立追蹤選取 Deadlock graph 事件，可視化呈現死結參與者、資源、SQL 陳述式。詳細設定參考 MSDN。

方法二：追蹤旗標 1222 記錄至錯誤記錄檔

-- 啟用全域旗標 1222
DBCC TRACEON (1222, -1); GO
DBCC TRACESTATUS; GO

-- 模擬死結 (兩視窗同時執行相反順序 UPDATE)
-- 視窗 1
USE AdventureWorks; SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
WHILE 1=1 BEGIN
  BEGIN TRAN
  UPDATE Purchasing.Vendor SET CreditRating = 1 WHERE VendorID = 90;
  UPDATE Purchasing.Vendor SET CreditRating = 2 WHERE VendorID = 91;
  COMMIT TRAN; END

-- 視窗 2
USE AdventureWorks; SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
WHILE 1=1 BEGIN
  BEGIN TRAN
  UPDATE Purchasing.Vendor SET CreditRating = 2 WHERE VendorID = 91;
  UPDATE Purchasing.Vendor SET CreditRating = 1 WHERE VendorID = 90;
  COMMIT TRAN; END

幾秒後其中一視窗報錯：Msg 1205 ... chosen as the deadlock victim。此時查看 SQL Server 錯誤記錄檔，已記錄詳細死結圖 (資料庫、物件、鎖模式、SQL 文本)。

錯誤記錄檔死結資訊

-- 關閉旗標
DBCC TRACEOFF (1222, -1); GO

旗標 1222：將詳細死結資訊寫入錯誤記錄檔；-1 表示全域啟用。

方法三：設定死結優先權 (SET DEADLOCK_PRIORITY)

SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | -10 ~ 10 | @var }

HIGH：除非對方同為 HIGH，否則不會被選為犧牲者。
NORMAL (預設)：若對方 HIGH 可能被選；若對方 LOW 則安全。
LOW：幾乎確定被選為犧牲者。
同優先權時，**回滾成本較低** 的交易會被選中。

-- 範例：將視窗 1 設為 LOW，使其優先成為犧牲者
SET DEADLOCK_PRIORITY LOW;
BEGIN TRAN
UPDATE Purchasing.Vendor SET CreditRating = 1 WHERE VendorID = 2;
UPDATE Purchasing.Vendor SET CreditRating = 2 WHERE VendorID = 1;
COMMIT TRAN;