skip to content

Two concurrent transactions each issue an UPDATE against the same row in an MVCC database. Walk through what each transaction experiences from the moment the second UPDATE is issued until both finish.

level: middleimportance: must knowfreq 60%

answer

  1. First updater wins, second waits
  2. Lock held to commit, not statement end
  3. READ COMMITTED: re-check predicate on new version
  4. Snapshot isolation: serialization failure, retry
  5. Blind read-then-write = lost update

basics

~20 s

The first updater takes an exclusive row lock and creates a new version. The second UPDATE finds the row locked and blocks. When the first commits, the second either re-reads the new version and applies its change, or aborts with a serialization error, depending on isolation level.

solid answer

~60 s

MVCC removes read locks, not write locks. The first transaction to update the row takes an **exclusive lock on that row** and writes a new version. The second UPDATE, on reaching the same row, discovers it is locked by an in-flight transaction and **waits** — this is *first-updater-wins*. What happens on release depends on the outcome and the isolation level: - First transaction **rolls back**: the second proceeds as if nothing happened. - First transaction **commits**, second is at READ COMMITTED: the second re-evaluates against the newly committed version (re-checking its WHERE predicate) and applies its update on top. Its own snapshot for reads is older, which is why the pattern `UPDATE ... SET x = x + 1` is safe but reading `x` earlier in the transaction and writing a computed value is not. - First commits, second is at REPEATABLE READ / SNAPSHOT: the second cannot see the newer version consistently, so the engine raises a **serialization failure** and the application must retry. Deadlocks appear when two transactions take these row locks in opposite order; the engine detects the cycle and aborts a victim.

code

sql · 9 lines
sql
-- session 1
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 7;
-- (no COMMIT yet: row 7 is exclusively locked)

-- session 2
BEGIN;
UPDATE accounts SET balance = balance - 5 WHERE id = 7;  -- waits here
-- unblocks when session 1 commits or rolls back

go deeper

for a junior

Know that the second UPDATE waits until the first transaction commits or rolls back, and that this is the one case where MVCC still blocks.

for a middle

Describe first-updater-wins plus both post-release branches: re-check-and-apply at READ COMMITTED, serialization failure at snapshot isolation, and why blind read-then-write loses updates.

for a senior

Add operational judgment: locks held to commit make transaction length the real variable, hot rows cap write throughput, deadlocks come from inconsistent ordering, and retry loops are mandatory above READ COMMITTED.

for a principal

Treat the hot row as a capacity limit to design around — sharded counters, append-then-aggregate, queueing — and decide deliberately which paths pay for serializable isolation and retries.

## The one place MVCC still blocks MVCC's headline property is that readers and writers do not block each other. It says nothing about two writers. A row can have many historical versions but only one *current* version, and two transactions cannot both create the successor to the same version without one of them losing an update. So engines keep an exclusive, transaction-duration lock on the row being modified. ## Step by step 1. **T1 issues `UPDATE t SET ... WHERE id = 7`.** It locates the current version of row 7, marks it as being superseded by T1, takes an exclusive row lock, and writes the new version. Other transactions can still *read* row 7 — they see the pre-T1 version, because T1's version is uncommitted and therefore invisible to everyone else. 2. **T2 issues an UPDATE on the same row.** T2 scans, reaches row 7, and finds that the current version is locked by an in-progress transaction. It cannot decide the outcome yet, so it **waits on T1's transaction identifier**. This is the *first-updater-wins* rule: whoever locked the row first is allowed to proceed; the other one queues. 3. **T1 ends.** - *T1 rolls back*: T1's version never becomes visible, the lock is released, and T2 continues against the original version. No error. - *T1 commits*: the lock is released and T2 must decide what to do about a row that changed under it. 4. **T2's decision depends on isolation level.** - **READ COMMITTED**: T2 takes a fresh look at the newly committed version and re-evaluates its WHERE clause against it. If the predicate still matches, T2 updates the new version; if T1's change made the predicate false, T2 skips the row. This re-check means a statement can end up operating on data newer than the snapshot it started with — a small, deliberate inconsistency that keeps throughput high. - **REPEATABLE READ / SNAPSHOT isolation**: T2 has promised itself a stable view for its whole lifetime, and it cannot both honour that view and update a version it is not allowed to see. Engines resolve this by aborting T2 with a **serialization failure**, leaving retry to the application. (Engines differ: some snapshot engines abort, while some — InnoDB, for example — read the latest committed row for the UPDATE and apply next-key locking instead.) ## Why `x = x + 1` is safe but read-then-write is not A single statement such as `UPDATE accounts SET balance = balance - 10 WHERE id = 7` reads and writes the row inside the same locked operation, and at READ COMMITTED the re-check step makes it operate on the newest committed version. No update is lost. By contrast, `SELECT balance` earlier in the transaction, arithmetic in application code, then `UPDATE ... SET balance = 90` uses a value from the reader's snapshot. Because the read took no lock, another transaction may have changed the row in between, and the blind write silently discards it. That is the **lost update** anomaly, and it is a direct consequence of readers not blocking writers. ## Practical consequences - **Contention concentrates on hot rows.** Reads scale, but every writer to the same row serializes behind the current holder. A single counter row caps write throughput at roughly one update per transaction round trip. - **Lock waits grow with transaction length.** The lock is held to commit, not to statement end, so a slow transaction that updates a popular row punishes every other writer of that row. Keep write transactions short and take the hot-row lock as late as possible. - **Deadlocks come from ordering.** If T1 updates row A then B while T2 updates B then A, both wait forever; the engine's detector aborts a victim. The fix is a consistent ordering of updates, not a longer timeout. - **Applications must handle retries.** Any code path running at REPEATABLE READ or SERIALIZABLE has to be prepared for a serialization failure and re-run the whole transaction — not just the failed statement, since the snapshot is what went stale. - **Waits look different from deadlocks.** A lock wait is a healthy queue that resolves at commit; a deadlock is an immediate abort. Confusing the two leads people to raise timeouts when they should be reordering or shortening transactions. ## How to answer Say explicitly that MVCC removes read locks, not write locks; name first-updater-wins; describe the wait, then split the outcome by rollback vs commit and by isolation level; finish with the lost-update caveat and retry requirement. That covers the mechanism and the application-visible behaviour.

  • Why does the second transaction get an error under snapshot isolation but not under READ COMMITTED?
    READ COMMITTED lets a statement re-read the newest committed version and re-evaluate its WHERE clause, so it can safely proceed on top of the other transaction's change. Snapshot/REPEATABLE READ promises a stable view for the whole transaction, so updating a version it is not allowed to see would break that promise; the engine aborts with a serialization failure and the application retries the transaction.
  • How would you reduce write-write blocking on a frequently updated row?
    Shorten the write transaction and acquire the hot-row lock as late as possible so the lock is held for microseconds, not for the duration of external calls. Structurally, split the hot row into shards or per-writer rows that are aggregated on read, or move the update to an append-only insert that is rolled up later. Consistent update ordering across transactions prevents the deadlocks that contention makes likely.
  • Is a lock wait the same thing as a deadlock?
    No. A lock wait is a transaction queuing behind a lock it will eventually get once the holder commits or aborts; it resolves on its own or hits a lock timeout. A deadlock is a cycle where each transaction waits for a lock another holds, so no one can proceed; the engine detects the cycle and aborts a victim immediately. Raising timeouts helps neither — the fixes are shorter transactions and consistent lock ordering.

saying these in an interview costs you the question

  • Claiming the second UPDATE overwrites the first because MVCC has no write locks
  • Saying the second transaction always aborts, regardless of isolation level
  • Believing the row lock is released at the end of the statement rather than at commit
  • Assuming SELECT ... then UPDATE with a computed value is safe because the whole thing is in one transaction
  • Treating a serialization failure as a bug to suppress rather than a transaction to retry

context

resolving links…