sql - 快照打开，仍然死锁，ROWLOCK

Question

我使用以下代码在数据库中打开了快照隔离

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

摆脱了很多僵局。

但是，当我需要每小时运行一个脚本来清理100,000多行时，数据库仍然会产生死锁。

有什么方法可以避免清理脚本中的死锁，是否需要在该查询中专门设置ROWLOCK？

是否可以增加数据库使用的行级别锁的数量？

如何提升锁？从行级到页面级再到表级？

我的删除脚本非常简单:

delete statvalue
from statValue,
(select  dateadd(minute,-60, getdate()) as cutoff_date) cd
where temporaryStat = 1
and entrydate < cutoff_date

现在，我正在寻找快速解决方案，但是长期解决方案会更好。

非常感谢，
帕特里克

Answer 1

SNAPSHOT隔离只能减轻(某些)涉及读取的死锁，但绝对不能避免写入与写入死锁。如果您每小时产生100k +行，即每秒约30次插入，那么删除扫描几乎可以保证与其他写入操作发生冲突。如果您要做的只是插入，从不更新，则删除块，而不是行级锁死锁，但是由于表足够大并且删除正在进行扫描，因此引擎可能会选择页面锁来进行删除，因此，您可能会陷入僵局。

如果没有条目日期的索引，则删除别无选择，只能扫描整个表。这种经常在顶部插入并在底部删除的表实际上是队列，您应该按entrydate对其进行组织。这意味着输入日期可能应该是聚集索引中最左侧的键。这种组织方式可以清楚地分隔在表的一端出现的插入与在另一端出现的删除。但这是一个相当大的改变，特别是如果您使用statvalueid读取这些值。我想现在您有一个基于自动递增字段(StatValueId)的聚集索引。我也假设entrydate和statvalueid是相关的。如果两个假设都成立，则应该删除statvalueid的基数:找到可以安全删除的最大ID，然后删除该ID左侧的聚集索引上的所有内容:

declare @statvalueidmax int;
select @statvalueidmax = max(statvalueid) 
 from statvalue with (readpast)
 where entrydate < dateadd(minute,-60, getdate());

delete statvalue
 where statvalueid <= @statvalueidmax;

我做出了许多假设，它们可能是错误的。但是这个主旨是，您必须将插入内容与删除内容分开，这样它们才不会重叠。