SELECT and UPDATE table so there is no overlap of Threads

Question

Hi,

Say I have the following table:

ID|Read
-------
 1|true
 2|false
 3|false
 4|false

... and I need to read the smallest ID, that has [Read] == false; plus, update that I have now read it.

So if i execute my Stored Procedure dbo.getMinID, it will return ID: 2, and update [Read] -> true.

CREATE PROCEDURE [dbo].[getMinID]
(
  @QueryID INT OUTPUT 
)
BEGIN
  SELECT TOP 1 @QueryID = [ID] from Table
  UPDATE Table SET [Read] = 1 WHERE [ID] = @QueryID 
END

The problem is that I have ten (10) asynchronous Threads executing dbo.getMinID, at the same time, and I CANNOT have them select the SAME [ID] under any circumstances. I am worried that a second thread my execute between my SELECT and UPDATE statement, thus returning [ID]: 2 in both scenarios.

How can I ensure that I do not select/update the same record twice, no matter how many threads are acting upon the Stored Procedure? ALSO, please keep in mind that the table CONSTANTLY has new rows added, so I cannot lock the table!

Answer 1

Hi, put the select and update and the select statement in a transaction and in the start of the transaction lock the table so the outher threads will wait. Best Regards, Iordan

Answer 2

If you want it to be atomic, you must lock something, but that doesn't mean you have to lock it for long. I would first try with some tightly scoped transactions, but I'd also be interested to try the update variant that does a SELECT at the same time :

UPDATE TOP (1) [foo]
SET [read] = 1
OUTPUT INSERTED.id
WHERE [read] = 0

You could see if that has any concurrency issues - in honesty, I don't know without checking! You may need to add something like WITH (ROWLOCK). Personally, though, I'd want to keep it simple and try a serializable transaction.

Also note that this doesn't guarantee which record you will get (first? last?)

Answer 3

Make your transaction isolation level SERIALIZABLE and place an exclusive lock with your SELECT command:

SELECT TOP 1 @QueryID = [ID] from Table WITH (XLOCK) ORDER BY id DESC
UPDATE Table SET [Read] = 1 WHERE [ID] = @QueryID 

This will place an XLOCK on the top key range and will prevent concurrent queries from reading the top record.

This way, no transactions will ever get the same record.

Answer 4

If you mean a concurrency safe queue type locking, then use ROWLOCK, UPDLOCK, READPAST hints?

SQL Server Process Queue Race Condition

BEGIN TRAN

SELECT TOP 1 @QueryID = [ID] from Table WITH (ROWLOCK, UPDLOCK, READPAST)
UPDATE Table SET [Read] = 1 WHERE [ID] = @QueryID 

COMMIT TRAM

However, in one statement. something like

WITH T AS
(
    --ORDER BY with TOP , or perhaps MIN is better?
    SELECT TOP 1 [Read], [ID] from Table
    WITH (ROWLOCK, UPDLOCK, READPAST) ORDER BY [Read]
)
UPDATE
    T
SET
    [Read] = 1;