如何防止 SELECT 上的分區列儲存死鎖

我在 SQL Server 2016 中有三個聚集列儲存索引 (CCI) 表。所有這些 CCI 都在相同的分區方案中，基於租戶 ID。最近，並且不一致的是，我在從連接到這些表的簡單選擇語句上遇到了死鎖。死鎖的範例查詢：

SELECT  TOP 33 r.tenantid
FROM    Table_r r
       INNER JOIN Table_cm cm ON r.MyKey=cm.MyKey 
       INNER JOIN Table_pe pe ON r.MyKey=pe.MyKey 
WHERE   r.TenantId = 69
       AND pe.TenantId = 69
       AND cm.TenantId = 69

錯誤資訊：

事務（程序 ID 56）在通用等待對象資源上與另一個程序死鎖，並已被選為死鎖犧牲品。重新執行事務。

線索：

• 如果查詢使用除 CCI 之外的另一個索引，則它不會死鎖。
• 如果我刪除三個租戶 ID 過濾器中的兩個，它不會死鎖。
• 如果我 SELECT top 32 或更低，它不會死鎖。
• 如果我添加 OPTION (MAXDOP 1) 它不會死鎖。
• 我可以在我的加擾的 PROD 副本、PROD READ-ONLY Secondary 和 PROD 本身中重現這個。
• 我無法在 DEV 或 INT 中重現此行為。
• 如果我將 WITH(NOLOCK) 添加到所有 3 個表連接，它仍然會死鎖
• 查詢本身會死鎖。當沒有其他活動程序時，它將死鎖。
• 沒有並行性的查詢計劃不會死鎖

死鎖xml在這裡

我們的產品版本：

Microsoft SQL Server 2016 (SP2-CU5) (KB4475776) - 13.0.5264.1 (X64) 2019 年 1 月 10 日 18:51:38 版權所有 (c) Microsoft Corporation Enterprise Edition (64-bit) o​​n Windows Server 2012 R2 Standard 6.3 (Build 9600) :)（管理程序）

如何防止此查詢出現死鎖？

由於您使用的是 SQL Server 2016，因此值得一提的是，至少有一個針對列儲存索引的並行死鎖的公共錯誤修復：

修復：在 SQL Server 2016 和 2017 中對聚集列儲存索引執行並行查詢時發生死鎖

（感謝Denis Rubashkin最初提供連結）

這是作為 SP1 CU7 的一部分發布的。如果你達不到那個CU，你應該試一試。此修復也將包含在 SP2（任何 CU）中。

一般來說，修復查詢內並行死鎖的兩種方法：

• 避免並行性（通過調整查詢使其不並行，使用MAXDOP提示等） - 這在Thomas Costers的另一個答案中有所介紹
• 將最新的服務包/累積更新應用到 SQL Server

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