What is the proper Isolation Level to enable this multiple reader solution?

Question

I have a database table containing a queue of work items processed concurrently by multiple readers, with these requirements:

• Each item should be processed by one reader only.
• If a reader fails for any reason, the item should be returned to the queue for another reader to process.

Here is the pseudo code of a possible solution, each reader would do the following:

---

1) Read next item from queue and store it locally somewhere.

2) START TRANSACTION

3) Delete the item, to prevent other readers from seeing it. If deletion fails, it means another worker already pulled the item so go back to step 1.

4) Process the item (stored locally in step 1). This could be long running.

5) COMMIT TRANSACTION : item deletion is committed, go to step 1 to process next item

OR

6) ROLLBACK TRANSACTION (explicit rollback or reader failure): item deletion is rolled back and returns back to queue for another reader

---

My question is: what is the lowest isolation level I need to ensure that after step 3 (item deletion), other readers cannot see it? By lowest, I mean I want to maximize concurrency while maintaining integrity.

Note I'm using SQL Server 2005, if it matters (I think this should be product agnostic, right?)

Any other feedback on this approach in general is welcome.

Answer 1

An alternative method for handling this that I've used is:

1. Each work item has a column for State and another for ReaderID. State can be 'N'ew, 'A'ctive, or 'C'omplete. (I also use 'E'rror.)

2. Readers each have their own ID.

3. A Reader's first action is to UPDATE the first queued item with state 'N' with a single SQL statement that sets the state to 'A' and the ReaderID for itself.

4. The Reader then processes the work item, and when done, sets the state to 'C'.

For your purposes, you avoid issues with isolation by having the checkout action take place in one statement.

The SQL would be something like:

UPDATE queue
SET State = 'A', ReaderID = @myWorkerID
WHERE queueid = (SELECT MIN(queueid) FROM queue WHERE state = 'N')

Answer 2

If once you have removed the item from the 'Queue' mechanism, no prcess can access it, you don't need to keep the Transaction open while you actually process the item.

REPEATABLE READ isolation level:

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction. Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads. Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level. Use this option only when necessary.

Answer 3

If you use a rowlevel lock and the readpast statement in your query, you can select just those rows that are not locked.

Looks like this.

SELECT queue FROM queue with (rowlock, xlock, readpast) WHERE state = 'N'

I suggest that you read up on locking hints in SQL server which will greatly assist you.