SQL Server 死鎖成因、監控與預防最佳實務完整指南

死鎖：兩個以上執行緒互相持有資源並等待對方釋放，形成循環等待。本文解析死鎖成因、提供監控預存程序、列出七大預防原則與五項具體建議，助你從根源降低死鎖發生率。

死鎖經典情境

-- 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 阻塞)
-- 死鎖發生，SQL Server 選擇犧牲者

監控死鎖：sp_who_lock 預存程序

CREATE PROCEDURE sp_who_lock
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @spid SMALLINT, @bl SMALLINT, @cnt INT, @i INT = 1;
    
    CREATE TABLE #tmp_lock_who (
        id INT IDENTITY(1,1), 
        spid SMALLINT, 
        bl SMALLINT
    );
    
    INSERT #tmp_lock_who (spid, bl)
    SELECT 0, blocked FROM sys.sysprocesses WHERE blocked > 0
      AND blocked NOT IN (SELECT spid FROM sys.sysprocesses WHERE blocked > 0)
    UNION
    SELECT spid, blocked FROM sys.sysprocesses WHERE blocked > 0;
    
    SELECT @cnt = COUNT(*) FROM #tmp_lock_who;
    IF @cnt = 0
    BEGIN
        PRINT '目前無阻塞與死鎖資訊';
        RETURN;
    END
    
    WHILE @i <= @cnt
    BEGIN
        SELECT @spid = spid, @bl = bl FROM #tmp_lock_who 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);  -- 顯示阻塞者最後一批 T-SQL
        SET @i += 1;
    END
    DROP TABLE #tmp_lock_who;
END
GO

用法：EXEC sp_who_lock; 即可輸出阻塞鏈與問題 SQL。

七大死鎖預防原則

1. 統一存取順序：所有交易依相同順序鎖定資源 (如先 TableA 再 TableB)，消除循環等待條件。
2. 避免交易中互動：移除 WAITFOR、使用者輸入等待，縮短鎖持有時間。
3. 縮短交易範圍：單一批次完成、減少網路往返、盡早 COMMIT/ROLLBACK。
4. 降低隔離等級：可接受髒讀時用 READ COMMITTED 或 READ UNCOMMITTED (NOLOCK)，減少共享鎖持有。
5. 使用繫結連接：sp_getbindtoken/sp_bindsession 讓多連線共用鎖空間，避免自我阻塞。
6. 優化索引：精確索引縮小掃描範圍，減少鎖升級為資料表鎖機率。
7. 鎖升級控制：ALTER TABLE ... SET (LOCK_ESCALATION = AUTO/DISABLE) (SQL 2008+)。

五項具體實作建議

1. 熱門資料表建立叢集索引，減少 RID 查找與鎖競爭。
2. 避免大範圍 UPDATE/DELETE (如 WHERE 1=1)，分批處理 (每批 1000-5000 列)。
3. 確保 UPDATE/DELETE 的 WHERE 條件走索引求 (Index Seek)。
4. 巢狀交易謹慎使用 COMMIT/ROLLBACK，避免隱含交易計數器錯亂。
5. 查詢允許髒讀的報表類 SQL，加上 WITH (NOLOCK) 或設定 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED。

死鎖資訊擷取進階

• 擴充事件 (Extended Events) xml_deadlock_report：輕量、完整圖形化死鎖圖。
• 追蹤旗標 1222/1204：錯誤日誌記錄死鎖詳細資訊 (效能影響較大)。
• sys.dm_tran_locks + sys.dm_exec_requests：即時鎖等待鏈查詢。

系統化套用上述原則與監控機制，可將死鎖發生率壓至極低，保障高併發環境穩定運行。