Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING

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

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.

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

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

On Thu, 2026-04-30 at 14:48 +1200, 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.

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?

I admit that the behavior difference surprised me too.

I tried to spot the difference, and using the pageinspect extension I see the following:

- after the DELETE, "infomask" is set to 0x0100
- after the SELECT ... FOR UPDATE, "infomask" is set to 0x01c0

Now 0x0100 is HEAP_XMIN_COMMITTED, a hint bit.

The difference is that in the SELECT ... FOR UPDATE case, there are also HEAP_XMAX_EXCL_LOCK
and HEAP_XMAX_LOCK_ONLY set, which means that "xmax" stores an exclusive row lock.

In other words, after the DELETE, there is *no* row lock on the row. "xmax" stores
the transaction ID of the transaction that deleted the row - only that transaction is still
active, and its effects not yet visible.

So I'd say that the documentation is not quite accurate. Really, the DELETE does not place
a row lock on the row.

That must account for the behavior difference: after the SELECT ... FOR UPDATE, the
INSERT ... ON CONFLICT interprets the row lock as a conflict and moves on, while in the
DELETE case it sees no conflict (yet), but has to wait for the transaction to complete before
it knows how to proceed.

I cannot say if that is intentional; as I said initially, I am surprised too.

Yours,
Laurenz Albe

On 30 Apr 2026, at 6:42 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

So I'd say that the documentation is not quite accurate. Really, the DELETE does not place
a row lock on the row.

That must account for the behavior difference: after the SELECT ... FOR UPDATE, the
INSERT ... ON CONFLICT interprets the row lock as a conflict and moves on, while in the
DELETE case it sees no conflict (yet), but has to wait for the transaction to complete before
it knows how to proceed.

I cannot say if that is intentional; as I said initially, I am surprised too.

Thank you for your additional insights, Laurenz.

Kind regards,
Matt

On Fri, 2026-05-01 at 07:07 +1200, Matt Magoffin wrote:

On 30 Apr 2026, at 6:42 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

So I'd say that the documentation is not quite accurate.  Really, the DELETE does not place
a row lock on the row.

That must account for the behavior difference: after the SELECT ... FOR UPDATE, the
INSERT ... ON CONFLICT interprets the row lock as a conflict and moves on, while in the
DELETE case it sees no conflict (yet), but has to wait for the transaction to complete before
it knows how to proceed.

I cannot say if that is intentional; as I said initially, I am surprised too.

Thank you for your additional insights, Laurenz.

Also, the behavior difference only occurs with ON CONFLICT DO NOTHING.
If you use ON CONFLICT ... DO UPDATE ..., the update will block.
That makes the behavior difference somewhat less bad in my eyes.

Yours,
Laurenz Albe

On 1 May 2026, at 7:35 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

Also, the behavior difference only occurs with ON CONFLICT DO NOTHING.
If you use ON CONFLICT ... DO UPDATE ..., the update will block.
That makes the behavior difference somewhat less bad in my eyes.

Yes, I had noticed that as well. In my case my goal is to both block and “do nothing” if after blocking a matching row is found. If this behaviour isn’t expected, I thought I could change to

ON CONFLICT DO UPDATE SET id = EXCLUDED.id

to essentially “do nothing” but I thought I would incur an actual update and I wanted to avoid the churn I presumed that would include.

Kind regards,
Matt