Get "next" row from SQL Server database and flag it in single transaction

Question

I have a SQL Server table that I'm using as a queue, and it's being processed by a multi-threaded (and soon to be multi-server) application. I'd like a way for a process to claim the next row from the queue, flagging it as "in-process", without the possibility that multiple threads (or multiple servers) will claim the same row at the same time.

Is there a way to update a flag in a row and retrieve that row at the same time? I want something like this psuedocode, but ideally, without blocking 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's the best way to use T-SQL to accomplish this? I remember seeing a statement one time that would DELETE rows and, at the same time, deposit the DELETED rows into a temp table so you could do something else with them, but I can't for the life of me find it now.

Answer 1

Main thing is to use a combination of table hints as shown below, within a 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 will lock the next available row it finds that's available, preventing other processes from grabbing it.
ROWLOCK will ensure only the individual row is locked (I've never found it to be a problem not using this as I think it will only use a rowlock anyway, but safest to use it).
READPAST will prevent a process being blocked, waiting for another to finish.

Answer 2

You can use the OUTPUT clause

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