SQL Server 死結成因深度剖析與七大預防策略實戰

死結本質：資源競爭的循環等待

交易 1 持有表 A 排他鎖、請求表 B；交易 2 持有表 B 排他鎖、請求表 A。雙方各佔一資源、互相等待，形成循環等待鏈。若無外部干預（死結偵測器選犧牲者回滾），將永久僵持。

典型成因場景

1. 存取順序不一致：SP1 先鎖 A 再鎖 B，SP2 卻先鎖 B 再鎖 A。
2. 隱性升級衝突：交易 A 共享鎖讀取、嘗試升級排他鎖寫入；交易 B 已持有排他鎖，雙方互不相讓。
3. 大批次掃描：無索引導致表掃描，持有大量列/頁鎖，極易與其他交易衝突。
4. 使用者互動夾雜交易：交易等待前端輸入，長時間持有鎖封鎖他人。

即時偵測與定腳本

CREATE PROC dbo.sp_WhoLock
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @spid SMALLINT, @blk SMALLINT;
    CREATE TABLE #tmp (id INT IDENTITY(1,1), spid SMALLINT, blk SMALLINT);

    INSERT #tmp(spid, blk)
    SELECT 0, blocked FROM sys.sysprocesses WHERE blocked > 0
      AND blocked NOT IN (SELECT spid FROM sys.sysprocesses WHERE blocked > 0)
    UNION ALL
    SELECT spid, blocked FROM sys.sysprocesses WHERE blocked > 0;

    DECLARE @cnt INT = (SELECT COUNT(*) FROM #tmp), @i INT = 1;
    IF @cnt = 0 PRINT '目前無阻塞/死結';

    WHILE @i <= @cnt
    BEGIN
        SELECT @spid = spid, @blk = blk FROM #tmp WHERE id = @i;
        IF @spid = 0
            PRINT '引發死結的根因 SPID: ' + CAST(@blk AS VARCHAR) + '，其 SQL:';
        ELSE
            PRINT 'SPID ' + CAST(@spid AS VARCHAR) + ' 被 SPID ' + CAST(@blk AS VARCHAR) + ' 阻塞，SQL:';
        DBCC INPUTBUFFER(@blk);
        SET @i += 1;
    END
    DROP TABLE #tmp;
END;

執行 EXEC sp_WhoLock 即可列出阻塞鏈與對應 SQL 文本。

七大預防策略

1. 統一存取順序：所有模組、SP、觸發器對同組表遵循固定順序（如按表名字母序）。
2. 拒絕交易中使用者互動：前端驗證完成後再開啟交易；長流程採非同步隊列。
3. 縮短交易範圍：單批次、最小邏輯單元、盡早 COMMIT/ROLLBACK。
4. 降低隔離等級：可接受髒讀/不可重複讀時用 READ COMMITTED 或 SNAPSHOT，縮短 S 鎖壽命。
5. 繫結連接：sp_getbindtoken/sp_bindsession 讓多連接共享鎖空間，避免自阻塞。
6. 索引覆蓋關鍵查詢：叢集索引 + 非叢集覆蓋索引，將掃描轉為 Seek，大幅減少鎖數量。
7. 避免大範圍 DML：分批次更新/刪除（每批 1,000-5,000 列），配合 WAITFOR DELAY 釋放鎖喘息。

輔助優化技巧

• 熱點表建立叢集索引，減少頁分裂與鎖競爭。
• UPDATE/DELETE 必須走索引 Seek，避免掃描鎖全表。
• 巢狀交易謹慎設計 COMMIT/ROLLBACK 邏輯，避免計數器不平衡。
• 允許髒讀的報表查詢加 WITH (NOLOCK) 或設定 SNAPSHOT 隔離。

死結發生時的緊急處置

1. 執行 sp_WhoLock 確認犧牲者 SPID。
2. 若業務允許，KILL <spid> 強制回滾釋放鎖。
3. 檢視錯誤日誌確認 1205 錯誤細節，導入應用層重試邏輯（指數退避 + 隨機抖動）。

結語

死結無法 100% 根除，但透過「統一順序、縮短交易、索引優化、隔離等級選擇」四大支柱，可將發生機率壓至極低。建立監控告警與自動化 KILL 機制，讓系統在極端情況下也能自癒。