數據庫死鎖導致網站不可用：根因分析與自動化解決方案

現象回顧

專案上線後網站突然無法訪問，重啟主機暫時恢復，排查發現是高併發修改同一條 Stat 表記錄導致死鎖與阻塞。

死鎖成因

• 多用戶並發提取查詢並修改同一條記錄。
• 大數據量操作加劇鎖持有時間。
• 缺乏合理的索引與事務隔離級別控制。

核心知識點

1. SQL 死鎖 vs 阻塞：死鎖是循環等待，阻塞是單向等待。
2. 壓測複現：在程序中循環 1 萬次數據庫操作，多窗口並發執行。
3. 定位源頭 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
        SELECT '引發死鎖的進程: ' + CAST(@bl AS VARCHAR(10)) + '，其 SQL 如下'
    ELSE
        SELECT 'SPID: ' + CAST(@spid AS VARCHAR(10)) + ' 被 SPID: ' + 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

自動化監控與殺進程存儲過程

創建 p_lockinfo，支持參數控制：是否殺掉死鎖進程、無死鎖時是否顯示正常進程。

CREATE PROC p_lockinfo
    @kill_lock_spid BIT = 1,      -- 1=殺掉死鎖進程
    @show_spid_if_nolock BIT = 1  -- 1=無死鎖時顯示正常進程
AS
BEGIN
    -- 將死鎖進程與受害者進程寫入臨時表 #t
    -- 循環執行 DBCC INPUTBUFFER 獲取 SQL 文本
    -- @kill_lock_spid=1 時自動 KILL 死鎖 SPID
    -- 最後輸出進程信息與對應 SQL
END
GO

調用示例： EXEC p_lockinfo（默認殺掉死鎖並顯示詳情）。

預防建議

• 縮短事務範圍，盡早提交。
• 統一訪問表的順序，避免交叉鎖。
• 為高頻更新表添加合適索引，減少掃描範圍。
• 考慮樂觀鎖或行版本控制（Snapshot Isolation）降低阻塞。