长查询引发事务死锁求助：夜间作业与同表更新操作冲突

你的夜间批量作业和更新存储过程因锁资源竞争触发了死锁——这种情况在长时间运行的批量查询与并发更新操作共存时很常见，我来帮你拆解问题并给出可行的解决方案：

一、先搞清楚死锁的根源 #

你的夜间作业要跑4-5分钟，期间对300万条记录的表执行6次SELECT，默认的READ COMMITTED隔离级别下，SELECT会持有共享锁直到语句执行完毕；而更新存储过程需要排他锁来修改数据。如果两者的锁请求形成循环等待（比如作业先锁了A行等待B行，更新先锁了B行等待A行），SQL Server就会选择一个牺牲品终止事务，也就是你看到的错误。作业运行时间越长，锁持有窗口越大，死锁概率就越高。

二、针对性解决方案 #

1. 优化锁策略，减少锁冲突

• 启用快照隔离（推荐）：如果业务可以接受读取版本化的数据（不会读到脏数据，但可能是稍旧的快照），可以开启数据库的快照隔离：

  ALTER DATABASE [你的数据库名] SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  然后在夜间作业的事务里设置隔离级别为SNAPSHOT：

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  

  这样作业的SELECT不会持有共享锁，而是读取行版本，完全避免和更新操作的排他锁冲突。
• 使用NOLOCK提示（谨慎选择）：如果业务允许脏读（比如批量统计、数据迁移对实时性要求不高），可以给SELECT语句加WITH (NOLOCK)提示：

  SELECT * FROM 目标表 WITH (NOLOCK) WHERE ...;
  

  这种方式不会加共享锁，但要注意可能读到未提交的更新数据。

2. 缩短事务与锁持有时间

• 拆分批量作业为小事务：不要把整个4-5分钟的操作放在一个大事务里，比如每次处理1000条记录就提交一次事务，这样锁会及时释放，大大降低死锁风险。
• 优化更新存储过程：检查更新操作的逻辑，确保事务里只包含必要的语句，避免在事务中做无关的查询、等待操作，尽可能快地提交事务，减少排他锁的持有时间。

3. 通过索引优化减少锁范围

• 给查询和更新语句加合适的索引：如果夜间作业的SELECT是全表扫描，会锁定大量行；更新操作如果没有索引，也会做全表扫描并锁定很多行。给SELECT的过滤字段、JOIN字段加覆盖索引，给更新操作的WHERE条件字段加索引，让SQL Server快速定位到目标行，减少锁的行数和范围。
• 查看死锁图定位具体资源：用SQL Server的扩展事件（推荐）或Profiler捕获死锁图，看看死锁发生在哪个索引、哪些行上，针对性优化索引或调整查询逻辑。

4. 调度错开（最直接的规避方式）

如果业务允许，把夜间作业和更新操作的执行时间完全错开，比如让作业在凌晨1点运行，更新操作安排在凌晨6点，从根源上避免两者同时访问目标表。

三、额外提醒 #

• 不要盲目使用NOLOCK，一定要评估业务对数据一致性的要求；
• 启用快照隔离后，数据库会生成行版本，需要确保有足够的tempdb空间。

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