SQL Server 死鎖實戰複盤：大事務 + 低選擇性索引 = 災難，禁用索引秒級恢復

死鎖四必要條件回顧

1. 互斥：資源獨佔。
2. 請求與保持：持有資源又請求新資源。
3. 非搶佔：資源不可強制剝奪。
4. 循環等待：進程環狀等待彼此持有的資源。

現場還原：倉庫揀貨系統卡死

• 症狀：大量工作線程 `SUSPENDED`，等待 `KEY` 鎖，`waitresource` 指向同一非叢集索引 `IX_PricingExpressProductCode_State`。
• 錯誤日誌關鍵片段 (`xp_readerrorlog`)：

-- 兩個進程互相持有 X 鎖、請求 U 鎖，形成經典死鎖
-- Process A: UPDATE ... WHERE State=3 AND OrderOut=4055484 AND PricingExpressProductCode IN ('UKNIR')
-- Process B: 同樣語句，不同參數值
-- 死鎖圖：A 持有 Key1 X 鎖 → 請求 Key2 U 鎖；B 持有 Key2 X 鎖 → 請求 Key1 U 鎖
-- Victim: Process B 被選中終止

根因分析三步走

1. 大事務放大鎖持有時間

開發確認：該 `UPDATE` 在一個包含 7~8 個操作的**大事務**中。事務越長，鎖釋放越晚，併發衝突概率指數級上升。

2. 低選擇性非叢集索引導致鍵鎖範圍過大

• 索引 `IX_PricingExpressProductCode_State` 包含欄位 `PricingExpressProductCode`、`State`。
• 該欄位**重複值極多** (大量 `UKNIR`、`UKRRM` 等)，索引選擇性極低。
• 執行計畫顯示：**Index Seek → Key Lookup → Clustered Index Update**，但因重複值多，實際掃描大量鍵範圍，產生大量 `U`/`X` 鍵鎖。

3. 索引毫無助益，反成幫兇

• 該索引未被任何查詢有效使用 (選擇性太低，優化器傾向掃描聚集索引)。
• 但 DML (`UPDATE`) 仍需維護此索引，**額外產生鎖、I/O、日誌**，純粹增加負擔。

解決方案：一鍵禁用索引

ALTER INDEX [IX_PricingExpressProductCode_State] ON [dbo].[Orderxxx] DISABLE;

• ✅ 死鎖即時消失。
• ✅ 倉庫作業恢復正常。
• ✅ 後續觀察：查詢效能無下降 (優化器原本就不走此索引)，寫入效能顯著提升。

事後改進清單

死鎖排查標準流程 (SOP)

1. 啟用 Trace Flag 1222/1204 或 Extended Events `xml_deadlock_report` 自動收集死鎖圖。
2. 分析 `victim`、 `resource-list`、 `process-list` 定位具體表、索引、SQL 文本。
3. 檢查執行計畫、索引統計、事務邊界、併發模式。
4. 制定最小變更修復 (禁用索引、加提示、拆事務、調隔離級別)。
5. 驗證修復並持續觀察 24-48h。

總結

**大事務 + 爛索引 = 死鎖溫床**。錯誤日誌是第一現場，`ALTER INDEX ... DISABLE` 是止血神器。長期治本靠「小事務、好索引、有監控、勤複盤」。