public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Matt Magoffin <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: [email protected]
Subject: Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
Date: Thu, 30 Apr 2026 08:42:28 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>

On Thu, 2026-04-30 at 14:48 +1200, Matt Magoffin wrote:
> > On 30 Apr 2026, at 11:37 AM, Adrian Klaver <[email protected]> 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: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
  In-Reply-To: <[email protected]>

* 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