Transactions & 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

Lock granularity: row vs table vs page

Pessimistic locking (SELECT ... FOR UPDATE, shown above) can be applied at different granularities: row-level locks only the specific rows a query touches — the finest granularity, minimizing contention between unrelated queries. Table-level locks the entire table — coarse, simple, but serializes unrelated operations that happen to touch the same table. Page-level (a middle ground some databases use internally) locks a fixed-size block of rows stored together on disk. Most modern relational databases default to row-level locking for standard DML, escalating to coarser locks only in specific circumstances (e.g. certain DDL operations, or explicit table locks) — row-level is almost always the right default to assume and reach for explicitly.

Lock timeout configuration

SET LOCK_TIMEOUT 5000; -- milliseconds — fail rather than block indefinitely

Without a timeout, a transaction waiting on a lock held by another (possibly stuck, possibly just slow) transaction blocks indefinitely — a lock timeout converts an indefinite hang into a bounded failure the application can catch and retry, the same tradeoff tryLock(timeout) makes for in-process locks (see Deadlock, Starvation, Livelock).

Optimistic locking in JPA specifically

@Entity
class Account {
    @Id Long id;
    double balance;
    @Version Long version; // JPA increments this automatically on every UPDATE
}

// On save, if the version in the database no longer matches what was loaded:
// throws OptimisticLockException — the application must catch it and decide: retry, or surface a conflict to the user

@Version is JPA's direct implementation of the optimistic-locking pattern shown above — Hibernate automatically appends AND version = ? to the generated UPDATE statement and checks the affected row count, exactly matching the manual version column technique, just wired in declaratively rather than hand-written.

Choosing between optimistic and pessimistic

Optimistic fits low-contention, retry-friendly workflows — most web application updates, where two users editing the same record at the exact same moment is rare, and a retry-on-conflict is cheap and non-disruptive. Pessimistic fits high-contention scenarios where a retry would be wasteful or where correctness under guaranteed contention matters more than throughput — decrementing limited inventory during a flash sale is the canonical example, where many concurrent requests targeting the same row is the expected case, not an edge case.

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.