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.
Best Answer
There are some tricky scenarios which can result in the same row being read twice from an index, even under the
READ COMMITTED
isolation level.Your query does not qualify for an allocation order scan, so the storage engine will read the data from the table in the order of the clustered key.
For your table, you have the
InProgress
as the first column of the clustered key. It's likely that you're getting row or page locks as you scan through the table. If you read a row near the start of the scan, release the lock on it, that row is updated such thatInProgress
changes from 0 to 1, and then the row is read again in a different page then you could see duplicateWorkID
values from your query.There are lots of workarounds. You could insert into a heap and simply remove duplicate values. You could add a
DISTINCT
to the query. You could also enable a row-versioning isolation level, to provide a stable view of the committed state of the database, either as at the beginning of the transaction (snapshot isolation), or as at the beginning of the statement (read committed snapshot isolation).Perhaps it's appropriate to add locking hints or to change the structure of the table. For a rather fun solution (probably not appropriate for production), you could try reading the index backwards. This can be done with a superfluous
TOP
along with anORDER BY
. Below is a very simple demo to illustrate the point:The following query has the Ordered:false property but it'll still read the data in clustered key order:
However, the following query will read the data in reverse clustered order:
We can see this by looking at the scan properties:
For your table, this means that if a row is updated such that
InProgress
changes from 0 to 1 it will be far less likely that it'll show up twice. It may not show up at all which could be a different problem.