Failures and antipatterns · Intermediate

Lost updates: competing read-modify-write operations

Use a balance example to compare atomic updates, version predicates and transaction-level conflict handling.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Individually sensible operations can conflict
  2. Two credits to a balance
  3. Detect conflicts with a version predicate
  4. Choose a complete concurrency policy
  5. Ask Logeia about this articleCheck your understanding

Individually sensible operations can conflict

A lost update can occur when several operations read the same old value, compute replacement values and overwrite one another’s change. Separating a read from a later write leaves room for another operation between them. Each statement can be syntactically valid and individually successful.

The combined outcome can still violate the business invariant. Define what the complete concurrent operations must preserve, such as the sum of accepted credits, instead of using successful SQL execution as the only correctness check. The transaction’s isolation behavior is part of that reasoning.

Two credits to a balance

If A and B both read balance 100, A may calculate 110 and B may calculate 120. Writing those replacements can leave 120, losing A’s credit, although the intended combined result is 130. This example assumes an execution and isolation policy that permits the stale replacement to be applied.

For a simple increment, an UPDATE that expresses balance = balance + delta inside the database is a candidate repair. Do not generalize that one expression to every multi-row or conditional business operation; the complete invariant can require additional coordination.

Detect conflicts with a version predicate

A version predicate can detect that the row changed after it was read. Update only when both the identifier and expected version match, and advance the version as part of the same operation. A zero-row result needs interpretation: it can indicate a conflict or a missing record rather than success.

Decide what follows a detected conflict. Recompute from fresh data or ask the user to resolve it, depending on the operation. Blindly applying the same stale replacement without the predicate defeats the purpose of detecting the conflict.

Choose a complete concurrency policy

Row locking can protect a read followed by related work when the operation requires it. Keep such transactions short and use a consistent acquisition order for multiple rows. Serializable execution is another candidate, but applications must handle aborts and whole-transaction retries appropriately.

The label MVCC alone does not specify the exact isolation guarantees in use. Match the database documentation to a controlled two-session test. If the transaction also triggers external actions such as email, prevent duplicate effects on retry; database concurrency control does not automatically undo or deduplicate those external operations.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 13

Choose a learning path