PreparedStatement参数化触发SQL Server 2019死锁（JDBC）

为什么两种查询方式会导致死锁差异？ #

核心原因是SQL Server对参数化查询和字符串拼接查询的执行计划处理逻辑完全不同：

• 字符串拼接生成的SQL，每个参数值对应唯一的语句，SQL Server会为每条语句单独生成执行计划。如果MAKE和MODEL的组合选择性高，执行计划会直接走索引定位少量行，锁的范围极小，高并发下冲突概率低。
• 参数化查询（PreparedStatement）的SQL模板固定，SQL Server会复用执行计划。如果触发参数嗅探问题（比如首次执行时用了选择性极低的参数，生成全表/全页扫描的执行计划），后续所有请求都会复用这个不合理的计划，导致扫描大量行、持有更多锁，高并发下和insert/delete的锁冲突概率陡增，最终引发死锁。此外，参数化查询的锁持有逻辑（如锁升级触发条件）也可能和拼接版本不同，进一步加剧冲突。

可尝试的解决方法 #

1. 针对单个查询禁用参数嗅探 #

在查询末尾添加OPTION (RECOMPILE)，让SQL Server为每次请求生成适配当前参数的执行计划，避免复用不合适的计划：

SELECT * FROM CARS WHERE MAKE = ? AND MODEL = ? OPTION (RECOMPILE);

注：该选项会增加少量编译开销，但对于高选择性查询，收益远大于开销。

2. 强制使用指定索引 #

如果MAKE和MODEL上有复合索引，直接强制查询走该索引，确保快速定位目标行，缩小锁范围：

SELECT * FROM CARS WITH (INDEX(idx_cars_make_model)) WHERE MAKE = ? AND MODEL = ?;

前提是已创建对应复合索引：

CREATE INDEX idx_cars_make_model ON CARS(MAKE, MODEL);

3. 启用快照隔离（READ_COMMITTED_SNAPSHOT） #

将数据库的READ_COMMITTED_SNAPSHOT设置为ON，读操作不再加共享锁，而是读取行的版本化数据，从根源上避免读锁与写锁（insert/delete）的冲突：

ALTER DATABASE YourDatabaseName SET READ_COMMITTED_SNAPSHOT ON;

该方式比READ_UNCOMMITTED更安全，不会读取脏数据，适配高并发读写场景。

4. 捕获并分析死锁图 #

用SQL Server的扩展事件或SQL Server Profiler捕获死锁图，明确死锁双方的语句、锁资源类型和等待关系，比如：

• 是否是select的共享锁与delete的排他锁互相等待？
• 死锁涉及的资源是行、页还是表？
  通过死锁图可精准定位问题，比如是否是某条delete语句锁范围过大，或是select执行计划确实存在扫描问题。

5. 缩短事务持有时间 #

检查代码中事务的范围，确保select操作和后续业务逻辑（若有）在尽可能短的事务内完成，避免锁长时间持有。比如不要在事务中包含IO操作、外部调用等耗时步骤。

6. 调整锁升级阈值（谨慎操作） #

如果锁升级是问题根源，可调整SQL Server的锁升级阈值，比如将表级锁升级的阈值从默认5000锁提升，或是针对单表禁用锁升级：

ALTER TABLE CARS SET (LOCK_ESCALATION = DISABLE);

注：不推荐直接禁用锁升级，优先通过优化执行计划减少锁数量。

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