The anomaly catalog
This table distinguishes demonstrated schedules from broader PostgreSQL 18 contracts. The concept catalog defines the anomaly names. The Scenarios below run on PostgreSQL 18.6, with plain SELECTs over transactional tables unless stated otherwise. READ UNCOMMITTED has READ COMMITTED behavior, as documented in the isolation manual.
D means the linked Scenario executes and asserts that particular schedule, not every possible schedule. M means a Documented contract in the linked PostgreSQL 18 manual. † means an Entailed guarantee without a direct Scenario at that level; the derivation is in How to use this table. Own writes and sequence operations are outside claims about concurrent table-row snapshot changes.
| Code | Anomaly or operation | READ COMMITTED | REPEATABLE READ | SERIALIZABLE |
|---|---|---|---|---|
| G0 | Dirty write, overwrite before the prior writer ends | D: second writer waits; M: row locks | † row locks | † row locks |
| G1a | Dirty read of another transaction's table change | M: excluded; D: READ UNCOMMITTED example | M: excluded | M: excluded |
| G1b | Intermediate read | D: draft excluded; † | † | † |
| G1c | Circular information flow through dirty reads | D: cross-reads excluded; † | † | † |
| OTV | Committed rows hidden by an uncommitted overwrite | D: committed versions retained; † | † | † |
| P2 | Non-repeatable plain SELECT due to concurrent commit | D: different values | M: excluded; D: stable values | M: excluded |
| G-single | Read skew across separate plain SELECTs | D: inconsistent total | D: consistent total; † | † |
| PMP | Phantom from a concurrent commit, plain SELECT | D: new matching row | M: excluded in PostgreSQL; D: new row excluded | M: excluded |
| P4 | In-transaction read-modify-write of a post-snapshot changed target | D: overwrite commits | M: conflict; D: 40001 and fresh retry | M: same target-row conflict rule |
| G2-item | Write skew, on-call count and separate-row writes | Not executed here | D: both commit, count zero | D: B fails, count one; M: serial equivalence |
| G2 | Predicate-based serialization anomaly beyond this on-call schedule | Not executed here | M: serialization anomalies possible | M: excluded among committed participating transactions |
| Fekete example | Read-only report anomaly | Not executed here | D: two receipts before third commits | D: cashier rejected; M: serial equivalence |
How to use this table
The Documented contracts come from Table 13.1 and the level descriptions: READ COMMITTED plain SELECTs exclude uncommitted concurrent changes; REPEATABLE READ uses a stable snapshot plus own writes; SERIALIZABLE adds serial-equivalence monitoring. The row-lock manual documents waits for concurrent writers and locking reads. These contracts apply to PostgreSQL 18 and the operations specified, not every engine or every SQL function.
The † cells have no direct execution at the stated level. Their derivations are:
- G0: the documented row locks on these UPDATEs prevent overwriting an uncommitted target at each level, regardless of whether a later committed version can be updated.
- G1b and the dirty-read G1c cycle: excluding uncommitted versions excludes a draft that is replaced before commit and cross-reads of those uncommitted writes. This does not exclude every dependency cycle or prove serializability at READ COMMITTED.
- OTV: a plain SELECT's snapshot selects committed versions, so uncommitted overwrites cannot hide the earlier committed versions. Separate READ COMMITTED statements may use different snapshots; a later committed partial overwrite can change a subset.
- Read skew: a stable snapshot excludes intervening concurrent commits between these plain SELECTs. Own writes remain visible, so the guarantee assumes the reader does not itself change the observed accounts or mix in nontransactional effects.
An existence demonstration can show that a level permits an anomaly. Its absence in one run cannot establish impossibility. The table does not claim full Hermitage coverage, predicate-write-skew execution, or universal detection of stale application values read outside the updating transaction.
For the displayed read-modify-write race, see fixing lost updates. A cross-row rule needs each serial transaction to preserve it and relevant writers to use SERIALIZABLE, or another suitable coordination mechanism. On 40001, retry the whole transaction with fresh reads and decisions or return a controlled failure; do not report a commit.
The guarantees you get for free
The following executions illustrate the contracts and derivations above. They use READ COMMITTED; statements with stronger-level guarantees in the table need their manual support or marked derivation, not just these examples.
Dirty writes (G0)
B's first UPDATE waits until A commits. Both transactions update both rows in the displayed order, so the final asserted prices are B's. A mixed final state is not the definition of a dirty write; overwriting an uncommitted write is.
Both batch jobs update the same two rows. A dirty write would overwrite a row before its previous writer commits or rolls back; the wait below prevents that overwrite in this schedule.
A> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN
B> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN
A> UPDATE items SET price = 11 WHERE id = 1;
UPDATE 1B wants the same row. Even at READ COMMITTED it must wait for A's lock.
B> UPDATE items SET price = 12 WHERE id = 1;
⏳ B is waiting for a lock…
A> UPDATE items SET price = 21 WHERE id = 2;
UPDATE 1
A> COMMIT; -- releases both row locks
COMMIT
⏵ B resumes:
UPDATE 1
B> UPDATE items SET price = 22 WHERE id = 2;
UPDATE 1
B> COMMIT;
COMMIT
A> SELECT id, price FROM items ORDER BY id; -- both final prices are B's in this order
id | price
----+-------
1 | 12
2 | 22
(2 rows)Verified against PostgreSQL 18.6 · Run it yourself · Scenario source
Intermediate reads (G1b)
B reads 100 while A's draft 999 is uncommitted, then 110 after A commits its final value. An older REPEATABLE READ snapshot could still see 100 after that commit.
A> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN
A> UPDATE accounts SET balance = 999 WHERE id = 1; -- a working draft — A isn't done yet
UPDATE 1
B> SELECT balance FROM accounts WHERE id = 1; -- the draft 999 is invisible
balance
---------
100
(1 row)
A> UPDATE accounts SET balance = 110 WHERE id = 1; -- A settles on the final value…
UPDATE 1
A> COMMIT;
COMMIT
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
110
(1 row)B's two SELECTs returned 100 and 110, never A's uncommitted draft 999. A transaction with an older stable snapshot need not see 110 yet.
Verified against PostgreSQL 18.6 · Run it yourself · Scenario source
Circular information flow (G1c)
Neither transaction's plain SELECT reads the other's uncommitted UPDATE. Both see 100, then both writes commit. This schedule is not serializable: excluding dirty-read cycles does not exclude the read/write dependency cycle created by those old reads.
A adjusts alice's balance while B adjusts bob's — then each peeks at the other's row. If both saw the other's uncommitted write, information would flow in a circle: A → B → A. No serial order can do that.
A> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN
B> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN
A> UPDATE accounts SET balance = 111 WHERE id = 1;
UPDATE 1
B> UPDATE accounts SET balance = 222 WHERE id = 2;
UPDATE 1
A> SELECT balance FROM accounts WHERE id = 2; -- B's uncommitted 222 is invisible to A
balance
---------
100
(1 row)
B> SELECT balance FROM accounts WHERE id = 1; -- and A's uncommitted 111 is invisible to B
balance
---------
100
(1 row)
A> COMMIT;
COMMIT
B> COMMIT;
COMMIT
A> SELECT id, balance FROM accounts ORDER BY id; -- both writes landed — but neither transaction ever saw the other's
id | balance
----+---------
1 | 111
2 | 222
(2 rows)Verified against PostgreSQL 18.6 · Run it yourself · Scenario source
Observed transaction vanishes (OTV)
C sees both of A's committed balances while B's overwrites remain uncommitted, then both of B's balances after B commits. This does not promise that committed values remain forever unchanged or that multiple statements share one snapshot.
A rewrites both balances. B will overwrite one of them right after.
A> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN
A> UPDATE accounts SET balance = 11 WHERE id = 1;
UPDATE 1
A> UPDATE accounts SET balance = 19 WHERE id = 2;
UPDATE 1
B> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN
B> UPDATE accounts SET balance = 12 WHERE id = 1;
⏳ B is waiting for a lock…
A> COMMIT; -- A's lock released — B's overwrite of alice proceeds
COMMIT
⏵ B resumes:
UPDATE 1C now watches from the side. B holds an uncommitted overwrite of alice — but A's committed transaction must still be visible in full.
C> SELECT id, balance FROM accounts ORDER BY id; -- A's writes appear together — 11 AND 19
id | balance
----+---------
1 | 11
2 | 19
(2 rows)
B> UPDATE accounts SET balance = 18 WHERE id = 2;
UPDATE 1
C> SELECT id, balance FROM accounts ORDER BY id; -- B's drafts change nothing for C
id | balance
----+---------
1 | 11
2 | 19
(2 rows)
B> COMMIT;
COMMIT
C> SELECT id, balance FROM accounts ORDER BY id; -- only now does C move on — to ALL of B, atomically
id | balance
----+---------
1 | 12
2 | 18
(2 rows)Verified against PostgreSQL 18.6 · Run it yourself · Scenario source
Further reading
- Hermitage: a separate collection of isolation tests
- Anomalies by engine
- The same catalog for MySQL