public inbox for pgsql-general@postgresql.org  

help / color / mirror / Atom feed

From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Matt Magoffin <postgresql.org@msqr.us>
To: pgsql-general@lists.postgresql.org
Subject: Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
Date: Wed, 29 Apr 2026 16:37:22 -0700
Message-ID: <ba59699c-a5af-4a27-affd-6836dc813047@aklaver.com> (raw)
In-Reply-To: <087DA595-FB65-49F4-89E9-AE9F5CBF6E4C@msqr.us>
References: <087DA595-FB65-49F4-89E9-AE9F5CBF6E4C@msqr.us>

On 4/29/26 4:07 PM, Matt Magoffin wrote:
> Hello, I was hoping to confirm some transaction behaviour I am seeing (in Postgres 17) in read-committed isolation mode that caught me off guard is, in fact, expected. First some setup:
> 
> CREATE TABLE txtest (id INTEGER NOT NULL PRIMARY KEY);
> INSERT INTO txtest (id) VALUES (1);
> 
> Then in one session, I run:
> 
> BEGIN; SELECT * FROM txtest WHERE id = 1 FOR UPDATE;
> 
> Then, in a different session, I run:
> 
> INSERT INTO txtest
> SELECT id
> FROM (VALUES
> 	(1),
> 	(2)
> ) AS t(id)
> ON CONFLICT
> DO NOTHING;
> 
> This completes immediately, with
> 
> INSERT 0 1
> 
> and indeed there are 2 rows now in that session:
> 
> SELECT * FROM txtest;
>   id
> ----
>    1
>    2
> 
> This is what caught be off guard, as I had been thinking the INSERT would block until the first session’s transaction finished. Now, back in session #1, I run:
> 
> DELETE FROM txtest WHERE ID = 1; COMMIT;
> 
> Now in both sessions there is 1 row, with “2”, where I had been hoping to end up with both “1” and “2” after the INSERT waited for the SELECT … FOR UPDATE to complete first.
> 
> If I change session #1’s query from SELECT … FOR UPDATE to an immediate DELETE, I get what I expected, i.e.
> 
> BEGIN; DELETE FROM txtest WHERE id = 1;
> 
> Then in session #1 the same INSERT … ON CONFLICT DO NOTHING statement blocks until session #1 commits, and it results in
> 
> INSERT 0 2
> 
> The difference in transaction behaviour between SELECT … FOR UPDATE and DELETE I did not understand from the documentation, so would appreciate any confirmation/clarification/insight on what I’m seeing so I can better understand.

 From here:
https://www.postgresql.org/docs/17/explicit-locking.html#LOCKING-ROWS

"FOR UPDATE

     FOR UPDATE causes the rows retrieved by the SELECT statement to be 
locked as though for update. This prevents them from being locked, 
modified or deleted by other transactions until the current transaction 
ends. That is, other transactions that attempt UPDATE, DELETE, SELECT 
FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY 
SHARE of these rows will be blocked until the current transaction ends; 
conversely, ..."

Nothing about an INSERT.

And from here:

https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative 
action.

So in your first case the INSERT is never done and there is no lock for 
the INSERT in any case.

> 
> Thank you,
> Matt Magoffin
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, postgresql.org@msqr.us, pgsql-general@lists.postgresql.org
  Subject: Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
  In-Reply-To: <ba59699c-a5af-4a27-affd-6836dc813047@aklaver.com>

* 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