Hello, I was hoping to confirm some transaction behaviour I am seeing (in Postgres 17) in read-committed isolation mode
thatcaught 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
waitedfor 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
