淺談 SQL Server 鎖機制與死鎖解決實戰

問題情境

多使用者並發環境下，兩筆交易同時 `INSERT` 同一資料表，接著各自 `SELECT COUNT(*)` 檢查唯一性條件。預設隔離等級下，`INSERT` 取得 `IX` 鎖 (意圖排他)，`SELECT` 需 `S` 鎖 (共享)。`S` 鎖與 `IX` 鎖相容，但**不同連接持有 `IX` 鎖時，彼此請求 `S` 鎖會被對方的 `IX` 鎖阻擋**，最終觸發死鎖，SQL Server 選擇犧牲其中一個交易回滾。

錯誤程式碼 (VB/ASP)

cn.BeginTrans
cn.Execute "INSERT INTO TableA ..."
Set rs = cn.Execute("SELECT COUNT(*) FROM TableA WHERE ...")  -- 這裡死鎖
If rs.RecordCount > 0 Then cn.RollbackTrans Else cn.CommitTrans

解決方案一：降低隔離等級至 READ UNCOMMITTED (髒讀)

cn.BeginTrans
cn.Execute "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
cn.Execute "INSERT INTO TableA ..."
Set rs = cn.Execute("SELECT COUNT(*) FROM TableA WHERE ...")
If rs.RecordCount > 0 Then cn.RollbackTrans Else cn.CommitTrans
cn.Execute "SET TRANSACTION ISOLATION LEVEL READ COMMITTED"  -- 還原

• 允許讀取未提交資料，`SELECT` 不申請 `S` 鎖，避免死鎖。
• 風險： 可能讀到臨時/回滾資料 (髒讀)，業務邏輯必須容忍。

解決方案二：調整鎖粒度與順序 (推薦)

• **統一存取順序**：所有交易依固定順序存取資料表 (如先 TableA 再 TableB)，消除循環等待條件。
• **使用 `UPDLOCK` / `HOLDLOCK` 提示**：在 `SELECT` 時主動申請更新鎖，序列化衝突交易。

  SELECT COUNT(*) FROM TableA WITH (UPDLOCK, HOLDLOCK) WHERE ...

• **縮短交易範圍**：僅在必要時開啟交易，盡早提交/回滾。
• **考慮 `ROWLOCK` 提示**：強制列級鎖減少衝突 (需評估鎖升級開銷)。

解決方案三：應用層邏輯重構

• 將唯一性檢查移至資料庫端：建立 `UNIQUE CONSTRAINT` 或 `UNIQUE INDEX`，直接 `INSERT` 捕捉錯誤碼 2627/2601，省去 `SELECT COUNT(*)`。
• 使用 `MERGE` / `INSERT ... WHERE NOT EXISTS` 單一陳述式完成檢查與插入 (SQL 2008+)。

死鎖監控與分析

-- 開啟死鎖追蹤旗標 (SQL 2000/2005)
DBCC TRACEON(1204, -1)  -- 輸出至錯誤記錄檔
DBCC TRACEON(1222, -1)  -- XML 格式 (SQL 2005+)

-- 或使用擴充事件 (SQL 2008+)
CREATE EVENT SESSION deadlock_monitor ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file (SET filename=N'deadlock.xel');

結語

死鎖本質是資源競爭與存取順序不一致。優先從**資料庫約束、單一陳述式、統一存取順序**著手解決；隔離等級調整僅作權宜之計，必須評估髒讀/不可重複讀風險。