Message-ID: <bd0599f050972d15202ba30ab872972a050ba8e0.camel@cybertec.at>
Subject: Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT
 DO NOTHING
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Matt Magoffin <postgresql.org@msqr.us>, Adrian Klaver
	 <adrian.klaver@aklaver.com>
Cc: pgsql-general@lists.postgresql.org
Date: Thu, 30 Apr 2026 08:42:28 +0200
In-Reply-To: <8BCF50C4-D36B-4453-9B09-AA717AE6F563@msqr.us>
References: <087DA595-FB65-49F4-89E9-AE9F5CBF6E4C@msqr.us>
	 <ba59699c-a5af-4a27-affd-6836dc813047@aklaver.com>
	 <8BCF50C4-D36B-4453-9B09-AA717AE6F563@msqr.us>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.3 (3.58.3-1.fc43) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/bd0599f050972d15202ba30ab872972a050ba8e0.camel%40cybertec.at>
Precedence: bulk

On Thu, 2026-04-30 at 14:48 +1200, Matt Magoffin wrote:
> > On 30 Apr 2026, at 11:37=E2=80=AFAM, Adrian Klaver <adrian.klaver@aklav=
er.com> wrote:
> >=20
> > So in your first case the INSERT is never done and there is no lock for=
 the INSERT in any case.
>=20
> Thanks for the info, Adrian. And so for my 2nd case, where the INSERT is =
blocked by the
> DELETE statement, I see the docs say
>=20
> 	The FOR UPDATE lock mode is also acquired by any DELETE on a row=E2=80=
=A6
>=20
> But I am not finding the info that talks about why the INSERT =E2=80=A6 O=
N CONFLICT DO NOTHING does
> block until the DELETE finishes. I guess in my mind the SELECT =E2=80=A6 =
FOR UPDATE and DELETE were
> acquiring the same kind of row lock, so the behaviour of the INSERT would=
 be the same across both cases.
>=20
> I suppose what I=E2=80=99d be keen to confirm is that the blocking behavi=
our I get with the DELETE is
> expected behaviour, that I can count on. Do you know if that is true?

I admit that the behavior difference surprised me too.

I tried to spot the difference, and using the pageinspect extension I see t=
he following:

- after the DELETE, "infomask" is set to 0x0100
- after the SELECT ... FOR UPDATE, "infomask" is set to 0x01c0

Now 0x0100 is HEAP_XMIN_COMMITTED, a hint bit.

The difference is that in the SELECT ... FOR UPDATE case, there are also HE=
AP_XMAX_EXCL_LOCK
and HEAP_XMAX_LOCK_ONLY set, which means that "xmax" stores an exclusive ro=
w lock.

In other words, after the DELETE, there is *no* row lock on the row.  "xmax=
" stores
the transaction ID of the transaction that deleted the row - only that tran=
saction is still
active, and its effects not yet visible.

So I'd say that the documentation is not quite accurate.  Really, the DELET=
E does not place
a row lock on the row.

That must account for the behavior difference: after the SELECT ... FOR UPD=
ATE, the
INSERT ... ON CONFLICT interprets the row lock as a conflict and moves on, =
while in the
DELETE case it sees no conflict (yet), but has to wait for the transaction =
to complete before
it knows how to proceed.

I cannot say if that is intentional; as I said initially, I am surprised to=
o.

Yours,
Laurenz Albe