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)
#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
JOINs 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?
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.