刪除查詢會阻塞其他並發事務

DELETE我的生產 SQL 伺服器中的查詢導致死鎖。我知道 DELETE 導致了這種情況，因為我檢查了擴展事件並檢查了死鎖 XML，發現這個 DELETE 是阻塞的，這最終會導致死鎖。

DELETE兩個表tb1 和tb2 上也是如此。tb1 的主鍵在 tb2 中作為外鍵可用，CASCADE DELETE在 tb2 外鍵中使用。

我什Allow Page Locks至FALSE在 tb1 和 tb2 中進行了聚集索引，但仍然沒有運氣。

READ COMMITTED SNAPSHOT在我嘗試我的數據庫之前，我想嘗試所有其他選項。

任何幫助將不勝感激。

附加資訊：

使用READ COMMITTED SNAPSHOT也是一個挑戰，因為它涉及風險。

• 我的數據庫很大（~1.6 TB），RAM 為 128 GB。
• 使用 SQL Server 2016

這是 XDL

<deadlock>
<victim-list>
 <victimProcess id="process257a65d6ca8" />
</victim-list>
<process-list>
 <process id="process257a65d6ca8" taskpriority="0" logused="6024" waitresource="KEY: 6:72057794784329728 (bb7a6e52eae1)" waittime="4485" ownerId="45816472292" transactionname="user_transaction" lasttranstarted="2019-01-08T11:30:06.837" XDES="0x24883ec2a70" lockMode="RangeS-U" schedulerid="26" kpid="39320" status="suspended" spid="217" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-01-08T11:30:06.883" lastbatchcompleted="2019-01-08T11:30:06.870" lastattention="1900-01-01T00:00:00.870" clientapp="iyeTek.Services.MSP.AICS.Host.MSP" hostname="EAICS-APP-01" hostpid="5356" loginname="pasl_msp" isolationlevel="read committed (2)" xactid="45816472292" currentdb="6" currentdbname="iyeTek.Services.MSP.AICS" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
  <executionStack>
   <frame procname="adhoc" line="1" stmtstart="42" stmtend="116" sqlhandle="0x0200000032f3ca31d40d7b71d6e79a1044a3a86617a5be1e0000000000000000000000000000000000000000">
unknown    </frame>
   <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
  </executionStack>
  <inputbuf>
(@0 uniqueidentifier)DELETE [dbo].[Form]
WHERE ([Id] = @0)   </inputbuf>
 </process>
 <process id="process252a472e8c8" taskpriority="0" logused="7552" waitresource="KEY: 6:72057794784329728 (1a150171029d)" waittime="706" ownerId="45816472158" transactionname="user_transaction" lasttranstarted="2019-01-08T11:30:06.733" XDES="0x23eb032a430" lockMode="RangeS-U" schedulerid="30" kpid="66604" status="suspended" spid="283" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-01-08T11:30:06.827" lastbatchcompleted="2019-01-08T11:30:06.803" lastattention="1900-01-01T00:00:00.803" clientapp="iyeTek.Services.MSP.AICS.Host.MSP" hostname="EAICS-APP-03" hostpid="6216" loginname="pasl_msp" isolationlevel="read committed (2)" xactid="45816472158" currentdb="6" currentdbname="iyeTek.Services.MSP.AICS" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
  <executionStack>
   <frame procname="adhoc" line="1" stmtstart="42" stmtend="116" sqlhandle="0x0200000032f3ca31d40d7b71d6e79a1044a3a86617a5be1e0000000000000000000000000000000000000000">
unknown    </frame>
   <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
  </executionStack>
  <inputbuf>
(@0 uniqueidentifier)DELETE [dbo].[Form]
WHERE ([Id] = @0)   </inputbuf>
 </process>
 <process id="process245373d2ca8" taskpriority="0" logused="77556" waitresource="KEY: 6:72057794784329728 (bb7a6e52eae1)" waittime="3216" ownerId="45816432209" transactionname="user_transaction" lasttranstarted="2019-01-08T11:30:00.563" XDES="0x255954753d0" lockMode="RangeS-U" schedulerid="33" kpid="19100" status="suspended" spid="381" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-01-08T11:30:08.130" lastbatchcompleted="2019-01-08T11:30:08.120" lastattention="1900-01-01T00:00:00.120" clientapp="iyeTek.Services.MSP.AICS.Host.MSP" hostname="EAICS-APP-03" hostpid="6216" loginname="pasl_msp" isolationlevel="read committed (2)" xactid="45816432209" currentdb="6" currentdbname="iyeTek.Services.MSP.AICS" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
  <executionStack>
   <frame procname="adhoc" line="1" stmtstart="42" stmtend="116" sqlhandle="0x0200000032f3ca31d40d7b71d6e79a1044a3a86617a5be1e0000000000000000000000000000000000000000">
unknown    </frame>
   <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
  </executionStack>
  <inputbuf>
(@0 uniqueidentifier)DELETE [dbo].[Form]
WHERE ([Id] = @0)   </inputbuf>
 </process>
</process-list>
<resource-list>
 <keylock hobtid="72057794784329728" dbid="6" objectname="iyeTek.Services.MSP.AICS.dbo.FormNarrative" indexname="PK_FormNarrative" id="lock24c95634480" mode="RangeS-U" associatedObjectId="72057794784329728">
  <owner-list>
   <owner id="process252a472e8c8" mode="RangeS-U" />
  </owner-list>
  <waiter-list>
   <waiter id="process257a65d6ca8" mode="RangeS-U" requestType="wait" />
  </waiter-list>
 </keylock>
 <keylock hobtid="72057794784329728" dbid="6" objectname="iyeTek.Services.MSP.AICS.dbo.FormNarrative" indexname="PK_FormNarrative" id="lock251d3ea0c00" mode="RangeX-X" associatedObjectId="72057794784329728">
  <owner-list>
   <owner id="process245373d2ca8" mode="RangeX-X" />
  </owner-list>
  <waiter-list>
   <waiter id="process252a472e8c8" mode="RangeS-U" requestType="wait" />
  </waiter-list>
 </keylock>
 <keylock hobtid="72057794784329728" dbid="6" objectname="iyeTek.Services.MSP.AICS.dbo.FormNarrative" indexname="PK_FormNarrative" id="lock24c95634480" mode="RangeS-U" associatedObjectId="72057794784329728">
  <owner-list>
   <owner id="process257a65d6ca8" mode="RangeS-U" requestType="wait" />
  </owner-list>
  <waiter-list>
   <waiter id="process245373d2ca8" mode="RangeS-U" requestType="wait" />
  </waiter-list>
 </keylock>
</resource-list>
</deadlock>

如果您使用級聯刪除，那麼是的，它們會自動升級為可序列化鎖，如死鎖圖的 lockMode=“RangeS-U” 部分所示，這幾乎阻塞了子表上的其他所有人。更改讀取送出的快照不會改變任何內容。

您需要更改程式碼以首先對子表進行刪除，然後是父表，而不是依賴級聯刪除。在桌面上執行良好但大規模崩潰的那些很好的例子之一。

在我進行以下更改後，刪除現在沒有被阻止。

• 從父表和子表中刪除了對索引的頁級鎖定。
• 子表上只有一個聚集索引。在父表的外鍵上添加了非聚集索引。
• 由於移除了頁級鎖定，reorganize index 不會對這些索引起作用，因此在維護計劃中添加了 index Rebuild。

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