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

On 4/29/26 7:48 PM, Matt Magoffin wrote:
>
>> On 30 Apr 2026, at 11:37 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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.

This is beginning to get outside my level of understanding. As I see it
in the first case the below applies:

"SELECT FOR UPDATE will wait for a concurrent transaction that has run
any of those commands on the same row, and will then lock and return the
updated row (or no row, if the row was deleted). ..." where other
commands are "UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY
UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE".

In your case you where doing an INSERT and the ON CONFLICT DO NOTHING
meant a DELETE would not reached.

In the second case you locked with an explicit DELETE in the first
session which prevented the second session from determining whether the
ON CONFLICT DO NOTHING actually applied until the first session committed.

>
> 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?
>
> Cheers,
> Matt

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com