FIFO queue table for multiple workers in SQL Server

I was attempting to answer the following stackoverflow question:

• What SQL Server 2005/2008 locking approach should I use to process individual table rows in multiple server application instances?

After posting a somewhat naive answer, I figured I'd put my money where my mouth was and actually test the scenario I was suggesting, to be sure I wasn't sending the OP off on a wild goose chase. Well, it's turned out to be much harder than I thought (no surprise there to anyone, I'm sure).

Here's what I've tried and thought about:

-
First I tried a TOP 1 UPDATE with an ORDER BY inside a derived table, using ROWLOCK, READPAST. This yielded deadlocks and also processed items out of order. It must be as close to FIFO as possible, barring errors that require attempting to process the same row more than once.

-
I then tried selecting the desired next QueueID into a variable, using various combinations of READPAST, UPDLOCK, HOLDLOCK, and ROWLOCK to exclusively preserve the row for update by that session. All of the variations I tried suffered from the same issues as before as well as, for certain combinations with READPAST, complaining:


You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

This was confusing because it was READ COMMITTED. I have run into this before and it is frustrating.

-
Since I started writing this question, Remus Rusani posted a new answer to the question. I read his linked article and see that he is using destructive reads, since he said in his answer that it "is not realistically possible to hold on to locks for the duration of the web calls." After reading what his article says regarding hot spots and pages requiring locking to do any update or delete, I fear that even if I were able to work out the correct locks to do what I'm looking for, it would not be scalable and could not handle massive concurrency.

Right now I'm not sure where to go. Is it true that maintaining locks while t

You need exactly 3 lock hints

• READPAST

• UPDLOCK

• ROWLOCK

I answered this previously on SO: https://stackoverflow.com/questions/939831/sql-server-process-queue-race-condition/940001#940001

As Remus says, using service broker is nicer but these hints do work

Your error about isolation level usually means replication or NOLOCK is involved.

StackExchange Database Administrators Q#20399, answer score: 13