Compare how a plain SELECT behaves in a relational engine that uses strict two-phase locking with shared read locks against one that uses multi-version concurrency control, and explain what that difference costs and buys.
answer
- 2PL: consistency by freezing rows
- MVCC: consistency by keeping versions
- S lock held to commit blocks writers
- MVCC deadlocks only writer-writer
- MVCC price: version space + oldest snapshot pins cleanup
basics
~20 sUnder two-phase locking a SELECT takes shared locks held to commit, so it blocks any writer of those rows and can deadlock. Under MVCC it takes no row locks and reads a snapshot, so it never blocks writers, but it may read data that is already stale and the engine must store and reclaim old versions.
solid answer
~60 s**Pure 2PL:** reading a row requires a **shared (S) lock**, incompatible with the exclusive lock a writer needs. Under *strict* 2PL those S locks are held until commit. So a reader gives you a consistent view by physically freezing the rows: writers queue behind the report, read-write deadlocks become possible, and lock manager memory and lock escalation become real operational concerns. The upside is that what you read is the current committed state, and serializability follows from the protocol. **MVCC:** the reader takes no row locks; it consults a snapshot and reads the version visible to it. Writers proceed concurrently by adding new versions. Reads never block writes and never deadlock with them. The costs move elsewhere: multiple versions consume space and must be reclaimed, long readers hold that reclamation back, the data read is as-of-snapshot rather than as-of-now, and plain snapshot isolation is weaker than serializable — it permits write skew, so engines add explicit locking or a serializable layer on top. Many engines are hybrids: MVCC by default with explicit lock requests available when you deliberately want a reader to block writers.
go deeper
Contrast the two mechanisms in one sentence each: 2PL takes shared read locks so readers and writers take turns; MVCC reads a version so they do not.
Derive the consequences from the mechanism — blocking, deadlock surface, freshness — and name at least one real cost on each side.
Bring in operations: long readers stalling writes under 2PL versus long snapshots pinning version cleanup under MVCC, and where you would deliberately request locking reads.
Discuss workload fit and the isolation ladder: which paths tolerate snapshot semantics, which need serializable and retries, and how the contention profile shifts from readers to hot rows.
## Two ways to get a consistent read A transaction that reads several rows wants them to look like a single point in time. There are two fundamentally different ways to arrange that. **Lock the world still.** Two-phase locking (2PL) says a transaction acquires locks in a growing phase and releases them in a shrinking phase, never acquiring after the first release. *Strict* 2PL holds all locks to commit. Reads take shared locks; writes take exclusive locks; shared and exclusive are incompatible. Consistency comes from the fact that nobody could change what you read while you held the lock, and the protocol yields serializable schedules. **Read a frozen copy.** MVCC keeps old row versions alive and gives each transaction a snapshot rule for choosing which version it may see. Consistency comes from the versions themselves, so nothing has to be locked to read it. ## Behaviour of a plain SELECT, side by side | | Strict 2PL with S locks | MVCC snapshot read | |---|---|---| | Locks taken on read | Shared lock per row (or page/table after escalation) | None | | Lock lifetime | Until commit | n/a | | Effect on concurrent writers | They wait for the reader | They proceed immediately | | Effect of a concurrent writer on the reader | Reader waits for the writer's X lock | Reader sees the older visible version | | Deadlock potential with writers | Yes, including lock upgrades | No read-write deadlocks | | Freshness of the data read | Latest committed at read time | As of the snapshot; may already be stale | | Main resource cost | Lock manager entries, escalation, waiting | Version storage plus reclamation work | ## What the 2PL side buys It buys a very strong guarantee cheaply expressed: if you read it and hold the lock, it cannot change. A read-modify-write becomes safe simply by being inside the transaction, because the shared lock (upgraded to exclusive) prevents anyone else's write. There is no version storage, no reclamation background work, and no notion of reading the past — the data you read is the live data. ## What the 2PL side costs A long analytical query holds shared locks across a wide swath of the table, so the write path stalls until it finishes. Systems mitigate this with escalation (many row locks become one table lock — which makes blocking worse, not better), with dirty reads via a read-uncommitted hint (correctness thrown away for throughput), or with a snapshot mode bolted on. Read-write deadlocks are common, especially with lock upgrades: two transactions both hold a shared lock on the same row and both try to upgrade, so one must die. And the lock manager itself is a shared, contended structure. ## What MVCC buys and costs It buys a write path that is unaffected by read volume and read latency unaffected by write transactions. Reporting can coexist with OLTP. Deadlock surface shrinks to writer-writer cycles. It costs storage for versions plus a reclamation mechanism, and the reclamation is *held back by the oldest live snapshot* — which is why a forgotten idle-in-transaction session is an operational incident in MVCC engines and merely a lock problem in 2PL engines. It also changes the correctness story. Because the read took no lock, the row can change immediately after you read it. Read-modify-write in application code is unsafe by default and needs either an explicit lock request, a conditional write that checks the value or a version column, or a serializable isolation level with retries. Snapshot isolation alone allows **write skew**: two transactions each read an overlapping set, each check an invariant that holds in their own snapshot, and each write a different row, leaving the invariant violated. Engines address this either by adding predicate/next-key locking on top of MVCC or by implementing serializable snapshot isolation, which detects dangerous read-write dependency structures and aborts a transaction. ## Hybrids are the norm Production engines are rarely pure. Typical designs use MVCC for ordinary reads, exclusive row locks for writes, optional explicit read locks so you can *choose* to block writers where correctness demands it, and a serializable mode layered on the snapshot machinery. Some also let a session request an explicitly locking read that behaves like the 2PL case for just those rows. ## How to answer Don't reduce the comparison to \"MVCC is faster\". Say what each protocol uses to obtain a consistent read (locks vs versions), derive the blocking behaviour from that, then name the price each pays: 2PL pays in waiting, deadlocks and lock-manager pressure; MVCC pays in version storage, reclamation held hostage by long snapshots, stale-by-design reads, and a weaker default isolation that needs help to reach serializable.
- Under MVCC, how do you deliberately make a reader block writers when correctness needs it?Use an explicitly locking read — SELECT ... FOR UPDATE (or FOR SHARE) — which takes real row locks held to commit, giving 2PL semantics for exactly those rows. Alternatively use a conditional write that verifies the value or a version column, and retry when it fails, or run the transaction at a serializable isolation level and handle the aborts.
- Why is an idle-in-transaction session more damaging in an MVCC engine than a pure locking engine?Its snapshot may still need old versions, so the engine cannot reclaim any version newer than that snapshot anywhere in the database. Dead versions accumulate across all tables, inflating storage and slowing scans, even though the idle session touches nothing. In a pure locking engine an idle transaction only blocks the specific rows it locked.
- What anomaly does plain snapshot isolation allow that strict 2PL does not?Write skew: two transactions read overlapping data, each verifies an invariant that holds in its own snapshot, then each writes a different row, so neither conflicts at write time yet the combined result breaks the invariant — for example both on-call doctors signing off simultaneously. Strict 2PL prevents it because the reads hold shared locks. MVCC engines fix it with explicit locking or serializable snapshot isolation.
2PL is checking a library book out so nobody can annotate it while you read; MVCC is photocopying the page you need, letting the annotator scribble on the original immediately — at the cost of piles of photocopies someone must eventually shred.
saying these in an interview costs you the question
- Saying MVCC is strictly better with no costs to name
- Claiming a snapshot read is always as fresh as a locking read
- Thinking MVCC provides serializability by itself
- Not knowing that shared locks in strict 2PL are held to commit, not to end of statement
- Believing lock escalation improves concurrency