How to prevent multi threaded application to read this same Sql Server record twice

Question

I am working on a system that uses multiple threads to read, process and then update database records. Threads run in parallel and try to pick records by calling Sql Server stored procedure.

They call this stored procedure looking for unprocessed records multiple times per second and sometimes pick this same record up.

I try to prevent this happening this way:

UPDATE  dbo.GameData
SET     Exported = @Now,
        ExportExpires = @Expire,
        ExportSession = @ExportSession
OUTPUT  Inserted.ID INTO @ExportedIDs
WHERE ID IN (   SELECT TOP(@ArraySize) GD.ID 
                FROM        dbo.GameData GD 
                WHERE       GD.Exported IS NULL 
                ORDER BY    GD.ID ASC)

The idea here is to set a record as exported first using an UPDATE with OUTPUT (remembering record id), so no other thread can pick it up again. When record is set as exported, then I can do some extra calculations and pass the data to the external system hoping that no other thread will pick this same record again in the mean time. Since the UPDATE that has in mind to secure the record first.

Unfortunately it doesn't seem to be working and the application sometimes pick same record twice anyway.

How to prevent it?

Kind regards Mariusz

Answer 1

I think you should be able to do this atomically using a common table expression. (I'm not 100% certain about this, and I haven't tested, so you'll need to verify that it works for you in your situation.)

;WITH cte AS
(
    SELECT TOP(@ArrayCount)
        ID, Exported, ExportExpires, ExportSession
    FROM dbo.GameData WITH (READPAST)
    WHERE Exported IS NULL
    ORDER BY ID
)
UPDATE cte
SET Exported = @Now,
    ExportExpires = @Expire,
    ExportSession = @ExportSession
OUTPUT INSERTED.ID INTO @ExportedIDs