Isolation levels and MVCC
Part of the free LogicWiz SQL & Databases course, Module 13: Transactions and concurrency. The queries and practice run in the app on real Postgres.
Meher's month-end report adds up all balances, then lists them. Payments keep landing in between, so the total and the list never agree. How can a report see one frozen moment while the bank keeps running?
You'll learn to
- Match each isolation level to the problems it prevents
- Use REPEATABLE READ for a consistent report, and retry when Postgres says it can't serialize
- Explain MVCC: row versions, snapshots, and why readers never block writers
- Recognise write skew and the level that stops it
Learn
The four SQL isolation levels
| Level | Dirty read | Non-repeatable read | Phantom | In Postgres |
|---|---|---|---|---|
| READ UNCOMMITTED | possible | possible | possible | behaves as READ COMMITTED |
| READ COMMITTED | no | possible | possible | the default |
| REPEATABLE READ | no | no | possible | no phantoms either (snapshot) |
| SERIALIZABLE | no | no | no | also stops write skew |
The first four columns are the SQL standard, the table exams ask for. The last column is what Postgres actually does: it is stricter than the standard at the two lower levels.
Check and set the level
SHOW default_transaction_isolation;
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT current_setting('transaction_isolation') AS level;
COMMIT;
The level is set per transaction, in BEGIN or with SET TRANSACTION ISOLATION LEVEL … as the first statement after BEGIN.
MVCC: Postgres keeps old versions
Postgres uses MVCC (multi-version concurrency control). An UPDATE doesn't overwrite a row. It writes a new version of the row and marks the old one as replaced.
Each row version carries the ID of the transaction that created it (xmin) and the one that replaced it (xmax). A transaction reads using a snapshot: "which transactions had committed when I looked?". It sees only the versions those transactions made.
So a reader never waits for a writer, and a writer never waits for a reader. Old versions that nobody can see any more are cleaned up later by VACUUM.
See the new row version
SELECT ctid, xmin, balance FROM accounts WHERE account_id = 1;
UPDATE accounts SET balance = balance - 150 WHERE account_id = 1;
SELECT ctid, xmin, balance FROM accounts WHERE account_id = 1;
ctid is the row version's physical spot: (page, slot). Before the update Aarav's row is at (0,1); after it, the new version sits at (0,9), after the 8 original rows. The xmin changes to the ID of the transaction that wrote it (the exact number differs from run to run).
When is the snapshot taken?
- READ COMMITTED: a new snapshot for every statement. Each SELECT sees everything committed before it started. Simple and fast, but two SELECTs in one transaction can disagree.
- REPEATABLE READ: one snapshot for the whole transaction, taken at its first query. Every SELECT sees the same frozen moment, so no non-repeatable reads and no phantoms.
- SERIALIZABLE: the same snapshot, plus Postgres watches what each transaction read and wrote, and cancels one if the result couldn't have come from running them one at a time.
REPEATABLE READ: a report that sees one moment
| Session A | Session B |
|---|---|
| BEGIN ISOLATION LEVEL REPEATABLE READ; | |
| SELECT SUM(balance) FROM accounts; | |
| UPDATE accounts SET balance = balance + 10000 WHERE accountid = 6; | |
| INSERT INTO upitxns VALUES (21, 1, 7, 45.00, '2025-08-09 08:50', 'success', 'chai'); | |
| SELECT SUM(balance) FROM accounts; | |
| SELECT COUNT() FROM upitxns WHERE fromacc = 1; | |
| COMMIT; | |
| SELECT SUM(balance) FROM accounts; |
REPEATABLE READ refuses to overwrite a newer change
| Session A | Session B |
|---|---|
| BEGIN ISOLATION LEVEL REPEATABLE READ; | |
| SELECT balance FROM accounts WHERE accountid = 5; | |
| UPDATE accounts SET balance = balance - 500 WHERE accountid = 5; | |
| UPDATE accounts SET balance = balance - 200 WHERE accountid = 5; | |
| COMMIT; |
Write skew: only SERIALIZABLE stops it
| Session A | Session B |
|---|---|
| BEGIN ISOLATION LEVEL SERIALIZABLE; | |
| SELECT COUNT() FROM oncall WHERE onduty; | |
| BEGIN ISOLATION LEVEL SERIALIZABLE; | |
| SELECT COUNT() FROM oncall WHERE onduty; | |
| UPDATE oncall SET onduty = false WHERE doctor = 'Dr Sneha'; | |
| UPDATE oncall SET onduty = false WHERE doctor = 'Dr Kabir'; | |
| COMMIT; | |
| COMMIT; |
MySQL (InnoDB) defaults to REPEATABLE READ, not READ COMMITTED, and it does allow dirty reads at READ UNCOMMITTED. Oracle and SQL Server default to READ COMMITTED. In interviews, say which database you mean.
In one line
READ COMMITTED (default) takes a snapshot per statement, REPEATABLE READ one per transaction, SERIALIZABLE also stops write skew; MVCC keeps row versions so readers and writers never block.
Interview corner
Explain the isolation levels and what each prevents.
READ UNCOMMITTED allows dirty reads; READ COMMITTED stops them; REPEATABLE READ also stops non-repeatable reads; SERIALIZABLE also stops phantoms, so the result equals some one-at-a-time order. In Postgres, READ UNCOMMITTED acts as READ COMMITTED and REPEATABLE READ also prevents phantoms.
What is MVCC?
Multi-version concurrency control: an update writes a new row version instead of overwriting. Each transaction reads the versions visible in its snapshot, so readers never block writers and writers never block readers. Old versions are removed later by VACUUM.
What is write skew?
Two transactions read the same data, each decides based on it, and each updates a different row, so no row conflict is detected, yet together they break a rule (both on-call doctors go off duty). Snapshot isolation (Postgres REPEATABLE READ) allows it; SERIALIZABLE detects it and aborts one transaction. You can also prevent it by locking the rows you read with FOR UPDATE.