SELECT and UPDATE table so there is no overlap of Threads

c#, concurrency, sql, sql-server-2005, t-sql

Solution

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 TRAN -- 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;

Problem

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!

Original source

Related problems