SQL Server Process Queue Race Condition

Question

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.

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

Answer 1

Edit:

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.

Original:

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.

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

The update can also be written like this:

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

Refresh, Oct 2011

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

Answer 2

You can use Service Broker. Also you can use sp_getapplock to serialize access to your rows - that will eliminate race conditions:

"Assisting Concurrency by creating your own Locks (Mutexs in SQL) " http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/30/855.aspx