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.
@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(
/* A) Select row 1 into @temp */
INSERT INTO @temp
PARTITION BY a.workQueueID
ORDER BY a.taskPriority DESC
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 */
SET workerID = @workerID,
StartTime = GETDATE()
WHERE itemID = @itemID
/* D) Return xml string to task worker */
SELECT @itemID [itemID]
FOR XML AUTO, ELEMENTS, TYPE