public inbox for pgsql-general@postgresql.org  

help / color / mirror / Atom feed

From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Matt Magoffin <postgresql.org@msqr.us>
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
Date: Thu, 30 Apr 2026 08:42:28 +0200
Message-ID: <bd0599f050972d15202ba30ab872972a050ba8e0.camel@cybertec.at> (raw)
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>

On Thu, 2026-04-30 at 14:48 +1200, Matt Magoffin wrote:
> > On 30 Apr 2026, at 11:37 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> > 
> > So in your first case the INSERT is never done and there is no lock for the INSERT in any case.
> 
> 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
> 
> 	The FOR UPDATE lock mode is also acquired by any DELETE on a row…
> 
> But I am not finding the info that talks about why the INSERT … ON CONFLICT DO NOTHING does
> block until the DELETE finishes. I guess in my mind the SELECT … 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.
> 
> I suppose what I’d be keen to confirm is that the blocking behaviour 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 the 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 HEAP_XMAX_EXCL_LOCK
and HEAP_XMAX_LOCK_ONLY set, which means that "xmax" stores an exclusive row 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 transaction is still
active, and its effects not yet visible.

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

That must account for the behavior difference: after the SELECT ... FOR UPDATE, 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 too.

Yours,
Laurenz Albe

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-general@postgresql.org
  Cc: laurenz.albe@cybertec.at, postgresql.org@msqr.us, adrian.klaver@aklaver.com, pgsql-general@lists.postgresql.org
  Subject: Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
  In-Reply-To: <bd0599f050972d15202ba30ab872972a050ba8e0.camel@cybertec.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox