如何在PostgreSQL中复现幻读？REPEATABLE READ未出现幻读原因问询

首先直接回答核心疑问：

1. 你测试的纯查询场景没有复现幻读，是PostgreSQL的MVCC（多版本并发控制）实现特性导致的
2. PostgreSQL的REPEATABLE READ并没有完全阻止幻读，仅避免了快照读场景下的幻读，在涉及写入的场景下仍会出现幻读，只有SERIALIZABLE级别会完全禁止所有幻读场景。

原理说明 #

SQL标准对隔离级别的定义是基于异常现象的禁止规则：REPEATABLE READ仅要求禁止脏读、不可重复读，允许幻读出现。但PostgreSQL的REPEATABLE READ采用了快照隔离（Snapshot Isolation, SI）实现，能力超出了标准要求：
事务启动时会生成一个全局一致性快照，整个事务周期内的所有普通SELECT（即快照读，不需要加锁的查询）都基于这个快照获取数据，天然不会读到其他事务后续提交的新增、修改数据，所以你测试的两次纯SELECT结果一致，不会出现幻读。

PostgreSQL REPEATABLE READ级别幻读复现方案 #

如果要复现幻读，需要触发当前读操作（UPDATE、DELETE、INSERT ... SELECT、SELECT FOR UPDATE等需要读取最新数据并加锁的操作），具体步骤如下：

准备阶段（和你的初始化操作一致） #

CREATE TABLE balances (id varchar PRIMARY KEY, balance int);
INSERT INTO balances (id, balance) VALUES ('Alice', 40), ('Bob', 50);

操作步骤 #

-- 终端T1操作：开启REPEATABLE READ事务，先做快照读统计
T1=> BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
T1=> SELECT COUNT(*) FROM balances WHERE balance > 10;
 count 
-------
     2
(1 row)

-- 终端T2操作：插入符合条件的新数据并提交
T2=> INSERT INTO balances (id, balance) VALUES ('Charlie', 60);
T2=> COMMIT;

-- 回到终端T1操作：执行UPDATE当前读，再统计
T1=> UPDATE balances SET balance = balance + 10 WHERE balance > 10;
UPDATE 3 -- 这里返回更新了3行，和之前查询的2行不一致，即出现幻读
T1=> SELECT COUNT(*) FROM balances WHERE balance > 10;
 count 
-------
     3
(1 row)
T1=> COMMIT;

和SERIALIZABLE级别的差异 #

如果将T1的隔离级别改为SERIALIZABLE，执行上述操作时，T1执行UPDATE语句会直接抛出could not serialize access due to concurrent update错误，事务会回滚，从根本上避免了幻读导致的逻辑不一致。

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