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

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

Dan
1#
Dan Published in 2018-02-12 23:07:06Z

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
Razvan Socol
2#
Razvan Socol Reply to 2018-02-13 20:02:24Z

A) One solution is to wrap everything in a transaction and use a UPDLOCK hint the first time you access the workQueue table.

B) Another solution is to make the UPDATE in the same statement where you choose the item, for example:

/*
CREATE TABLE workQueue (
    workQueueID INT NOT NULL,
    itemID INT UNIQUE,
    workerID INT NULL,
    StartTime DATETIME NULL,
    taskPriority INT NOT NULL
)

CREATE INDEX IX_workQueue ON dbo.workQueue (taskPriority DESC) WHERE workerID IS NULL

INSERT INTO workQueue VALUES (1,1,NULL,NULL,2)
INSERT INTO workQueue VALUES (1,2,NULL,NULL,1)
INSERT INTO workQueue VALUES (2,3,NULL,NULL,4)
INSERT INTO workQueue VALUES (2,4,NULL,NULL,3)
*/

DECLARE @workerID INT=7 -- the task worker
,@workQueueID INT=1 -- different work queues
,@itemID INT -- the unique task item

UPDATE x
SET workerID = @workerID, StartTime = GETDATE(),
    @itemID=itemID, 
FROM (
    SELECT TOP 1 A.*
    FROM workQueue a
    WHERE workerID IS NULL
    AND workQueueID=@workQueueID
    ORDER BY a.taskPriority DESC
) x

SELECT  @itemID [itemID]
FOR XML PATH, ELEMENTS, TYPE

It's not clear to me how does the worker relate to the different work queues: any worker can take an item from any work queue? If there is a single item in each work queue (but with different priorities), is there any rule which item gets chosen? Due to the PARTITION BY in your code, it seems there is no rule (any item may be chosen).

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO