Horton
Menu

Thread

  1. Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING

    Matt Magoffin <postgresql.org@msqr.us> — 2026-04-29T23:07:14Z

    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

  2. Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING

    Adrian Klaver <adrian.klaver@aklaver.com> — 2026-04-29T23:37:22Z

    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

  3. Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING

    Matt Magoffin <postgresql.org@msqr.us> — 2026-04-30T02:48:32Z

    > 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

  4. Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING

    Adrian Klaver <adrian.klaver@aklaver.com> — 2026-04-30T04:32:34Z

    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

  5. Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-04-30T06:42:28Z

    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

  6. Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING

    Matt Magoffin <postgresql.org@msqr.us> — 2026-04-30T19:07:30Z

    > 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

  7. Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-04-30T19:35:43Z

    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

  8. Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING

    Matt Magoffin <postgresql.org@msqr.us> — 2026-05-04T19:53:39Z

    
> 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