Transactions and Isolation: What Each Level Actually Prevents

ACID, the anomalies and MVCC, with six classic anomalies replayed at three isolation levels by two live PostgreSQL 18 sessions — including the one that leaves nobody on call at the level almost everyone runs.

Isolation is the letter of ACID everyone skips. Atomicity and durability are easy to believe in; consistency is mostly your problem, not the database’s. Isolation is the one that decides whether two things happening at once produce an answer that could never have happened at all — and it is the one you configure, usually by accident, by leaving the default alone.

This part doesn’t describe the levels. It replays the classic anomalies at each of PostgreSQL’s three real levels, with two live database sessions interleaved statement by statement, and reports what PostgreSQL 18.6 actually did.

Try this first

Two doctors are on call. Each opens the rota, sees that two are on call, and takes themselves off — at the same moment, in two transactions.

How many doctors are on call afterwards? Write down your answer for the default isolation level, the one you are almost certainly running.

ACID, and where the word comes from

Worth getting the history right, because the usual telling is wrong in a small way.

Jim Gray’s 1981 paper names three properties — atomicity, consistency and durability. Isolation isn’t among them; the word appears in that paper only inside the title of a reference. It was Härder and Reuter, in 1983, who added isolation as a fourth property and coined the acronym — and who were pleased enough with it to pun. Having listed atomicity, consistency, isolation and durability, they write that whether a system supports the transaction is “the ACID test of the system’s quality”.

A note on that quotation, because this series checks its sources and this one wouldn’t pass. The paper is paywalled: every attempt to download the PDF returned 403, and the text used here came from a third-party extraction proxy. The wording is almost certainly right, but nobody reading this can fetch the file and check it, so treat it as the one citation here you should verify yourself if it matters to you. The list of four properties is de-hyphenated from that extraction, which renders it “consist-ency”.

PostgreSQL’s own manual barely uses the word. In the whole of the version 18 documentation, “ACID” appears on exactly one page: the glossary.

That is not a criticism of the acronym. It’s a hint about where the real content is. “Isolation” is a single letter hiding four levels, and the levels are where the behaviour lives.

The levels, as the standard has them and as PostgreSQL has them

The SQL standard defines four isolation levels. Serializable is defined by serializability itself — the result must match some serial order — and the other three are defined by phenomena that must not happen. Here is PostgreSQL’s table, in its wording:

Isolation level	Dirty read	Non-repeatable read	Phantom read	Serialization anomaly
Read uncommitted	Allowed, but not in PG	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible	Possible
Repeatable read	Not possible	Not possible	Allowed, but not in PG	Possible
Serializable	Not possible	Not possible	Not possible	Not possible

Two things in that table are worth stopping on, and both are PostgreSQL telling you it differs from the standard.

PostgreSQL has three levels, not four. The manual: “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.” Note what that does not say — READ UNCOMMITTED is accepted, and transaction_isolation will report it back to you. It just doesn’t behave any differently.

PostgreSQL’s Repeatable Read is stronger than the standard requires. It forbids phantom reads, which the standard permits at that level: “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.”

And the fourth column, serialization anomaly, is not one of the standard’s three phenomena at all. That is the column this part is really about.

Six anomalies, replayed

Our lab opens two psql sessions and feeds them statements in a fixed interleaving, so each anomaly happens on purpose rather than by luck. Here is what PostgreSQL 18 did at each level.

Anomaly	read committed	repeatable read	serializable
dirty read	prevented	prevented	prevented
non-repeatable read	it happened	prevented	prevented
phantom read	it happened	prevented	prevented
lost update	it happened	update refused	update refused
lost update, arithmetic in the database	one waited	update refused	update refused
read skew	it happened	prevented	prevented
write skew	it happened	it happened	commit refused

It happened means the anomaly occurred. Refused means the database raised an error — and note which statement it raised it on, because that is not always the commit. One waited means a statement blocked on a lock and then proceeded. Prevented means the anomaly simply did not occur.

Read that table with the default in mind: read committed is the left column, and it is what you get unless someone changed it. Then read the bottom row, because write skew is the one anomaly that survives repeatable read — and repeatable read is where people stop when they decide to be careful.

Dirty reads: not on offer

In our script the first transaction sets the balance to 0 and does not commit. A dirty read would return 0. At every level the second session read 100 — the committed value — both before and after the rollback.

This is the anomaly the standard’s lowest level allows and PostgreSQL doesn’t implement, because its concurrency control has no way to show you a row version that isn’t committed. Note that the lab replays three levels; read uncommitted is not a fourth behaviour to test, it is the same one under another name.

Non-repeatable read and phantom read: the two the levels are named for

At read committed, reading the same row twice gave 100 and then 42, and counting the same set twice gave 2 and then 3. Both are the documented behaviour: read committed takes a fresh snapshot for each statement, so each statement sees everything committed before it began.

At repeatable read, both queries returned the earlier answer. Note the phantom count: 2 and then 2. The standard would permit 3 there. PostgreSQL gives you the stronger guarantee.

Read skew: the report that adds up wrong

This one has no name in the standard. A transaction reads account 1 (100), another transaction moves 100 from account 1 to account 2 and commits, and the first transaction then reads account 2.

A (read committed) reads account 1        -> 100
B moves 100 from account 1 to account 2, commits
A reads account 2                         -> 200
A's report totals                         -> 300
the money actually in the two accounts    -> 200

Two hundred in the accounts; a report saying three hundred. Nothing was corrupted and no row was ever wrong — the two reads simply happened either side of a transfer. Repeatable read fixes it, because both reads come from the same snapshot.

Lost update: the one that depends on where you do the arithmetic

Two transactions read 100. Each wants to add to it — one 10, the other 20 — and this is where it matters who does the adding.

The application does it. It read 100, added 20 in its own memory, and wrote the answer:

A: update accounts set balance = 110 where id = 1
B: update accounts set balance = 120 where id = 1

At read committed, B blocked on A’s lock, waited, and then wrote its literal 120 anyway. Final balance: 120. A’s ten units are gone, and no error was raised.

The database does it. Same interleaving, same isolation level, arithmetic moved into SQL:

A: update accounts set balance = balance + 10 where id = 1
B: update accounts set balance = balance + 20 where id = 1

Final balance: 130. Nothing was lost.

That difference is the single most useful thing in this section. When B’s UPDATE unblocked, PostgreSQL re-evaluated it against the row it found after waiting — the one A had just committed — so balance + 20 added to 110 rather than to the 100 B had read. The documented behaviour saves you, but only if the value you are writing is computed from the row the database re-reads, not from a number your application is holding.

At repeatable read and serializable, B blocked in exactly the same way — it blocks at every level — but when it was released it failed rather than proceeding, with ERROR: could not serialize access due to concurrent update. Note where that error landed: on the UPDATE, not on the commit. A serialization failure is not something that only happens at commit time.

So the honest summary of the read-committed row in that table is narrower than “read committed loses updates”. It is: read committed loses updates when your application does the arithmetic, which is exactly what a read-modify-write in application code looks like.

Write skew: the one to worry about

Two doctors, both on call. Each transaction reads the count — two — decides that’s enough cover, and takes its own doctor off call.

Neither transaction wrote a row the other wrote, so the write-write check that snapshot isolation relies on has nothing to complain about. But each one’s read overlaps the other’s write: both counted both doctors, and then each changed one of the rows the other had counted. That read-write overlap is invisible to a snapshot and is exactly what serializable is built to notice.

read committed    both read "2 on call" -> 0 on call at the end
repeatable read   both read "2 on call" -> 0 on call at the end
serializable      both read "2 on call" -> 1 on call at the end, one commit refused

Zero doctors on call, at read committed and at repeatable read. Only serializable stops it, by refusing one of the two commits.

This is the anomaly worth knowing by name, because it is invisible to the mental model most people carry. Nothing was overwritten. Neither transaction read stale data. Each was correct alone. The rule they were both enforcing — at least one doctor on call — was broken by the combination, and a snapshot cannot see it, because a snapshot only tells you which versions you may read. It says nothing about what the other transaction is about to write to a row you just read.

The example is not ours: it is Example 1 in Cahill, Röhm and Fekete’s 2008 paper on serializable snapshot isolation. Worth knowing that their version has a detail most retellings drop — their application updates first and then checks the count, rolling back if it has hit zero. The anomaly is that the check passes in both transactions, because neither can see the other’s write. Our script checks first, which is the simpler shape and the one people actually write.

A note on the name: “write skew” is not PostgreSQL documentation vocabulary. It appears nowhere in the version 18 manual. The definition comes from Berenson and colleagues’ 1995 critique of the SQL standard’s isolation levels, where it is anomaly A5B, alongside read skew as A5A — and neither is one of the standard’s three phenomena. PostgreSQL’s wiki does name it, and defines it well: “When two concurrent transactions each determine what they are writing based on reading a data set which overlaps what the other is writing, you can get a state which could not occur if either had run before the other.”

MVCC: what a snapshot does and doesn’t do

PostgreSQL never updates a row in place — Part 16 measured the consequences. Each update writes a new version and leaves the old one, tagged with the transaction that created it and the transaction that superseded it.

rows in the table                      4
row versions in the page mid-update    5
versions carrying a creating xid       5
versions marked superseded (xmax set)  1

Four rows, five versions while the update is uncommitted, and one version marked as superseded: the old one, still there, still visible to anybody whose snapshot predates the change.

That is the mechanism behind the whole part. A snapshot is a rule for which versions are visible to you, so reading never blocks writing and writing never blocks reading.

Be careful with that sentence, though, because it is narrower than “nothing waits”. Writers still block writers — which is precisely what the lost-update transcript showed two sections ago, where B sat waiting on A’s lock. MVCC removes the reader-writer contention. It does not remove contention.

And what a snapshot cannot do is notice that two transactions, each reading a consistent snapshot, are about to write things that contradict each other. That takes serializable, which tracks read-write dependencies between transactions and aborts one when the pattern could not have arisen from any serial order. Note that serializable does not take an older snapshot than repeatable read — it takes the same one and adds the dependency tracking.

What serializable costs

Serializable’s price is that transactions fail and your application must run them again. Our lab repeats the write-skew shape 25 times at each level:

Isolation level	Transactions	Aborted	Pairs where one aborted
read committed	50	0	0 of 25
repeatable read	50	0	0 of 25
serializable	50	25	25 of 25

Read that as a yes-or-no, not as a rate. The interleaving is fixed and A always commits first, so at serializable exactly one of the two transactions aborts, every single round — 25 out of 25 pairs. The lab isn’t sampling a percentage; it is establishing that this shape costs you a transaction whenever it happens, and that at the two lower levels it costs you nothing because nothing is detected.

One honest detail about the mechanism. The transactions read with select sum(balance) ... where owner = 'ada' on a four-row table, so there is no index and the plan is a sequential scan — and PostgreSQL documents that “a sequential scan will always necessitate a relation-level predicate lock”, which “can result in an increased rate of serialization failures”. So the conflict here is over the whole table, not over the two rows actually written. On a large table with an index on the predicate, the locks are finer and unrelated transactions conflict less. Anyone benchmarking serializable on a toy table is measuring that, not their workload.

What survives the caveats is the shape of the bill: serializable does not slow transactions down so much as make some of them not count, and your code has to notice and re-run them. The retry must re-run the whole transaction, reads included, because the reads are what stopped being trustworthy.

And the same applies at repeatable read. The lost-update runs raised could not serialize access due to concurrent update there too, which is why PostgreSQL’s documentation says applications using either level must be prepared to retry. Repeatable read is cheaper than serializable; it is not free of retries.

The other way: locking explicitly

You don’t have to change isolation level to stop a lost update. You can take a lock:

A: begin isolation level read committed                 
B: begin isolation level read committed                 
A: select balance from accounts where id = 1 for update 100
B: select balance from accounts where id = 1 for update waits, then returns 110
A: update accounts set balance = 110 where id = 1       
A: commit                                               
B: select balance from accounts where id = 1            110
B: commit                                               
final balance: 110

SELECT ... FOR UPDATE makes the second reader wait rather than proceed on a value that is about to change. The important line is the fourth one: when B’s locking read was released, it returned 110 — the value A had just committed — rather than the 100 it would have read a moment earlier. That is the guarantee, and it is why the pattern is safe: the row you locked is the row you then act on.

That’s a real answer, and often the right one for a single hot row. Its costs are the usual ones for locks: you serialise by hand, you have to remember to do it on every path that touches the row, and you can deadlock. Serializable gets you the same guarantee without remembering, and charges you in retries instead.

Explain it like I’m ten

Two people editing the same shopping list.

• Dirty read: you read your friend’s list while they’re still writing it, and they rub it out again. You bought custard for nothing. (Databases mostly don’t let this happen.)
• Non-repeatable read: you check the list, look away, and check again — and “milk” has changed to “oat milk” under your eyes.
• Phantom: you count six things, look away, count again, and now there are seven.
• Lost update: you both add one item to your own copy and then each write out the whole list. One item disappears, and nobody ever finds out.
• Read skew: you check the fridge for milk, then go and check the cupboard for biscuits — and in between, someone moves the milk into the cupboard. Your report of the kitchen is of a state it was never in.
• Write skew: you both look in the fridge, both see there’s one carton of milk, and so you both cross milk off the list. Nobody buys milk. Neither of you did anything wrong.

The last one is the one that catches people, because nobody made a mistake.

The precise version

• Each transaction sees a snapshot: which row versions are visible to it. The other versions still exist.
• Repeatable read means one snapshot for the whole transaction instead of one per statement. Serializable takes the same snapshot as repeatable read and adds tracking of what each transaction read.
• Write skew survives snapshots because a snapshot says nothing about what another transaction is about to write to a row you just read. Catching it needs the database to track which transaction read what — that’s serializable snapshot isolation.
• Where the analogy breaks: two people can talk to each other. Transactions can’t, which is the whole problem.

Choosing a level

If this is true	Then
You read and write single rows, and always write what you just read in the same statement	Read committed is fine
You compute one value from several rows and need them to agree	Repeatable read, with retries
One transaction’s decision depends on rows another might be changing — a limit, a quota, a rota, a balance check	Serializable, with retries
One hot row, and you want a queue rather than an abort	SELECT ... FOR UPDATE at read committed
You need it correct, and you’re not sure which case you’re in	Serializable, and measure the abort rate

The row most people need and skip is the third. “Check a rule, then act on it” is the shape of write skew, and it is everywhere: don’t overbook the flight, don’t let the balance go negative, don’t let the last admin remove themselves.

Trade-offs

• Higher isolation doesn’t make operations succeed; it makes failures visible. Our lost update went from a silent loss of ten units to an error.
• Serializable buys correctness and charges you in retries. In our deliberately nasty loop one of the two transactions aborted in every single round.
• Repeatable read stops more than people think — including phantoms in PostgreSQL — but it does not stop write skew, and it is not free of retries: it raises serialization failures too, so it needs the same retry loop.
• Explicit locks are precise and easy to get wrong. They work only if every code path that touches the row takes them.
• The default is read committed, so this is a decision you make by not making it.
• Levels don’t mean the same thing in different engines. InnoDB’s repeatable read is not PostgreSQL’s.

Common mistakes

• Assuming the default protects you. At read committed our lab lost an update, skewed a report and emptied the on-call rota.
• Thinking repeatable read is enough for a business rule. It isn’t: zero doctors on call, at repeatable read.
• Using serializable — or repeatable read — without retry logic. Both raise serialization failures, and without a retry loop those become 500s.
• Retrying only the failed statement. The whole transaction has to be re-run, reads included.
• Reading a value, then writing it back in a separate statement, without a lock or a higher level. That’s the lost update shape.
• Believing PostgreSQL has no read uncommitted. It accepts the level and reports it; it just behaves as read committed.
• Carrying assumptions between databases. InnoDB documents that its consistent-read snapshot applies to SELECT but not necessarily to DML, so a DELETE can affect rows the same transaction’s SELECT cannot see. PostgreSQL raises an error in the equivalent situation at repeatable read and above; at read committed it behaves much as InnoDB does.
• Testing concurrency with one session. Every anomaly here needs two, interleaved on purpose.

Interview questions

Try to answer each one before opening the model answer.

1. What do the isolation levels actually prevent?

Show a strong answer

• Read uncommitted: in the standard, dirty reads are allowed. In PostgreSQL it behaves as read committed, so they aren’t.
• Read committed: no dirty reads. Each statement gets a fresh snapshot, so two reads in one transaction can disagree.
• Repeatable read: one snapshot for the whole transaction. In PostgreSQL this also excludes phantoms, which is stronger than the standard requires.
• Serializable: the result must match some serial order of the transactions. This is the only level that catches write skew.
• Measured, not recited: at read committed our lab saw a non-repeatable read (100 then 42), a phantom (2 then 3), a lost update and write skew.

Likely follow-up: “Which does the standard’s table miss?” Write skew and read skew aren’t among its three phenomena — they come from Berenson’s 1995 critique.

2. Explain write skew, and why snapshots can’t stop it.

Show a strong answer

• The shape: two transactions each read overlapping data, each check a rule, and each write something different. Apart, both are correct; together they break the rule.
• The example: two doctors on call, each checks that two are on call, each goes off call. Our lab: zero doctors on call, at read committed and repeatable read.
• Why snapshots fail: neither transaction wrote a row the other read, so there is no conflict to detect. The contradiction is in the rule, not in any row.
• What catches it: serializable, which tracks read-write dependencies between transactions and aborts one when the pattern couldn’t arise from any serial order.
• Or an explicit lock or a constraint, if you can express the rule as one.

Likely follow-up: “Where does this show up in a real system?” Any check-then-act on shared state: seat inventory, quotas, balances, “the last admin can’t remove themselves”.

3. Your service uses serializable and gets intermittent errors under load. What’s happening?

Show a strong answer

• Those are serialization failures, and they’re by design. The database detected a pattern that couldn’t come from any serial order and aborted one transaction.
• The fix is a retry loop, re-running the whole transaction — reads included, because the reads are what became untrustworthy.
• Retry with backoff and a cap, and give up into a user-visible error rather than retrying forever.
• Then reduce the conflicts: shorter transactions, touching fewer rows, and not reading more than you need — the read set is what creates the dependencies.
• Measure the rate. Ours hit 50% on a pathological two-row workload; a real one that high means the design, not the level, is the problem.

Likely follow-up: “How would you tell a serialization failure from a deadlock?” Different error codes. A deadlock is detected after a wait; a serialization failure can arrive on any statement or at commit.

4. When would you use SELECT FOR UPDATE instead of a higher isolation level?

Show a strong answer

• When the contention is one known row and you’d rather queue than retry — a counter, a balance, a job claim.
• It turns the anomaly into a wait: the second reader blocks until the first commits, then reads the new value.
• It’s precise and it’s fragile: it only works if every path that touches the row takes the lock. One SELECT without it and the guarantee is gone.
• And it can deadlock if two transactions take locks in different orders, so take them in a consistent order.
• Serializable is the same guarantee without the discipline, paid for in aborts instead of waits.

Likely follow-up: “What about the weaker row locks?” There are four modes. FOR NO KEY UPDATE is exclusive but allows FOR KEY SHARE; FOR SHARE is shared and still blocks other updates and deletes; FOR KEY SHARE is the weakest, and is the one that stops a delete while allowing other updates.

5. What is MVCC, and what does it buy?

Show a strong answer

• Every update writes a new row version rather than changing the old one; each version records which transaction created it and which removed it.
• A transaction sees a snapshot: the set of versions visible to it, so a reader never blocks a writer and a writer never blocks a reader.
• Isolation levels are snapshot policies: read committed takes one per statement, repeatable read one per transaction.
• The cost is the old versions, which is bloat and vacuum — measured in Part 16.
• What it can’t do is see conflicts between transactions’ reads and writes, which is why serializable needs extra machinery on top.

Likely follow-up: “How does serializable detect the conflict then?” It tracks dependencies between concurrent transactions and aborts one when they would form a cycle no serial order could produce.

6. How would you prevent double-booking a seat?

Show a strong answer

• First choice: make the database enforce it with a unique constraint on the seat. Then concurrency is somebody else’s problem and the loser gets a constraint violation.
• If the rule is a count rather than a row — “no more than N” — that’s write skew, and it needs serializable or an explicit lock on something shared.
• A lock on the parent row (the flight, not the seat) is the common trick: it serialises bookings for that flight only.
• At read committed with neither, you will oversell, and the bug will be rare, real and unreproducible in testing.
• Whatever you choose, test it with two concurrent sessions, because a single-session test passes every time.

Likely follow-up: “Why not just check-then-insert?” That’s exactly the shape that fails: the check and the insert are two moments, and someone else can act between them.

7. What’s the difference between a deadlock and a serialization failure?

Show a strong answer

• A deadlock is two transactions each holding a lock the other wants. The database notices after a timeout and kills one.
• A serialization failure is the database concluding this outcome couldn’t come from any serial order. It can be raised on any statement, not only at commit: in our lab the repeatable-read conflict was raised on the UPDATE.
• Deadlocks are about locks; serialization failures are about snapshots and dependencies. You can get either without the other.
• Both are retryable, and both are best reduced by shorter transactions touching fewer rows in a consistent order.
• Distinguish them by error code rather than by message text, and log which one you got — they point at different fixes.

Likely follow-up: “How do you avoid deadlocks?” Take locks in a consistent order everywhere, keep transactions short, and prefer one lock on a parent to many on children.

8. How do you choose an isolation level for a new service?

Show a strong answer

• Start from the rules the data has to obey, not from performance. Write them down as sentences: “at least one doctor on call”, “the balance never goes negative”.
• Any rule of the form check-then-act on shared state needs serializable or an explicit lock. That’s the write skew shape.
• Anything that computes one answer from several rows wants at least repeatable read, or it will report totals that were never true.
• Read committed is a fine default for single-row reads and writes, which is most traffic.
• Then measure the abort rate under realistic contention, and build the retry loop before you need it.

Likely follow-up: “Would you set it globally or per transaction?” Per transaction, for the ones that need it — a global change affects queries that were fine and adds retries you didn’t budget for.

Sources

• Lab: system-design/checks/part19_isolation/postgres.py — two live psql sessions against PostgreSQL 18 in a container, interleaved statement by statement, replaying six anomalies at three isolation levels, plus a contention loop and a SELECT FOR UPDATE transcript
• PostgreSQL 18: transaction isolation, introduction to MVCC, caveats, explicit locking, serialization failure handling, client connection defaults, glossary
• H. Berenson, P. Bernstein, J. Gray, J. Melton, E. O’Neil, P. O’Neil, A Critique of ANSI SQL Isolation Levels (Microsoft Research MSR-TR-95-51); M. Cahill, U. Röhm, A. Fekete, “Serializable Isolation for Snapshot Databases”, SIGMOD 2008; D. Ports and K. Grittner, “Serializable Snapshot Isolation in PostgreSQL”, VLDB 2012
• T. Härder and A. Reuter, “Principles of Transaction-Oriented Database Recovery”, ACM Computing Surveys, 1983 (where the ACID acronym is coined); J. Gray, “The Transaction Concept: Virtues and Limitations”, 1981
• PostgreSQL wiki: SSI — where write skew is named and worked through; MySQL 8.4: InnoDB consistent nonlocking reads

What to remember

• The default is read committed, so choosing nothing is choosing that.
• Dirty reads don’t happen in PostgreSQL at any level. The standard’s lowest level is accepted and ignored.
• PostgreSQL’s repeatable read is stronger than the standard’s: no phantoms.
• Write skew survives read committed and repeatable read. It is the anomaly that breaks business rules while every transaction is individually correct.
• Serializable catches it, and charges you in aborted transactions. Write the retry loop first.
• A higher level doesn’t make things succeed. It makes the failure loud instead of silent.
• Test concurrency with two sessions, interleaved deliberately. One session passes every time.

Isolation is not about speed. It is about which impossible states your database is willing to let you reach.