DBMS deep dive
Transaction Isolation Levels: deep dive
Isolation levels decide which concurrency anomalies your transaction is allowed to see. The SQL standard defines them by forbidden anomalies, but real engines differ, so the safe answer always names the engine.
The anomalies, each in one line
- Dirty read: you read a row another transaction changed but has not committed, and it may later roll back.
- Non-repeatable read: you read the same row twice and get different values, because someone committed an update in between.
- Phantom read: you run the same range query twice and get a different set of rows, because someone inserted or deleted matching rows.
- Lost update: two transactions read a value, both write a new value based on it, and one write silently overwrites the other.
- Write skew: two transactions read overlapping data, each updates a different row, and together they break an invariant neither broke alone.
Anomaly table: standard vs real engines
| Level | Dirty read | Non-repeatable read | Phantom | Notes |
|---|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible | PostgreSQL treats it as READ COMMITTED, so no dirty reads there |
| READ COMMITTED | Prevented | Possible | Possible | Default in PostgreSQL, Oracle and SQL Server; each statement gets a fresh snapshot |
| REPEATABLE READ | Prevented | Prevented | Standard allows them | Default in MySQL InnoDB. PostgreSQL (snapshot isolation) prevents phantoms; InnoDB prevents them for plain reads via the snapshot and for locking reads via next-key locks |
| SERIALIZABLE | Prevented | Prevented | Prevented | PostgreSQL uses SSI and aborts conflicts; InnoDB turns plain SELECTs into shared-locking reads |
Memorise the two defaults: PostgreSQL defaults to READ COMMITTED, and MySQL InnoDB defaults to REPEATABLE READ. Code that moves between them can change behaviour without any SQL change.
Worked example: lost update at REPEATABLE READ
Stock for item 7 is 10. Two checkout transactions run at the same time at REPEATABLE READ, and each reads the stock before writing:
-- T1 -- T2
BEGIN; BEGIN;
SELECT stock FROM items WHERE id = 7; SELECT stock FROM items WHERE id = 7;
-- both see 10
UPDATE items SET stock = 9 WHERE id = 7;
COMMIT;
UPDATE items SET stock = 9 WHERE id = 7;
-- PostgreSQL RR: ERROR could not serialize access due to concurrent update
-- MySQL RR: succeeds, stock = 9, one sale is lost
-- Engine-independent fixes:
UPDATE items SET stock = stock - 1 WHERE id = 7 AND stock > 0; -- atomic
SELECT stock FROM items WHERE id = 7 FOR UPDATE; -- lock the row firstPostgreSQL's REPEATABLE READ detects that the row changed after the snapshot was taken and aborts T2, which then has to retry. InnoDB's UPDATE always works on the latest committed version, not the snapshot, so T2 overwrites T1 and a sale disappears. Same isolation level name, different guarantee. The details come from each engine's MVCC implementation.
Write skew: the anomaly snapshots miss
Here is the classic case. A hospital rule says at least one doctor must be on call. Alice and Bob are both on call. Each runs SELECT count(*) FROM doctors WHERE on_call (result: 2), decides it is safe to leave, and updates their own row. The two transactions touch different rows, so snapshot isolation sees no conflict, both commit, and nobody is on call. Only SERIALIZABLE prevents this, or an explicit lock on the rows you read (SELECT ... FOR UPDATE), or a constraint the database can enforce.
Choosing a level
- READ COMMITTED with atomic
UPDATE ... SET x = x - 1statements, orSELECT ... FOR UPDATEon the rows you plan to change, covers most OLTP code. - Use REPEATABLE READ for reports and exports that must see one consistent snapshot across many queries.
- Use SERIALIZABLE for invariants that span several rows, and wrap the transaction in a retry loop for serialization failures (SQLSTATE
40001).
Common mistakes
- Quoting the SQL-standard anomaly table as if it described PostgreSQL or MySQL behaviour exactly.
- Running SERIALIZABLE without retry logic, so users see random errors under load.
- Read-modify-write in application code (
SELECT, compute,UPDATEwith a literal) at READ COMMITTED, which invites lost updates. - Setting the level after the first query. In PostgreSQL,
SET TRANSACTIONhas to come before any query in the transaction.
Interview questions
›How does READ COMMITTED behave differently under MVCC and under locking?
Under MVCC (PostgreSQL, Oracle, InnoDB) each statement reads a fresh committed snapshot and never waits for writers. Under classic locking, as in SQL Server without RCSI, readers take short shared locks and can block behind a writer's exclusive lock.
›What is the difference between snapshot isolation and serializability?
Snapshot isolation guarantees each transaction reads a consistent snapshot and that no two concurrent transactions write the same row. It still allows write skew. Serializability guarantees the result equals some serial order of the transactions, which rules out write skew as well.
›What is a next-key lock in InnoDB?
It is a lock on an index record plus the gap before it. Locking reads and updates at REPEATABLE READ take these locks, so other sessions cannot insert into the scanned range. That is how InnoDB prevents phantoms for locking reads.
›How does PostgreSQL implement SERIALIZABLE without heavy locking?
It uses Serializable Snapshot Isolation. Transactions run on snapshots while the engine tracks read-write dependencies with SIRead predicate locks. When it finds a dangerous structure of two consecutive rw-conflicts, it aborts one transaction.
›Which isolation level should a bank transfer use?
READ COMMITTED is enough if the transfer uses atomic conditional updates (balance = balance - x WHERE balance >= x) or locks both rows with SELECT FOR UPDATE in a consistent order to avoid deadlocks. Use SERIALIZABLE with retries if the rule spans rows you do not update.