Some more examples
Here are some further minimal examples that might be of interest in addition to the one provided in A.H.'s answer. I'm using this test setup to conveniently run them on PostgreSQL 13.5.
Common setup:
0: CREATE TABLE "MyInt"(i INTEGER);
0: INSERT INTO foo VALUES(0);
The first thing I'd like to point out is that even read-only statements like SELECT can matter.
For example, this example does not raise any errors:
0: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
1: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
0: UPDATE "MyInt" SET i = 1
0: COMMIT
1: UPDATE "MyInt" SET i = 2
1: COMMIT
The only difference between this example and the one in the aforementioned answer is that the one in that answer is that in this one thread 1 does the UPDATE after the COMMIT of thread 0. TODO why does it matter?
However, if we simply add a SELECT from thread 1 before thread 0 commits as in:
0: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
1: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
0: UPDATE "MyInt" SET i = 1
1: SELECT * FROM "MyInt"
0: COMMIT
1: UPDATE "MyInt" SET i = 2
1: COMMIT
then the last UPDATE blows up with:
could not serialize access due to concurrent update
TODO understand exactly why the SELECT matters/how it is tracked by PostgreSQL. What seems to happen is that PostgreSQL makes considerations of type:
could such database serialization property possibly be violated if the client had such and such information?
which requires it to also track SELECT statements.
Example that blows up only on SERIALIZABLE
Here is another interesting example acting on two different rows. This example blows up only on SERIALIZABLE, but not on REPEATABLE READ (TODO why):
INSERT INTO "MyInt" VALUES (0)
INSERT INTO "MyInt" VALUES (10)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
UPDATE "MyInt" SET i = 1 WHERE i = 0
SELECT * FROM "MyInt"
COMMIT
UPDATE "MyInt" SET i = 11 WHERE i = 10
COMMIT
The error message in this case is:
could not serialize access due to read/write dependencies among transactions
Unfortunately, after hours staring at https://www.postgresql.org/docs/13/transaction-iso.html#XACT-REPEATABLE-READ I'm still unable to precisely explain why some of those blow up and others don't, but I felt that the examples were of enough interest to publish regardless, maybe someone can clarify the exact sequence of steps taken by PostgreSQL in future edits/comments.
Examples from the tests
Another source of many such examples is, unsurprisingly, the in-tree tests under src/test/isolation, and they are quite readable too. A grep for could not serialize
Files under src/test/isolation/specs/*.spec determine which steps are to be done, and corresponding files under src/test/isolation/expected/*.out contain the exact expected psql raw output. So we can just read the .out files to see many full examples of failures.
For example src/test/isolation/expected/insert-conflict-do-nothing-2.out contains:
starting permutation: beginrr1 beginrr2 donothing1 donothing2 c1 c2 show
step beginrr1: BEGIN ISOLATION LEVEL REPEATABLE READ;
step beginrr2: BEGIN ISOLATION LEVEL REPEATABLE READ;
step donothing1: INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING;
step donothing2: INSERT INTO ints(key, val) VALUES(1, 'donothing2'), (1, 'donothing3') ON CONFLICT DO NOTHING; <waiting ...>
step c1: COMMIT;
step donothing2: <... completed>
ERROR: could not serialize access due to concurrent update
step c2: COMMIT;
step show: SELECT * FROM ints;
key|val
---+----------
1|donothing1
(1 row)
The only thing not shown clearly is the table creation statement which we can see in the corresponding .spec file src/test/isolation/specs/insert-conflict-do-nothing-2.spec:
CREATE TABLE ints (key int, val text, PRIMARY KEY (key) INCLUDE (val));
From which we learn that INSERT INTO ON CONFLICT DO NOTHING can also cause serialization failures.
Some of the tests also link to a paper: https://www.cs.umb.edu/~poneil/ROAnom.pdf A Read-Only Transaction Anomaly Under Snapshot Isolation. You know a software is serious when the bug reports are addressed by papers.
