Isolation levels, and what each one lets through

You have four level names, a grid of ticks and crosses, and nothing in it that tells you which row to pick. The grid is not a difficulty setting you slide towards safe until performance complains. Each column is a contract, and what a contract lists is what the database is still allowed to do to you. So read it backwards: name the thing that would corrupt your data, find the levels that forbid it, take the cheapest of those.

The anomalies, named#

Every one of these is two sessions and four statements. Written out that way they stop being vocabulary.

Dirty read. T1 updates account 5088 to a balance of 40 and does not commit. T2 reads the row and sees 40. T1 rolls back. T2 is now holding a number that no committed state of the database ever contained — and the value was not wrong when it was read, it simply stopped existing.

Non-repeatable read. T1 reads account 5088 and gets 100. T2 updates that row to 40 and commits. T1 reads the same row again and gets 40. One transaction, one row, two answers — so any check T1 made on the first value is worthless by the time it acts on it.

Phantom read. T1 runs SELECT count(*) FROM orders WHERE tenant = 7 and gets 12. T2 inserts an order for tenant 7 and commits. T1 runs the identical query and gets 13. Nothing T1 already read has changed; what changed is the set of rows that match.

Lost update. Two sessions each withdraw 10 from the same account. T1 reads a balance of 100; T2 reads 100 too. T1 writes 90 and commits; T2 writes 90 and commits. Two withdrawals happened and one of them is gone — not overwritten by a later decision, just gone, because T2 computed from a number that had already been superseded.

Write skew. Two rows and a rule that spans both. At least one doctor must stay on call. T1 checks the count, sees two, and takes doctor A off. T2 checks the same count, in its own snapshot also sees two, and takes doctor B off. Both checks were true when they ran. Neither transaction touched a row the other wrote, so there is no collision to detect — and after both commit the rule is broken.

Now the grid, with the standard’s own answers in it.

The last two rows are the interesting ones. The phenomena the levels are defined against are dirty read, non-repeatable read, phantom read and serialization anomaly — lost update and write skew are not among them by name. Serializable forbids both anyway, because it promises a result matching some order of running the transactions one at a time. Below it, whether you get caught is a question about your engine, not about the standard.

What the standard says, and what your database does#

The levels are defined by which phenomena must not occur at each one. That is a floor, not a specification of behaviour, and PostgreSQL’s documentation says so directly: the standard specifies which anomalies must not occur at certain levels, so higher guarantees are acceptable. Two engines can honour the same column of that grid and still behave differently under the same level name — and the differences are not exotic. They are the defaults you are running on now.

PostgreSQL has four level names and three behaviours. Its documentation states that you can request any of the four standard levels but that internally only three distinct levels are implemented: read uncommitted behaves like read committed. So the top row of that grid has no PostgreSQL column — you cannot ask for dirty reads there, and asking gets you something stricter without an error. The same page records that its repeatable read implementation does not allow phantom reads either. Two of the five rows are wrong for PostgreSQL before you write a line of code.

MySQL’s InnoDB gives one level two behaviours. Its default is repeatable read, and under it a plain SELECT reads a snapshot established by the transaction’s first read, while a locking read — SELECT … FOR UPDATE or FOR SHARE — uses the most recent state of the database instead. The documentation is explicit that these are two different table states, that they are generally inconsistent with each other, and that mixing locking and non-locking statements in one repeatable read transaction is not recommended, because what you wanted in such a case is usually serializable. The practical shape of that: the same row can hand you two different values inside one transaction, depending on which kind of statement asked for it.

And before reaching for read committed as a performance fix on that engine, know what it costs. Under read committed InnoDB disables gap locking except for foreign-key and duplicate-key checking, which its documentation notes may permit phantom rows — less locking, bought with a wider set of anomalies.

Write skew is the claim to test all of this against, and PostgreSQL’s documentation works one through. A table holds rows in two classes. Transaction A sums the values of class 1 and inserts the total as a new row in class 2; concurrently, transaction B sums class 2 and inserts its total as a new row in class 1. At repeatable read both are allowed to commit. At serializable one commits and the other is rolled back with could not serialize access due to read/write dependencies among transactions, because there is no order of running the two one after the other that produces what they produced together.

Which gives the rule the rest of this page rests on. Do not pick a level by how safe it sounds. Name the anomaly that would corrupt your data — the rule that must hold, and the interleaving that breaks it — then take the cheapest level that forbids it on the engine you are running, confirmed against that engine’s documentation rather than against a grid.

The read-modify-write that passes review#

Here is the bug this page exists for: the most common correctness fault in transactional code, and it passes every review. Read a value, compute the new one in the application, write it back:

-- Two sessions withdrawing 10 from the same account. Each statement runs
-- after the one above it. Every statement here is individually correct.

-- T1
BEGIN;
SELECT balance FROM accounts WHERE id = 5088;
-- 100

-- T2
BEGIN;
SELECT balance FROM accounts WHERE id = 5088;
-- 100 as well

-- T1 · the application computes 100 - 10 and writes the result
UPDATE accounts SET balance = 90 WHERE id = 5088;
COMMIT;

-- T2 · the application computed 100 - 10 too, from the value it read
UPDATE accounts SET balance = 90 WHERE id = 5088;
COMMIT;

-- Two withdrawals of 10 against a balance of 100. The row says 90.

Nothing there is a mistake anyone would circle in a diff. The SELECT is correct, the arithmetic is correct, and the UPDATE writes exactly the value the application computed. The fault is in the gap between the second and third statements, where the number T2 is holding quietly stops being true — and a gap is not a line of code. It will not reproduce, either: one request at a time, the sessions never overlap. It needs two in the same few milliseconds, so it arrives under load, in production, as money that does not add up and no error in the logs.

The first fix removes the gap rather than defending it. If the new value is a function of the old one, say that in SQL and let the database read and write in one operation:

-- The same work as one statement. The database reads the current row and
-- writes the new value in the same operation, under the lock the UPDATE takes.

BEGIN;
UPDATE accounts SET balance = balance - 10
 WHERE id = 5088 AND balance >= 10;
COMMIT;

-- Run twice concurrently, the second UPDATE waits for the first to end,
-- then subtracts from 90 rather than from the 100 it would have read earlier.
-- The row says 80. The guard in the WHERE clause is evaluated against the
-- same current row, so it cannot be satisfied by a value that has since moved.

The difference between the two blocks is balance = balance - 10 against balance = 90. That is the whole lesson: the second carries a number decided elsewhere, at a time that has passed.

Three fixes, and each one costs something:

The third one carries a condition that is easy to skip past. Retrying means running the transaction from the beginning again, and if the work it does is not safe to repeat — an order, an email, a message onto a queue — the retry turns one correctness bug into another. Idempotency keys and safe retries is about making an operation safe to repeat.

Isolation is marked optional on the persistence roadmap rather than required. The nodes that are required run from how a row sits on a page through to deadlocks.

Every level above read-committed buys its guarantee with locks or with versions, and both can end up waiting on each other. What that looks like when it happens is a deadlock, drawn.