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