Transaction Isolation Levels

Transaction Isolation Levels

Isolation levels let you trade consistency for concurrency. Higher isolation = fewer anomalies but more contention. Understanding this trade-off is essential for database interviews.

The Four Anomalies

Dirty Read — reading uncommitted data from another transaction.

-- T1 writes but hasn't committed
T1: UPDATE accounts SET balance = 0 WHERE id = 1;
-- T2 reads the uncommitted 0
T2: SELECT balance FROM accounts WHERE id = 1; -- sees 0!
-- T1 rolls back — T2 read data that never existed
T1: ROLLBACK;

Non-Repeatable Read — the same row returns different values in the same transaction.

T1: SELECT balance FROM accounts WHERE id = 1; -- 500
-- T2 updates and commits
T2: UPDATE accounts SET balance = 300 WHERE id = 1; COMMIT;
T1: SELECT balance FROM accounts WHERE id = 1; -- 300 (different!)

Phantom Read — a range query returns different rows on re-execution.

T1: SELECT COUNT(*) FROM orders WHERE user_id = 5; -- 3
-- T2 inserts a new order and commits
T2: INSERT INTO orders(user_id, ...) VALUES (5, ...); COMMIT;
T1: SELECT COUNT(*) FROM orders WHERE user_id = 5; -- 4 (phantom!)

Lost Update — two transactions read the same value and both write, losing one update.

T1: balance = SELECT balance; -- reads 100
T2: balance = SELECT balance; -- reads 100
T1: UPDATE SET balance = 100 + 50; -- writes 150
T2: UPDATE SET balance = 100 + 30; -- writes 130 (T1's update lost!)

Isolation Levels Matrix

*PostgreSQL's REPEATABLE READ also prevents phantoms via MVCC snapshot.

Practical Examples

-- Set isolation level for a session (PostgreSQL)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Check current level
SHOW TRANSACTION ISOLATION LEVEL;

-- Default in most databases
-- PostgreSQL: READ COMMITTED
-- MySQL InnoDB: REPEATABLE READ

How Databases Implement Isolation

Pessimistic Locking — lock rows before reading/writing.

SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- exclusive lock
SELECT * FROM accounts WHERE id = 1 FOR SHARE;  -- shared lock

Optimistic Locking — no locks; detect conflicts at commit time using a version column.

SELECT id, balance, version FROM accounts WHERE id = 1;
-- Application increments version
UPDATE accounts
SET balance = 450, version = version + 1
WHERE id = 1 AND version = 3; -- fails if version changed

MVCC (PostgreSQL, MySQL InnoDB) — readers never block writers; each transaction sees a consistent snapshot.

Choosing an Isolation Level

• READ COMMITTED — default for most apps; good balance of performance and correctness
• REPEATABLE READ — use when a transaction must see consistent data across multiple reads (e.g., generating a report)
• SERIALIZABLE — use for financial transactions where correctness is paramount

Interview Tips

1. Know the default isolation level for at least two databases (PostgreSQL = READ COMMITTED, MySQL = REPEATABLE READ).
2. Explain MVCC — it's the key reason modern databases can have high concurrency without constant lock contention.
3. Explain why SERIALIZABLE is rare in production — it serializes transactions, drastically reducing throughput.