SQL Server进程队列竞态条件

Question

我有一个订单队列，由多个订单处理器通过存储过程访问。每个处理器都传递一个唯一的ID，用于锁定下一个20个订单供其独占使用。然后，存储过程将这些记录返回给订单处理器进行操作。

有时会出现多个处理器能够检索到相同的“OrderTable”记录的情况，在这种情况下，它们尝试同时对其进行操作。这最终会导致在进程后面抛出错误。

我的下一步行动是允许每个处理器获取所有可用订单，然后轮流处理处理器，但我希望仅使该部分代码线程安全，并允许处理器在需要时获取记录。

简而言之 - 你有任何想法为什么我会遇到这种竞争条件，以及如何解决这个问题。

BEGIN TRAN
    UPDATE  OrderTable WITH ( ROWLOCK )
    SET     ProcessorID = @PROCID
    WHERE   OrderID IN ( SELECT TOP ( 20 )
                                        OrderID
                                FROM    OrderTable WITH ( ROWLOCK )
                                WHERE   ProcessorID = 0)
COMMIT TRAN


SELECT  OrderID, ProcessorID, etc...
FROM    OrderTable
WHERE   ProcessorID = @PROCID

- William Edmondson

Answer 1

编辑：

我谷歌搜索了一下以检查我的答案："在SQL Server中使用READPAST和UPDLOCK处理数据队列"。我已经有好几年没读过和尝试过这个解决方案了。

如果使用READPAST提示，则会跳过已锁定的行。您已经使用了ROWLOCK，因此应避免锁定升级。您还需要UPDLOCK，就像我发现的那样。

因此，进程1锁定20行，进程2将接下来的20行，进程3将占用41至60行，等等。

更新也可以这样编写：

UPDATE TOP (20)
    foo
SET
    ProcessorID = @PROCID
FROM
    OrderTable foo WITH (ROWLOCK, READPAST, UPDLOCK)
WHERE
    ProcessorID = 0

刷新，2011年10月

如果您需要一次完成SELECT和UPDATE操作，可以使用OUTPUT子句更加优雅地完成此操作。

- gbn

---

有趣...我会试一试。 - William Edmondson

我知道这个问题很老了，但是在UPDATE语句中使用UPDLOCK提示是否会在读取要更新的行时强制使用更新锁（而不是共享锁）？换句话说，如果你不使用UPDLOCK，是否可能存在竞争条件并导致两个更新语句选择相同的行？ - Nelson Rothermel

是的，因为否则会有两个进程可以读取共享/读锁，并且READPAST不起作用。 - gbn

@gbn 为什么需要ROWLOCK？使用另外两个提示并使用带有输出的进程状态列不足以满足要求吗？（UPDATE SET Status = 'InProcess' OUTPUT Deleted.* WHERE Status = 'Pending'） - Uri Abramson

@UriAbramson 这只是一个猜测，但如果没有使用ROWLOCK，则进程1会更新20行，但锁定超过20行（例如40行）。进程2更新20行，但跳过40行。因此，将错过20行！ROWLOCK意味着仅锁定更新的行。 - Ian Warburton

Answer 2

您可以使用Service Broker。此外，您还可以使用sp_getapplock来序列化对行的访问-这将消除竞争条件：

“通过创建自己的锁（SQL中的Mutex）来协助并发” http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/30/855.aspx

- A-K