PostgreSQL在不同事务上读提交

Question

我正在运行一些测试来更好地理解postgresql的read提交。我有两个并行运行的事务:

-- transaction 1
begin;
select id from item order by id asc FETCH FIRST 500 ROWS ONLY;
select pg_sleep(10);
commit;
--transaction 2 
begin;
select id from item order by id asc FETCH FIRST 500 ROWS ONLY;
commit;

第一个事务将选择前500个id，然后通过休眠10s来保持id第二个事务将同时查询表中的前500行。

根据我对读提交的理解，第一个事务将选择1到500条记录，第二个事务将选择501到1000条记录。但实际结果是两个事务都选择1到500条记录。

如果有人能指出哪一部分是错的，我将非常感激。由于

Answer 1

你误解了read committed的含义。这意味着事务不能看到(选择)未提交的更新。试试以下命令:

create table read_create_test( id integer generated always as identity 
, cola text 
) ; 

insert into read_create_test(cola)
select 'item-' || to_char(n,'fm000')
from generate_series(1,50) gs(n); 

-- transaction 1 
do $$
max_val integer; 
begin
insert into read_create_test(cola)
select 'item2-' || to_char(n+100,'fm000')
from generate_series(1,50) gs(n);

select max(id)
into max_val
from read_create_test; 
raise notice 'Transaction 1 Max id: %',max_val;
select pg_sleep(30);      -- make sure 2nd transaction has time to start 
commit; 
end;
$$; 
-- transaction 2 (run after transaction 1 begins but before it ends)  
do $$
max_val integer; 
begin
select max(id)
into max_val
from read_create_test; 

raise notice 'Transaction 2 Max id: %',max_val;
end;
$$; 
-- transaction 3 (run after transaction 1 ends)  
do $$
max_val integer; 
begin
select max(id)
into max_val
from read_create_test; 

raise notice 'Transaction 3 Max id: %',max_val;
end;
$$;

分析结果，记住A transaction cannot see uncommitted DML。