Inconsistent data when combining (LEFT) JOIN with SELECT FOR UPDATE

Question

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.

1. 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
;

2. Transaction B tries to perform the same query but cannot acquire a lock and therefore waits.

3. 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;

4. 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.

Answer 1

You may be seeing an out-of-order effect on the diff table as a consequence of the FOR UPDATE locking clause.

The documentation of SELECT FOR UPDATE warns about this in the following "Caution" paragraph:

It is possible for a SELECT command running at the READ COMMITTED transaction isolation level and using ORDER BY and a locking clause to return rows out of order. This is because ORDER BY is applied first. The command sorts the result, but might then block trying to obtain a lock on one or more of the rows. Once the SELECT unblocks, some of the ordering column values might have been modified, leading to those rows appearing to be out of order (though they are in order in terms of the original column values).

You may also be interested in the answer in the pgsql mailling-list for that question: SELECT ... FOR UPDATE OF SKIP LOCKED returns can same row when table is filtered on different table than locked, where Thomas Munro points to this section of the doc and explains:

That may seem pretty surprising, but it's because FOR UPDATE follows the update chain, allowing you to see committed tuples that aren't visible to the active snapshot, as longs as they still satisfy the WHERE clause

and later

You'd need to make sure that the row locking applies to the same relation(s) as the WHERE clause to avoid that.

Your query cannot make sure of that, if it's locking counter and expecting the corresponding values of diff at the same level of visibility.

In general, the Read Committed isolation level doesn't provide any guarantee in the face of concurrent writes, so the generic solution with concurrency anomalies is to use higher isolation levels and deal with serialization failures.

Answer 2

In essence, concurrently updated rows are taken into account, but concurrently inserted rows are not.

In default READ COMMITTED isolation level, each command can only see rows that have been committed before it began. UPDATE adds new row versions, not new rows.

The inserted row (diff_id = 3) in your example is not visible to the concurrent transaction that started before the row had been committed. Taking a lock with FOR UPDATE has no bearing on the visibility of rows. But the new row version added by the concurrent UPDATE will be taken into account.

Essential quotes from the manual:

1.

[...] a SELECT query (without a FOR UPDATE/SHARE clause) sees only data committed before the query began;

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time.

The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row.

The whole chapter is a recommended read.

The best course of action for this case might be SERIALIZABLE transactions