Transaction isolation levels define how concurrent transactions interact with each other — specifically, which anomalies are allowed to occur when multiple transactions read and write the same data simultaneously. SQL standard defines four levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Each level trades consistency guarantees for concurrency and performance. PostgreSQL, MySQL InnoDB, and Oracle implement these levels differently. Understanding isolation levels is essential for any system design involving concurrent database access, and is a frequent interview topic for backend and distributed systems roles.
Concurrency Anomalies
Without isolation, concurrent transactions can produce incorrect results. The four standard anomalies: Dirty read: transaction A reads data written by transaction B that has not yet committed. If B rolls back, A read invalid data. Non-repeatable read: transaction A reads a row; transaction B updates and commits that row; A reads the row again and gets a different value within the same transaction. Phantom read: transaction A reads a set of rows matching a condition; transaction B inserts new rows matching that condition; A re-reads and gets more rows. Lost update: two transactions both read a value, both compute an update based on the read, and both write — the second write overwrites the first (classic bank balance update race). Serialization anomaly: the result of concurrent transactions is inconsistent with any serial execution order.
-- Isolation level examples
-- READ COMMITTED (PostgreSQL default): no dirty reads
-- Each statement sees a fresh snapshot (not the entire transaction)
-- Allows non-repeatable reads and phantom reads
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- sees 100
-- Another transaction commits: balance = 50
SELECT balance FROM accounts WHERE id = 1; -- NOW sees 50 (non-repeatable read)
COMMIT;
-- REPEATABLE READ (MySQL InnoDB default): no dirty or non-repeatable reads
-- Transaction sees snapshot from its start time
-- Prevents non-repeatable reads, allows phantoms (MySQL prevents phantoms too via gap locks)
-- SERIALIZABLE: full isolation
-- PostgreSQL uses Serializable Snapshot Isolation (SSI)
-- Detects read-write conflicts and aborts transactions that would create anomalies
-- Highest consistency, highest overhead
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ... complex read-modify-write ...
COMMIT; -- May fail with "could not serialize access due to concurrent update"
-- Application must retry on serialization failure
-- Optimistic concurrency control alternative:
UPDATE orders SET status = 'shipped', version = version + 1
WHERE id = 123 AND version = 5; -- fails if another transaction updated it
-- check rows_affected = 1, else retryMVCC: Multi-Version Concurrency Control
Modern databases (PostgreSQL, MySQL InnoDB) implement isolation using MVCC: instead of locking rows for reads, they maintain multiple versions of each row. Each row version has a creation transaction ID and a deletion transaction ID. A transaction sees only rows whose creation transaction committed before the transaction started and whose deletion transaction has not yet committed. This means reads never block writes and writes never block reads — reads always see a consistent snapshot. PostgreSQL stores all row versions in the heap (the table itself), using VACUUM to remove dead row versions. MySQL InnoDB stores row versions in the undo log. MVCC enables the Snapshot Isolation level efficiently, but Repeatable Read under MVCC can still allow write skew (a type of serialization anomaly not covered by the four standard anomalies).
Choosing the Right Isolation Level
Read Committed (PostgreSQL default): appropriate for most OLTP applications. Each statement sees committed data. Prevents dirty reads. Susceptible to non-repeatable reads and lost updates (use SELECT FOR UPDATE or optimistic locking for write conflicts). Repeatable Read: use when a transaction reads the same data multiple times and consistency between reads is required (balance calculation, report generation). Serializable: use for financial transactions, inventory management, or any workflow where write skew could produce incorrect results. Test-and-set operations (reserve the last seat on a flight) require Serializable or application-level locking. Performance hierarchy: Read Committed (fastest, highest concurrency) > Repeatable Read > Serializable (slowest, must detect and abort conflicting transactions). PostgreSQL SSI is relatively efficient (not lock-based) but adds overhead vs. Repeatable Read.
Key Interview Discussion Points
- Write skew: two concurrent transactions each read a set of rows, make a decision based on the read, and write to different rows — the combined result violates an invariant that neither write alone would violate; example: two doctors both read “is there an on-call doctor?” (yes), both go off-call — now no doctor is on call; only Serializable prevents this
- SELECT FOR UPDATE: acquire a write lock on the selected rows within the current transaction, preventing other transactions from modifying those rows until commit — prevents lost updates and write skew for specific rows
- Snapshot Isolation vs Serializable: Snapshot Isolation prevents dirty reads, non-repeatable reads, and phantoms but allows write skew; Serializable (SSI in PostgreSQL) additionally prevents write skew
- Distributed transactions: achieving Serializable isolation across multiple shards requires 2-phase locking (2PL) or distributed SSI — Google Spanner uses TrueTime + 2PL for external consistency; CockroachDB implements SSI across distributed nodes
- Lock granularity: row-level locks (PostgreSQL, MySQL InnoDB) enable high concurrency; table-level locks serialize all access to a table; advisory locks (pg_advisory_lock) allow application-level locking on arbitrary integer keys