public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Matt Magoffin <[email protected]>
Cc: [email protected]
Subject: Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
Date: Wed, 29 Apr 2026 21:32:34 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>

On 4/29/26 7:48 PM, 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.

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
[email protected]

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]
  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