SQL Server 死鎖根因深度解析與解決預防全攻略

什麼是死鎖？

兩個或多個進程在執行過程中，因爭奪資源造成循環等待，若無外力干預，皆無法推進。系統處於死鎖狀態，涉及進程稱為死鎖進程。

死鎖產生的四個必要條件（缺一不可）

1. 互斥條件：資源排他使用，同一時刻僅一進程佔用
2. 請求與保持：持有資源又請求新資源，請求不到時不釋放已有資源
3. 不剝奪條件：資源只能由持有者主動釋放，不可強制搶佔
4. 環路等待：存在進程-資源循環鏈（P0→P1→...→Pn→P0）

打破任一條件即可避免死鎖。

SQL Server 鎖機制詳解

核心鎖模式

鎖粒度層級

Table → Page → Row / Key / Key-Range。細粒度併發高但開銷大，SQL Server 動態升級（Lock Escalation）。

查看當前鎖狀態

CREATE VIEW dblocks AS
SELECT request_session_id AS spid,
       DB_NAME(resource_database_id) AS dbname,
       CASE WHEN resource_type='object' THEN OBJECT_NAME(resource_associated_entity_id)
            WHEN resource_associated_entity_id=0 THEN 'n/a'
            ELSE OBJECT_NAME(p.object_id) END AS entity_name,
       index_id, resource_type AS resource,
       resource_description AS description,
       request_mode AS mode, request_status AS status
FROM sys.dm_tran_locks t
LEFT JOIN sys.partitions p ON p.partition_id=t.resource_associated_entity_id
WHERE resource_database_id=DB_ID();

死鎖追蹤與案例分析

Profiler 追蹤事件

• Locks → Deadlock graph
• TSQL → SQL:BatchStarting

經典案例：雙進程交叉等待頁鎖

• 進程 A 持有頁 1686247 U 鎖，等待頁 1689766 U 鎖
• 進程 B 持有頁 1689766 U 鎖，等待頁 1686247 U 鎖
• 形成環路 → 死鎖

執行計劃揭示根因

更新語句使用 Index Scan（索引掃描） 而非 Seek，掃描範圍大、持鎖久、併發衝突概率高。

解決與預防策略

針對性優化（治標）

1. 建立聚集索引消除堆表掃描
2. 對 WHERE/JOIN/UPDATE 列建立包含索引轉為 Index Seek
3. 優化後鎖定範圍縮小、持鎖時間縮短，死鎖概率大幅降低

應用層處理（治本）

• 捕獲錯誤碼 1205，自動重試（通常二次即成功）
• 事務邏輯冪等設計，重試不產生副作用

開發規範預防死鎖（七大原則）

1. 避免併發執行修改類語句
2. 事務一次性鎖定所有所需資源
3. 統一加鎖順序（如按表名字母序、主鍵序）
4. 縮短事務執行時間，輸入操作前置
5. 使用最低隔離級別（Read Committed 通常足夠）
6. 大表拆分（水平/垂直分表）降低鎖競爭
7. 優化索引、避免大事務，讓鎖「拿得快、放得早」

死鎖無法 100% 根除，但通過「優化索引縮小鎖範圍 + 統一加鎖順序 + 應用層重試」組合拳，可將影響降至最低。