I have recently stumbled upon a seemingly odd behavior of
SELECT ... FOR UPDATE when combined with
(LEFT) JOIN. Here is the table structure as well as a scenario to reproduce the result:
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 );
There are two concurrent transactions A & B, both performing the same queries.
- Transaction A starts with that query and is able to acquire the lock and proceed.
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 ;
Transaction B tries to perform the same query but cannot acquire a lock and therefore waits.
Transaction A will do the following queries:
update counter set current_counter = current_counter + 100 where counter_id = 1 ; insert into diff (diff_increase, counter_id) values (100, 1) ; commit;
- Transaction A has completed and the state of the database should now be the following:
-- 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
Transaction B sees the updated counter (
current_counter = 200) and the last diff (
diff_id = 3).
Transaction B continues with the new state of the
counter table (meaning
current_counter = 200) while the
diff_id is still 2 instead of 3.
Is this behavior expected? If so, why does one and the same query see different states of the database? Does this not violate the guarantees of the
READ COMMITTED isolation level?
Tested with PostgreSQL 13 on Linux.
FOR UPDATEI would agree. But the observable effect is that the query returns the state of
counterafter the commit of transaction A and the state of
diffbefore the commit of transaction A. I don't understand how that could be expected as this is looks like an inconsistency within one query.
READ COMMITTEDguarantees is that you won't get any dirty reads.