SQL Server 进程队列竞争条件 [英] SQL Server Process Queue Race Condition

问题描述

我有一个订单队列，多个订单处理程序通过存储过程访问该队列.每个处理器传入一个唯一的 ID，用于锁定接下来的 20 个订单供自己使用.然后，存储过程将这些记录返回给订单处理程序以进行处理.

I have an order queue that is accessed by multiple order processors through a stored procedure. Each processor passes in a unique ID which is used to lock the next 20 orders for its own use. The stored procedure then returns these records to the order processor to be acted upon.

在某些情况下，多个处理器能够检索相同的OrderTable"记录，此时它们会尝试同时对其进行操作.这最终会导致在流程后期抛出错误.

There are cases where multiple processors are able to retrieve the same 'OrderTable' record at which point they try to simultaneously operate on it. This ultimately results in errors being thrown later in the process.

我的下一步行动是允许每个处理器获取所有可用的订单，然后循环处理处理器，但我希望简单地使这部分代码线程安全，并允许处理器随时获取记录.

My next course of action is to allow each processor grab all available orders and just round robin the processors but I was hoping to simply make this section of code thread safe and allow the processors to grab records whenever they like.

如此明确 - 知道为什么我会遇到这种竞争条件以及我如何解决这个问题.

So Explicitly - Any idea why I am experiencing this race condition and how I can solve the problem.

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

推荐答案

我用谷歌搜索我的答案:"Processing Data Queues in SQL Server with READPAST and UPDLOCK.我已经有好几年没有阅读并使用此解决方案了.

I googled to check my answer: "Processing Data Queues in SQL Server with READPAST and UPDLOCK". It's been years since I read about and played with this solution.

原文:

如果您使用 READPAST 提示，则会跳过锁定的行.您已经使用过 ROWLOCK，因此您应该避免锁升级.我发现你还需要 UPDLOCK.

If you use the READPAST hint, then locked rows are skipped. You've used ROWLOCK so you should avoid lock escalation. You also need UPDLOCK, as I found out.

所以进程 1 锁定 20 行，进程 2 将获取接下来的 20 行，进程 3 获取行 41 到 60 等

So process 1 locks 20 rows, process 2 will take the next 20, process 3 takes rows 41 to 60, etc

更新也可以这样写:

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

刷新，2011 年 10 月

Refresh, Oct 2011

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

This can be done more elegantly with the OUTPUT clause if you need a SELECT and an UPDATE in one go.

这篇关于SQL Server 进程队列竞争条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！