我最近偶然发现了SELECT ... FOR UPDATE与(LEFT) JOIN. 这是表结构以及重现结果的场景：
表结构
create table counter (
counter_id serial primary key,
current_counter int not null default 0
);
create table diff (
diff_id serial primary key,
diff_increase int not null default 0,
counter_id serial references counter(counter_id) not null
);
设想
有两个并发事务 A 和 B，都执行相同的查询。
- 事务 A 以该查询开始，并且能够获取锁并继续。
select *
from counter
left join diff on counter.counter_id = diff.counter_id
where counter.counter_id = 1
order by diff.diff_id desc
limit 1
for update of counter
;
事务 B 尝试执行相同的查询，但无法获取锁，因此等待。
事务 A 将执行以下查询：
update counter
set current_counter = current_counter + 100
where counter_id = 1
;
insert into diff (diff_increase, counter_id) values (100, 1)
;
commit;
- 事务 A 已完成，数据库的状态现在应如下所示：
-- counter table
counter_id | current_counter
------------------------------
1 | 200
-- diff table
diff_id | diff_increase | counter_id
--------------------------------------
1 | 50 | 1
2 | 50 | 1
3 | 100 | 1
预期行为
事务 B 看到更新的计数器 ( current_counter = 200) 和最后的差异 ( diff_id = 3)。
实际行为
事务 B 继续使用counter表的新状态（意思是current_counter = 200），而diff_id仍然是 2 而不是 3。
这种行为是预期的吗？如果是这样，为什么同一个查询会看到数据库的不同状态？这不违反READ COMMITTED隔离级别的保证吗？
在 Linux 上使用 PostgreSQL 13 进行测试。