Get the “next” row from a SQL Server database and mark it in a separate transaction

Question

I have a SQL Server table that I use as a queue, and it is being processed by a multi-threaded (and will soon be multi-server) application. I would like the process to require that the next line from the queue be placed as an "in-process", without the possibility of simultaneously submitting multiple threads (or multiple servers) to the same line.

Is there a way to update a flag in a string and get this string at the same time? I want something like this psuedocode, but ideally, without locking the whole table:

Block the table to prevent others from reading
Grab the next ID in the queue
Update the row of that item with a "claimed" flag (or whatever)
Release the lock and let other threads repeat the process

What is the best way to use T-SQL for this? I remember one time I noticed an expression that would delete rows and at the same time put DELETED rows in a temporary table so that you can do something else with them, but I can’t find it for life now.

Answer 1

You can use the OUTPUT clause

UPDATE myTable SET flag = 1
WHERE
id = 1
AND 
flag <> 1
OUTPUT DELETED.id

Answer 2

The main thing is to use a combination of table hints, as shown below, as part of the transaction.

DECLARE @NextId INTEGER
BEGIN TRANSACTION

SELECT TOP 1 @NextId = ID
FROM QueueTable WITH (UPDLOCK, ROWLOCK, READPAST)
WHERE BeingProcessed = 0
ORDER BY ID ASC

IF (@NextId IS NOT NULL)
    BEGIN
        UPDATE QueueTable
        SET BeingProcessed = 1
        WHERE ID = @NextID
    END

COMMIT TRANSACTION

IF (@NextId IS NOT NULL)     
    SELECT * FROM QueueTable WHERE ID = @NextId

UPDLOCK , , .
ROWLOCK , ( , , , , , ).
READPAST , .