Row locks - manually using them

Question

I basically have an application that has, say 5 threads, which each read from a table. The query is a simple SELECT TOP 1 * from the table, but I want to enforce a lock so that the next thread will select the next record from the table and not the locked one. When the application has finished it's task, it will update the locked record and release the lock and repeat the process again. Is this possible?

Answer 1

The kind of approach I'd recommend is to have a field in the record along the lines of that indicates whether the record is being processed or not. Then implement a "read next from the queue" sproc that does the following, to ensure no 2 processes pick up the same record:

BEGIN TRANSACTION

-- Find the next available record that's not already being processed.
-- The combination of UPDLOCK and READPAST hints makes sure 2 processes don't 
-- grab the same record, and that processes don't block each other.
SELECT TOP 1 @ID = ID
FROM YourTable WITH (UPDLOCK, READPAST)
WHERE BeingProcessed = 0

-- If we've found a record, set it's status to "being processed"
IF (@ID IS NOT NULL)
    UPDATE YourTable SET BeingProcessed = 1 WHERE ID = @ID

COMMIT TRANSACTION

-- Finally return the record we've picked up
IF (@ID IS NOT NULL)
    SELECT * FROM YourTable WHERE ID = @ID

For more info on these table hints, see MSDN