在SQL Server中如何避免聚合查询产生的幻读现象？

REPEATABLE READ下聚合查询仍会幻读，因其只锁已存在行不锁间隙；SERIALIZABLE通过RangeS-S锁阻塞范围插入删除，但需索引支持；RCSI可规避幻读但增加tempdb压力且不解决“先查后插”逻辑漏洞。

聚合查询在REPEATABLE READ下为什么还会幻读

SQL Server 的 REPEATABLE READ 会锁住已读取的行（key lock），但不锁“不存在的行”之间的间隙（gap）。当你执行 COUNT(*) FROM orders WHERE status = 'pending'，而另一个事务刚好插入一条满足该条件的新记录并提交，第二次聚合就会多出一行——这不是脏读或不可重复读，是典型的幻读。

常见错误现象：SELECT COUNT(*) 第一次返回 12，几毫秒后再次执行返回 13，且两次都在同一事务中。此时即使加了 WITH (HOLDLOCK)，若查询没走索引，SQL Server 可能只锁行、不锁间隙，照样漏防。

用SERIALIZABLE隔离级别锁住整个扫描范围

SERIALIZABLE 是 SQL Server 唯一能真正阻止这类幻读的内置级别，它会对查询涉及的索引范围加 RangeS-S 锁，阻塞其他事务在该范围内插入或删除。

• 必须显式开启：在 BEGIN TRAN 后、第一个语句前执行 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• 依赖索引：WHERE status = 'pending' 字段必须有索引，否则会升级为表锁，引发严重阻塞
• 避免长事务：锁持有时间越长，并发冲突概率越高；不要在事务里做耗时操作（如网络调用、大文件处理）
• 示例写法：

  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRAN;SELECT COUNT(*) FROM orders WITH (HOLDLOCK) WHERE status = 'pending';-- 后续业务逻辑COMMIT;

更轻量的替代方案：启用READ_COMMITTED_SNAPSHOT

如果数据库允许配置变更，READ_COMMITTED_SNAPSHOT（RCSI）比 SERIALIZABLE 更实用：它让所有 READ COMMITTED 查询自动读取 tempdb 中的行版本快照，天然规避幻读，且不加锁。

• 启用方式：ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON
• 生效后，原 READ COMMITTED 行为不变，但底层不再依赖共享锁，而是读事务启动时刻的已提交版本
• 注意 tempdb 压力会上升，尤其在高更新频率场景下
• 无法解决“先查再插”类逻辑漏洞——RCSI 保证读一致性，但不保证写逻辑原子性

容易被忽略的三个硬约束

幻读防护从来不是设个隔离级别就完事的事。真正起作用的，是隔离级别 + 索引 + SQL 写法三者咬合：

• 没索引的 WHERE 条件（比如 status 列无索引），SERIALIZABLE 会退化成表锁，性能断崖式下跌
• 应用层分两步：先 SELECT 判断，再单独 INSERT —— 这中间的时间窗口，任何隔离级别都救不了
• 触发器里的 SELECT 不继承外层事务的隔离级别，也不能在触发器内执行 SET TRANSACTION ISOLATION LEVEL（SQL Server 直接报错）