SQL Server锁升级

Question

我的应用程序每晚运行一个清除过程，以便从OLTP应用程序的主表中删除旧记录。我在清除过程中遇到了锁升级，这会阻塞对表的并发插入，因此我修改了清除过程，以循环和删除4900个块中的记录，这应该远远低于SQL Server的锁升级阈值5000。虽然锁升级已大大减少，但SQL Server事件探查器仍会报告循环中以下DELETE语句的偶尔锁升级：

-- outer loop increments @BatchMinId and @BatchMaxId variables
  BEGIN TRAN

  -- limit is set at 4900
  DELETE TOP (@limit) h
  OUTPUT DELETED.ChildTable1Id,
         DELETED.ChildTable2Id,
         DELETED.ChildTable3Id,
         DELETED.ChildTable4Id
  INTO #ChildRecordsToDelete
  FROM MainTable h WITH (ROWLOCK)
  WHERE h.Id >= @BatchMinId AND h.Id <= @BatchMaxId AND h.Id < @MaxId AND 
        NOT EXISTS (SELECT 1 FROM OtherTable ot WHERE ot.Id = h.Id);

  -- delete from ChildTables 1-4 (no additional references to MainTable)
  COMMIT TRAN;
-- end loop

在SQL Server事件探查器中，报告的锁升级事件(应该是escalated lock count)的"IntegerData2“列的范围从10197到10222，看起来不接近4,900(我的清除批处理大小)加上1,250 (number of additional locks SQL Server may take before attempting escalation )的任何倍数。

假设我显式地将DELETE语句限制在4900行，那么如何获得更多的锁，特别是SQL Server将升级为表锁的程度？在我求助于完全禁用这个表上的锁升级之前，我想了解一下这一点。

Answer 1

我不能评论你的问题，因为我在这个网站上没有足够的声誉，所以我在这里评论。

我在晚上运行的清理任务中也遇到了类似的问题。delete语句被"GHOST CLEANUP“锁定。在这里看一下这个：SQL Server Lock Timeout Exceeded Deleting Records in a Loop

希望这能有所帮助。我当时发现的一个奇怪的解决方案是: 1)插入记录以保存在另一个具有相同结构的表中。(复制) 2)截断表以进行清理3)将数据从副本插入到现在为空的表中。4)截断复制表以释放空间。

这个技巧比删除本身更快，因为删除是在瞬间完成的，因为截断。不知何故，插入的成本比删除成本要低。

但是，我仍然建议避免这种解决方案。您还可以将块减少到100到500之间。这增加了清理所需的时间，但锁升级的可能性较小。