I have a stored procedure that queries a busy queue table that is used to distribute work in our system. The table in question has a primary key on WorkID and no duplicates.
A simplified version of the query is:
INSERT INTO #TempWorkIDs (WorkID)
SELECT
W.WorkID
FROM
dbo.WorkTable W
WHERE
(@bool_param = 0 AND
((W.InProgress = 0
AND ISNULL(W.UserID, -1) != @userid_param
AND (@bool_filtered = 0
OR W.TypeID IN (SELECT TypeID FROM #Types AS t)))
OR
(@bool_param = 1
AND W.InProgress = 1
AND W.UserID != @userid_param)
OR
(@Auto_Param = 0
AND W.UserID = @userid_param)))
OR
(@bool_param = 1 AND W.UserID = @userid_param)
OPTION
(RECOMPILE)
The #Types
table is populated earlier in the procedure.
As I said, WorkTable
is busy, and sometimes while this query is running I SUSPECT one of the records is moving from one set of filters in the WHERE
to another. Specifically, this happens when someone starts to work on an item, and the W.InProgress
changes from 0 to 1. When this happens I get a duplicate key violation when I try to add a primary key to the temp table this query is inserting into.
I have confirmed in the query plan generated when the error occurs that there is no parallelism, the isolation level is READ COMMITTED
, and there are no duplicate records in the source table. You can also see there are no JOIN
s or other way to get cartesian products in here.
This is the anonymized query plan:
The question is, what is causing the duplicates and how can I get it to stop?
I think READ COMMITTED
should work here, I need locking. I am almost positive the dupes occur when the InProgress
bit on a record changes while I'm querying. I know this because the table stores the time of that change and it's within milliseconds of when I query and get the error.