Feedback-ID: i76984098:Fastmail
Message-ID: <c73a6a92-a5de-4350-923e-d7176134efa8@aklaver.com>
Date: Wed, 29 Apr 2026 21:32:34 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO
 NOTHING
To: Matt Magoffin <postgresql.org@msqr.us>
Cc: pgsql-general@lists.postgresql.org
References: <087DA595-FB65-49F4-89E9-AE9F5CBF6E4C@msqr.us>
 <ba59699c-a5af-4a27-affd-6836dc813047@aklaver.com>
 <8BCF50C4-D36B-4453-9B09-AA717AE6F563@msqr.us>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <8BCF50C4-D36B-4453-9B09-AA717AE6F563@msqr.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/c73a6a92-a5de-4350-923e-d7176134efa8%40aklaver.com>
Precedence: bulk

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

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.klaver@aklaver.com