死鎖四必要條件回顧
- 互斥條件:資源不可共享,同一時間僅一進程持有。
- 請求與保持:持有資源的進程可再請求新資源。
- 非搶佔:已分配資源不可強制剝奪。
- 循環等待:進程組成環狀等待鏈,各自持有下一進程所需資源。
現場症狀
- 倉庫揀貨系統卡死,大量 Session 阻塞。
- 錯誤日誌 (`xp_readerrorlog`) 捕獲到經典死鎖圖:兩個 SPID (153、454) 在同一非叢集索引 `IX_PricingExpressProductCode_State` 上互相持有 X 鎖、請求 U 鎖,形成循環。
- 死鎖受害者:SPID 454 (process5c26988)。
- 衝突 SQL 皆為同一 `UPDATE` 陳述式,條件含 `State`、`OrderOut`、`PricingExpressProductCode IN (...)`。
深入分析
1. 同一陳述式為何死鎖?
開發確認該 `UPDATE` 在一個**大交易**中 (7-8 個步驟)。交易越長,持有鎖時間越久,死鎖機率指數級上升。
2. 索引特性
- 非叢集索引 `IX_PricingExpressProductCode_State` (Key: `PricingExpressProductCode`, `State`)。
- 索引選擇性極低:`PricingExpressProductCode` 僅有極少數 distinct 值 (如 'UKNIR', 'UKRRM', 'UKRLE'),大量重複。
- 執行計畫顯示 **Index Scan** (而非 Seek),掃描大量頁面,持有大量鎖,增加衝突面。
3. 鎖升級與順序
大交易中,先前步驟可能已在叢集索引 (PK) 上持有 X 鎖,後續 `UPDATE` 需在非叢集索引上取得 U/X 鎖;另一 Session 反向順序 (先非叢集後叢集),形成經典**鎖順序反向**死鎖。
解決方案:停用低選擇性索引
經評估,該索引對查詢效能貢獻極低 (掃描成本接近表掃描),且導致嚴重死鎖。決定**停用索引**:
ALTER INDEX [IX_PricingExpressProductCode_State] ON [dbo].[Orderxxx] DISABLE;
結果驗證
- 停用後,**死鎖徹底消失**,倉庫作業恢復流暢。
- 查詢計畫轉為叢集索引掃描/表掃描,因資料量與並發度在可接受範圍,無效能退化。
- 後續規劃:重設計索引 (加入高選擇性前導欄位)、拆分大交易、調整隔離等級 (如 `READ COMMITTED SNAPSHOT`)。
經驗總結
- 錯誤日誌是第一現場:`xp_readerrorlog` / 系統健康擴充事件 (system_health) 必須會讀。
- 大交易是死鎖溫床:拆小交易、縮短持鎖時間、固定存取順序 (按 PK 順序)。
- 低選擇性索引害大於利:定期評估 `sys.dm_db_index_usage_stats`、`sys.dm_db_index_operational_stats`,果斷停用/刪除負效益索引。
- 監控預警**:部署死鎖擴充事件 Session + Agent 警報,第一時間感知。