public inbox for [email protected]  

help / color / mirror / Atom feed

From: Matt Magoffin <[email protected]>
To: [email protected]
Subject: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
Date: Thu, 30 Apr 2026 11:07:14 +1200
Message-ID: <[email protected]> (raw)

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.

Thank you,
Matt Magoffin

view thread (3+ messages)  latest in thread

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