Two engines both advertise REPEATABLE READ: one implements it by holding shared locks on every row read until commit, the other by giving the transaction one fixed multiversion snapshot. How does observable behaviour differ — blocking, phantoms, and failure modes?
answer
- 2PL: S locks held to commit -> waits, deadlocks
- MVCC: no read locks -> staleness, 40001 aborts
- phantoms possible in 2PL, absent in snapshot plain reads
- long snapshot = version bloat / oldest open txn
- MVCC removes read locks, not write locks
basics
~20 sLock-based: readers block writers, phantoms still possible, failures appear as lock waits and deadlocks. Snapshot-based: reads never block, plain reads see no phantoms, but the view goes stale and conflicting writes fail with a serialization error the application must retry.
solid answer
~50 sSame guarantee, opposite operating characteristics. **Lock-based (strict 2PL).** Reading takes a shared lock held to end of transaction. A writer for that row waits. Reads are therefore never stale — the data physically cannot change — but read-heavy transactions stall writers, lock footprints grow with the rows touched, and the characteristic failure is a lock wait timeout or a deadlock resolved by aborting a victim. Phantoms remain possible because non-existent rows cannot be locked. **Snapshot-based (MVCC).** Reads take no locks and never block or get blocked; every read is served from one snapshot fixed at the transaction's first read. Plain range queries are phantom-free. The cost moves elsewhere: your view goes stale as the world commits past you, so a write against a row changed since your snapshot cannot silently proceed — the engine aborts you with a serialization failure (SQLSTATE 40001) — and long transactions hold back version cleanup. Operationally: tune for lock waits in the first, for aborts, retries and version bloat in the second.
go deeper
Know that the same level can be built from read locks or from snapshots, and that one waits while the other can fail.
Contrast blocking behaviour, phantom behaviour and the typical error each produces.
Discuss operations: lock waits and deadlocks versus abort rates, retry paths, version bloat and monitoring the oldest open transaction.
Frame the choice as where you pay for consistency — latency variance from waiting versus application complexity from retries — and how that shapes transaction-length policy across services.
## Same contract, two machines Both implementations promise the same thing: a row you read once keeps its value for your whole transaction. How they achieve it drives everything you observe in production. ## Lock-based REPEATABLE READ Mechanism: strict two-phase locking with long-duration shared locks. Reading row R takes an S lock on R; the lock is not released at end of statement but held until COMMIT or ROLLBACK. An UPDATE needs an X lock, which conflicts with S, so the writer waits. Consequences: - **Readers block writers, writers block readers.** Concurrency drops as read sets grow. A transaction that scans a large range locks a large range. - **Reads are current, not stale.** Whatever you read is still true at commit time, because nobody could change it. - **Deadlock is the main failure mode.** Two transactions that read then upgrade to write in opposite orders deadlock; the engine picks a victim and aborts it. Lock-wait timeouts are the other visible symptom. - **Phantoms persist.** Rows not yet inserted cannot be S-locked, so a repeated range query can grow. Preventing that requires locking ranges or predicates — machinery associated with the strongest level. - **Lock memory and escalation.** Very large read sets pressure the lock manager; some engines escalate to coarser locks, collapsing concurrency further. ## Snapshot (MVCC) REPEATABLE READ Mechanism: each write creates a new row version; each transaction has a snapshot — a description of which transactions had committed at a point in time — fixed at its first read and used for every subsequent read. Consequences: - **Reads never block and are never blocked.** Read-mostly workloads scale far better; a long analytical transaction does not stop OLTP writers. - **Plain reads are phantom-free.** Later INSERTs produce invisible versions, so repeated range queries are stable. - **Your view is stale on purpose.** The longer the transaction, the more the visible world diverges from reality. Decisions made from the snapshot may rest on data another transaction has already changed. - **Write conflicts surface as aborts, not waits.** If you update a row whose latest version was committed after your snapshot, the engine cannot let you overwrite silently (that would be a lost update) and cannot show you the new value (that would break read stability), so it aborts your transaction with a serialization failure. Applications must retry. - **Version retention is the operational cost.** Old versions cannot be reclaimed while any snapshot might need them, so one long-running transaction inflates table and index size and degrades scans across the whole database — a classic production incident. - **Writes still take locks.** MVCC removes read locks, not write locks: two transactions updating the same row still serialize on it, and deadlocks between writers remain possible. ## The mixed case Some MVCC engines serve plain SELECTs from the snapshot but make *locking* reads and the read part of UPDATE/DELETE see the latest committed rows and take range locks. In such an engine one transaction can plausibly count five matching rows with a SELECT and then have an UPDATE with the same predicate affect six. Any correctness argument must therefore be made per statement type, not per level name. ## How to choose and operate - Read-heavy, longer transactions, dashboards, exports: snapshot behaviour is far friendlier — but bound the transaction's lifetime and monitor the oldest open transaction. - Read-then-write logic in a snapshot engine: assume aborts and implement a retry path from the start; the alternative is taking an explicit row lock on read so the conflict becomes a wait you control. - Lock-based engine: keep read sets narrow (index access, not scans), order access consistently to reduce deadlocks, and keep the transaction short because every lock is held to the end. ## The one-line summary Lock-based REPEATABLE READ pays for consistency with **waiting**; snapshot REPEATABLE READ pays with **staleness plus retries and version retention**. The guarantee you quote in a design doc is the same; the incidents you get paged for are not.
- On the snapshot engine, how do you turn a possible abort into a wait instead?Take an explicit row lock when you read the row you intend to modify — a locking read — so the second transaction blocks at read time rather than discovering the conflict at write time. You trade throughput and deadlock risk for predictable behaviour and no retry logic. It is the right call when the conflicting path is short and retries would be expensive or hard to make idempotent.
- Why does one long-running REPEATABLE READ transaction hurt the whole database on an MVCC engine?Its snapshot may still need old row versions, so garbage collection of dead versions cannot advance past it anywhere in the database. Tables and indexes accumulate dead tuples, scans read more pages, and space is not reclaimed. Monitoring the age of the oldest open transaction, and bounding or killing such transactions, is standard operating practice.
Locking is reserving the whole shelf while you work; multiversioning is photographing the shelf and working from the photo — nobody waits, but the item may be gone when you reach for it.
saying these in an interview costs you the question
- Claiming MVCC snapshots block writers — snapshot reads take no locks at all.
- Claiming MVCC removes all locking, forgetting that writers still lock rows and can deadlock.
- Assuming the level name alone tells you whether you will see waits or serialization aborts.
- Ignoring that a snapshot transaction's view is stale, and treating a snapshot read as validated at commit time.
- Not mentioning version retention and bloat as the cost of long snapshots.