Thread

Lost update in an ordered batch, but only with index scan

Bernice Southey <bernice.southey@gmail.com> — 2025-12-28T23:07:14Z

Hi,

PostgreSQL 18.1 (Ubuntu 18.1-1.pgdg24.04+2) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit

I've been following the recommendation of ordering batch updates to
avoid deadlocks. I just added a new case, and it consistently loses
updates in my tests.

Here's a simplified version.

--setup
create table t(id int primary key, balance int default 0);
insert into t(id) select i from generate_series(1, 10000) i;
analyze t;

--session 1
begin;
with l as (select id from t where id in (1, 2) order by id for update)
update t set balance = balance + 1 from l where t.id = l.id;

UPDATE 2

--session 2
with l as (select id from t where id in (1, 2) order by id for update)
update t set balance = balance + 1 from l where t.id = l.id;

UPDATE 1 (after committing session 1)

If I only have a few rows in the table, it does a seq scan, and then
both rows update in session 2.

Apologies if I'm just doing something wrong and this isn't a bug.

Thanks, Bernice