Database Isolation Levels โ How to Get It Right
A few years ago, a production bug landed on a review: a digital wallet. End of day, balance off by a few hundred thousand. No exceptions in the logs. Code looked correct โ SELECT balance, check funds, UPDATE to deduct. Ran fine in isolation. The problem: production never runs in isolation.
Two withdrawal requests arrived nearly simultaneously. Both read balance = 500k. Both checked "enough funds?" โ yes. Both deducted. The second write overwrote the first. One deduction silently vanished.
The fix was straightforward. The interesting question was: why do engineers who write database code every day almost never think about isolation levels?
Table of Contents
- The Root Problem: Concurrent Transactions and Anomalies
- The 5 Anomalies โ Standard and Beyond
- The 4 Isolation Levels โ Standard Spec
- Spec โ Implementation: PostgreSQL Deep Dive
- MySQL and Oracle Differences
- Two Layers: What You Set vs What Database Does
- MVCC โ How Isolation Is Actually Achieved
- Practical Strategies: Handle Anomalies Without Raising Isolation
- The Question to Answer First
The Root Problem
When multiple transactions run concurrently and touch the same data, certain wrong outcomes become possible โ collectively called anomalies. Isolation level is a dial: how much concurrency do you allow, and which anomalies do you accept as the price?
The ANSI SQL 1992 standard defined three anomalies and four isolation levels around them. In 1995, a landmark paper โ "A Critique of ANSI SQL Isolation Levels" โ showed that the standard was too vague, and had missed an entire class of anomalies. The two most important additions: lost update and write skew โ both silently corrupt data with zero exceptions in the logs.
| t | Transaction A | Transaction B | Note |
|---|---|---|---|
| T1 | SELECT balance โ $500 | SELECT balance โ $500 | Both read same value |
| T2 | Compute: $500 - $100 = $400 | Compute: $500 - $200 = $300 | Both compute independently |
| T3 | UPDATE balance = $400; COMMIT | (waiting) | A commits first |
| T4 | (done) | UPDATE balance = $300; COMMIT | ๐ฅ B overwrites A! $100 withdrawal lost. |
The 5 Anomalies โ Standard and Beyond
1. Dirty Read (ANSI SQL 1992 โ P1)
Transaction A modifies a row but hasn't committed. Transaction B reads that uncommitted value. Then A rolls back. B just acted on data that never officially existed.
T1: UPDATE balance = $0 WHERE id = 1; -- not yet committed
T2: SELECT balance FROM accounts WHERE id = 1; -- sees $0!
T1: ROLLBACK; -- T2 read data that never existed
Prevented by: READ COMMITTED or higher.
PostgreSQL silently upgrades READ UNCOMMITTED to READ COMMITTED. Dirty reads are impossible in PostgreSQL regardless of what you declare.
2. Non-Repeatable Read (ANSI SQL 1992 โ P2)
In the same transaction, the same row is read twice and returns different values โ because another transaction committed a change between the two reads.
T1: SELECT balance FROM accounts WHERE id = 1; -- returns $500
T2: UPDATE accounts SET balance = $300 WHERE id = 1; COMMIT;
T1: SELECT balance FROM accounts WHERE id = 1; -- returns $300 โ different!
Prevented by: REPEATABLE READ or higher.
3. Phantom Read (ANSI SQL 1992 โ P3)
Like non-repeatable, but at the set level. A range query returns a different count on two executions within the same transaction โ because another transaction inserted a qualifying row in between.
T1: SELECT COUNT(*) FROM accounts WHERE balance > 100; -- returns 5
T2: INSERT INTO accounts (balance) VALUES (200); COMMIT;
T1: SELECT COUNT(*) FROM accounts WHERE balance > 100; -- returns 6 โ phantom!
Prevented by: SERIALIZABLE (standard) โ or REPEATABLE READ in PostgreSQL (per-transaction snapshot naturally blocks phantoms).
4. Lost Update (1995 Critique โ P4)
The e-wallet bug. Two transactions both read the same row, compute based on that value, and write their result. The second write silently overwrites the first.
T1: balance = SELECT balance FROM accounts WHERE id=1; -- 500
T2: balance = SELECT balance WHERE id=1; -- 500
T1: UPDATE accounts SET balance = 500 - 100 = 400 WHERE id=1;
T2: UPDATE accounts SET balance = 500 - 200 = 300 WHERE id=1; โ T1's deduction is lost!
-- Final: 300. Should be: 200. 100 vanished silently.
Prevented by: REPEATABLE READ (PostgreSQL aborts the loser) / SELECT FOR UPDATE / atomic UPDATE.
The most dangerous anomaly in production because it produces no exception. The only symptom is an end-of-day balance reconciliation failure.
5. Write Skew (1995 Critique โ P5)
Two transactions read overlapping data, make individually valid decisions, then write to different rows. Their combined result violates a shared invariant.
-- Rule: at least 1 doctor on-call at all times
-- Current: An and Binh are both on-call
T1 (An requests leave):
SELECT COUNT(*) FROM doctors WHERE on_call = true; -- sees 2, ok
UPDATE doctors SET on_call = false WHERE id = 1; -- An's row
T2 (Binh requests leave, concurrent):
SELECT COUNT(*) FROM doctors WHERE on_call = true; -- also sees 2, ok
UPDATE doctors SET on_call = false WHERE id = 2; -- Binh's row
-- Both COMMIT. 0 doctors on-call. Invariant BROKEN.
Why write skew is harder than lost update: Lost update has two transactions writing the same row โ there's a direct collision the database can detect. Write skew has each transaction writing a different row โ no direct collision exists. The conflict is at the invariant level, invisible to the database unless you tell it what to guard.
Prevented by: SERIALIZABLE (SSI) โ or materializing the invariant into a lockable row.
The 4 Isolation Levels โ Standard Spec
| Isolation Level | Dirty Read | Non-Repeatable | Phantom Read | Lost Update | Write Skew |
|---|---|---|---|---|---|
READ UNCOMMITTED | โ Possible | โ Possible | โ Possible | โ Possible | โ Possible |
READ COMMITTED | โ Prevented | โ Possible | โ Possible | โ Possible | โ Possible |
REPEATABLE READ | โ Prevented | โ Prevented | โ Possible | โ Possible | โ Possible |
SERIALIZABLE | โ Prevented | โ Prevented | โ Prevented | โ Prevented | โ Prevented |
| Isolation Level | Dirty Read | Non-Repeatable | Phantom | Lost Update | Write Skew |
|---|---|---|---|---|---|
| READ UNCOMMITTED | โ | โ | โ | โ | โ |
| READ COMMITTED | โ | โ | โ | โ | โ |
| REPEATABLE READ | โ | โ | โ * | โ | โ |
| SERIALIZABLE | โ | โ | โ | โ | โ |
This table describes the spec โ what anomalies each level must prevent. It says nothing about how the database achieves it. That distinction matters enormously.
The four levels are a dial: higher = safer, but more lock contention, more deadlocks, lower throughput.
A ticket-booking system set all transactions to SERIALIZABLE "just to be safe." At peak load, transactions queued waiting for locks, timeouts cascaded, and the dashboard went red. The fix: drop to READ COMMITTED and handle inventory specifically with targeted SELECT FOR UPDATE. ACID isolation is not an on/off switch โ it's a dial. Choose deliberately.
Spec โ Implementation: PostgreSQL Deep Dive
| Level Name | Actual Mechanism | Snapshot Scope | Key Notes |
|---|---|---|---|
| READ UNCOMMITTED | READ COMMITTED | Per-statement snapshot | Dirty reads impossible. Silently upgraded. |
| READ COMMITTED | READ COMMITTED | Per-statement snapshot | Default. Each SQL gets fresh snapshot. Two SELECTs can differ. |
| REPEATABLE READ | Snapshot Isolation (SI) | Per-transaction snapshot | Also blocks phantoms + aborts on lost update. Write skew still possible. |
| SERIALIZABLE | SSI (Serializable Snapshot Isolation) | Per-transaction + dependency tracking | Catches write skew via read-write cycle detection. Abort rate rises under load. |
Rule: Never trust an isolation level by its name. Always read what the specific database version actually does at that level. The spec is a floor, not a ceiling. Implementations can differ widely โ and in the case of Oracle, the highest level name doesn't even match the spec's guarantee.
The same level name can have completely different behaviour across databases. The spec defines the floor โ what anomalies a level must prevent. Each database is free to prevent more. Trusting a level name without verifying the implementation is how subtle bugs enter production.
PostgreSQL Has No Real READ UNCOMMITTED
You can declare it, but PostgreSQL silently runs it as READ COMMITTED. Dirty reads are impossible in PostgreSQL, regardless of what you set.
PostgreSQL READ COMMITTED โ Per-Statement Snapshot
Each SQL statement takes a fresh snapshot at execution time. Two SELECTs in the same transaction can see different committed data โ because each looks at a newer snapshot.
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- snapshot at 10:00:01 โ $500
-- (another transaction commits: $300)
SELECT balance FROM accounts WHERE id = 1; -- snapshot at 10:00:04 โ $300 โ different!
COMMIT;
PostgreSQL REPEATABLE READ โ Per-Transaction Snapshot (Snapshot Isolation)
One snapshot is taken at the first statement and held for the entire transaction. Every read sees the same frozen world.
BEGIN; -- snapshot taken here
SELECT balance FROM accounts WHERE id = 1; -- $500
-- (another transaction commits: $300)
SELECT balance FROM accounts WHERE id = 1; -- still $500 โ same snapshot
COMMIT;
PostgreSQL REPEATABLE READ prevents more than the standard requires:
- Phantom reads โ the frozen snapshot blocks new rows from appearing
- Lost updates โ if two transactions try to update the same row, the second is aborted with
ERROR: could not serialize access due to concurrent update. It doesn't silently overwrite โ it loudly fails so your application can retry.
But REPEATABLE READ still allows write skew โ An and Binh write different rows, no direct collision, both commit.
PostgreSQL SERIALIZABLE โ SSI (Serializable Snapshot Isolation)
PostgreSQL tracks read-write dependency edges between concurrent transactions. If it detects a cycle that would produce a non-serializable outcome, it aborts one transaction. This is how it catches the doctor write skew scenario.
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM doctors WHERE on_call = true;
-- If ok:
UPDATE doctors SET on_call = false WHERE id = ?;
COMMIT;
-- Must catch serialization_failure and retry!
MySQL and Oracle Differences
MySQL InnoDB: REPEATABLE READ & The Supremum Gap Lock Pitfall
MySQL InnoDB defaults to REPEATABLE READ โ higher than PostgreSQL's default. Unlike PostgreSQL (which relies purely on per-transaction MVCC snapshots to prevent phantoms), MySQL InnoDB uses Next-Key Locks (Record Lock + Gap Lock):
- When an existing row is locked, InnoDB locks the record plus the gap preceding it.
- When an empty table or range with no matching rows is queried via
SELECT ... FOR UPDATE, InnoDB places a Gap Lock on thesupremumpseudo-record (a virtual boundary representing all values greater than the highest existing key).
The Production Trap: Replenishment Deadlocksโ
In high-throughput queue or unit-reservation systems (such as Shopify's inventory reservation engine), worker transactions execute:
SELECT id FROM reservation_units WHERE shop_id = 12 AND item_id = 456 LIMIT 10 FOR UPDATE SKIP LOCKED;
If the table or SKU range is currently empty (depleted pool needing refill):
- Under
REPEATABLE READ, the query acquires a gap lock covering thesupremumpseudo-record. - An inline or background replenishment worker attempts to
INSERT INTO reservation_units VALUES (...). - The
INSERTattempts to acquire an Insert Intention Lock on that exact same gap. - Result: The
INSERTblocks on the reader's supremum gap lock. When multiple concurrent readers do this, mutual gap lock dependencies trigger immediate deadlocks (ERROR 1213: Deadlock found when trying to get lock; try restarting transaction).
The Production Solution: Drop to READ COMMITTEDโ
By switching the isolation level to READ COMMITTED specifically for reservation/queue transactions:
- InnoDB disables gap locking for search and index scans (gap locks are only retained for foreign key constraint checks and duplicate key checks).
- Concurrent replenishment
INSERTstatements execute immediately without waiting on reader locks.
-- Set per transaction in MySQL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT id FROM reservation_units WHERE shop_id = 12 AND item_id = 456 LIMIT 10 FOR UPDATE SKIP LOCKED;
-- ...
COMMIT;
Oracle: "SERIALIZABLE" is Actually Snapshot Isolation
Oracle's "SERIALIZABLE" is actually Snapshot Isolation under the hood โ it prevents phantoms but still allows write skew. Same label, fundamentally weaker guarantee than PostgreSQL's SERIALIZABLE. A team that relied on the name without reading Oracle's documentation could ship broken invariant enforcement.
Two Layers: What You Set vs What Database Does
Transaction acquires all required Shared (S) or Exclusive (X) locks on rows/pages as it executes queries. ZERO locks may be released during this phase.
- Can acquire new locks (S-Lock / X-Lock)
- Can upgrade S-Lock to X-Lock
- CANNOT release any locks
| Requested \ Held | Shared (S) | Exclusive (X) |
|---|---|---|
| Shared (S) | โ OK | โ BLOCK |
| Exclusive (X) | โ BLOCK | โ BLOCK |
| Layer | What it is | Example |
|---|---|---|
| Isolation Level (Spec) | Behavioural contract โ which anomalies the DB promises to prevent | REPEATABLE READ means non-repeatable reads won't happen |
| Implementation | How the DB achieves the promise โ MVCC, locks, SSI | PostgreSQL uses per-transaction snapshot + abort-on-conflict |
These layers are independent. The spec says what; the implementation says how. The same "what" can have different "hows" โ and sometimes the name on the dial doesn't match the actual behaviour underneath (Oracle's SERIALIZABLE).
MVCC โ How Isolation Is Actually Achieved
Multi-Version Concurrency Control: when you UPDATE a row, the database doesn't overwrite the old value. It creates a new version, keeps the old one around for transactions that started before this change.
Think of it as photocopying the morning newspaper before boarding a train. Outside, news keeps updating. But your copy stays consistent from page 1 to the last page โ you're never reading a mix of yesterday's and today's articles.
-- PostgreSQL hidden columns on every row:
xmin: transaction ID that CREATED this version
xmax: transaction ID that DELETED/REPLACED this version (0 = still live)
-- T100 reads: sees rows where xmin <= 100 and xmax = 0 (or xmax > 100)
-- T102 writes: creates new version (xmin=102), old version still exists with xmax=102
-- T100 can still read the old version โ no lock needed
Why MVCC wins: Readers never block writers. Writers never block readers. Each transaction reads its own snapshot. No global serialization lock.
When you do need to enforce ordering โ SELECT ... FOR UPDATE โ PostgreSQL steps out of pure MVCC mode and places a real row lock. MVCC handles the "read without blocking" case; explicit locks handle "I need exclusive write access before I proceed."
Practical Strategies
In practice, most teams keep the database default and handle anomalies at the query level. Raising isolation level adds lock contention and requires retry loops everywhere. These fixes usually cost less.
Handling Lost Update
Option 1 โ Atomic UPDATE (cleanest, no extra lock):
UPDATE accounts
SET balance = balance - 70
WHERE id = ? AND balance >= 70;
-- Check affected rows: 0 means insufficient funds OR lost race โ handle accordingly
The database locks this row for the duration of the statement. No gap between read and write โ the race condition can't exist.
Option 2 โ Optimistic Locking (low contention):
UPDATE accounts
SET balance = ?, version = version + 1
WHERE id = ? AND version = ?;
-- 0 rows updated = someone changed it โ retry
@Entity
public class Account {
@Version // JPA handles this automatically
private Long version;
}
Best when conflicts are rare. Under heavy contention (flash sale inventory), retries pile up exactly when you need throughput most.
Option 3 โ Pessimistic Locking (hot rows):
BEGIN;
SELECT balance FROM accounts WHERE id = ? FOR UPDATE; -- locks row immediately
-- compute ...
UPDATE accounts SET balance = ? WHERE id = ?;
COMMIT;
Other transactions trying to SELECT FOR UPDATE the same row block until this one commits. No retries needed, but lock duration directly impacts concurrency.
Handling Write Skew
Before reaching for SERIALIZABLE โ which requires retry loops everywhere and sees abort rate climb under load โ check if you can materialize the invariant into a lockable row.
Write skew happens because the invariant ("at least 1 doctor on-call") lives in an aggregate that no transaction locks. Create a concrete row representing the constraint and lock it:
BEGIN;
-- Lock the on-call slot row โ makes the invisible invariant visible to the lock manager
SELECT * FROM on_call_slots WHERE shift_id = ? FOR UPDATE;
SELECT COUNT(*) FROM doctors WHERE on_call = true AND shift_id = ?;
-- IF count > 1 THEN
UPDATE doctors SET on_call = false WHERE id = ?;
-- ELSE raise exception
COMMIT;
By locking on_call_slots, both concurrent transactions queue on the same lock. The second sees the real post-first-commit state and correctly rejects the request. The invariant is now visible to the lock manager.
The Question to Answer First
The spec is the label on the dial.
The implementation is what actually runs.
Think of it as photocopying the morning newspaper before boarding a train. Outside, news keeps updating. But your copy stays consistent: you never read a mix of yesterday's and today's articles in the same paper.
Result: Readers never block writers. Writers never block readers. The database achieves isolation without everyone waiting in a single lock queue.
Every time you lower the isolation level, you take more responsibility onto your application. The database does not warn you โ it silently gives you the result, correct or not, exactly as contracted. The only symptom of a violated invariant is an off-by-one balance at 2am during peak traffic.
The question worth asking before picking an isolation level: "What invariant must always hold in this operation โ and what data do I read to make the decision but never write?" The data you read-but-not-write is your write skew risk surface. Identify it first, then pick your tool.
Isolation level is a contract: the database hides these anomalies; you handle the rest. Every time you lower the level, you accept more responsibility โ and the database won't remind you. It silently produces the correct-or-incorrect result as specified by the contract.
"In this business operation, what invariant must always hold โ and what data do I read to make the decision but not write?"
The data you read-but-not-write is your write skew risk surface. Once you identify it:
- If it fits in one statement โ atomic UPDATE. Done.
- If it's low contention โ optimistic locking with
@Version. - If it's a hot row โ
SELECT FOR UPDATE. - If it's an aggregate invariant โ materialize into a lockable row.
- If it's genuinely complex โ SERIALIZABLE with retry loops as last resort.
The isolation level you need is the consequence of this analysis โ not a setting you pick upfront and hope for the best.
Summary: Default Behavior by Database
| Database | Default Level | Snapshot Scope | Notable Quirks |
|---|---|---|---|
| PostgreSQL | READ COMMITTED | Per-statement | No real READ UNCOMMITTED. REPEATABLE READ also blocks phantoms + aborts on lost update. SERIALIZABLE uses SSI. |
| MySQL InnoDB | REPEATABLE READ | Per-transaction | Uses gap locks for phantom prevention. Different from PG. |
| Oracle | READ COMMITTED | Per-statement | "SERIALIZABLE" is actually Snapshot Isolation โ still allows write skew. |
| SQL Server | READ COMMITTED | Lock-based by default | Must opt in to MVCC mode (READ_COMMITTED_SNAPSHOT). |
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; applies only to that one transaction. The rest of your system keeps running at its own level. Changing the database-wide default has a much wider blast radius โ reserve that for carefully considered infrastructure decisions.
// Spring โ set per method, affects only that transaction
@Transactional(isolation = Isolation.SERIALIZABLE) // for write skew prevention
@Transactional(isolation = Isolation.READ_COMMITTED) // PostgreSQL default
@Transactional(isolation = Isolation.REPEATABLE_READ) // when you re-read rows