我的任务是更新生产表中的 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
计划:
如果我们查看实际计划,当前查询正在从表中读取太多数据以进行更新。这是来自索引搜索
BoxTrackInfo
:btid
这是对扫描出来的每一行的索引查找BlueTrackEvents
。btStatusID
在检查行是否符合更新条件时获取更新锁。只有 1,401 行符合更新条件,但在此过程中会使用更多锁 - 导致锁升级到表级别。你真的想要一个不同的计划形状 - 寻找
BoxTrackInfo
表btStatusID
然后加入到BlueTrackEvents
,这应该会获得更少的锁。为此,添加这样的索引应该会有所帮助:这应该更有效地定位符合条件的行,希望允许更新在没有锁升级的情况下完成。
附带说明一下,当前的执行计划验证了
btStatusID
使用合并半连接的外键约束:在您的情况下,这可能没什么大不了的,因为
LBoxTrackStatus
表中只有 267 行。如果该表较大,您可以考虑在查询中添加LOOP JOIN
或FAST 1
提示,以便获得嵌套循环 FK 验证。有关详细信息,请参阅此帖子:为什么我在 INSERT 上遇到快照隔离问题?