SELECT and UPDATE so that there is no overlapping threads

Question

Let's say that I have the following table:

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

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

So, if I run 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 select them SAME [ID] under any circumstances. I am concerned that the second thread ran between my SELECT and UPDATE statements, returning [ID]: 2 in both scenarios.

How can I guarantee that I do not select / update the same record twice, no matter how many threads act on the stored procedure? ALSO, keep in mind that the table CONSTANTLY added new rows, so I cannot lock the table!

Answer 1

If you mean locking the concurrency type of a secure queue, then use the prompts ROWLOCK, UPDLOCK, READPAST?

SQL Server Process Queue Queue Status

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

However, in one statement. sort of

 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; 

Answer 2

If you want it to be atomic, you have to block something, but that does not mean that you have to lock it for a long time. At first, I would try with some limited-scope transactions, but I would also be interested in trying an upgrade option that does SELECT at the same time:

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

You can see if there are any problems with concurrency - honestly, I don’t know without checking! Perhaps you need to add something like WITH (ROWLOCK) . Personally, however, I would like to keep it simple and try a serializable transaction.

Also note that this does not guarantee which record you will receive (first? Last?)

Answer 3

Make the transaction isolation level SERIALIZABLE and place an exclusive lock using the 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 XLOCK in the top range of keys and prevent matching requests from reading the top record.

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

Answer 4

put the select and update and select statement in the transaction and lock the table at the beginning of the transaction so that the outer threads wait. Best regards, Jordan