SQL Table use as a queue does not work correctly

I am using a table as a queue to process records. I use this query and sometimes the same ID is returned when it should never happen. I have reviewed pretty much everything and cannot find the reason why it returns the same ID for different worker.

In this case I have 8 workers retrieving the next batch to process and once in a while I get an error that the batch was already processed. My understanding is that the ID returned by the query should never be the same for a different worker running the same query.

Please advise!

BEGIN TRANSACTION
UPDATE TOP(1) 
    tblBatch WITH (ROWLOCK, UPDLOCK, READPAST)
SET 
    BatchStatusID = 2
OUTPUT 
    inserted.BatchID
FROM 
    tblBatch
WHERE 
    tblBatch.BatchID IN (
        SELECT 
            tblBatch.BatchID
        FROM 
            tblBatch 
                INNER JOIN tblBatchName ON tblBatch.BatchID = tblBatchName.BatchID
        WHERE
            (tblBatch.BatchStatusID = 1)
        GROUP BY
            tblBatch.BatchID
        HAVING 
            COUNT(tblBatchName.BatchID) >= 1 AND COUNT(tblBatchName.BatchID) <= 2147483647
    )
COMMIT

I added the ROWLOCK in addition to the UPDLOCK and READPAST and use a transaction. This made no difference.

Your primary issue is that the inner subquery referencing tblBatch also needs the locking hints WITH (ROWLOCK, UPDLOCK, READPAST).

You also need to make sure that it's going to lock directly on the correct rows. You need an index as follows

tblBatch (BatchStatusID, BatchID)

But then it looks like you would probably be better of using a window function and updating the subquery directly.

Also the join doesn't seem to be doing anything so you could probably remove it.

UPDATE TOP (1) 
    b
SET 
    BatchStatusID = 2
OUTPUT 
    inserted.BatchID
FROM 
    (
        SELECT 
            b.*,
            COUNT(*) OVER (PARTITION BY b.BatchID) AS Count
        FROM
            tblBatch b WITH (ROWLOCK, UPDLOCK, READPAST)
        WHERE
            b.BatchStatusID = 1
    ) b
    WHERE
        b.count BETWEEN 1 AND 2147483647;

But then it makes no sense anyway. Assuming BatchId is the primary key, the count will always be exactly 1. And COUNT can never be zero anyway, and cannot be more than the max of an int value, so the count check is pointless.

So you might as well just forget about it and using a single level update.

UPDATE TOP(1) 
    b
SET 
    BatchStatusID = 2
OUTPUT 
    inserted.BatchID
FROM
    tblBatch b WITH (ROWLOCK, UPDLOCK, READPAST)
WHERE
    tblBatch.BatchStatusID = 1;

Note that an explicit transaction is not necessary: a single statement is atomic anyway.