不返回記錄的查詢掛在鎖上

我們注意到這種奇怪的情況，即不返回記錄的查詢將在指定時等待鎖定WITH(UPDLOCK)。

查詢很簡單

SELECT primaryKey FROM someTable WITH(UPDLOCK) 
WHERE columA = 'A' 
     AND columnB BETWEEN 6 AND 8 
     AND columnC != 'C' ;

執行此查詢的系統使用隔離級別read uncommitted，我們無法更改。

有沒有一種方法，例如提示，不會讓這個等待，但如果它會產生結果，仍然會鎖定記錄？

我注意到索引使用在其中起作用，因為其他查詢沒有這種行為。但遺憾的是，定義索引也不在可能的範圍內。

我想出的唯一解決方案是IF EXISTS在執行實際查詢之前在其中拋出一個，但由於競爭條件，它仍然可能發生。

類似的問題已在此處得到解答：為什麼 UPDLOCK 會導致 SELECTs 掛起（鎖定）？

如果沒有索引來定位要鎖定的行，所有測試的行都會被鎖定，並且對符合條件的行的鎖定會一直保持到事務完成。

但我不認為那裡提供的答案適用於我的問題版本，因為查詢不會返回行來“限定”。

遺憾的是，我無法訪問該系統。我們只能對某些表執行查詢。

目的是鎖定和讀取記錄（如果存在）並最終更新它。如果失去，請插入。

UPDLOCK之所以使用它，是因為在我的理解中，當鎖定未送出的讀取隔離時，這是正確的。

該表有一個主鍵，但未在 where 子句中使用。

沒有直接的方法可以做你想做的事：在讀取未送出的隔離中讀取行，只U對與給定謂詞匹配的行加鎖。

UPDLOCK工作原理

當您指定 時UPDLOCK，SQL ServerU在行或頁級別獲取鎖。如果需要一個表鎖，SQL Server 會X取而代之的是一個鎖。

無論是否找到任何匹配的行，表和頁鎖都會保留到事務結束。使用行級鎖時，SQL Server 總是在測試之前U對行進行鎖，以查看它是否匹配條件。

在測試一行的特殊情況下，發現與同一數據訪問運算符中的謂詞不匹配（例如掃描或查找），目前行上的鎖在獲取下一行上的鎖U之前被釋放。U否則，U鎖會像往常一樣保持到事務結束。

這是對 SQL Server 如何實現UPDLOCK提示的描述。我確實理解文件中並不清楚，或者您希望它如何表現。然而，事情就是這樣。

解決方法

沒有完美的方法來實現您想要的，但有幾個部分解決方案。

第一個是WITH (UPDLOCK, READPAST, ROWLOCK)：

SELECT 
   ST.PrimaryKey
FROM dbo.SomeTable AS ST 
   WITH (UPDLOCK, READPAST, ROWLOCK) 
WHERE
   ST.ColumnA = 'A' 
   AND ST.ColumnB BETWEEN 6 AND 8 
   AND ST.ColumnC != 'C';

如果要測試的行具有不兼容的行級鎖，這將跳過阻塞。如果在頁或表級別持有不兼容的鎖，查詢仍將阻塞。

---

第二種解決方法是在會話隔離級別讀取，然後U通過加入主鍵來鎖定單獨的數據訪問：

SELECT 
   CA.PrimaryKey 
FROM dbo.SomeTable AS ST -- No hints here
CROSS APPLY 
(
   SELECT TOP (1) 
       ST2.PrimaryKey
   FROM dbo.SomeTable AS ST2 
       WITH (UPDLOCK, FORCESEEK)
   WHERE
       ST2.PrimaryKey = ST.PrimaryKey
) AS CA
WHERE
   ST.ColumnA = 'A' 
   AND ST.ColumnB BETWEEN 6 AND 8 
   AND ST.ColumnC != 'C';

重要的是要完全遵循那裡的模式，包括頂部。如果正確實施，此方法在實踐中應該非常可靠。

上插

我不得不提到這個問題似乎是一個 upsert 模式。UPDLOCK單獨服用是不夠的。

您還需要圍繞 upsert 的每個部分的事務，以及SERIALIZABLE確保任何不存在的行在執行插入之前繼續不存在的提示。更新鎖只能對存在的行進行。請參閱Michael J Swart 的SQL Server UPSERT 模式和反模式。

當然，當您使用SERIALIZABLE語義時，您的阻塞機會可能會大大增加。

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