Issue #009: Isolation levels visualised — from dirty reads to serialisable anomalies
Consider a banking application running a nightly report that sums all account balances. The report runs as a single long read transaction. While it runs, thousands of transfer transactions are committing — debiting one account, crediting another. If the report reads account A before its transfer is processed and account B after, it captures an inconsistent snapshot: A’s old balance plus B’s new balance. The sum is wrong. No data was corrupted. No transaction failed. The isolation level just wasn’t strong enough for the workload.
This is a real class of bug that appears in production systems regularly. It’s not dramatic — it doesn’t destroy data, it just produces wrong numbers that take days or weeks to notice. And it’s completely preventable once you understand what isolation levels actually control.
There are four isolation levels in the SQL standard. Each one makes a specific set of anomalies impossible. Each one costs more in locking or tracking overhead. Most applications are running at the wrong level for their workload — either too weak (producing wrong results) or stronger than necessary (throttling concurrency without benefit). This issue maps every anomaly, every level, and the precise mechanism Postgres uses to implement the strongest one.
What isolation actually means
Before anomalies, let’s be precise about what isolation is trying to achieve.
Ideally, concurrent transactions would behave as if they ran one at a time in some serial order. If transaction T1 and T2 run concurrently, the result should be identical to either “T1 then T2” or “T2 then T1.” When this holds, we say the execution is serialisable.
Full serialisability is achievable but expensive — it requires the database to track enough information to detect when two concurrent transactions would produce a result that’s impossible in any serial order, and abort one of them. The SQL standard defines weaker levels that relax this guarantee in exchange for better concurrency.
The four levels, from weakest to strongest:
The matrix builds up row by row — each anomaly appearing alongside which levels prevent it. Reading it column by column: READ UNCOMMITTED prevents nothing. READ COMMITTED (Postgres’s default) prevents only dirty reads. REPEATABLE READ prevents dirty reads and non-repeatable reads. SERIALISABLE prevents all four anomaly classes, including the subtle write skew.
Anomaly #1: The dirty read
A dirty read occurs when transaction T1 reads data written by transaction T2, and T2 subsequently rolls back. T1 has read data that, from the database’s perspective, never officially existed.
The animated diagram shows a clean example:
T2 begins and updates
balance = 900(was 1,000). T2 has not committed.T1 reads balance — sees 900.
T2 rolls back. Balance is officially 1,000 again.
T1 proceeds having operated on a value that was never committed.
The damage depends on what T1 did with that read. If it just displayed balance = 900 and the user immediately refreshed, the worst outcome is mild confusion. If T1 used the dirty value to calculate a transfer amount, approve a loan, or write an audit record, the consequences compound.
The fix: READ COMMITTED. At this level, a transaction can only read rows where the writing transaction has committed. T1’s read of T2’s row will either wait for T2 to commit or rollback, or will see the pre-T2 version — never the dirty intermediate state.
Postgres behaviour: Postgres never allows dirty reads — even at READ UNCOMMITTED, Postgres internally behaves as READ COMMITTED. This is standards-compliant: the SQL standard says READ UNCOMMITTED may allow dirty reads, not that it must. Postgres chose to eliminate dirty reads at all levels.
Anomaly #2: The non-repeatable read
A non-repeatable read occurs when T1 reads the same row twice within a single transaction and gets different values — because T2 updated and committed that row between T1’s two reads.
The diagram shows a reporting transaction:
T1 begins and reads
balance = 1,000for account 42.T2 updates balance to 800 and commits.
T1 reads balance again — sees 800.
T1 is internally inconsistent. If this is a financial report, it captured account 42 at two different times. The report’s totals are wrong even though no transaction committed invalid data.
This is surprisingly common in real applications. An ORM that lazy-loads relationships can hit this: the same entity is fetched twice in the same request (once directly, once via a join), and the second fetch reflects an update that happened between the two loads.
The fix: REPEATABLE READ. At this level, T1’s snapshot is taken at the start of the transaction and held fixed. Every read within T1 sees the same version of every row — the version that existed when T1 began — regardless of commits that happen during T1’s lifetime.
Postgres implementation: Postgres implements REPEATABLE READ using MVCC snapshot isolation. When T1 begins, Postgres records the current transaction ID as T1’s snapshot boundary. Any row modified by a transaction with a higher ID (i.e., committed after T1 began) is invisible to T1. The old row version is kept alive in the heap specifically so T1 can read it. This is why VACUUM must avoid removing row versions that active transactions might still need — long transactions prevent cleanup of old versions and cause table bloat.
Anomaly #3: The phantom read
A phantom read is subtler than a non-repeatable read. Instead of the same row returning different values, a query returns a different set of rows when run twice.
The canonical example:
-- T1: find all accounts with balance > 500
SELECT * FROM accounts WHERE balance > 500;
-- Returns: {Alice: 1000, Carol: 750}
-- T2: INSERT INTO accounts VALUES ('Dave', 600); COMMIT;
-- T1: same query again
SELECT * FROM accounts WHERE balance > 500;
-- Returns: {Alice: 1000, Carol: 750, Dave: 600} ← phantom!T1 ran the same query twice. The second result set has a row that didn’t exist in the first. Dave is the phantom — a row that appeared from nowhere, inserted by a committed concurrent transaction.
REPEATABLE READ prevents the non-repeatable read anomaly (re-reading the same row), but in many databases it does not prevent phantom reads — because the newly inserted row was never read by T1 before, so there’s no “previously seen version” to lock it to.
Postgres is unusual here: Postgres’s REPEATABLE READ implementation actually prevents phantom reads too, because Postgres uses snapshot isolation rather than traditional range locking. T1’s snapshot was taken before Dave’s INSERT — Dave’s row doesn’t exist in T1’s snapshot, and T1 will never see it, even on a re-query. This is stricter than the SQL standard requires for REPEATABLE READ and is one reason Postgres’s REPEATABLE READ is considered particularly strong.
Traditional databases (MySQL InnoDB): MySQL prevents phantom reads at REPEATABLE READ using next-key locks — range locks that prevent other transactions from inserting into the range covered by a query. This is correct but more restrictive than Postgres’s approach, because it locks not just the rows you’ve read but the gaps between them.
Anomaly #4: Write skew — the hardest one
This is the anomaly that most engineers haven’t encountered by name, even when they’ve been bitten by it. It doesn’t involve reading stale data or phantom rows. Both transactions read correct, committed, up-to-date data. Both transactions commit successfully. And yet the result is impossible if either transaction had run alone.
The animated diagram shows the hospital on-call scenario:
Business rule: At least one doctor must be on call at all times.
State: Alice and Bob are both on call (count = 2).
T1 (Alice’s client): Reads count of on-call doctors → 2. Since count ≥ 2, safe to go off call. Deletes Alice from on-call table. Commits.
T2 (Bob’s client): Reads count of on-call doctors → 2. Since count ≥ 2, safe to go off call. Deletes Bob from on-call table. Commits.
Result: Zero doctors on call. Business rule violated. Both transactions read correct data. Both committed without error. No dirty reads. No phantom rows. And yet the combined result is wrong.
The key insight is timing: both T1 and T2 read the count before either deletion was committed. Each saw 2 and independently concluded it was safe to proceed. Neither could see what the other was about to do.
If the transactions had run serially — Alice first, then Bob — Bob’s read would have seen count = 1 and would have blocked or rejected the deletion. The serial result is safe. The concurrent result is not. This is the definition of a serialisation anomaly: a result that’s impossible in any serial ordering.
Other write skew examples that appear in real systems:
-- Meeting room double-booking:
-- T1: SELECT count(*) FROM bookings WHERE room=5 AND time='09:00' -- returns 0
-- T2: SELECT count(*) FROM bookings WHERE room=5 AND time='09:00' -- returns 0
-- T1: INSERT INTO bookings(room, time, user) VALUES (5, '09:00', 'Alice')
-- T2: INSERT INTO bookings(room, time, user) VALUES (5, '09:00', 'Bob')
-- Both commit → room 5 double-booked at 09:00
-- Username uniqueness with application-level checks:
-- T1: SELECT count(*) FROM users WHERE username='alice' -- returns 0
-- T2: SELECT count(*) FROM users WHERE username='alice' -- returns 0
-- T1: INSERT INTO users(username) VALUES ('alice')
-- T2: INSERT INTO users(username) VALUES ('alice')
-- Both commit → two users named 'alice' (if no UNIQUE constraint)The second example is exactly why UNIQUE constraints must live in the database (CODD Rule 10), not in application code. A database UNIQUE constraint is enforced atomically. Application-level uniqueness checks are vulnerable to write skew.
The fix: SERIALISABLE isolation. At this level, the database detects that T1 and T2 have a dependency cycle (T1’s decision to proceed depended on data that T2 was about to modify, and vice versa) and aborts one of them. The application retries the aborted transaction, which now sees the committed result of the other and makes the correct decision.
How Postgres implements SERIALISABLE: SSI
Postgres uses an algorithm called Serialisable Snapshot Isolation (SSI), introduced in PostgreSQL 9.1. SSI is remarkable because it provides full serialisability without the coarse locking that traditional serialisable implementations require.
Traditional serialisable isolation works by locking everything that’s read, preventing concurrent writers from touching it. This is safe but severely limits concurrency — a long-running read-only transaction blocks all writers on the rows it has read.
SSI works differently. It lets transactions run concurrently using Postgres’s normal MVCC snapshot isolation, but it tracks read-write dependencies between transactions. When it detects a cycle of dependencies that would make the execution non-serialisable, it aborts one of the involved transactions (the “pivot” transaction).
The dependency tracking uses SIREAD locks — a new kind of lock that doesn’t block other transactions but records “transaction X has read this row.” These locks are much lighter than write locks and allow concurrent reads without blocking.
What SSI tracks:
If T1 reads a row that T2 later writes (rw-antidependency T1→T2)
And T2 reads a row that T1 later writes (rw-antidependency T2→T1)
→ This is a dangerous cycle → abort one transactionIn the hospital example:
T1 reads the on-call count (will be modified by T2’s DELETE)
T2 reads the on-call count (will be modified by T1’s DELETE)
Both have read-write antidependencies on each other
SSI detects the cycle and aborts one
The other completes successfully
The retried transaction sees count = 1 and correctly refuses to go off call
The cost of SSI: SIREAD locks consume memory. A long-running SERIALISABLE transaction that reads millions of rows can accumulate millions of SIREAD locks. Postgres manages this with lock summarisation — consolidating many per-row locks into a per-page or per-relation lock — which may produce false positives (unnecessary aborts) but never false negatives (missed violations).
The max_pred_locks_per_transaction parameter controls the lock table size. If SERIALISABLE transactions are aborting more than expected, check whether lock summarisation is causing overly aggressive conflict detection.
Choosing the right isolation level
The practical question: what level should your application use?
READ COMMITTED (Postgres default) — Correct for most OLTP workloads where each transaction is short, operates on a small number of rows, and doesn’t have read-modify-write logic that depends on other concurrent transactions. The reporting anomaly at the top of this issue is the sign you need to go higher.
REPEATABLE READ — Correct for long-running reads that must see a consistent snapshot: financial reports, data exports, analytics queries running against OLTP data. Also correct when you have explicit optimistic locking — read a row, check a version column, update only if the version hasn’t changed. In Postgres, REPEATABLE READ is surprisingly strong (it also prevents phantoms) and rarely causes performance issues.
SERIALISABLE — Necessary when your application has read-then-write logic that depends on the reads being stable: the on-call problem, meeting room bookings, maintaining invariants across multiple rows. If your application implements business rules by checking a condition before writing, and that condition involves rows that other transactions might also be checking and writing, you need SERIALISABLE.
The common mistake is using READ COMMITTED with application-level uniqueness or business-rule checks. That’s vulnerable to write skew. The correct fix is either:
Move the constraint into the database (UNIQUE, CHECK, FOREIGN KEY constraints are write-skew-safe)
Use SERIALISABLE isolation
Use explicit locking (
SELECT ... FOR UPDATE) to prevent concurrent modification of the rows the decision depends on
Per-transaction isolation in Postgres:
-- Set isolation for a single transaction:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Or set the session default:
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Or set for the whole database:
ALTER DATABASE mydb SET default_transaction_isolation = 'serializable';The phantom that Postgres prevents but the standard doesn’t require
One specific clarification worth making explicit before the next issue:
The SQL standard says REPEATABLE READ may allow phantom reads. It doesn’t say it must. Postgres’s REPEATABLE READ prevents phantoms because its snapshot isolation implementation is stricter than the standard’s minimum.
This creates a subtle portability issue: if you write an application that relies on REPEATABLE READ preventing phantoms, it will work correctly on Postgres but may exhibit phantom reads on MySQL InnoDB (which uses range locking rather than snapshot isolation for REPEATABLE READ). MySQL’s range locking does prevent phantoms through a different mechanism, so in practice both databases are safe — but the reason each is safe differs.
When writing cross-database code or switching databases, verify the isolation semantics, not just the level name.
Vault assets for this issue: isolation_demo.py — accessible in GitHub vault
