如何避免表锁升级？

Question

我有一项任务要更新生产表中的500万行，而不需要将整个表锁定很长时间

因此，我使用了以前帮助过我的方法--每次更新顶部(N)行，在块之间间隔1-N秒。

这一次从每次更新top (1000)行开始，监视进程中lock_escalation事件的扩展事件会话。

lock_escalation在每次更新操作中都会出现，所以我开始将每块1000 -> 500 -> 200 -> 100 -> 50行的行数降低到1。

在此之前(不使用此表，对于删除操作--而不是更新)，将行数降低到200或100，有助于消除lock_escalation事件，但这一次，即使每次更新操作有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

Answer 1

如果我们查看实际计划，当前的查询将从表中读取太多要更新的数据。这来自于BoxTrackInfo上的索引搜索：

这是在btid上查找BlueTrackEvents扫描后出现的每一行的索引。在检查btStatusID时获取更新锁，以查看行是否符合更新的条件。只有1 401行符合更新的条件，但在进程中使用了更多的锁，从而导致锁升级到表级别。

您确实需要一种不同的计划形式--在btStatusID上查找D6表，然后加入到BlueTrackEvents中，这样可以获得更少的锁。为此，添加这样的索引应该会有所帮助：

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

这样可以更有效地定位符合条件的行，希望能够在不升级锁的情况下完成更新。

另外，当前的执行计划使用合并半连接验证btStatusID上的外键约束：

在您的情况下，这可能不是什么大问题，因为LBoxTrackStatus表中只有267行。如果该表更大，您可以考虑在查询中添加一个LOOP JOIN或FAST 1提示，以获得嵌套循环FK验证。详情请参阅本帖：

为什么在插入上出现快照隔离问题？