A transaction at REPEATABLE READ on a multiversion engine reads a row, computes with it, then updates it — and the engine aborts the transaction with a serialization failure (SQLSTATE 40001). Why is that abort necessary, and how should the application be written to cope?

seniorshould knowfreq 45%

answer

first-updater-wins
can't overwrite (lost update), can't reveal (non-repeatable)
SQLSTATE 40001 -> rollback, re-run whole transaction
re-read on retry; never reuse stale values
hot row: lock on read or single self-referencing UPDATE

basics

~20 s

Another transaction updated that row after your snapshot. Overwriting would lose their update; showing you their value would break read stability — so the engine aborts you. The fix is to catch the failure, roll back, and re-run the whole transaction, re-reading everything.

solid answer

~50 s

The application must therefore treat the transaction as retryable as a unit. Catch the serialization failure at the outermost transaction boundary, roll back, then re-execute the entire transaction body — including the reads, because values from the aborted attempt are worthless. Add capped attempts with jittered backoff, and make sure no non-transactional side effects (emails, queue publishes, external calls) happen before commit, or they will be repeated.

If conflicts are frequent, remove the race instead: take a locking read up front, or express the update as a single self-referencing statement that reads the current row.

At REPEATABLE READ my reads come from one snapshot. If another transaction committed a new version of that row after my snapshot, the engine has no correct option when I try to write it: applying my write would silently discard their committed update (a lost update computed from stale input), and letting me see their value would violate the read stability the level promised. So it aborts me — first-updater-wins. The application must therefore treat the transaction as **retryable as a unit**. Catch the serialization failure at the outermost transaction boundary, roll back, then re-execute the entire transaction body — including the reads, because values from the aborted attempt are worthless. Add capped attempts with jittered backoff, and make sure no non-transactional side effects (emails, queue publishes, external calls) happen before commit, or they will be repeated. If conflicts are frequent, remove the race instead: take a locking read up front, or express the update as a single self-referencing statement that reads the current row.

code

sql · 9 lines

sql

-- conflict-prone: read, compute in app, write back
SELECT stock FROM items WHERE id = 42;
UPDATE items SET stock = 7 WHERE id = 42;

-- conflict-free: the engine reads and writes in one statement
UPDATE items
   SET stock = stock - 3
 WHERE id = 42
   AND stock >= 3;

go deeper

for a junior

Recognise the error class as concurrent update, try again, and know that the whole transaction is re-run.

for a middle

Explain first-updater-wins and why neither overwriting nor revealing the new value is acceptable at this level.

for a senior

Design the retry: bounded attempts with jitter, retryable-versus-terminal error classification, no external side effects before commit, and metrics on abort rate.

for a principal

Decide policy — which paths retry, which take locks, which are rewritten as single statements — and set transaction-length and hot-key rules so abort rates stay bounded under load.

The scenario

Your transaction begins, its snapshot is fixed, it reads stock = 10, application code computes 10 - 3, and it issues UPDATE items SET stock = 7 WHERE id = 42. In between, another transaction set stock to 4 and committed.

Why the engine cannot just proceed

Writes, unlike reads, always operate on the newest committed version — there is only one row to update. The engine detects that the newest version of row 42 was created by a transaction that committed after your snapshot. Its options are:

Apply your write. You would write 7, computed from a value that is no longer true, destroying the other transaction's committed change. That is a lost update.
Show you the new value and continue. Your transaction would then have seen two different values for the same row — exactly the non-repeatable read this level forbids. (This is what READ COMMITTED does instead: it re-reads the newest row and re-evaluates the predicate.)
Abort you. The only choice consistent with the level's promise.

So it raises a serialization failure — SQLSTATE class 40, typically 40001 — and your transaction is dead; every subsequent statement fails until you roll back. The rule is often called first-updater-wins: the transaction that reached the row first commits, the later one is told to try again.

Note when this fires: on the UPDATE, DELETE or locking read itself, as soon as the conflicting writer has committed. If that writer is still open you first block on its row lock, then get the error when it commits.

The retry pattern

The abort is not a bug; it is the engine handing correctness back to you. The application contract at this level is: any transaction may fail with 40001, and the correct response is to run it again.

A correct retry loop:

Wraps the whole transaction — BEGIN through COMMIT — not a single statement. A retry inside the failed transaction is impossible; it is already aborted.
Re-executes the reads. The point of retrying is to recompute on fresh data. Caching the previously read values and re-issuing only the UPDATE reintroduces the lost update.
Has a bounded attempt count (3-5 is typical) and backoff with jitter, so a hot row does not become a retry storm.
Treats 40001 and deadlock errors as retryable, and everything else — constraint violations, syntax, authorization — as terminal.
Keeps the transaction body free of non-idempotent side effects: no emails, no payment calls, no message publishes before commit. Anything external belongs after a successful commit, or behind an outbox row written inside the transaction.
Logs the retry rate as a metric. A rising 40001 rate is a contention signal and often the earliest warning of a hot key.

When retrying is the wrong answer

Retries are cheap when conflicts are rare and the transaction is short. If the same row is contended by many concurrent transactions, retrying converts contention into wasted work — every attempt redoes the reads and loses again, and throughput can collapse. Better options:

Lock on read. Take a locking read of the rows you intend to modify at the start. Conflicting transactions then queue instead of aborting, and you never observe a stale value. Cost: blocking, and deadlock risk if lock order is inconsistent.
Make the update self-contained. UPDATE items SET stock = stock - 3 WHERE id = 42 AND stock >= 3 computes from the current row inside the engine; there is no read-decide-write window at all, and you check the affected row count instead. This eliminates most conflicts in counter-style workloads.
Shrink the transaction. Do the computation before opening the transaction; a snapshot that lives for milliseconds conflicts far less often than one that lives for seconds.
Reduce the isolation level for that path if a statement-scoped view is genuinely sufficient — but only together with one of the two techniques above, since a weaker level does not by itself prevent the lost update.

The summary

The abort is the multiversion engine's way of refusing to lose an update while keeping your reads stable. Applications that use snapshot-style isolation must be built with an idempotent, whole-transaction retry path from day one; frequently-conflicting paths should be redesigned so the conflict is either taken as a lock or removed by expressing the change as one statement.

## The scenario Your transaction begins, its snapshot is fixed, it reads `stock = 10`, application code computes `10 - 3`, and it issues `UPDATE items SET stock = 7 WHERE id = 42`. In between, another transaction set stock to 4 and committed. ## Why the engine cannot just proceed Writes, unlike reads, always operate on the newest committed version — there is only one row to update. The engine detects that the newest version of row 42 was created by a transaction that committed *after* your snapshot. Its options are: 1. **Apply your write.** You would write 7, computed from a value that is no longer true, destroying the other transaction's committed change. That is a lost update. 2. **Show you the new value and continue.** Your transaction would then have seen two different values for the same row — exactly the non-repeatable read this level forbids. (This is what READ COMMITTED does instead: it re-reads the newest row and re-evaluates the predicate.) 3. **Abort you.** The only choice consistent with the level's promise. So it raises a serialization failure — SQLSTATE class 40, typically 40001 — and your transaction is dead; every subsequent statement fails until you roll back. The rule is often called **first-updater-wins**: the transaction that reached the row first commits, the later one is told to try again. Note when this fires: on the UPDATE, DELETE or locking read itself, as soon as the conflicting writer has committed. If that writer is still open you first block on its row lock, then get the error when it commits. ## The retry pattern The abort is not a bug; it is the engine handing correctness back to you. The application contract at this level is: **any transaction may fail with 40001, and the correct response is to run it again.** A correct retry loop: - Wraps the **whole** transaction — BEGIN through COMMIT — not a single statement. A retry inside the failed transaction is impossible; it is already aborted. - **Re-executes the reads.** The point of retrying is to recompute on fresh data. Caching the previously read values and re-issuing only the UPDATE reintroduces the lost update. - Has a **bounded attempt count** (3-5 is typical) and **backoff with jitter**, so a hot row does not become a retry storm. - Treats 40001 and deadlock errors as retryable, and everything else — constraint violations, syntax, authorization — as terminal. - Keeps the transaction body **free of non-idempotent side effects**: no emails, no payment calls, no message publishes before commit. Anything external belongs after a successful commit, or behind an outbox row written inside the transaction. - Logs the retry rate as a metric. A rising 40001 rate is a contention signal and often the earliest warning of a hot key. ## When retrying is the wrong answer Retries are cheap when conflicts are rare and the transaction is short. If the same row is contended by many concurrent transactions, retrying converts contention into wasted work — every attempt redoes the reads and loses again, and throughput can collapse. Better options: - **Lock on read.** Take a locking read of the rows you intend to modify at the start. Conflicting transactions then queue instead of aborting, and you never observe a stale value. Cost: blocking, and deadlock risk if lock order is inconsistent. - **Make the update self-contained.** `UPDATE items SET stock = stock - 3 WHERE id = 42 AND stock >= 3` computes from the current row inside the engine; there is no read-decide-write window at all, and you check the affected row count instead. This eliminates most conflicts in counter-style workloads. - **Shrink the transaction.** Do the computation before opening the transaction; a snapshot that lives for milliseconds conflicts far less often than one that lives for seconds. - **Reduce the isolation level** for that path if a statement-scoped view is genuinely sufficient — but only together with one of the two techniques above, since a weaker level does not by itself prevent the lost update. ## The summary The abort is the multiversion engine's way of refusing to lose an update while keeping your reads stable. Applications that use snapshot-style isolation must be built with an idempotent, whole-transaction retry path from day one; frequently-conflicting paths should be redesigned so the conflict is either taken as a lock or removed by expressing the change as one statement.

Can you retry just the failed statement instead of the whole transaction?
No. Once a serialization failure is raised the transaction is in an aborted state and every further statement in it fails, so there is nothing to continue. Even if the engine allowed it, the values you read earlier came from a snapshot now known to be inconsistent with your write, so recomputing from them would reproduce the same wrong result. The retry unit is the whole transaction, reads included.
Would dropping to READ COMMITTED make this error go away, and is that safe?
The error goes away because READ COMMITTED re-reads the newest committed row for the update and continues. But the lost update does not go away: your application already computed 7 from a stale 10, and it will happily write 7. It is only safe if you also either take a locking read or express the change as a single statement that computes from the current row.

saying these in an interview costs you the question

Treating the serialization failure as a database bug rather than a required outcome.
Retrying only the UPDATE while reusing values read in the aborted transaction.
Unbounded retry loops without backoff, which amplify contention on hot rows.
Sending emails, publishing events or calling external services inside a transaction that may be retried.
Claiming REPEATABLE READ prevents all lost updates everywhere, with no retry or locking needed.

A transaction at REPEATABLE READ on a multiversion engine reads a row, computes with it, then updates it — and the engine aborts the transaction with a serialization failure (SQLSTATE 40001). Why is that abort necessary, and how should the application be written to cope?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

The scenario

Why the engine cannot just proceed

The retry pattern

When retrying is the wrong answer

The summary

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions5 questions

▾related topics3 topics

The scenario

Why the engine cannot just proceed

The retry pattern

When retrying is the wrong answer

The summary

answer

go deeper

context