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

gbn · Accepted Answer · 2011-11-01T21:03:36.313

64

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.

edited Nov 01 '11 at 21:03

answered Jun 02 '09 at 14:51

gbn

422,506
82
585
676

Interesting...I will give this a try – William Edmondson Jun 02 '09 at 15:40
Adding the additional hints really helped. No more duplicates. Thanks. – William Edmondson Jun 09 '09 at 20:10
I know this is old, but is the `UPDLOCK` hint in the `UPDATE` statement forcing update locks (instead of shared locks) while *reading* the rows to update? In other words, if you don't use `UPDLOCK`, is it possible for a race condition to exist and two update statements to select the same rows? – Nelson Rothermel Jun 08 '12 at 19:19
3

@NelsonRothermel: yes, because otherwise it is shared/read locks that 2 processes can read and READPAST won't work – gbn Jun 08 '12 at 20:14
@gbn Why is the ROWLOCK required? won't it be enough to use the 2 other hints and use a process status column with output? (UPDATE SET Status = 'InProcess' OUTPUT Deleted.* WHERE Status = 'Pending') – Uri Abramson Nov 23 '15 at 10:09
1

@UriAbramson This is a guess, but if ROWLOCK isn't used, then process 1 updates 20 rows but locks more than 20 rows (e.g. 40 rows). Process 2 updates 20 rows but skips 40 rows. So 20 rows will have been missed! ROWLOCK means only rows which are updated are locked. – Ian Warburton Sep 13 '16 at 15:37
gbn, thanks for this answer. We tested it with a large number or workers and it worked flawlessly with no deadlocks. – Onkel-j Nov 17 '16 at 19:10
I'm experiencing a weird situation in which, at a certain instance in time, there are hundreds of items in my table as a queue, yet majority of the users end up not being able to dequeue any items. I am sure that items outnumber users by a great margin. Users dequeue one item at a time, dequeue SP is just an update(ROWLOCK, READPAST, UPDLOCK) on a CTE with an OUTPUT clause. Is this behaviour expected? This doesn't occur constantly, but it happens enough times to make the users complain. – Selçuk Cihan Mar 14 '17 at 07:26
If i remove the "order by" on a column of a (NOLOCK) joined table in the CTE, then it works as expected. If i change "order by" to order on a column in the main table of CTE again it works fine. However, i do need the ordering by the joined table's column. – Selçuk Cihan Mar 14 '17 at 08:08

score 7 · Answer 2 · answered Jun 02 '09 at 15:21

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

SQL Server Process Queue Race Condition

2 Answers2

Linked