SQL Server 死鎖成因分析與預防解決完整指南

什麼是死鎖

死鎖指兩個或多個執行緒在爭奪資源時互相等待，若無外力干預將永遠無法推進。資料庫場景中：

• 事務 1 持有表 A 排他鎖，需求表 B 鎖
• 事務 2 持有表 B 排他鎖，需求表 A 鎖
• 雙方提交/回滾前不釋放鎖 → 形成循環等待 → 死鎖

常見觸發場景

1. 預存程序鎖表順序不一致：部分程序先鎖 A 後鎖 B，另一部分鎖順序相反
2. 大批量更新/刪除：長事務持有大量鎖，增加衝突機率
3. 索引缺失導致全表掃描：掃描過程持有大量共享/更新鎖
4. 使用者互動在事務中：等待輸入導致事務長時間持鎖

死鎖監控與捕捉

系統預存程序 sp_who_lock

CREATE PROCEDURE sp_who_lock
AS
BEGIN
    DECLARE @spid int, @bl int, @intCount int, @intCounter int
    CREATE TABLE #tmp_lock_who (id int identity(1,1), spid smallint, bl smallint)
    
    INSERT INTO #tmp_lock_who(spid, bl)
    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
    
    SELECT @intCount = COUNT(*) FROM #tmp_lock_who
    IF @intCount = 0
        SELECT '目前無阻塞與死鎖' AS message
    ELSE
    BEGIN
        SET @intCounter = 1
        WHILE @intCounter <= @intCount
        BEGIN
            SELECT @spid = spid, @bl = bl FROM #tmp_lock_who WHERE id = @intCounter
            IF @spid = 0
                SELECT '引發死鎖進程: ' + CAST(@bl AS varchar(10)) + '，其 SQL:'
            ELSE
                SELECT '進程 ' + CAST(@spid AS varchar(10)) + ' 被 ' + CAST(@bl AS varchar(10)) + ' 阻塞，SQL:'
            DBCC INPUTBUFFER(@bl)  -- 顯示阻塞者正在執行的語句
            SET @intCounter = @intCounter + 1
        END
    END
    DROP TABLE #tmp_lock_who
    RETURN 0
END

執行 EXEC sp_who_lock 即可即時捕捉阻塞鏈與問題 SQL。

死鎖預防核心原則

1. 統一存取順序（最有效）

所有並發事務按相同順序訪問物件（表/頁/列）。例：所有程序先鎖表 A 再鎖表 B，絕不出現反序。

2. 避免事務中使用者互動

批次處理速度遠快於人工回應。事務等待輸入會長時間持鎖，阻塞其他事務，降低吞吐量。

3. 縮短事務長度，單批次提交

• 長事務 = 長時間持有排他/更新鎖 = 高死鎖風險
• 單批次減少網路往返，加快鎖釋放

4. 降低隔離等級

• READ COMMITTED (預設) → 考慮 READ UNCOMMITTED (NOLOCK) 或啟用 快照隔離 (Snapshot Isolation) (SQL 2005+)
• 快照隔離以版本鏈實現讀不阻塞寫、寫不阻塞讀，大幅減少讀寫死鎖

5. 使用繫結連接

同一應用程式的多連接共享鎖，避免自我阻塞。

具體調優建議清單

死鎖發生時的自動處理

SQL Server 死鎖監視器定期偵測，選擇「回滾成本最低」的犧牲者，拋出錯誤 1205。應用層需捕捉 1205 並實施重試邏輯（指數退避）。

總結

死鎖無法 100% 避免，但可透過「統一鎖順序、縮短事務、降低隔離、補強索引、監控告警」將發生率壓至極低。架構層面引入快照隔離是現代 SQL Server 解決讀寫死鎖的銀彈方案。