如何避免表鎖升級？

我的任務是更新生產表中的 500 萬行，而無需長時間鎖定整個表

因此，我使用了多次幫助我的方法 - 一次更新前 (N) 行，塊之間的間隔為 1-N 秒

這次從一次更新頂部 (1000) 行開始，監視擴展事件會話以查找lock_escalation程序中的事件

lock_escalation在每次更新操作期間出現，所以我開始將每塊1000 -> 500 -> 200 -> 100 -> 50行的行數降低到 1

之前（不使用此表，並且對於刪除操作 - 不更新），將行數降低到 200 或 100，有助於擺脫lock_escalation事件

但是這一次，即使每 1 次更新操作有 1 行，表lock_escalation仍然顯示。每個更新操作的持續時間大致相同，無論一次是 1 行還是 1000 行

在我的情況下如何擺脫表鎖升級？

@@TRANCOUNT 為零

擴展事件：

程式碼：

set nocount on

declare 
   @ChunkSize              int = 1000,                         -- count rows to remove in 1 chunk 
   @TimeBetweenChunks      char(8) = '00:00:01',               -- interval between chunks
   
   @Start                  datetime,
   @End                    datetime,
   @Diff                   int,
   
   @MessageText            varchar(500),
   
   @counter                int = 1,
   @RowCount               int = 1,
   @TotalRowsToUpdate      bigint,
   @TotalRowsLeft          bigint
   


-- total row count to update
set @TotalRowsToUpdate = (select count(*)
                           from [Table1]
                               join [Table2] on
                                   btid = tBtID
                           where   btStatusID = 81)


set @TotalRowsLeft = @TotalRowsToUpdate
set @MessageText = 'Total Rows to Update = ' + cast(@TotalRowsLeft as varchar) raiserror (@MessageText,0,1) with nowait
print ''



-- begin cycle
while @RowCount > 0 begin

   set @Start = getdate()

   -- update packages
   update top (@ChunkSize) bti
       set btstatusid = 154,
           btType = 1
   from [Table1] bti
       join [Table2] on
           btid = tBtID
   where   btStatusID = 81
   

   set @RowCount = @@ROWCOUNT

   -- measure time
   set @End = getdate()
   set @Diff = datediff(ms,@Start,@End)

   set @TotalRowsLeft = @TotalRowsLeft - @RowCount
   set @MessageText = cast(@counter as varchar) + ' - Updated ' + cast(@RowCount as varchar) + ' rows in ' + cast(@Diff as varchar) + ' milliseconds - total ' + cast(@TotalRowsLeft as varchar) + ' rows left...'

   -- print progress message
   raiserror (@MessageText,0,1) with nowait


   set @counter += 1

   WAITFOR DELAY @TimeBetweenChunks

end

計劃：

https://www.brentozar.com/pastetheplan/?id=SyozGWMLw

如果我們查看實際計劃，目前查詢正在從表中讀取太多數據以進行更新。這是來自索引搜尋BoxTrackInfo：

btid這是對掃描出來的每一行的索引查找BlueTrackEvents。btStatusID在檢查行是否符合更新條件時獲取更新鎖。只有 1,401 行符合更新條件，但在此過程中會使用更多鎖 - 導致鎖升級到表級別。

你真的想要一個不同的計劃形狀 - 尋找BoxTrackInfo表btStatusID然後加入到BlueTrackEvents，這應該會獲得更少的鎖。為此，添加這樣的索引應該會有所幫助：

CREATE NONCLUSTERED INDEX IX_btStatusID 
ON dbo.BoxTrackInfo (btStatusID)
INCLUDE (btType);

這應該更有效地定位符合條件的行，希望允許更新在沒有鎖升級的情況下完成。

---

附帶說明一下，目前的執行計劃驗證了btStatusID使用合併半連接的外鍵約束：

在您的情況下，這可能沒什麼大不了的，因為LBoxTrackStatus表中只有 267 行。如果該表較大，您可以考慮在查詢中添加LOOP JOIN或FAST 1提示，以便獲得嵌套循環 FK 驗證。有關詳細資訊，請參閱此文章：

為什麼我在 INSERT 上遇到快照隔離問題？

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