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

在 REPEATABLE READ 隔离级别下，聚合查询仍然可能遭遇幻读现象，不少 SQL Server 开发者都曾遇到这个陷阱。根本原因在于：该级别仅锁定已存在的行（Key Lock），却不会锁定行与行之间的间隙。而 SERIALIZABLE 隔离级别虽然能通过 RangeS-S 锁阻止范围内的插入与删除操作，但这严重依赖索引支持。RCSI（READ_COMMITTED_SNAPSHOT）虽然可以规避幻读，但会增加 tempdb 数据库的压力，并且无法有效防御“先查询后插入”这类逻辑漏洞。

为什么 REPEATABLE READ 下的聚合查询仍然会出现幻读

许多人认为启用 REPEATABLE READ 就能彻底解决问题，实际情况并非如此。该隔离级别确实会锁定已读取的行（Key Lock），但对于那些尚未存在的行间间隙，它却置之不理。假设你执行 COUNT(*) FROM orders WHERE status = 'pending'，另一个事务恰好在这时插入一条新的 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' 字段没有索引，SQL Server 会升级为表锁，导致并发性能急剧下降
• 避免长事务——锁持有时间越长，阻塞越严重，切勿在事务中执行网络调用或大文件处理等耗时操作
• 示例写法如下：

  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 无法解决业务逻辑中的“先查后插”漏洞——它只保证读一致性，不保证写操作的原子性。

容易被忽略的三个硬性约束

最后，总结三个容易忽视的硬性约束。第一，如果 WHERE 条件（如 status 列）没有索引，SERIALIZABLE 会降级为表锁，性能瞬间断崖式下跌。第二，应用层如果采用两步操作：先 SELECT 判断，再单独 INSERT——这中间的时间窗口，任何隔离级别都无法挽救。第三，触发器中的 SELECT 不继承外层事务的隔离级别，你也不能在触发器内执行 SET TRANSACTION ISOLATION LEVEL，SQL Server 会直接报错。因此，幻读防护从来不是仅靠设置隔离级别就能解决，必须将隔离级别、索引设计与 SQL 写法三者紧密配合才能生效。