深入解析 SQL Server 鎖機制與死鎖解決方案

在單用戶或單機環境下操作資料庫時，我們鮮少需關注資料表鎖定與死鎖問題。然而，一旦進入多用戶並行的網路環境，鎖定機制便成為影響系統穩定性與效能的關鍵因素。若缺乏深入分析，輕則導致效能低落，重則引發系統癱瘓。本文將透過實戰案例，剖析 SQL Server 鎖升級與死鎖產生的根本原因，並提供三種切實可行的解決策略。

案例還原：雙用戶同時新增資料引發的死鎖

開發過程中，兩位用戶近乎同步執行新增資料的交易程序，原始碼邏輯如下：

cn.BeginTrans
cn.Execute "insert into tableA ....."
Set rs = cn.Execute("select count(*) from tableA where ...")
If rs.RecordCount > 0 Then
    '表A 的字段A不能重複
    cn.RollbackTrans
Else
    cn.CommitTrans
End If

鎖衝突的技術剖析

當 SQL Server 執行 INSERT 命令且未指定鎖提示時，預設會對資料表申請 IX 鎖 (Intent Exclusive Lock)。IX 鎖之間相容，因此第一個用戶與第二個用戶的連線皆能成功取得 TableA 的 IX 鎖。

問題發生在後續的 SELECT COUNT(*) 查詢。該查詢需申請 S 鎖 (Shared Lock)。假設用戶 A 率先執行查詢，由於 TableA 已被用戶 B 持有 IX 鎖，S 鎖與 IX 鎖不相容，用戶 A 進入等待狀態。緊接著用戶 B 也執行查詢，同樣申請 S 鎖，此時資料庫偵測到循環等待，判定為死鎖，選擇犧牲較晚申請 IX 鎖的連線（用戶 B）並回滾其交易。對應用層而言，這是一筆莫名其妙的交易失敗。

三大解決方案深度解析

方案一：調整交易隔離等級至 READ UNCOMMITTED

在交易開始後立即設定隔離等級，允許「髒讀」，使 SELECT 不需申請 S 鎖，直接讀取未提交的資料，繞過鎖衝突。

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
End If
cn.Execute "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" ' 恢復預設等級

優點：實作簡單，無需修改 SQL 結構。
缺點：可能讀到稍後被回滾的「髒資料」，業務邏輯必須容忍此風險。

方案二：使用 TABLOCK 提示強制表級鎖

在 INSERT 陳述式中加入 WITH (TABLOCK)，強制 SQL Server 直接申請 X 鎖 (Exclusive Lock)。X 鎖具有排他性，第二個用戶的 INSERT 將在第一階段即被阻塞，無法取得 IX 鎖，從根源上杜絕了後續的死鎖循環。

cn.BeginTrans
cn.Execute "insert into tableA with (tablock) ....."
Set rs = cn.Execute("select count(*) from tableA where ...")
If rs.RecordCount > 0 Then
    cn.RollbackTrans
Else
    cn.CommitTrans
End If

優點：邏輯嚴謹，確保序列化存取。
缺點：並發度大幅下降，高併發場景下易成效能瓶頸。

方案三：引入專用鎖資源表實現應用層互斥

建立一張專用資料表 tmpLockTable，在交易伊始先更新該表的一筆資料，以取得該資源的 X 鎖，作為應用層的「全域互斥體」。後續對 TableA 的操作即在受保護的序列化區段中進行。

cn.BeginTrans
cn.Execute "update tmpLockTable set FieldLock=1" ' 取得應用層互斥鎖
cn.Execute "insert into tableA ....."
Set rs = cn.Execute("select count(*) from tableA where ...")
If rs.RecordCount > 0 Then
    cn.RollbackTrans
Else
    cn.CommitTrans
End If

優點：鎖粒度可控，不影響 TableA 正常並發；邏輯清晰，易於維護。
缺點：需額外維護鎖表；若交易執行時間過長，仍會阻塞其他請求。

最佳實踐建議

• 優先評估方案三：對於「檢查後插入」此類非原子性業務，應用層互斥是架構上最乾淨的解法。
• 避免長交易：無論採用何種方案，務必縮短交易範圍，盡早提交或回滾。
• 考慮原子性寫法：若資料庫版本支援，嘗試使用 MERGE 或 INSERT ... WHERE NOT EXISTS 等單一陳述式完成「不重複則插入」，從根本上消除檢查與插入之間的時間窗口。

掌握鎖相容性矩陣、理解隔離等級對鎖行為的影響、善用鎖提示與應用層協調，是資料庫開發者從「寫出能跑的 SQL」進階為「打造高併發健壯系統」的必修課。