Home T-SQL query returns same result to multiple users when results should be unique
Reply: 0

T-SQL query returns same result to multiple users when results should be unique

user2413
1#
user2413 Published in May 21, 2018, 6:47 pm

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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.318973 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO