Transaction Isolation And MVCC

What's being tested

This topic tests whether you can reason precisely about concurrent transactions, not merely recite isolation-level names. You should be able to identify anomalies, explain how MVCC implements visibility, choose an appropriate isolation level, and recognize when locks or serializable execution are required. OpenAI systems often coordinate durable state, quotas, jobs, permissions, and billing-like invariants, where a rare race can become a correctness or safety incident. The interviewer is probing whether you can connect an abstract guarantee to concrete database behavior, latency, contention, and failure modes.

Core knowledge

• Atomicity, consistency, isolation, durability (ACID) are separate properties. Isolation controls interleaving; consistency means declared constraints remain valid; durability depends on logging and storage. Do not claim “ACID” automatically means every transaction behaves serializably.

• Read committed usually prevents dirty reads but allows non-repeatable reads and phantoms. In Postgres, each statement gets a snapshot; two SELECTs in one transaction may observe different committed states.

• Repeatable read provides a stable transaction-level snapshot in many MVCC databases. In Postgres, it prevents ordinary non-repeatable reads and phantoms but can abort on concurrent update conflicts; MySQL InnoDB has related but not identical semantics.

• Serializable isolation guarantees an outcome equivalent to some serial transaction order. Implementations use strict two-phase locking, Serializable Snapshot Isolation (SSI), or validation; stronger correctness costs blocking, aborts, memory, or throughput.

• MVCC stores multiple row versions, commonly with transaction IDs and commit metadata. A reader chooses versions visible to its snapshot, so readers can avoid blocking writers; old versions require garbage collection such as VACUUM in Postgres.

• A snapshot is typically defined by committed transactions visible at a point in time. A version is visible if its creating transaction committed before the snapshot and its deleting transaction was not yet visible; exact metadata and rules are engine-specific.

• Lost update occurs when concurrent read-modify-write operations overwrite one another. Prevent it with an atomic SQL update, a conditional predicate such as WHERE version = ?, SELECT ... FOR UPDATE, or serializable execution—not merely by wrapping reads and writes in a transaction.

• Write skew is the critical snapshot-isolation anomaly: two transactions read a shared invariant, update disjoint rows, and jointly violate that invariant. Lock a common coordination row, use a constraint that the database can enforce, or use serializable isolation.

• Pessimistic locking acquires locks before changing or reading protected data; SELECT ... FOR UPDATE is a common pattern. Optimistic concurrency control detects conflicts at commit or through version predicates, often improving throughput when contention is low but causing retries under load.

• Deadlocks arise from cyclic lock waits, even when every individual transaction is correct. Keep transactions short, access tables and rows in a consistent order, configure lock timeouts, and retry only bounded, safely replayable transactions.

• Isolation is distinct from durability and replication visibility. A committed write on the primary may not immediately appear on a lagging replica; route correctness-sensitive reads appropriately, and do not use replica reads for a read-your-writes guarantee without a session or log-position mechanism.

• Transaction boundaries should match the invariant being protected. Holding a transaction across network calls increases lock duration and failure exposure; doing the business decision outside the transaction without a recheck creates a race. Prefer short database-local transactions with explicit predicates.

Worked example

Representative prompt: “Explain transaction isolation and MVCC.” In the first 30 seconds, a strong candidate would ask which database engine is intended, whether the goal is throughput or strict serializability, and whether examples should include concurrent writes, while declaring that isolation semantics are engine-specific. They would organize the answer around four pillars: anomalies and guarantees, snapshot/version visibility, lock-based versus optimistic conflict handling, and operational tradeoffs such as aborts, deadlocks, and vacuum. They would explain that READ COMMITTED commonly takes statement snapshots, while repeatable read takes a transaction snapshot, so readers can avoid blocking writers but may observe different behavior across engines. They would use a write-skew example, such as two doctors independently taking themselves off call while an invariant requires one doctor to remain, to show why snapshot isolation is not always serializable. They would explicitly flag the tradeoff between serializable isolation and lower isolation: serializable protects invariants but may increase blocking or transaction retries. They would distinguish SELECT ... FOR UPDATE from a plain read and explain why a lock must cover the rows or coordination object relevant to the invariant. They would close by saying that, with more time, they would examine the engine’s execution plans, lock-wait metrics, retry policy, and replica-read behavior under failure.

A second angle

Representative prompt: “Two concurrent requests can both observe available quota and exceed the limit; how would you prevent it?” The framing shifts from explaining internals to selecting a correctness mechanism for a concrete invariant. A strong answer would prefer an atomic conditional update such as UPDATE quota SET used = used + ? WHERE used + ? <= limit, then verify affected-row count, rather than relying on a separate read followed by a write. If the invariant spans multiple rows or resources, they would consider a coordination-row lock, a database constraint, or serializable isolation with bounded retries. They would also discuss idempotency so a client retry does not consume quota twice.

Common pitfalls

Pitfall: Saying “repeatable read prevents all anomalies” is too broad. It may prevent dirty and non-repeatable reads while still permitting write skew, depending on the database; name the engine and identify the invariant being protected.

Pitfall: Listing dirty read, phantom read, and serializability definitions without describing an interleaving sounds memorized. Show two transactions, state what each reads, and identify the incorrect final state or guarantee.

Pitfall: Treating MVCC as “readers never block writers” ignores locks, predicate conflicts, schema changes, garbage collection, and serialization failures. Explain the common fast path, then mention the cases that still block or abort.

Connections

An interviewer may pivot to deadlock detection, optimistic concurrency control, database indexing and lock scope, or distributed transaction boundaries. They may also connect isolation to idempotency, retry safety, replication lag, and consistency models such as linearizability.

Further reading

• “Serializable Snapshot Isolation in PostgreSQL” — explains how Postgres detects dangerous structures without traditional blocking for every read.

• Designing Data-Intensive Applications, Martin Kleppmann — clear treatment of isolation anomalies, MVCC, serializability, and distributed consistency.

• A Critique of ANSI SQL Isolation Levels, Berenson et al. — formalizes why isolation-level names alone are insufficient.