萬盛學電腦網

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

SQL Server 死鎖原理與六大避免策略:從四必要條件到實戰最佳實務

一、死鎖四個必要條件 (Coffman Conditions)

  1. 互斥:資源獨佔,同一時間僅一進程持有
  2. 請求與保持:持有資源時可申請新資源
  3. 非搶佔:資源不可被強制剝奪,僅持有者釋放
  4. 循環等待:進程環狀等待彼此持有的資源

四條件同時滿足 → 死鎖。打破任一條件即可避免。

二、六大避免死鎖實戰策略

1. 統一存取順序 (打破循環等待)

所有並發交易按相同順序存取資料表/資源。

-- 好:所有交易先鎖 Supplier 再鎖 Part
-- 壞:交易A 鎖 Supplier→Part,交易B 鎖 Part→Supplier → 死鎖風險

實務:將所有資料修改封裝在儲存程序中,強制統一存取順序。

2. 避免交易中使用者互動 (縮短持鎖時間)

交易中等待使用者輸入 (如彈窗確認) 會長時間持有鎖,阻塞其他交易。
→ 業務邏輯前置驗證,交易僅包含純資料庫操作。

3. 保持交易簡短、單一批次 (縮短持鎖時間)

  • 拆分大交易為小批次
  • 避免交易中跨網路呼叫、複雜運算
  • 使用 SET NOCOUNT ON 減少網路往返

4. 使用較低隔離等級 (減少共享鎖持有)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- 預設
-- 或
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 髒讀,但無共享鎖
-- 或啟用資料庫層級
ALTER DATABASE [DB] SET READ_COMMITTED_SNAPSHOT ON; -- RCSI,讀不阻寫、寫不阻讀

RCSI (Read Committed Snapshot Isolation) 是現代高並發系統首選,透過版本存儲實現讀寫不阻塞。

5. 使用繫結連接

同一應用程式的多個連接共享鎖空間,避免自我阻塞。
sp_getbindtoken / sp_bindsession (較少用,現代多用連接池 + MARS)。

6. SELECT 加 WITH (NOLOCK) / READUNCOMMITTED (解決讀寫死鎖)

SELECT * FROM Orders WITH (NOLOCK) WHERE CustomerID = 123;
  • 不加共享鎖 (或加 Sch-S 穩定性鎖),允許髒讀
  • 適用:報表、統計、可容忍髒讀的查詢
  • 禁用:財務核對、庫存扣減、任何需精確一致性的場景

三、死鎖偵測與處理機制

  • SQL Server 鎖監視器每 ~5 秒巡檢一次死鎖環
  • 犧牲者選擇:DEADLOCK_PRIORITY 低者、執行成本低者 (較少記錄回滾)
  • 錯誤 1205 回傳客戶端,交易回滾
  • 追蹤旗標 1204/1222、擴充事件 xml_deadlock_report 分析死鎖圖

四、最佳實務總結清單

  1. 所有 DML 封裝在儲存程序,強制存取順序
  2. 啟用 READ_COMMITTED_SNAPSHOT (需評估 tempdb 壓力)
  3. 交易簡短、單批次、無使用者互動
  4. 報表查詢加 NOLOCK (明確註釋風險)
  5. 索引優化:減少掃描範圍 → 減少鎖升級 → 降低死鎖機率
  6. 監控 sys.dm_os_wait_stats (LCK_M_*)、死鎖圖、設定告警

總結

死鎖本質是並發與一致性的權衡。透過架構層面統一存取順序、縮短交易、選擇適當隔離等級、善用 RCSI/NOLOCK,可將死鎖機率壓至極低。遇到死鎖必須分析死鎖圖,精準修正存取模式,而非單純重試。

copyright © 萬盛學電腦網 all rights reserved