public inbox for [email protected]
help / color / mirror / Atom feedConfirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
3+ messages / 2 participants
[nested] [flat]
* Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
@ 2026-04-29 23:07 Matt Magoffin <[email protected]>
2026-04-29 23:37 ` Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING Adrian Klaver <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Matt Magoffin @ 2026-04-29 23:07 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
2026-04-29 23:07 Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING Matt Magoffin <[email protected]>
@ 2026-04-29 23:37 ` Adrian Klaver <[email protected]>
2026-04-30 02:48 ` Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING Matt Magoffin <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Adrian Klaver @ 2026-04-29 23:37 UTC (permalink / raw)
To: Matt Magoffin <[email protected]>; [email protected]
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
[email protected]
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING
2026-04-29 23:07 Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING Matt Magoffin <[email protected]>
2026-04-29 23:37 ` Re: Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING Adrian Klaver <[email protected]>
@ 2026-04-30 02:48 ` Matt Magoffin <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Matt Magoffin @ 2026-04-30 02:48 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: [email protected]
> On 30 Apr 2026, at 11:37 AM, Adrian Klaver <[email protected]> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-04-30 02:48 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-29 23:07 Confirmation on concurrent SELECT FOR UPDATE with ON CONFLICT DO NOTHING Matt Magoffin <[email protected]>
2026-04-29 23:37 ` Adrian Klaver <[email protected]>
2026-04-30 02:48 ` Matt Magoffin <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox