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
