Which transaction isolation levels actually prevent a lost update, and how do lock-based and snapshot-based engines differ in the way they stop one?
answer
- SQL-92 never names lost update — it is P4 from the critique
- READ COMMITTED: not prevented, in either family
- 2PL RR: read lock held → block or upgrade deadlock
- snapshot RR: write-write conflict → first updater wins, serialization error
- prevention by isolation = errors you must retry
basics
~20 sREAD UNCOMMITTED and READ COMMITTED do not prevent it. Lock-based REPEATABLE READ prevents it by holding read locks to commit, turning the race into a block or deadlock. Snapshot-based engines detect the concurrent write to the same row and abort one transaction with a serialization error. SERIALIZABLE always prevents it.
solid answer
~50 sThe ANSI SQL-92 levels are defined by three phenomena — dirty read, non-repeatable read, phantom — and **lost update is not one of them**; it was added later as P4 in the critique of that standard. So you cannot read the answer off the standard's table. In a **lock-based** implementation, REPEATABLE READ holds the shared read lock until commit. The second reader cannot get the exclusive lock to write, so it blocks, and if both read first you get a deadlock and one abort. Under READ COMMITTED the read lock is dropped immediately, so nothing stops it. In a **snapshot/MVCC** engine, reads do not lock, but two transactions writing the same row are detected: the second waits and then either re-reads (READ COMMITTED, single statement only) or aborts with a serialization failure (REPEATABLE READ / snapshot isolation) — **first updater wins**. Practically: raising the level converts silent corruption into errors you must retry. Explicit locking, atomic updates or version checks are the portable fix.
go deeper
Know that the common default, READ COMMITTED, does not prevent it, and that stronger levels do.
Contrast the two mechanisms: read locks held to commit versus write-conflict detection aborting the second writer.
Discuss first-updater-wins, the retry requirement, portability of level names, and why explicit locking or version columns are usually the better fix.
Decide which transactions justify a stronger level, own the retry and idempotency policy, and weigh abort rates and version-retention cost against the simpler per-row techniques.
## Why the standard does not answer the question SQL-92 defines isolation levels by which of three phenomena they forbid: dirty read, non-repeatable read, phantom. Lost update is absent. The 1995 critique of the standard ("A Critique of ANSI SQL Isolation Levels") pointed out that the phenomena are ambiguous and incomplete, and named the missing ones — including **P4, Lost Update**, and A5B, write skew. Anyone answering purely from the SQL-92 table will get this wrong, which is why the question is asked. The second reason the standard cannot answer it: the level *names* describe guarantees, not mechanisms, and two engines implementing the same name behave differently. You must ask *how* the engine implements it. ## Lock-based implementations (two-phase locking) In a classic 2PL engine, reads take shared locks and writes take exclusive locks; the difference between levels is how long the read locks are held. - **READ UNCOMMITTED**: no read locks at all. Nothing prevents a lost update. - **READ COMMITTED**: the shared lock is taken for the duration of the read statement and released immediately. Between your read and your write the row is unprotected. **Not prevented.** - **REPEATABLE READ**: the shared lock is held until commit. Now T2 cannot acquire the exclusive lock it needs to write while T1 holds its read lock, so T2 blocks. If both transactions read first, each holds a shared lock and each waits for the other to release before it can upgrade — a classic **upgrade deadlock**, which the engine breaks by aborting one. Either way the update is not silently lost; it is blocked or it errors. **Prevented.** - **SERIALIZABLE**: strictly stronger. **Prevented.** So under 2PL, the answer is "REPEATABLE READ and above" — but note that the prevention arrives as blocking and deadlocks, which is an operational cost. ## Snapshot / MVCC implementations Here readers never block writers and writers never block readers, because a reader sees a consistent older version of the data. Read locks do not exist, so the 2PL argument does not apply at all. Instead, **write–write conflict detection** does the work. When two transactions update the same row, the second one's `UPDATE` blocks on the first one's row lock. When the first commits, the second must decide what to do: - Under **READ COMMITTED**, each statement sees the latest committed data, so the blocked statement re-reads the newest row version and re-evaluates its `WHERE` clause and its expression. That makes a single self-referential statement (`SET x = x - 1`) or a predicated compare-and-set safe — but it does **not** help a read-modify-write split across two statements, because the earlier `SELECT` result is still stale. **Not prevented** in general. - Under **REPEATABLE READ / snapshot isolation**, showing the transaction a newer row version would violate its own snapshot, so the engine cannot re-read. It aborts the second transaction with a **serialization failure** instead. This is the **first-updater-wins** (or first-committer-wins) rule. **Prevented** — at the cost of an error the application must catch and retry. - **SERIALIZABLE** (snapshot-based engines typically implement serializable snapshot isolation) prevents it and also the anomalies snapshot isolation misses. Note the asymmetry with the 2PL case: snapshot isolation prevents lost update but famously does *not* prevent write skew, where two transactions write **different** rows and break a shared invariant. Lost update is a same-row conflict, so the write-conflict detector catches it. ## The practical consequences 1. **Defaults are unsafe.** READ COMMITTED is the default in most engines, and it does not prevent the anomaly. Assume you must handle it. 2. **Prevention by isolation level is prevention by failure.** At snapshot isolation you do not get correctness for free; you get an exception. If the application does not catch serialization failures and retry the whole transaction, the user gets an error page instead of corrupted data — better, but still a failure you must design for. The retry must re-run the transaction from the beginning, since its snapshot is poisoned. 3. **It is not portable.** The same level name means read-lock duration in one engine and snapshot semantics in another, and some engines take a locking read on `UPDATE` regardless of level. Code that relies on "REPEATABLE READ protects me" breaks when the engine changes. 4. **Higher levels cost more.** Longer-held locks mean more blocking and deadlocks; longer-lived snapshots mean the engine must retain old row versions, which inflates undo/version storage and delays cleanup. For these reasons the recommended fix order in application code is: express the change as an atomic in-place update if you can; otherwise use a version column (portable, no held locks, explicit conflict) or an explicit locking read (short, server-side critical sections). Raise the isolation level when you need a *transaction-wide* guarantee across many statements, not as the primary defence against a single row's read-modify-write — and when you do raise it, ship the retry loop with it. ## Summary table | Level | Lock-based (2PL) | Snapshot / MVCC | |---|---|---| | READ UNCOMMITTED | No | No (often unavailable) | | READ COMMITTED | No | No across statements; single statement re-reads | | REPEATABLE READ | Yes — blocks or upgrade-deadlocks | Yes — aborts with serialization failure | | SERIALIZABLE | Yes | Yes |
- Snapshot isolation prevents lost update but not every anomaly. What is the structural difference?Snapshot isolation detects conflicts on the *same row*: two transactions writing one row is a write–write conflict the engine can see, and it aborts the second. Anomalies where two transactions write *different* rows yet jointly break an invariant produce no write–write conflict, so the detector never fires. Catching those requires a serializable level that also tracks read–write dependencies.
- If raising the isolation level prevents lost updates, why not set every transaction to SERIALIZABLE and stop worrying?Because prevention arrives as aborted transactions and blocking, not as free correctness. Every write path then needs an idempotent retry of the whole transaction, throughput drops on contended data, and lock or snapshot retention costs rise. It is a legitimate choice for a small number of critical transactions with a disciplined retry layer, but as a global default it converts a data-correctness problem into an availability and latency problem.
saying these in an interview costs you the question
- Answering from the SQL-92 phenomena table, which never mentions lost update
- Saying READ COMMITTED is enough because the transaction only reads committed data
- Assuming REPEATABLE READ means the same mechanism in a locking engine and a multiversion engine
- Setting a higher isolation level without adding a retry loop for serialization failures
- Claiming snapshot isolation prevents every write anomaly because it prevents this one