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

The most simplest method is to use row locking:

BEGIN TRAN

SELECT *
FROM authors
WITH (HOLDLOCK, ROWLOCK)
WHERE au_id = '274-80-9391'

/* Do all your stuff here while the record is locked */

COMMIT TRAN

But if you are accessing your data and then closing the connection, you won't be able to use this method.

How long will you be needing to lock the rows for? The best way might actually be as you say to place a counter on the rows you select (best done using OUTPUT clause within an UPDATE).

Answer 2

The best idea if you want to select records in this manner would be to use a counter in a separate table.

You really don't want to be locking rows on a production database exclusively for any great period of time, therefore I would recommend using a counter. This way only one of your processes would be able to grab that counter number at a time (as it will lock as it is being updated) which will give you the concurrency that you need.

If you need a hand writing the tables and procedures that will do this (simply and safely as you put it!) just ask.

Answer 3

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

Answer 4

EDIT: ahh, nevermind, you're working in a disconnected style. How about this:

UPDATE TOP (@n) QueueTable SET Locked = 1
OUTPUT INSERTED.Col1, INSERTED.Col2 INTO @this
WHERE Locked = 0

<do your stuff>

---

Perhaps you are looking for the READPAST hint?

<begin or save transaction>

INSERT INTO @this (Col1, Col2) 
SELECT TOP (@n) Col1, Col2 
FROM Table1 WITH (ROWLOCK, HOLDLOCK, READPAST)

<do your stuff>

<commit or rollback>