Return unlocked rows in a "select top n" query

Question

I need to have a MsSql database table and another 8 (identical) processes accessing the same table in parallel - making a select top n, processing those n rows, and updating a column of those rows. The problem is that I need to select and process each row just once. This means that if one process got to the database and selected the top n rows, when the second process comes it should find those rows locked and select the rows from n to 2*n rows, and so on...

Is it possible to put a lock on some rows when you select them, and when someone requests top n rows which are locked to return the next rows, and not to wait for the locked ones? Seems like a long shot, but...

Another thing I was thinking - maybe not so elegant but sounds simple and safe, is to have in the database a counter for the instances which made selects on that table. The first instance that comes will increment the counter and select top n, the next one will increment the counter and select rows from n*(i-1) to n*i, and so on...

Does this sound like a good ideea? Do you have any better suggestions? Any thought is highly appreciated!

Thanks for your time.

Answer 1

Here's a sample I blogged about a while ago:

The READPAST hint is what ensures multiple processes don't block each other when polling for records to process. Plus, in this example I have a bit field to physically "lock" a record - could be a datetime if needed.

DECLARE @NextId INTEGER
BEGIN TRANSACTION

-- Find next available item available
SELECT TOP 1 @NextId = ID
FROM QueueTable WITH (UPDLOCK, READPAST)
WHERE IsBeingProcessed = 0
ORDER BY ID ASC

-- If found, flag it to prevent being picked up again
IF (@NextId IS NOT NULL)
    BEGIN
        UPDATE QueueTable
        SET IsBeingProcessed = 1
        WHERE ID = @NextId
    END

COMMIT TRANSACTION

-- Now return the queue item, if we have one
IF (@NextId IS NOT NULL)
    SELECT * FROM QueueTable WHERE ID = @NextId