實戰解決死鎖導致網站掛掉：排查腳本、自動殺進程與根因修正

專案上線後網站間歇性無法訪問，重啟主機暫時恢復，最終確認為資料庫死鎖導致連線池耗盡。本文分享完整排查過程、可直接使用的死鎖偵測/殺進程預存程序、以及根因修正方向。

死鎖成因分析

• 高併發場景下，多用戶同時查詢並更新同一張 Stat 表的同一筆資料。
• 交易邏輯：SELECT ... FROM Stat (取得共享鎖) → UPDATE Stat SET ... (嘗試升級排他鎖)。
• 當兩個交易交錯執行，形成循環等待：A 持有 S 鎖等 X 鎖，B 持有 X 鎖等 A 釋放 S 鎖 → 死鎖。

死鎖測試驗證法

-- 在測試環境模擬高併發
WHILE 1=1
BEGIN
    BEGIN TRAN;
    SELECT * FROM Stat WHERE Id = 1;  -- 取得 S 鎖
    WAITFOR DELAY '00:00:00.01';      -- 增加交錯機率
    UPDATE Stat SET Counter = Counter + 1 WHERE Id = 1;  -- 升級 X 鎖
    COMMIT TRAN;
END

同時開啟多個 SSMS 視窗執行上述腳本，即可快速複現死鎖 (錯誤 1205)。

即時排查腳本：找出封鎖鏈與執行 SQL

USE master;
GO
DECLARE @spid INT, @bl INT;
DECLARE s_cur CURSOR FOR
SELECT 0, blocked
FROM (SELECT * FROM sysprocesses WHERE blocked > 0) a
WHERE NOT EXISTS (SELECT * FROM (SELECT * FROM sysprocesses WHERE blocked > 0) b WHERE a.blocked = spid)
UNION
SELECT spid, blocked FROM sysprocesses WHERE blocked > 0;

OPEN s_cur;
FETCH NEXT FROM s_cur INTO @spid, @bl;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @spid = 0
        PRINT '引起死鎖的根源進程: ' + CAST(@bl AS VARCHAR(10)) + '，其 SQL 如下:';
    ELSE
        PRINT '進程 ' + CAST(@spid AS VARCHAR(10)) + ' 被進程 ' + CAST(@bl AS VARCHAR(10)) + ' 封鎖，SQL 如下:';
    DBCC INPUTBUFFER(@bl);
    FETCH NEXT FROM s_cur INTO @spid, @bl;
END
CLOSE s_cur;
DEALLOCATE s_cur;
EXEC sp_who2;

進階：自動偵測並殺掉死鎖進程的預存程序

CREATE PROCEDURE p_lockinfo
    @kill_lock_spid BIT = 1,        -- 1=殺掉死鎖進程, 0=僅顯示
    @show_spid_if_nolock BIT = 1    -- 無死鎖時是否顯示正常進程
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @count INT, @i INT = 1, @spid VARCHAR(10), @flag VARCHAR(20);

    -- 建立暫存表收集封鎖鏈
    SELECT IDENTITY(INT,1,1) AS id,
           flag, spid, kpid, blocked, dbid, db_name(dbid) AS dbname,
           uid, loginame, cpu, login_time, open_tran, status,
           hostname, program_name, hostprocess, nt_domain, net_address
    INTO #t
    FROM (
        SELECT '死鎖的進程' AS flag, spid, kpid, blocked, dbid, uid, loginame, cpu, login_time, open_tran,
               status, hostname, program_name, hostprocess, nt_domain, net_address,
               spid AS s1, 0 AS s2
        FROM master..sysprocesses a
        JOIN (SELECT blocked FROM master..sysprocesses GROUP BY blocked) b ON a.spid = b.blocked
        WHERE a.blocked = 0
        UNION ALL
        SELECT '|_犧牲品_>', spid, kpid, blocked, dbid, uid, loginame, cpu, login_time, open_tran,
               status, hostname, program_name, hostprocess, nt_domain, net_address,
               blocked AS s1, 1 AS s2
        FROM master..sysprocesses WHERE blocked <> 0
    ) a ORDER BY s1, s2;

    SELECT @count = @@ROWCOUNT;

    IF @count = 0 AND @show_spid_if_nolock = 1
    BEGIN
        INSERT #t (flag, spid, kpid, blocked, dbid, dbname, uid, loginame, cpu, login_time, open_tran, status, hostname, program_name, hostprocess, nt_domain, net_address)
        SELECT '正常的進程', spid, kpid, blocked, dbid, db_name(dbid), uid, loginame, cpu, login_time, open_tran, status, hostname, program_name, hostprocess, nt_domain, net_address
        FROM master..sysprocesses;
        SET @count = @@ROWCOUNT;
    END

    IF @count > 0
    BEGIN
        CREATE TABLE #t1 (id INT IDENTITY(1,1), a NVARCHAR(30), b INT, EventInfo NVARCHAR(255));
        IF @kill_lock_spid = 1
        BEGIN
            WHILE @i <= @count
            BEGIN
                SELECT @spid = CAST(spid AS VARCHAR(10)), @flag = flag FROM #t WHERE id = @i;
                INSERT #t1 EXEC('DBCC INPUTBUFFER(' + @spid + ')');
                IF @flag = '死鎖的進程' EXEC('KILL ' + @spid);
                SET @i = @i + 1;
            END
        END
        ELSE
        BEGIN
            WHILE @i <= @count
            BEGIN
                INSERT #t1 EXEC('DBCC INPUTBUFFER(' + (SELECT CAST(spid AS VARCHAR(10)) FROM #t WHERE id = @i) + ')');
                SET @i = @i + 1;
            END
        END
        SELECT t.*, EventInfo = t1.EventInfo FROM #t t JOIN #t1 t1 ON t.id = t1.id;
    END
END;
GO

使用方式

-- 僅顯示死鎖資訊
EXEC p_lockinfo @kill_lock_spid = 0, @show_spid_if_nolock = 1;

-- 顯示並自動殺掉死鎖進程
EXEC p_lockinfo @kill_lock_spid = 1, @show_spid_if_nolock = 1;

根因修正方向 (治本)

1. 統一存取順序：所有交易對多表操作必須遵循相同順序 (如永遠先鎖 Table A 再鎖 Table B)。
2. 縮短交易範圍：將 SELECT 移出交易，僅在 UPDATE 時開啟交易，減少 S 鎖持有時間。
3. 使用 UPDLOCK：在 SELECT 時加 WITH (UPDLOCK, HOLDLOCK)，直接取得更新鎖避免 S→X 升級衝突。

   BEGIN TRAN;
   SELECT * FROM Stat WITH (UPDLOCK, HOLDLOCK) WHERE Id = 1;
   UPDATE Stat SET Counter = Counter + 1 WHERE Id = 1;
   COMMIT TRAN;
   

4. 啟用 READ_COMMITTED_SNAPSHOT：資料庫層級開啟版本存放，讀取不加共享鎖，從根本消除讀寫封鎖與大部分死鎖。

   ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
   

5. 索引優化：確保 WHERE 條件欄位有適當索引，縮小掃描範圍，降低鎖升級機率。

監控與告警建議

• SQL Server Agent 定時作業執行 p_lockinfo，將結果寫入日誌表，超過閾值發送告警。
• Extended Events 監聽 xml_deadlock_report 事件，自動擷取死鎖圖 (`.xdl`) 供事後分析。
• 應用層捕捉錯誤 1205，實作指數退避重試 (最多 3 次)，避免使用者感知失敗。

結合即時偵測腳本、自動清理與架構層面修正，可徹底解決死鎖導致的服務不可用問題。