從表返回的重複記錄沒有重複

我有一個儲存過程，它查詢一個繁忙的隊列表，用於在我們的系統中分配工作。有問題的表在 WorkID 上有一個主鍵，並且沒有重複項。

查詢的簡化版本是：

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表在該過程的前面填充。

正如我所說，WorkTable它很忙，有時在執行此查詢時，我懷疑其中一條記錄正在從一組過濾器移動到另一組過濾器WHERE。具體來說，當有人開始處理某個項目並且W.InProgress從 0 更改為 1 時會發生這種情況。發生這種情況時，當我嘗試將主鍵添加到此查詢插入的臨時表中時，我會遇到重複鍵衝突。

我在出錯時生成的查詢計劃中已經確認沒有並行度，隔離級別為READ COMMITTED，源表中沒有重複記錄。您還可以看到這裡沒有JOINs 或其他方式來獲取笛卡爾積。

這是匿名查詢計劃：

問題是，是什麼導致了重複，我怎樣才能讓它停止？

我認為READ COMMITTED應該在這里工作，我需要鎖定。我幾乎可以肯定，當InProgress我查詢時記錄上的位發生變化時，就會發生欺騙。我知道這一點是因為該表儲存了該更改的時間，並且它在我查詢並得到錯誤的幾毫秒內。

有一些棘手的情況可能會導致從索引中讀取同一行兩次，即使在隔離級別下也是READ COMMITTED如此。

您的查詢不符合分配順序掃描的條件，因此儲存引擎將按照聚集鍵的順序從表中讀取數據。

對於您的表，您擁有InProgress作為聚集鍵的第一列。當您掃描表時，您可能會遇到行鎖或頁鎖。如果您在掃描開始附近讀取了一行，請釋放對它的鎖定，該行將更新為InProgress從 0 更改為 1，然後在不同的頁面中再次讀取該行，然後您可以WorkID從查詢中看到重複的值.

有很多解決方法。您可以插入堆中並簡單地刪除重複值。您可以DISTINCT在查詢中添加一個。您還可以啟用行版本控制隔離級別，以提供數據庫已送出狀態的穩定視圖，無論是在事務開始時（快照隔離）還是在語句開始時（讀取送出的快照隔離） ）。

也許添加鎖定提示或更改表的結構是合適的。對於一個相當有趣的解決方案（可能不適合生產），您可以嘗試向後讀取索引。這可以通過一個多餘TOP的和一個ORDER BY. 下面是一個非常簡單的展示來說明這一點：

CREATE TABLE #WorkTable (
   InProgress TINYINT NOT NULL,
   WorkID INT NOT NULL
   , PRIMARY KEY (InProgress, WorkID)
);

INSERT INTO #WorkTable WITH (TABLOCK)
SELECT (RN - 1) / 5000, RN
FROM
(
   SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
   FROM master..spt_values t1
   CROSS JOIN master..spt_values t2
) t
OPTION (MAXDOP 1);

以下查詢具有 Ordered:false 屬性，但仍會以聚集鍵順序讀取數據：

SELECT WorkId
FROM #WorkTable;

但是，以下查詢將以反向集群順序讀取數據：

SELECT TOP (9223372036854775807) WorkId
FROM #WorkTable
ORDER BY InProgress DESC, WorkId DESC;

我們可以通過查看掃描屬性看到這一點：

對於您的表，這意味著如果更新一行以InProgress從 0 更改為 1，則它出現兩次的可能性要小得多。它可能根本不會出現，這可能是一個不同的問題。

引用自：https://dba.stackexchange.com/questions/185897