SQL Server中Delete与Insert因主键索引竞争导致死锁问题排查

死锁原因分析 #

1. Serializable隔离级别的Range锁特性：该隔离级别为防止幻读，会给查询扫描的索引范围加上RangeS-S（范围共享锁），而非单条记录的行锁。Delete和Insert操作均依赖主键索引PK_UserRole_1，导致锁范围覆盖了整个操作涉及的索引区间。
2. 循环等待的锁竞争：Delete进程持有部分索引范围的RangeS-S锁，等待升级为X锁完成删除；Insert进程持有另一部分索引范围的RangeS-S锁，等待获取RangeI-N（范围插入意向锁）完成插入。两者形成循环等待，触发死锁。
3. 自引用外键的额外锁开销：虽然提前删除了父行规避外键异常，但Serializable级别下，数据库仍会扫描主键索引验证PrimaryRoleId的外键约束，进一步扩大了锁的覆盖范围，加剧了竞争。
4. 主键索引的垄断性：主键作为聚集索引，所有数据操作都要通过它执行，导致锁竞争集中在同一索引上，没有其他索引分流操作压力。

索引优化方案 #

• 创建Delete操作的覆盖索引：针对存储过程中引发RangeS-S锁的Delete语句，根据其过滤/关联字段（如临时表@roleTable关联的字段）创建非聚集覆盖索引，避免扫描主键聚集索引，缩小锁范围。示例：

  CREATE NONCLUSTERED INDEX IX_UserRole_DeleteFilter ON cs.UserRole (关联字段) INCLUDE (PrimaryRoleId, 其他必要字段)
  

• 给自引用外键创建独立索引：为PrimaryRoleId创建非聚集索引，让数据库验证外键约束时无需扫描主键索引，减少锁的持有时间和范围：

  CREATE NONCLUSTERED INDEX IX_UserRole_PrimaryRoleId ON cs.UserRole (PrimaryRoleId)
  

• 主键索引分区（可选）：如果主键是有序字段（如自增ID），可按范围将主键索引分区，让Delete和Insert操作落在不同分区内，避免跨分区的锁竞争。
• 优化事务粒度与隔离级别：若业务允许，将隔离级别降至Repeatable Read，避免Range锁的产生；必须使用Serializable时，拆分批量操作为小批次（如每次处理100条），缩短锁的持有时间。

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