SQL Server 2019中两条不同条件DELETE语句引发死锁的排查与解决

问题背景 #

• 数据库环境：SQL Server 2019，隔离级别为READ_COMMITTED，未开启READ_COMMITTED_SNAPSHOT
• 触发场景：两条针对同一表、不同Foreign_Key_Column值的DELETE语句发生死锁
• 排除推测：死锁图显示两个进程访问不同数据页，排除ID值相近导致同数据页锁冲突的可能
• 现有局限：若强制单连接串行执行会增加耗时，需更优解决方案
• 可用信息：相关DELETE语句、表与索引定义、死锁XML文件、执行计划

死锁原因分析 #

结合执行计划与SQL Server锁机制，常见诱因如下：

1. 无精准索引导致的扫描锁冲突
   若DELETE语句未利用Foreign_Key_Column的专用索引，会触发表扫描或非覆盖索引扫描。扫描过程中会对路径上的所有数据页添加意向排他锁（IX），当两个DELETE操作的扫描路径交叉、且锁获取顺序不一致时（如A扫描页1→页2，B扫描页2→页1），就会形成循环等待，触发死锁。
2. 外键约束的反向检查锁竞争
   若Foreign_Key_Column关联父表的外键约束，DELETE时会自动检查父表引用。若检查操作使用无索引扫描，同样会产生大范围锁，与另一DELETE的锁操作形成冲突。
3. 锁升级引发的表级锁冲突
   若DELETE涉及行数较多，SQL Server可能将页级锁升级为表级意向锁，此时两个操作会互相等待对方释放表级锁，引发死锁（虽死锁图显示不同数据页，但仍需排查此可能）。

解决方案 #

1. 创建/优化精准索引 #

为Foreign_Key_Column创建非聚集索引，确保DELETE能直接定位目标行，避免全表/大范围扫描：

CREATE NONCLUSTERED INDEX IX_YourTableName_ForeignKeyColumn 
ON YourTableName(Foreign_Key_Column);

若已有索引但未被执行计划选中，可强制指定索引：

DELETE FROM YourTableName WITH(INDEX(IX_YourTableName_ForeignKeyColumn))
WHERE Foreign_Key_Column = @TargetValue;

2. 开启READ_COMMITTED_SNAPSHOT #

启用行版本控制，让READ_COMMITTED隔离级别下的读操作不再加共享锁，从根源减少锁竞争：

ALTER DATABASE YourDatabaseName SET READ_COMMITTED_SNAPSHOT ON;

注意：执行此语句需数据库无活跃连接，建议在维护窗口操作

3. 调整锁升级策略 #

若锁升级是诱因，可禁用表级锁升级，限制为页级或行级锁：

ALTER TABLE YourTableName SET (LOCK_ESCALATION = DISABLE);

需监控锁数量变化，避免因锁过多导致性能下降

4. 统一事务执行顺序 #

若无法避免并行执行，可让所有DELETE操作按Foreign_Key_Column值的固定顺序（如从小到大）处理，确保锁获取顺序一致，消除循环等待条件。

内容的提问来源于stack exchange，提问作者Merlin Nestler