Re: MERGE behavior with REPEATABLE READ isolation level

On Tue, Feb 24, 2026 at 5:13 AM Tender Wang <tndrwang(at)gmail(dot)com> wrote:
> Alexander Korotkov <aekorotkov(at)gmail(dot)com> 于2026年2月15日周日 09:23写道：
> > Oh, sorry I missed the begin statement for s1. The complete case should look like this.
> >
> > s1# create table test (id int primary key, val int);
> > s1# insert into test values (1,0);
> >
> > s2# begin;
> > s2# update test set val = val + 100;
> >
> > s1# begin isolation level repeatable read;
> > s1# MERGE INTO test t USING (VALUES (1, 100)) AS s (id, inc)
> > ON t.id = s.id
> > WHEN MATCHED THEN
> > UPDATE SET val = t.val + s.inc
> > WHEN NOT MATCHED THEN
> > INSERT (id, val) VALUES (s.id, s.inc);
> > (waiting ...)
> >
> > s2# commit;
> >
> > s1# MERGE 1
> > s1# select * from test;
> > id | val
> > ----+-----
> > 1 | 200
> > (1 row)
> >
>
> I tried "update test set val = val + 100;" but the SQL reported a
> "could not serialize access due to concurrent update" error.
> It seems that the MERGE command should behave identically to UPDATE
> when performing a match action.
>
> I wrote a fix patch and attached it, and added your test case, too.

Thank you for the confirmation and for the patch. Regarding the test
case, can we handle this without introducing a new .spec file? I
think we can add 1-2 permutations to merge-update.spec. Even existing
step should work, you only need one new which begins transaction in RR
isolation mode.

------
Regards,
Alexander Korotkov
Supabase