public inbox for pgsql-general@postgresql.org
help / color / mirror / Atom feedRe: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
4+ messages / 2 participants
[nested] [flat]
* Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
@ 2026-04-30 06:42 Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2026-04-30 06:42 UTC (permalink / raw)
To: Matt Magoffin <postgresql.org@msqr.us>; Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
@ 2026-04-30 19:07 Matt Magoffin <postgresql.org@msqr.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: Matt Magoffin @ 2026-04-30 19:07 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org
> On 30 Apr 2026, at 6:42 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> 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.
Thank you for your additional insights, Laurenz.
Kind regards,
Matt
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
@ 2026-04-30 19:35 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Matt Magoffin <postgresql.org@msqr.us>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2026-04-30 19:35 UTC (permalink / raw)
To: Matt Magoffin <postgresql.org@msqr.us>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org
On Fri, 2026-05-01 at 07:07 +1200, Matt Magoffin wrote:
> > On 30 Apr 2026, at 6:42 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> >
> > 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.
>
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
@ 2026-05-04 19:53 Matt Magoffin <postgresql.org@msqr.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 4+ messages in thread
From: Matt Magoffin @ 2026-05-04 19:53 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org
> On 1 May 2026, at 7:35 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> 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.
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-05-04 19:53 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-30 06:42 Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING Laurenz Albe <laurenz.albe@cybertec.at>
2026-04-30 19:07 ` Matt Magoffin <postgresql.org@msqr.us>
2026-04-30 19:35 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-05-04 19:53 ` Matt Magoffin <postgresql.org@msqr.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox