阻止一行在存储过程执行时被读取

Question

我有许多.NET进程从SQL Server 2008数据库表中读取消息并逐个处理它们。我实现了一个简单的存储过程来“锁定”正在被任何一个进程读取的行，以避免任意两个进程处理同一行。

BEGIN TRAN

SELECT @status = status FROM t WHERE t.id = @id
IF @status = 'L'
BEGIN
    -- Already locked by another process. Skip this
    RETURN 0
END
ELSE
    UPDATE t SET status = 'L' WHERE id = @id
    RETURN 1
END

COMMIT

然而，这种方法有缺陷：有时一行数据会被“锁定”并被处理两次。我怀疑存在并发问题：两个进程在一个更新状态之前读取了该状态。

我认为可以通过某种方式实现读取块（即使事务块读取），但我不确定如何做到这一点。有人能帮忙吗？

非常感谢

Ryan

- Ryan

---

我应该补充说明这些进程是独立的 .net 进程，而不是多线程单个执行，因此无法在 c# 编程层面解决这个问题。 - Ryan

Answer 1

为什么不直接用整个东西来替换呢？

UPDATE t SET status = 'L' WHERE id = @id and status <> 'L'
RETURN @@ROWCOUNT

这样可以避免访问2个表格，而且不必保持锁定状态的时间更长。

- Martin Smith

---

我更喜欢你的答案，非常好！ - dcp

哦！当然。你的回答非常优雅，谢谢。不过，我选择了dcp的答案，因为它解决了我关于行锁定的具体问题。 - Ryan

Answer 2

你尝试过使用以下方法吗：

SELECT @status = status FROM t (UPDLOCK) WHERE t.id = @id

请参考这个链接了解更多细节。

你可能忽略了一件事情，那就是SELECT语句通常不会锁定行。因此，如果一个进程执行了SELECT但尚未执行UPDATE，然后另一个进程执行了SELECT，那么它将返回该行，因为您没有对其进行锁定。

使用UPDLOCK，您可以通过SELECT语句锁定该行，并防止其他进程在第一个进程提交事务之前再次获取它，这是您想要的行为。

编辑 当然，像Martin建议的那样使用一个语句是最简单的方法，完全避免了处理锁定问题的困扰。

- dcp

---

+1 实际上，使用 UPDLOCK 可能可以设计一种解决方案，锁定您处理的实际记录，而无需手动锁定策略 ;) - StuartLC