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:
Table Structure
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
);
Scenario
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
Expected Behavior
Transaction B sees the updated counter (current_counter = 200
) and the last diff (diff_id = 3
).
Actual Behavior
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 UPDATE
I would agree. But the observable effect is that the query returns the state ofcounter
after the commit of transaction A and the state ofdiff
before 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 COMMITTED
guarantees is that you won't get any dirty reads.