Contrast lock-based concurrency control using two-phase locking with multi-version concurrency control (MVCC): when one transaction reads a row and another writes the same row, what happens under each scheme, and what are the tradeoffs?

## The same question, two answers Both schemes answer "what happens when concurrent transactions touch the same row?", but they start from opposite assumptions. Two-phase locking is **pessimistic**: assume conflict, prevent it by mutual exclusion, make everyone else wait. MVCC is built on a different observation — a reader does not actually need the *current* value, it needs *a consistent* value — so it keeps multiple versions and hands each transaction the one its snapshot should see. ## The reader/writer case, step by step **Under 2PL.** A read acquires a shared (S) lock, a write an exclusive (X) lock; S and X are incompatible. If the reader got there first, the writer waits until the reader releases — and under rigorous 2PL that means until the reader *commits*. If the writer got there first, the reader waits for the writer's commit. So a long analytical query holding shared locks stalls every writer on its rows, and a slow writer stalls every reader. The upside is that this blocking *is* the correctness argument: the two-phase rule plus lock conflicts yields conflict-serializable schedules with no extra machinery. **Under MVCC.** The writer does not overwrite; it appends a new **version** of the row stamped with its transaction identifier (or a commit timestamp) and links it to the previous version. Each transaction has a **snapshot** — a rule for deciding which versions are visible, typically "versions committed before my start, excluding transactions still in flight then". The reader walks the version chain and takes the newest version visible to it. Nothing is locked on the read path, so: - readers do not block writers, - writers do not block readers, - **writers still block writers** — two transactions updating the same row conflict, resolved by a row-level write lock (the second waits) or first-updater-wins (the second aborts). That last point is the one candidates most often get wrong. MVCC removes the read/write conflict, not the write/write conflict. ## What MVCC costs Nothing is free; the costs are just moved from waiting to space and bookkeeping. 1. **Version storage.** Old versions must live somewhere — inline in the table with a background reclaimer, or in a separate undo/rollback area that readers walk backwards through. Either way, write amplification and space grow. 2. **Garbage collection.** Versions older than the oldest active snapshot are dead and must be reclaimed. This is a continuous background cost, and it is the source of the classic operational failure mode: **a long-running reader (or an idle-in-transaction session) pins the oldest snapshot, so nothing can be reclaimed, and version chains and table size grow without bound.** Read latency then degrades as readers traverse long chains. 3. **Visibility checks.** Every row touched requires evaluating whether its version is visible to this snapshot, which costs CPU and sometimes extra I/O for commit-status metadata. 4. **Isolation subtleties.** Snapshot reads mean a transaction can act on a view of the world that is already stale by commit time. ## The correctness gap: snapshot isolation is not serializability This is the heart of a senior answer. **Snapshot isolation (SI)** — every transaction reads from its own consistent snapshot, and concurrent writers to the same row conflict — eliminates dirty reads, non-repeatable reads and the usual phantom problems, yet it is *weaker than serializable*. The counterexample is **write skew**: two transactions each read an overlapping set of rows, check an invariant across them, and each writes a *different* row. No write/write conflict occurs, so both commit, and the invariant spanning the rows is violated — for example, two on-call doctors each check "at least one other person is on duty", see the other, and each removes themselves. Under 2PL this cannot happen: the reads take shared locks that conflict with the other's write, so one transaction blocks and then re-reads reality. MVCC has to recover the guarantee some other way: - **Serializable Snapshot Isolation (SSI)** — track read/write dependencies at runtime and abort a transaction when a dangerous structure (two consecutive rw-dependency edges around a pivot) appears. Optimistic: no blocking, but real workloads see serialization failures and the application must retry. - **Explicit locking reads** — the application promotes the relevant reads to locking reads, reintroducing 2PL-style blocking exactly where an invariant needs it. So the honest framing is: MVCC does not make serializability cheaper for free; it makes *read-mostly* work far cheaper, and pushes the serializability cost into either aborts (SSI) or selective locking. ## Hybrids, which is what you actually run Almost every mainstream relational engine is a hybrid: MVCC on the read path (snapshot reads, no shared locks) and locking on the write path (row write locks, held to commit, i.e. still strict). Some layer range/predicate locking on top of MVCC for their strongest level; others use SSI. The pure 2PL engine — shared locks on every read, held to commit — is now mostly found in older systems and in in-memory or deterministic databases where lock durations are microseconds and versioning would be pure overhead. ## Choosing between them - **Read-heavy, analytics mixed with OLTP, long queries** → MVCC decisively; blocking readers would be fatal, and consistent point-in-time reads are exactly what reporting wants. - **Short, uniform, write-heavy transactions on hot rows** → the version machinery mostly overhead; conflicts are write/write anyway, which both schemes serialize, and 2PL's blocking avoids the abort-and-retry storms optimistic schemes produce under high contention. - **Strong invariants across rows** → be explicit: either true serializable (SSI or locking reads) or accept SI and enforce the invariant with a constraint, a single-row aggregate, or deliberate locking. "We use snapshot isolation so we're safe" is not an answer. The framing to close with: 2PL pays for correctness in **waiting**; MVCC pays in **space, cleanup, and aborts**. Which currency is cheaper is a property of your workload, not of the engine.