萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

記一次倉庫資料庫死鎖排查全過程:從錯誤日誌到索引停用解決

死鎖四必要條件回顧

  1. 互斥條件:資源不可共享,同一時間僅一進程持有。
  2. 請求與保持:持有資源的進程可再請求新資源。
  3. 非搶佔:已分配資源不可強制剝奪。
  4. 循環等待:進程組成環狀等待鏈,各自持有下一進程所需資源。

現場症狀

  • 倉庫揀貨系統卡死,大量 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`)。

經驗總結

  1. 錯誤日誌是第一現場:`xp_readerrorlog` / 系統健康擴充事件 (system_health) 必須會讀。
  2. 大交易是死鎖溫床:拆小交易、縮短持鎖時間、固定存取順序 (按 PK 順序)。
  3. 低選擇性索引害大於利:定期評估 `sys.dm_db_index_usage_stats`、`sys.dm_db_index_operational_stats`,果斷停用/刪除負效益索引。
  4. 監控預警**:部署死鎖擴充事件 Session + Agent 警報,第一時間感知。
copyright © 萬盛學電腦網 all rights reserved