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.

Write skew: the anomaly most people miss

Dirty reads, non-repeatable reads and phantoms are about reading. The anomaly that bites real systems under Repeatable Read or Snapshot Isolation is write skew. Two transactions read overlapping data, each makes a decision based on what it read, and each writes a different row. Neither overwrote the other, so no lock conflict or lost-update check fires, yet together they break a rule.

Example rule: at least one doctor must be on call.

-- Both transactions run concurrently, under Repeatable Read / Snapshot Isolation
-- T1 (Dr. Asha)                                  -- T2 (Dr. Vikram)
SELECT count(*) FROM doctors WHERE on_call;       SELECT count(*) FROM doctors WHERE on_call;
-- sees 2 → "someone else is on call"              -- sees 2 → "someone else is on call"
UPDATE doctors SET on_call = false WHERE id = 1;  UPDATE doctors SET on_call = false WHERE id = 2;
COMMIT;                                            COMMIT;
-- Result: nobody is on call.

Fixes, from the most general to the most targeted:

• Serializable isolation. PostgreSQL's SSI detects this pattern and aborts one transaction with a serialization failure, which the application retries.
• Lock the rows the decision depends on: SELECT … FROM doctors WHERE on_call FOR UPDATE. The second transaction waits for the first, then sees the updated count.
• Materialize the conflict: make both transactions update one shared row (for example, a per-shift counter), so ordinary row locking serializes them.
• Enforce the rule with a constraint where the database can express it.

Booking systems face the same shape: two users each check that a room is free, then each insert a booking. Exclusion constraints, unique constraints or FOR UPDATE on a parent row prevent it.

Retrying serialization failures correctly

Under Serializable (and sometimes Repeatable Read), the database may abort a transaction with SQLSTATE 40001 (serialization failure) or 40P01 (deadlock). That's expected behaviour, not a bug, and the transaction must be retried from the beginning:

public <T> T inSerializableTx(Supplier<T> work) {
    for (int attempt = 1; ; attempt++) {
        try {
            return transactionTemplate.execute(status -> work.get());   // template configured with ISOLATION_SERIALIZABLE
        } catch (CannotAcquireLockException | ConcurrencyFailureException e) {
            if (attempt >= 3) throw e;
            sleepWithJitter(attempt);                                   // brief randomized backoff
        }
    }
}

• The retry wraps the whole transaction. Re-running only the failed statement would use stale reads.
• Keep external side effects out of retried transactions (sending emails, calling payment APIs), or they happen once per attempt. Record the intent in the database (an outbox) and act after the commit.

Follow-up questions this topic invites — and their answers

Q: What's the default isolation level in PostgreSQL and MySQL? A: PostgreSQL defaults to Read Committed. MySQL InnoDB defaults to Repeatable Read, which, through MVCC snapshots and next-key locks, also prevents most phantoms for consistent reads. Always check the actual default, because behaviour at the same named level differs between databases.

Q: What is write skew, and which isolation level prevents it? A: Two concurrent transactions read overlapping data, and then write different rows based on what they read, jointly violating an invariant. Snapshot isolation / Repeatable Read allows it. Serializable prevents it, as do explicit locks on the rows that were read, or constraints that encode the invariant.

Q: Why does Serializable need retry logic? A: To guarantee a serial-equivalent result, the database aborts one of two conflicting transactions, instead of letting both commit. The aborted transaction did nothing wrong, and succeeds when re-run, so applications must catch serialization failures and retry the whole transaction.

Q: Optimistic or pessimistic locking? A: Optimistic (version columns, retry on conflict) suits low-contention workloads and long user think-time, since no locks are held while the user edits. Pessimistic (SELECT … FOR UPDATE) suits high-contention hotspots, where retries would keep failing, and short critical sections, such as inventory decrements on a popular item.

Q: Does @Transactional in Spring use the database's default isolation? A: Yes, unless you set @Transactional(isolation = Isolation.SERIALIZABLE) or similar. Also remember that @Transactional only applies through Spring's proxy (not on self-invocation), and by default rolls back only on unchecked exceptions.