Transactions & isolation

What the four isolation levels prevent

A transaction groups statements so they succeed or fail together (COMMIT or ROLLBACK), that is the A in ACID. Isolation controls how much of other in-flight transactions yours can see. The SQL standard names four levels, defined by which read anomalies they forbid:

A dirty read is seeing another transaction's uncommitted change. A non-repeatable read is re-reading one row and getting a new committed value. A phantom read is re-running a range query (WHERE created > ...) and finding rows that were inserted or deleted meanwhile. The standard describes what each level forbids, not how the engine does it, so real databases are sometimes stricter. PostgreSQL and Oracle default to Read Committed; MySQL InnoDB defaults to Repeatable Read.

The read-modify-write bug

The concurrency bug you will actually hit is the lost update. Two requests read a value, each adds to it in application code, and one overwrites the other:

-- both sessions run this at the same time
SELECT balance FROM accounts WHERE id = 1;  -- both read 100
-- app computes 100 + 50
UPDATE accounts SET balance = 150 WHERE id = 1;  -- second write wins, first +50 is gone

Two safe rewrites. Compute in the database so the row lock serializes the increment:

UPDATE accounts SET balance = balance + 50 WHERE id = 1;

Or lock the row while you decide, so the second session waits:

SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

wrong "I'm on Repeatable Read, so my SELECT then UPDATE is safe." Repeatable Read keeps your reads consistent, but two transactions can still each read the old value and write based on it. Snapshot-based engines catch this only when both write the same row, and some anomalies (write skew) slip through even then. Consistent reads are not the same guarantee as a serialized write.

Locking, briefly

Two ways to protect a read-modify-write. Pessimistic: lock the row up front with SELECT ... FOR UPDATE and make others wait. Optimistic: add a version column, and on write do UPDATE ... WHERE version = :seen; if zero rows change, someone beat you and you retry. Pessimistic suits hot rows with real contention; optimistic wins when conflicts are rare and you would rather not hold locks. Keep transactions short either way, because a lock held across a network call is how throughput dies.

How MVCC and snapshot isolation work

Most modern engines (PostgreSQL, Oracle, MySQL InnoDB) do not implement isolation with read locks. They use MVCC: every UPDATE writes a new version of the row and leaves the old one visible to transactions that started earlier. Each statement or transaction reads against a snapshot assembled from version metadata (in Postgres, the xmin/xmax stamps plus the in-flight transaction set), so readers never block writers and writers never block readers.

Snapshot isolation is what Postgres calls Repeatable Read. Your transaction pins one snapshot at its first statement and reads it for its whole life, so dirty reads, non-repeatable reads, and phantoms are all gone (Postgres forbids phantoms here, stricter than the SQL standard). Writes still enforce order: if two transactions update the same row, the second blocks until the first commits, then gets a serialization failure (40001) under first-updater-wins. MVCC does lock, just on the rows a write actually touches.

Why write skew survives snapshot isolation

The hole is write skew. Two transactions read an overlapping set, then write disjoint rows, so the row-level conflict check never fires:

-- invariant: at least one doctor on call. Both start on call.
-- T1                                    -- T2
SELECT count(*) WHERE on_call;  -- 2     SELECT count(*) WHERE on_call;  -- 2
UPDATE doctors SET on_call=false         UPDATE doctors SET on_call=false
  WHERE name='Alice';                      WHERE name='Bob';
COMMIT;                                  COMMIT;   -- both succeed, zero on call

Each transaction read a consistent snapshot and wrote a row the other never read, so nothing looks wrong locally. The invariant spans rows, and no single row was double-written, so snapshot isolation lets both commit.

Three fixes, and interviewers grade which one you reach for. Run both as SERIALIZABLE: Postgres uses Serializable Snapshot Isolation, tracks the read/write dependency, detects the dangerous cycle, and aborts a victim with 40001 for you to retry. SSI adds no blocking and costs a few percent over snapshot isolation, but you must wrap writes in a retry loop. Or take explicit SELECT ... FOR UPDATE locks on the on-call rows to materialize the conflict. Or push the invariant into a constraint so the second commit fails outright.

wrong "Snapshot isolation is serializable, so I'm safe." Snapshot isolation is strictly weaker; write skew is the standard counterexample, and conflating the two is the fastest way to lose this question.

How databases detect and resolve deadlocks

A deadlock is a cycle in the wait-for graph: T1 holds row A and wants B, T2 holds B and wants A. Engines do not prevent this; they detect it and abort a victim. InnoDB rolls back the transaction that changed the fewest rows (least-weight victim); Postgres waits out deadlock_timeout (1s default), checks for a cycle, and aborts one side with error 40P01. Your application retries the failed transaction. The defenses: acquire locks in a consistent order everywhere, keep transactions short, and treat serialization and deadlock failures as normal retryable outcomes, not crashes. Durability closes the loop: COMMIT flushes the write-ahead log to disk (fsync), so the change survives a crash even though data pages are written back lazily.