Transactions, isolation anomalies and MVCC

Why it matters

Many concurrency bugs in backend systems are races between transactions rather than between threads: two requests read the same balance, both decide there is enough money, both write. Whether that can happen depends on the isolation level, and defaults are weaker than many people assume (PostgreSQL defaults to READ COMMITTED). Interviewers use these questions to see whether you can connect an invariant, such as "no double booking", to a mechanism that actually protects it.

Core ideas

ACID, read carefully. Atomicity is all-or-nothing on abort or crash, implemented with logging. Consistency means the application's invariants; the database only enforces the constraints you declare. Isolation describes what concurrent transactions may observe of each other. Durability means committed data survives crashes, under assumptions about storage.

Serializability is the reference. A schedule is serializable if its effect equals some serial order of the same transactions. Conflict serializability is a checkable sufficient condition: add an edge Ti → Tj whenever an operation of Ti conflicts with a later operation of Tj (same item, at least one write); if the graph has no cycle, the schedule is serializable.

Anomalies as interleavings.

• Dirty read: T2 reads a value T1 wrote but has not committed.
• Non-repeatable read: T1 reads x twice and sees T2's committed update in between.
• Phantom: T1 repeats a predicate query and sees rows T2 inserted.
• Lost update: both read x, both write a value computed from what they read; one write is lost.
• Write skew: both read an overlapping set, then each writes a different item; each write is fine alone, together they break an invariant (both on-call doctors go off call).

Locking. Strict 2PL takes shared locks to read and exclusive locks to write and holds them until commit; every schedule it admits is conflict-serializable, provided phantoms are blocked with predicate, range or next-key locks. The price is blocking and deadlocks, handled by detecting cycles in a waits-for graph or by prevention schemes such as wound-wait.

MVCC and snapshot isolation. Writers create new versions and each transaction reads a snapshot of committed versions, so readers and writers do not block each other. Under snapshot isolation (PostgreSQL's REPEATABLE READ) all reads come from one snapshot, and a transaction that updates a row changed by a concurrent committed transaction is aborted. That prevents lost updates on a row, but not write skew: the two transactions write different rows and never conflict on a write.

SSI. PostgreSQL's SERIALIZABLE keeps snapshot reads and tracks read-write dependencies with non-blocking predicate locks. When it sees two consecutive read-write dependencies through one transaction, a pattern every SI anomaly contains, it aborts a transaction with SQLSTATE 40001. The check is conservative, so applications must retry the whole transaction.

-- write skew under REPEATABLE READ; table doctors(id, on_call)
S1: BEGIN ISOLATION LEVEL REPEATABLE READ;
S1: SELECT count(*) FROM doctors WHERE on_call;          -- 2
S2: BEGIN ISOLATION LEVEL REPEATABLE READ;
S2: SELECT count(*) FROM doctors WHERE on_call;          -- 2
S1: UPDATE doctors SET on_call = false WHERE id = 1; COMMIT;
S2: UPDATE doctors SET on_call = false WHERE id = 2; COMMIT;
-- both commit and nobody is on call; under SERIALIZABLE one fails

Fixes in practice. Prefer one atomic statement (UPDATE acct SET bal = bal - 10 WHERE id = 1 AND bal >= 10), a constraint (UNIQUE, CHECK, exclusion), or explicit row locks (SELECT ... FOR UPDATE) on the rows that represent the invariant. Use SERIALIZABLE when the invariant spans rows you cannot easily lock, and always wrap it in a retry loop.

Common misconceptions

• "Isolation level names mean the same thing everywhere." PostgreSQL's REPEATABLE READ is snapshot isolation; MySQL InnoDB's uses snapshots for plain reads but its locking reads and updates act on the latest committed rows, so lost updates remain possible there. Read the documentation of the system you use.
• "Snapshot isolation is serializable." It prevents the ANSI phenomena but allows write skew and some read-only anomalies; some systems even label it SERIALIZABLE.
• "SELECT then UPDATE in one transaction is safe." Under READ COMMITTED two transactions can both read the old value; use an atomic update or a row lock.
• "SERIALIZABLE means no errors to handle." It means the database may abort you; correctness depends on retrying.
• "MVCC removes locking." Concurrent writers to the same row still wait for each other, and DDL takes table locks.
• "ACID consistency is CAP consistency." The first is about application invariants; the second is linearizability of replicated data.

How to approach it

Learn the anomalies as two-column timelines, then run them: two psql sessions reproduce lost update, write skew and a serialization failure in minutes. In interviews, start from the invariant, name the race, propose the cheapest mechanism that closes it and state its cost (blocking, aborts, retries). Senior and research conversations continue into how 2PL and SSI are implemented, how deadlocks are resolved and what long transactions do to version cleanup.