為什麼我們的查詢突然返回不應該的行（使用 READPAST 和 UPDLOCK 選項）？

我們有一個看起來像這樣的工作表

CREATE TABLE [dbo].[Clearing](
   [Skey] [decimal](19, 0) IDENTITY(1,1) NOT NULL,
   [BsAcctId] [int] NULL,
   [Status] [varchar](20) NULL,
    CONSTRAINT [csPk_Clearing] PRIMARY KEY CLUSTERED ( [Skey] ASC ) 
) 

有這樣的覆蓋指數

CREATE NONCLUSTERED INDEX [IX_Status] ON [dbo].[Clearing]
(
   [Status] ASC
)
INCLUDE ( [Skey], [BsAcctId])

我們使用這個查詢來選擇下一個工作

select top (1) Skey, BsAcctId, Status from Clearing with ( readpast, updlock )
 where (Clearing.Status = 'NEW')
 order by Clearing.Skey

（真實表大約有 10 列，它們都在 index include() 子句和 select 列列表中。）

執行計劃非常簡單。它使用 IX_Status 進行索引查找，然後使用頂部運算符。由於索引是按 (status, skey) 排序的，因此計劃不需要排序。

該表位於 AlwaysOn 可用性組的數據庫中。該組有 2 個數據庫伺服器。（這是一個測試系統。）

通常這個表和查詢工作得很好。所以我們去應用 Windows 更新，並照常進行。

1. 將主節點故障轉移到輔助節點
2. 在前一個主節點上應用 Windows 更新
3. 故障轉移回原始主節點
4. 在輔助節點上應用 Windows 更新

在第二次故障轉移並且所有工作程序都獲得到新主節點的新連接後，查詢開始失敗，因為多個程序開始獲得相同的作業。

問題與負載有關。在執行 4 個工作程序的情況下，它沒有發生。但是對於 10 名工人，這種情況始終如一。

這是使用 SQL Server 2016 Enterprise。我們沒有啟用查詢儲存來查看執行計劃是否在某些時候很奇怪。

關於為什麼在兩次故障轉移後查詢會開始失敗的任何建議？

既然查詢只用了索引，不碰表，那麼UPDLOCK可靠嗎？

更新 1 - 我們更改了流程以在執行選擇後列出 spid 持有的鎖（使用 sp_lock @@spid）。對於同一個 skey，我們看到 IX_Status 索引上持有不同的 KEY 鎖 (indid=9)

KEY (aad9d6e672f9)  U
KEY (154698b9131c)  U

更新 2 - 使用索引提示沒有幫助。

更新 3 - 刪除查詢中的 order by 子句避免了該問題。但是我們有第二張表有同樣的問題，需要排序。

更新 4 - 我們的工作程序維護一個數據庫連接池。ODBC 不會告訴我們何時發生故障轉移，因此與舊主伺服器的連接會保留在池中，直到我們嘗試使用它們並且它們失敗為止。我們懷疑在故障轉移 DB1 -> DB2 -> DB1 之後，到 DB1 的舊連接可能不會像他們應該的那樣失敗。我們進行了更改，以在任何一個連接失去後關閉所有池連接，這似乎避免了該問題。（SQL Server ODBC 添加了一個“連接彈性”功能，加劇了這種懷疑。）

您有兩個不同的索引可以滿足該查詢。因此，執行兩個不同計劃的兩個查詢可以分別將一個鍵鎖定在不同的索引上。

嘗試在查詢中強制索引。

從不同的索引獲取同一行

正如大衛在他的回答中提到的那樣，如果您碰巧通過不同的索引訪問該行，您可以從多個會話中獲得同一行。

該UPDLOCK提示僅適用於特定的訪問方法。鎖定非聚集索引行U不會阻止另一個查詢獲取不同索引（包括聚集索引，如果有）U上的鎖定。

從不同的會話執行這兩個查詢（帶有索引提示）會導致返回同一行：

-- Session 1
BEGIN TRANSACTION;

SELECT TOP (1) Skey, BsAcctId, Status 
FROM dbo.Clearing WITH(READPAST, UPDLOCK, INDEX(2))
WHERE ([Status] = 'NEW')
ORDER BY Skey;

-- Session 2
BEGIN TRANSACTION;

SELECT TOP (1) Skey, BsAcctId, Status 
FROM dbo.Clearing WITH(READPAST, UPDLOCK, INDEX(1))
WHERE ([Status] = 'NEW')
ORDER BY Skey;

故障轉移後，將為傳入的查詢編譯新的執行計劃 - 所以這可以解釋為什麼您在故障轉移後最終會出現新的行為。正如大衛也說過，你可以強制索引來避免這個問題。

作為旁注，您還應該使用ROWLOCK提示，因為READPAST只能跳過以行粒度獲取的鎖。

由於並發而在同一會話中獲取不同的行

您還提到了這一點：

問題與負載有關。在執行 4 個工作程序的情況下，它沒有發生。但是對於 10 名工人，這種情況始終如一。

所以聽起來故障轉移並不是唯一改變的事情——你還增加了應用程序方面的並發性。

我嘗試用​​一些數據載入你的表/索引：

INSERT INTO dbo.Clearing
   ([Status])
SELECT TOP 100
   'NEW'
FROM master.dbo.spt_values;

INSERT INTO dbo.Clearing
   ([Status])
SELECT TOP 10000
   'COMPLETE'
FROM master.dbo.spt_values v1
   CROSS JOIN master.dbo.spt_values v2;

然後我用您的查詢載入了 SQL Query Stress，將其設置為每 100 毫秒一次在 10 個執行緒上執行：

在執行時，我會定期執行相同的查詢，並EXEC sp_lock @spid1 = @my_spid;在 SSMS 中添加到末尾。如果我SELECT在同一個會話中多次執行查詢（不回滾），我可以獲得該會話持有的多個鎖：

您可以使用%%lockres%%謂詞看到：

SELECT * FROM dbo.Clearing WITH (NOLOCK, INDEX(2)) WHERE %%lockres%% = '(36aaaeef6267)';
SELECT * FROM dbo.Clearing WITH (NOLOCK, INDEX(2)) WHERE %%lockres%% = '(84d6be32a10d)';

在沒有太多並發的情況下，如果您SELECT在會話中多次執行，通常會得到同一行。但是隨著其他查詢一直在獲取和釋放鎖，很容易獲得不同的行。因此，請確保您不依賴於SELECT兩次返回相同的 ID（我們沒有您工作負載的全部上下文，所以這只是推測/僅供參考）。

意外的鎖定行為

僅僅依靠特定的鎖可能是不安全的。考慮一下 Paul White 的這篇博文（或其他類似的博文）中描述的優化：缺少共享鎖的情況

該文章概述了受 X 鎖保護的行仍然可以被SELECT查詢讀取的情況：

SQL Server 包含一個優化，允許它在正確的情況下避免使用行級共享 (S) 鎖。具體來說，如果沒有共享鎖就沒有讀取未送出數據的風險，它可以跳過共享鎖。

相關閱讀：

• Remus Rusanu使用表作為隊列
• 當 READPAST 不讀過去時 Erik Darling

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