單機單用戶環境下較少遇到鎖定問題,但多用戶並發網路環境中,鎖定與死鎖會造成嚴重困擾。以下以實際案例分析並提供三種解決方案。
兩用戶同時新增資料,程式流程:
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
INSERT,SQL Server 授予 tableA IX 鎖 (意圖排他鎖)。INSERT,也獲得 IX 鎖 (IX 鎖相容)。SELECT COUNT(*),需申請 S 鎖 (共享鎖),但 tableA 已有 User2 的 IX 鎖,S 鎖與 IX 鎖不相容 → User1 等待。SELECT 申請 S 鎖,發生死鎖,SQL Server 選擇犧牲較晚申請 IX 鎖的連線 (User2) 並回滾交易。結果:其中一筆交易被強制回滾,應用程式視為失敗。
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 鎖等待。需權衡資料一致性需求。
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 ...")
...
先更新一張專用鎖定表,強制序列化進入關鍵區段,再執行原本邏輯。同樣犧牲並發換取一致性。