From: Matt Magoffin <postgresql.org@msqr.us>
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81.1.6\))
Subject: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO
 NOTHING
Message-Id: <087DA595-FB65-49F4-89E9-AE9F5CBF6E4C@msqr.us>
Date: Thu, 30 Apr 2026 11:07:14 +1200
To: pgsql-general@lists.postgresql.org
Archived-At: <https://www.postgresql.org/message-id/087DA595-FB65-49F4-89E9-AE9F5CBF6E4C%40msqr.us>
Precedence: bulk

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 =3D 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=20

INSERT 0 1

and indeed there are 2 rows now in that session:

SELECT * FROM txtest;
 id=20
----
  1
  2

This is what caught be off guard, as I had been thinking the INSERT =
would block until the first session=E2=80=99s transaction finished. Now, =
back in session #1, I run:

DELETE FROM txtest WHERE ID =3D 1; COMMIT;

Now in both sessions there is 1 row, with =E2=80=9C2=E2=80=9D, where I =
had been hoping to end up with both =E2=80=9C1=E2=80=9D and =E2=80=9C2=E2=80=
=9D after the INSERT waited for the SELECT =E2=80=A6 FOR UPDATE to =
complete first.

If I change session #1=E2=80=99s query from SELECT =E2=80=A6 FOR UPDATE =
to an immediate DELETE, I get what I expected, i.e.

BEGIN; DELETE FROM txtest WHERE id =3D 1;

Then in session #1 the same INSERT =E2=80=A6 ON CONFLICT DO NOTHING =
statement blocks until session #1 commits, and it results in=20

INSERT 0 2

The difference in transaction behaviour between SELECT =E2=80=A6 FOR =
UPDATE and DELETE I did not understand from the documentation, so would =
appreciate any confirmation/clarification/insight on what I=E2=80=99m =
seeing so I can better understand.

Thank you,
Matt Magoffin