Subject: Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING

From: Laurenz Albe <[email-masked]>
Date: Thu, 30 Apr 2026 08:42:28 +0200

On Thu, 2026-04-30 at 14:48 +1200, Matt Magoffin wrote:
> > On 30 Apr 2026, at 11:37 AM, Adrian Klaver <[email-masked]> 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

---

From: Matt Magoffin <[email-masked]>
Date: Fri, 1 May 2026 07:07:30 +1200

> On 30 Apr 2026, at 6:42 PM, Laurenz Albe <[email-masked]> wrote:
> 

Thank you for your additional insights, Laurenz.

Kind regards,
Matt

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 30 Apr 2026 21:35:43 +0200

> Thank you for your additional insights, Laurenz.

Also, the behavior difference only occurs with ON CONFLICT DO NOTHING.
If you use ON CONFLICT ... DO UPDATE ..., the update will block.
That makes the behavior difference somewhat less bad in my eyes.

Yours,
Laurenz Albe

---

From: Matt Magoffin <[email-masked]>
Date: Tue, 5 May 2026 07:53:39 +1200

> On 1 May 2026, at 7:35 AM, Laurenz Albe <[email-masked]> wrote:
> 

Yes, I had noticed that as well. In my case my goal is to both block and “do nothing” if after blocking a matching row is found. If this behaviour isn’t expected, I thought I could change to

ON CONFLICT DO UPDATE SET id = EXCLUDED.id

to essentially “do nothing” but I thought I would incur an actual update and I wanted to avoid the churn I presumed that would include.

Kind regards,
Matt