萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 死鎖預防、偵測與解決完整指南

死鎖是並發系統不可避免的現象,但可透過設計規範、監控偵測、程式碼優化將發生率降至最低並快速恢復。

死鎖形成四必要條件 (Coffman Conditions)

  1. 互斥:資源獨佔 (鎖)
  2. 佔有並等待:持有資源等待新資源
  3. 非搶佔:資源只能自願釋放
  4. 循環等待:T1→R1→T2→R2→...→T1

資料庫鎖機制天然滿足前三條,**打破循環等待**是預防核心。

經典死鎖場景

-- Session 1                    -- Session 2
BEGIN TRAN                       BEGIN TRAN
UPDATE TableA SET ... WHERE Id=1  UPDATE TableB SET ... WHERE Id=2
-- 持有 TableA X 鎖              -- 持有 TableB X 鎖
UPDATE TableB SET ... WHERE Id=2  UPDATE TableA SET ... WHERE Id=1
-- 等待 TableB X 鎖 (被 S2 阻塞)   -- 等待 TableA X 鎖 (被 S1 阻塞)
-- ★ 死鎖發生 ★

預防策略:打破循環等待

  1. 統一存取順序:所有預存程序/批次**固定順序**存取表 (如按表名字母序)。單一入口點強制排序。
  2. 縮短交易範圍:只在必要時開啟交易,儘早 COMMIT/ROLLBACK
  3. 避免使用者互動在交易中:等待輸入 = 長時間持鎖
  4. 使用 ROWLOCK / UPDLOCK / HOLDLOCK 提示:精細控制鎖粒度與持有時間
  5. 索引優化:缺少索引導致掃描 → 升級為表鎖 → 死機率飆升
  6. 隔離級別權衡:READ COMMITTED SNAPSHOT (RCSI) / SNAPSHOT 隔離級別用版本存儲消除讀寫衝突

偵測與監控

系統健康工作階段 (Extended Events) - 推薦

-- 擷取死鎖圖 (XML)
SELECT CAST(target_data AS XML) 
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer';

追蹤旗標 1204/1222 (舊版相容)

DBCC TRACEON (1222, -1); -- 1222: XML 格式輸出至錯誤日誌
DBCC TRACEON (1204, -1); -- 1204: 文字格式

自訂監控預存程序 (即時阻塞/死鎖快照)

CREATE PROCEDURE dbo.sp_WhoLock
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @spid SMALLINT, @bl SMALLINT, @cnt INT, @i INT = 1;
    CREATE TABLE #Blockers (Id INT IDENTITY, Spid SMALLINT, Blocker SMALLINT);
    
    -- 找出阻塞鏈頭 (被阻塞但不阻塞他人)
    INSERT INTO #Blockers (Spid, Blocker)
    SELECT 0, blocked FROM sys.sysprocesses WHERE blocked > 0
    EXCEPT
    SELECT blocked, 0 FROM sys.sysprocesses WHERE blocked > 0;
    
    -- 所有被阻塞 SPID
    INSERT INTO #Blockers (Spid, Blocker)
    SELECT spid, blocked FROM sys.sysprocesses WHERE blocked > 0;
    
    SELECT @cnt = COUNT(*) FROM #Blockers;
    IF @cnt = 0 BEGIN PRINT '無阻塞/死鎖'; RETURN; END
    
    WHILE @i <= @cnt
    BEGIN
        SELECT @spid = Spid, @bl = Blocker FROM #Blockers WHERE Id = @i;
        IF @spid = 0
            PRINT '死鎖鏈頭/根阻塞者 SPID: ' + CAST(@bl AS VARCHAR) + ' 其輸入緩衝:';
        ELSE
            PRINT 'SPID ' + CAST(@spid AS VARCHAR) + ' 被 SPID ' + CAST(@bl AS VARCHAR) + ' 阻塞,輸入緩衝:';
        DBCC INPUTBUFFER(@bl);
        SET @i += 1;
    END
    DROP TABLE #Blockers;
END;

死鎖發生時的自動處理

  • SQL Server 死鎖監視器 (Lock Monitor) 每 5 秒掃描一次
  • 選擇 **死鎖犧牲者**:根據 DEADLOCK_PRIORITY (LOW/NORMAL/HIGH) 與 **撤銷成本** (Log 量) 選擇
  • 犧牲者收到錯誤 **1205**,交易回滾,鎖釋放,其他交易繼續
  • 應用層**必須捕獲 1205 並重試** (指數退避 + 隨機抖動)

應用層重試模式

-- C# 範例
int retry = 0, maxRetry = 3;
while (true) {
    try {
        using (var txn = conn.BeginTransaction()) {
            // ... 業務邏輯
            txn.Commit();
            break;
        }
    } catch (SqlException ex) when (ex.Number == 1205 && retry < maxRetry) {
        retry++;
        Thread.Sleep((int)Math.Pow(2, retry) * 100 + new Random().Next(100));
    }
}

事後分析:解讀死鎖圖

  • victim-process:被選中的犧牲者
  • process-list/process:參與執行緒,含 inputbuf (SQL 文本)、executionStack/frame (執行堆疊)
  • resource-list/keylock/pagelock/objectlock:爭用資源,含 owner-list/owner、waiter-list/waiter
  • 關鍵欄位:mode (S/U/X/IS/IU/IX/SIU)、waittype、lasttranstarted

死鎖不可全除,但可透過**統一存取順序、縮短交易、索引支援、RCSI 隔離、應用層重試**構建韌性系統。

copyright © 萬盛學電腦網 all rights reserved