萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 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
    '欄位重複
    cn.RollbackTrans
Else
    cn.CommitTrans
End If

鎖定衝突分析

  1. User1 執行 INSERT,SQL Server 授予 tableA IX 鎖 (意圖排他鎖)。
  2. User2 執行 INSERT,也獲得 IX 鎖 (IX 鎖相容)。
  3. User1 執行 SELECT COUNT(*),需申請 S 鎖 (共享鎖),但 tableA 已有 User2 的 IX 鎖,S 鎖與 IX 鎖不相容 → User1 等待。
  4. User2 同樣執行 SELECT 申請 S 鎖,發生死鎖,SQL Server 選擇犧牲較晚申請 IX 鎖的連線 (User2) 並回滾交易。

結果:其中一筆交易被強制回滾,應用程式視為失敗。

解決方案一:設定交易隔離等級為 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
End If
cn.Execute "SET TRANSACTION ISOLATION LEVEL READ COMMITTED"

允許讀取未提交資料 (髒讀),避免 S 鎖等待。需權衡資料一致性需求。

解決方案二:INSERT 加上 TABLOCK 提示

cn.BeginTrans
cn.Execute "INSERT INTO tableA WITH (TABLOCK) ..."
Set rs = cn.Execute("SELECT COUNT(*) FROM tableA WHERE ...")
...

TABLOCK 使 INSERT 直接取得表級 X 鎖 (排他鎖),阻塞後續連線的 INSERT 與 SELECT,序列化操作,避免死鎖。但降低並發度。

解決方案三:引入虛擬鎖定表

cn.BeginTrans
cn.Execute "UPDATE tmpLockTable SET FieldLock = 1"  -- 取得表級 X 鎖
cn.Execute "INSERT INTO tableA WITH (TABLOCK) ..."
Set rs = cn.Execute("SELECT COUNT(*) FROM tableA WHERE ...")
...

先更新一張專用鎖定表,強制序列化進入關鍵區段,再執行原本邏輯。同樣犧牲並發換取一致性。

建議

  • 方案一效能最好,但允許髒讀。
  • 方案二、三較嚴格,適用於不可容忍髒讀的業務。
  • 務必在所有相關預存程序/程式碼保持一致的鎖定順序,預防死鎖。
copyright © 萬盛學電腦網 all rights reserved