SQL Server Process Queue Race Condition
- by William Edmondson
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
