Lesson 16 · Transactions · Module 2

The Anomalies

Isolation levels are not named after what they give you. They are named after what they forbid — and the only way to understand one is to hold the specific interleaving it was invented to rule out. There are six worth knowing, and exactly one of them survives the level most production systems consider "safe enough".

The win in this lesson: shown two transactions interleaving, you will be able to name the anomaly and say which isolation level would have prevented it — including the case where the answer is "none of the ones you are running".

1. The levels are a list of prohibitions

Lesson 15 covered the mechanism: MVCC, snapshots, no read locks. This lesson covers the consequences. The SQL standard defines four isolation levels by naming three phenomena they must not permit; PostgreSQL's documentation adds a fourth and publishes the whole grid.

Two cells in that table are the whole lesson. The "Allowed, but not in PG" cells say Postgres is stricter than the standard requires. The Possible in the last column of the Repeatable read row says something far more interesting: a transaction can have a perfectly stable, perfectly consistent view of the database and still produce a result that no serial ordering could have produced.

"In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e., PostgreSQL's Read Uncommitted mode behaves like Read Committed. This is because it is the only sensible way to map the standard isolation levels to PostgreSQL's multiversion concurrency control architecture."

PostgreSQL 18 documentation, Transaction Isolation

So there are three levels to reason about, not four. Everything below is a two-transaction interleaving you can read top to bottom, and the level that kills it.

2. Dirty read — reading work that never happened

"dirty read — A transaction reads data written by a concurrent uncommitted transaction."

PostgreSQL 18, Transaction Isolation

T1                                     T2
-------------------------------------  --------------------------------------
BEGIN;
UPDATE accounts SET balance = 0
  WHERE id = 7;          -- not committed
                                       BEGIN;
                                       SELECT balance FROM accounts
                                         WHERE id = 7;      -- reads 0
                                       -- decides the account is empty
ROLLBACK;                -- balance is 100 again
                                       COMMIT;

T2 acted on a number that, by the end, had never existed in any committed state of the database. This is the one anomaly you can stop worrying about: Postgres does not implement Read Uncommitted, so a dirty read is not reachable at any level. Under MVCC it is not even cheap to allow — a transaction reads the row version its snapshot says is visible, and an uncommitted version never is.

3. Non-repeatable read — the same question, two answers

"nonrepeatable read — A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read)."

PostgreSQL 18, Transaction Isolation

T1  (READ COMMITTED)                   T2
-------------------------------------  --------------------------------------
BEGIN;
SELECT price FROM items
  WHERE id = 3;          -- 100
                                       BEGIN;
                                       UPDATE items SET price = 120
                                         WHERE id = 3;
                                       COMMIT;
SELECT price FROM items
  WHERE id = 3;          -- 120   the same row, a different answer
COMMIT;

Nothing is corrupted. T1 simply computed the total with one price and the line item with another, and the invoice does not add up. The cause is structural: Read Committed takes a fresh snapshot for every statement, so two statements in one transaction are two different moments in time.

The level that stops it: Repeatable Read. One snapshot is taken at the start and held for the whole transaction, so the second SELECT returns 100 — the value as of T1's start — no matter what T2 committed in between. The table above says Not possible, and it means it.

4. Phantom read — the same question, a different set

"phantom read — A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction."

PostgreSQL 18, Transaction Isolation

T1  (READ COMMITTED)                   T2
-------------------------------------  --------------------------------------
BEGIN;
SELECT count(*) FROM orders
  WHERE status = 'open';  -- 12
                                       BEGIN;
                                       INSERT INTO orders (status)
                                         VALUES ('open');
                                       COMMIT;
SELECT id FROM orders
  WHERE status = 'open';  -- 13 rows   a row that was not there
COMMIT;

The difference from a non-repeatable read is that no row T1 read was modified. A new row appeared inside the range T1 had already counted — which is why row-level locking cannot help you: you cannot lock a row that does not exist yet.

The level that stops it: in Postgres, Repeatable Read. The standard only requires Serializable to forbid phantoms, and Postgres exceeds that:

"The table also shows that PostgreSQL's Repeatable Read implementation does not allow phantom reads. This is acceptable under the SQL standard because the standard specifies which anomalies must not occur at certain isolation levels; higher guarantees are acceptable."

PostgreSQL 18, Transaction Isolation

A snapshot is a snapshot. A row inserted after it was taken is invisible whether it is a new version of an old row or an entirely new one, so Postgres gets phantom protection for free from the same machinery that gives it repeatable reads. Remember this when you read a textbook: the textbook's Repeatable Read permits phantoms; the one you are running does not.

5. Lost update — two read-modify-write cycles, one survivor

The first anomaly not in the table, and the first one that destroys data rather than confusing a reader. Both transactions read a value, compute a new one in application code, and write it back.

What stops it. Three different things, and they are not equivalent:

Do the arithmetic in SQL. UPDATE items SET stock = stock - 1 WHERE id = 3 is safe even at Read Committed, and the reason is a specific rule about what a writer does when it collides:

"In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress)… If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition."

PostgreSQL 18, Read Committed Isolation Level

Raise the level to Repeatable Read. The second writer does not silently win; it is refused:

"But if the first updater commits (and actually updated or deleted the row, not just locked it) then the repeatable read transaction will be rolled back with the message ERROR: could not serialize access due to concurrent update because a repeatable read transaction cannot modify or lock rows changed by other transactions after the repeatable read transaction began."

PostgreSQL 18, Repeatable Read Isolation Level

Lock the row on the way in with SELECT … FOR UPDATE, which serialises the two read-modify-write cycles by making the second one wait. This works, and it is the option that costs you concurrency rather than costing you a retry.

6. Write skew — the one that survives snapshot isolation

Everything above is prevented by turning a dial. Write skew is the anomaly that is still there after you have turned the dial as far as most teams ever turn it, and it is the reason the last column of Table 13.1 says Possible for Repeatable read.

The classic shape: a rule that spans several rows, enforced by each transaction reading those rows and then writing a different one.

Why snapshot isolation cannot see it

Repeatable Read in Postgres is snapshot isolation, and its conflict detection is about writes. Two writers touching one row is a detectable collision; two writers touching two rows is not a collision at all. The damage was done by the reads — each transaction read a premise that the other then falsified — and MVCC reads take no locks and leave no record.

"The Repeatable Read mode provides a rigorous guarantee that each transaction sees a completely stable view of the database. However, this view will not necessarily always be consistent with some serial (one at a time) execution of concurrent transactions of the same level."

PostgreSQL 18, Repeatable Read Isolation Level

Read that twice. Stable and consistent with a serial order are different properties, and Repeatable Read only promises the first. Which is precisely the definition of the fourth phenomenon in the table:

"serialization anomaly — The result of successfully committing a group of transactions is inconsistent with all possible orderings of running those transactions one at a time."

PostgreSQL 18, Transaction Isolation

Run Alice first: Bob's count returns 1, and Bob stays. Run Bob first: Alice stays. There is no serial order that empties the rota. The interleaved run produced an outcome that serial execution cannot.

The documentation's own version

Postgres ships an example of exactly this shape, with the numbers, so you can check yourself against it. Starting from a table mytab holding class 1 with values 10 and 20, and class 2 with values 100 and 200:

"Suppose that serializable transaction A computes SELECT SUM(value) FROM mytab WHERE class = 1; and then inserts the result (30) as the value in a new row with class = 2. Concurrently, serializable transaction B computes SELECT SUM(value) FROM mytab WHERE class = 2; and obtains the result 300, which it inserts in a new row with class = 1. Then both transactions try to commit. If either transaction were running at the Repeatable Read isolation level, both would be allowed to commit; but since there is no serial order of execution consistent with the result, using Serializable transactions will allow one transaction to commit and will roll the other back with this message: ERROR: could not serialize access due to read/write dependencies among transactions. This is because if A had executed before B, B would have computed the sum 330, not 300, and similarly the other order would have resulted in a different sum computed by A."

PostgreSQL 18, Serializable Isolation Level

"If either transaction were running at the Repeatable Read isolation level, both would be allowed to commit" is the sentence to carry out of this lesson.

The phantom flavour, where there is no row to lock

Write skew gets worse when the thing you are checking does not exist yet. Two transactions each run SELECT count(*) FROM bookings WHERE room = 5 AND slot OVERLAPS …, each see zero, and each insert a booking. There is no row to put FOR UPDATE on, because the conflict is with a row the other transaction has not written. The docs warn about leaning on that lock even when a row does exist:

"Also of note to those converting from other environments is the fact that SELECT FOR UPDATE does not ensure that a concurrent transaction will not update or delete a selected row. To do that in PostgreSQL you must actually update the row, even if no values need to be changed."

PostgreSQL 18 documentation, Data Consistency Checks at the Application Level

What actually stops it

Serializable, and the reason it works is that it stops ignoring reads:

"Serializable transactions are just Repeatable Read transactions which add nonblocking monitoring for dangerous patterns of read/write conflicts. When a pattern is detected which could cause a cycle in the apparent order of execution, one of the transactions involved is rolled back to break the cycle."

PostgreSQL 18, Data Consistency Checks at the Application Level

Note nonblocking. This is not table locking wearing a new name. Serializable takes the same snapshot Repeatable Read takes and runs at the same speed until something goes wrong; the price is paid as aborts at commit time, not as waiting. That changes the engineering question from "can we afford the locks?" to "do we have a retry loop?".

7. The standard versus what Postgres does

Three places where the received wisdom and the implementation disagree, and all three cost people production incidents:

The pattern is that the standard says what must not happen, never what must. An implementation is free to be stricter, and Postgres is stricter in two of the three cells that matter. Your job when reading a vendor's isolation docs is to find where they were stricter and where they merely met the floor.

8. Residual risk

Serializable is not free of obligations, it just moves them. Every transaction can fail at commit with SQLSTATE 40001, including transactions that did nothing unusual:

"It is important that an environment which uses this technique have a generalized way of handling serialization failures (which always return with an SQLSTATE value of '40001'), because it will be very hard to predict exactly which transactions might contribute to the read/write dependencies and need to be rolled back to prevent serialization anomalies."

PostgreSQL 18, Serializable Isolation Level

Three consequences worth holding on to.

A retry must replay the whole transaction, not the failed statement. The snapshot is invalid; re-running one statement inside the dead transaction is meaningless. And the retry is a retry — everything from Lesson 04 applies, including that a naive tight loop turns a contention problem into an outage.

Side effects inside a transaction become bugs. Charge a card, send an email, or publish to a queue between BEGIN and COMMIT, and a serialization failure replays it. The external world does not roll back.

Mixing levels silently disables the protection. The guarantee is conditional:

"If the Serializable transaction isolation level is used for all writes and for all reads which need a consistent view of the data, no other effort is required to ensure consistency. […] It may be a good idea to set default_transaction_isolation to serializable."

PostgreSQL 18, Data Consistency Checks at the Application Level

A single Read Committed writer touching the same rows can reintroduce the skew that Serializable was protecting you from, and nothing will report it. Isolation is a property of the set of transactions, not of the one you were careful about.

9. Check yourself

10. Back to your world

Find the places in your codebase where application code reads a value, decides something, and writes based on that decision. Every one of them is a lost update or a write skew waiting for traffic. Then ask the sharper question: which of your invariants span more than one row? "At most one primary", "the balance never goes negative", "no two bookings overlap", "there is always one admin left" — those are the write-skew candidates, and no amount of Repeatable Read protects them.

For each one, pick deliberately: Serializable plus a retry loop, an exclusion or unique constraint that turns the invariant into a row the database can collide on, or an explicit lock that costs concurrency. Write down which you chose and why. A design review that says "we use transactions" has not answered the question.