SQL Server中Select与Delete语句死锁问题排查及解决方案咨询

嗨，我来帮你梳理下这个Select和Delete语句引发死锁的排查思路和解决方向~

首先先明确下你提到的两个引发死锁的SQL语句：

-- Delete语句
(@pr0 int)delete from [table] where [column1] = @pr0

-- Select语句
(@pr0 int)select [column1], [column2], [column3], [column4], [column5] from [table] where [column2] = @pr0

你已经导出了死锁报告和生成了死锁图，但还没找到问题，那我们可以从这几个关键点入手分析：

1. 从死锁图/报告里抓核心信息 #

你可以重点查看死锁报告里这几个部分：

• 进程的锁持有与等待关系：确认Delete进程持有什么类型的锁（比如排他锁X），在等待什么锁；Select进程持有什么锁（比如共享锁S），又在等待什么锁。死锁的本质就是两个进程互相持有对方需要的资源。
• 锁定的资源类型：如果看到是RID（堆表的行标识符锁），说明你的表没有合适的索引，导致两个语句都做全表扫描，锁定了大量行，大大增加死锁概率；如果是KEY（索引键锁），那要看对应的索引是不是你预期的。
• 语句的执行计划：死锁报告里通常会包含执行计划的线索，看看Select是不是走了全表扫描，Delete是不是也没用到索引。

2. 最可能的根源：索引缺失 #

你的Delete语句按column1过滤，Select按column2过滤，如果这两个列都没有对应的非聚集索引，那：

• Delete语句会做全表扫描，扫描过程中给遇到的行加X锁；
• Select语句同样做全表扫描，给遇到的行加S锁；
  当两个语句交叉扫描到相同的行时，就很容易出现“Delete持有某行的X锁等Select释放S锁，Select持有另一行的S锁等Delete释放X锁”的死锁场景。

3. 针对性的解决方案建议 #

• 补全必要的索引：
  • 给column1创建非聚集索引，让Delete语句能快速定位到要删除的行，减少锁定范围：

    CREATE NONCLUSTERED INDEX IX_Table_Column1 ON [table] ([column1]);
    

  • 给column2创建覆盖索引，包含Select需要查询的所有列，这样Select可以直接从索引返回数据，不用访问基表，也就不会加基表的S锁：

    CREATE NONCLUSTERED INDEX IX_Table_Column2_Covering ON [table] ([column2])
    INCLUDE ([column1], [column3], [column4], [column5]);
    

• 调整事务隔离级别：
  开启数据库的READ_COMMITTED_SNAPSHOT选项，让默认的READ COMMITTED隔离级别下的Select语句使用行版本读取，不再加共享锁，从根本上避免和Delete的排他锁冲突：

  ALTER DATABASE YourDatabaseName SET READ_COMMITTED_SNAPSHOT ON;
  

  这个操作需要数据库没有活跃连接，建议在维护窗口执行。
• 临时应急方案（谨慎使用）：
  如果暂时没法加索引或改隔离级别，可以给Select语句加WITH (NOLOCK)提示，但要注意这会允许脏读，只有你的业务能接受读取未提交的数据时才用：

  select [column1], [column2], [column3], [column4], [column5] 
  from [table] WITH (NOLOCK)
  where [column2] = @pr0
  

4. 额外排查点 #

如果加了索引还是有死锁，那要检查这两个语句是不是被包含在更大的事务里？比如Delete之后还有其他操作，或者Select之前有其他写操作，导致事务持有锁的时间变长，增加死锁概率。尽量让事务保持短小，完成操作后立即提交。

备注：内容来源于stack exchange，提问作者AEA