Access前端搭配SQL Server后端，如何实现tblLock表的排他编辑锁定？

问题背景 #

在Access项目中通过tblLock表（含锁定区域、用户、时间戳字段）实现伪锁定时，仅靠表记录判断会存在竞态风险：如用户A、B同时为同一区域添加锁记录，均检测到无锁后成功写入。纯Access本地环境下，通过Set rs = CurrentDb.OpenRecordset(stSQL, dbOpenDynaset, dbDenyWrite)（示例SQL：SELECT * FROM tblLock WHERE [Area] = 'TestArea'）实现排他编辑可消除竞态，但切换至SQL Server后端后，修改为Set rs = CurrentDb.OpenRecordset(stSQL, dbOpenDynaset, dbSeeChanges + dbDenyWrite)会触发错误：“ODBC--Cannot lock all records.” 需适配SQL Server的锁机制，优先保留并发只读权限，必要时可放弃该权限。

方案1：优先保留并发只读访问（推荐） #

SQL Server对DAO的dbDenyWrite参数支持与Access本地不同，可通过行级更新锁+事务原子性实现排他编辑，同时允许其他用户只读访问：

代码示例 #

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim stSQL As String
Dim strArea As String
Dim strUser As String

strArea = "TestArea"
strUser = CurrentUser() ' 或自定义用户标识

Set db = CurrentDb()
db.BeginTrans ' 开启事务，保障操作原子性

' 用UPDLOCK锁定目标行（存在则锁，不存在不锁），READPAST跳过其他用户锁定的行，不阻塞只读访问
stSQL = "SELECT * FROM tblLock WHERE [Area] = '" & strArea & "' WITH (UPDLOCK, READPAST)"
Set rs = db.OpenRecordset(stSQL, dbOpenDynaset, dbSeeChanges)

If rs.EOF Then
    ' 无锁记录，插入新锁
    rs.AddNew
    rs!Area = strArea
    rs!User = strUser
    rs!Timestamp = Now()
    rs.Update
    MsgBox "锁定成功"
Else
    ' 校验锁归属或过期状态
    If rs!User = strUser Then
        MsgBox "您已锁定该区域"
    Else
        MsgBox "区域已被" & rs!User & "锁定"
    End If
End If

rs.Close
db.CommitTrans ' 提交事务
Set rs = Nothing
Set db = Nothing

核心逻辑 #

• WITH (UPDLOCK)：为查询到的行添加更新锁，其他用户可读取但无法修改/锁定该行
• WITH (READPAST)：跳过被其他用户锁定的行，避免阻塞只读请求
• 事务包裹检测+插入/更新操作，彻底消除竞态

方案2：必要时放弃并发只读访问（简化写法） #

若无需保留并发只读权限，可采用以下两种简化方式：

写法A：快照式排他锁 #

Dim rs As DAO.Recordset
Dim stSQL As String

stSQL = "SELECT * FROM tblLock WHERE [Area] = 'TestArea'"
' 用dbOpenSnapshot+dbDenyWrite强制排他访问，其他用户无法读写该行
Set rs = CurrentDb.OpenRecordset(stSQL, dbOpenSnapshot, dbDenyWrite + dbSeeChanges)

' 后续检测、插入/更新逻辑同方案1，需配合事务

写法B：直接执行SQL+事务 #

跳过DAO记录集，利用SQL Server原生语法实现原子操作，效率更高：

Dim db As DAO.Database
Dim strArea As String
Dim strUser As String

strArea = "TestArea"
strUser = CurrentUser()

Set db = CurrentDb()
db.BeginTrans

' 先尝试刷新自身已存在的锁
db.Execute "UPDATE tblLock SET [Timestamp] = Now() WHERE [Area] = '" & strArea & "' AND [User] = '" & strUser & "'", dbFailOnError

If db.RecordsAffected = 0 Then
    ' 更新失败，尝试插入新锁（IF NOT EXISTS确保原子性）
    db.Execute "IF NOT EXISTS (SELECT * FROM tblLock WHERE [Area] = '" & strArea & "') " & _
               "INSERT INTO tblLock ([Area], [User], [Timestamp]) VALUES ('" & strArea & "', '" & strUser & "', Now())", dbFailOnError
    If db.RecordsAffected = 0 Then
        MsgBox "区域已被其他用户锁定"
        db.Rollback
    Else
        MsgBox "锁定成功"
        db.CommitTrans
    End If
Else
    MsgBox "锁定已刷新"
    db.CommitTrans
End If

Set db = Nothing

关键注意事项 #

1. 必须使用事务：所有检测、插入/更新操作需在同一事务中执行，否则竞态问题无法根除
2. 锁过期清理：建议定期清理过期锁（如超过30分钟未刷新的记录），避免死锁
3. 参数化查询：示例中使用字符串拼接，实际项目需改用参数化查询防止SQL注入

内容的提问来源于stack exchange，提问作者Phil Freihofner