Home T-SQL query returns same result to multiple users when results should be unique
 I have a T-SQL query that returns a the next item ID from a work queue to a task worker. Prior to the return, the query updates the row with the workerID so it shouldn't be assigned more than once. This query works in most cases but lately we've discovered the same unique item ID is being returned to multiple task workers. Some environment information that may help: Overall volume is less than one hundred task workers. Task acquisition rarely happens more frequently than every 5 seconds and averages 10 seconds per work unit so the same worker makes between 6-10 acquisitions a minute. The table has ~1 million records and the overall query takes under 1 second to execute. The table PK is the itemID with a clustered index. I've not been able to replicate the issue outside of users running it in production so I suspect it's run-time related. This leaves me with a few questions: Is it possible that multiple queries initiated at nearly the same time would produce the same result before the update in C would exclude it from the query in A? If yes, what can be done about this? If not, where should I look next? Below is a summary of what the query looks like. A and B look redundant but this is legacy code I'm supporting. I need a plan before implementing changes. /* Definitions @workerID INT -- the task worker ,@workQueueID INT -- different work queues ,@itemID INT -- the unique task item */ /* Temp table to store next item */ DECLARE @temp TABLE( itemID INT, workQueueID INT, rownum INT ) /* A) Select row 1 into @temp */ INSERT INTO @temp SELECT * FROM ( SELECT A.itemID, A.workQueueID, ROW_NUMBER() OVER( PARTITION BY a.workQueueID ORDER BY a.taskPriority DESC ) 'RowNum' FROM workQueue a WHERE workerID IS NULL AND workQueueID = @workQueueID ) WHERE RowNum = 1 /* B) Set @itemID and @workQueueID */ SELECT TOP 1 @itemID = Q.itemID, @workQueueID = Q.workQueueID FROM @temp T JOIN workQueue Q ON Q.itemID = T.itemID WHERE Q.workerID IS NULL ORDER BY Q.workQueueID /* C) Assign workerID */ UPDATE workQueue SET workerID = @workerID, StartTime = GETDATE() WHERE itemID = @itemID /* D) Return xml string to task worker */ SELECT @itemID [itemID] FOR XML AUTO, ELEMENTS, TYPE