Lost Update Explained: When Two Writes Become One

Scenario context

A lost update occurs when two concurrent read-modify-write transactions both read the same committed value, both compute a new value from it, and both write back — with the second write silently discarding the first. No error is raised. Both transactions report success. The corruption is visible only in the final state. Atomic SQL operations (UPDATE SET val = val + 1) eliminate the vulnerability at any isolation l…

This walkthrough focuses on The Offline Spreadsheet Problem: What a Lost Update Actually Is.

Before making a design choice, clarify:

• Before defining the anomaly precisely, consider an analogy that makes the mechanism obvious.
• Two colleagues — Alice and Bob — each receive a printed copy of the same spreadsheet at 9 AM.
• The spreadsheet shows the marketing budget remaining is $5,000.

Concepts in play: database-isolation-levels-anomalies, caching, consistency-replication, data-warehousing-lakehouse

Stage 1 — The Flash Sale That Sold an Item It No Longer Had

• At 09:00:00 UTC on a high-traffic Friday, FlashMart's engineering team triggered a flash sale on the last remaining unit of the Gaming Headset Pro (product id = 7842).
• The item had been promoted to 400,000 newsletter subscribers for 48 hours.
• By 09:00:01, two purchase requests arrived simultaneously from different data centers.
• Server A (us-east-1) received Customer Alice's request at 09:00:01.003.

Observable outcome: Server A (us-east-1) received Customer Alice's request at 09:00:01.003.

Stage 2 — The Read-Modify-Write Gap: Where the Anomaly Is Born

• Lost updates emerge from a universal application pattern: the read-modify-write cycle .
• A transaction reads the current state of a row, computes a new value in application memory, and writes the result back.
• This three-step sequence contains a structural vulnerability: the gap between the read and the write.
• During that gap, another transaction can perform its own read of the same row.

sequenceDiagram
    participant T1 as Transaction T1
    participant DB as Database
    participant T2 as Transaction T2
    T1->>DB: READ row value = V
    T2->>DB: READ row value = V
    Note over T1,T2: Both hold V in app memory - gap window open
    T1->>T1: Compute new value V1 from V
    T2->>T2: Compute new value V2 from V
    T1->>DB: WRITE value = V1
    DB-->>T1: Commit OK
    T2->>DB: WRITE value = V2
    DB-->>T2: Commit OK
    Note over DB: V2 overwrites V1 - T1 update is lost

Non-atomic cycle — vulnerable to lost update:

  1. T1: READ  inventory_count = 1   → computes: 1 - 1 = 0
  2. T2: READ  inventory_count = 1   → computes: 1 - 1 = 0
  3. T1: WRITE inventory_count = 0   → committed
  4. T2: WRITE inventory_count = 0   → overwrites T1, committed
  Final: inventory_count = 0  (two sales processed, one decrement lost)

Observable outcome: During that gap, another transaction can perform its own read of the same row.

Stage 3 — Lost Update in Slow Motion: A Transaction-Level Sequence

• The following sequence diagram traces the exact database-level events during the FlashMart flash sale.
• Pay attention to the overlap window between T1's SELECT and T2's SELECT — that is where the anomaly is born.
• The diagram shows the four critical moments that produce the lost update: (1) both T1 and T2 read inventory count = 1 before either has written — they share an identical snapshot; (2) each independently computes the sam…
• The database ends at inventory count = 0 , which is correct for one sale but wrong for two.

sequenceDiagram
    participant T1 as Transaction T1 - Server A
    participant DB as Stock Database
    participant T2 as Transaction T2 - Server B

    T1->>DB: BEGIN
    T2->>DB: BEGIN
    T1->>DB: SELECT inventory_count FROM products WHERE id=7842
    DB-->>T1: inventory_count = 1
    T2->>DB: SELECT inventory_count FROM products WHERE id=7842
    DB-->>T2: inventory_count = 1
    Note over T1: Computes 1 - 1 = 0, ready to write
    Note over T2: Computes 1 - 1 = 0, ready to write
    T1->>DB: UPDATE products SET inventory_count = 0 WHERE id=7842
    DB-->>T1: 1 row updated
    T1->>DB: COMMIT
    DB-->>T1: T1 committed successfully
    T2->>DB: UPDATE products SET inventory_count = 0 WHERE id=7842
    DB-->>T2: 1 row updated
    T2->>DB: COMMIT
    DB-->>T2: T2 committed successfully
    Note over DB: Final state: inventory_count = 0. T1 update overwritten.

Observable outcome: The database ends at inventory count = 0 , which is correct for one sale but wrong for two.

Stage 4 — Lost Update, Dirty Write, and Write Skew: Knowing the Difference Under Pressure

• Lost updates are frequently confused with two related anomalies — dirty writes and write skew — in interviews and code reviews.
• All three are write conflicts involving concurrent transactions, but they differ in the data each transaction reads and the rows each transaction writes.
• Lost update is the only one of the three that involves exclusively committed reads.
• T1 and T2 both see valid, committed data — the anomaly arises purely from the temporal gap between their respective reads and writes.

Observable outcome: T1 and T2 both see valid, committed data — the anomaly arises purely from the temporal gap between their respective reads and writes.

Stage 5 — How Databases Actually Detect (and Miss) Lost Updates

• The most consequential practical distinction in production systems is not which isolation level is configured in theory — it is whether your specific database implementation detects lost updates at that level.
• Internals: How MVCC and Write Tracking Create (and Close) the Lost-Update Window Every major database that supports snapshot-based isolation uses Multi-Version Concurrency Control (MVCC) .
• When T1 reads a row, it does not acquire a traditional read lock — it receives a pointer to the most recently committed version of that row as of the transaction's start time.
• The row itself can continue to receive new committed versions from other transactions.

ERROR:  could not serialize access due to concurrent update
DETAIL:  Process 12345 updated this row while this transaction was in progress.

Observable outcome: The row itself can continue to receive new committed versions from other transactions.

Stage 6 — Isolation Level Prevention Matrix: Which Levels Actually Catch Lost Updates

• Not all isolation levels prevent lost updates, and — as MySQL's behavior demonstrates — even the same named level can behave differently across databases.
• This table reflects both ANSI SQL standard semantics and the documented behavior of the three most common production databases.
• The practical takeaway from this table: never assume your isolation level prevents lost updates without verifying your database's specific implementation.
• In MySQL, treat Repeatable Read as equivalent to Read Committed for lost-update purposes.

Observable outcome: In MySQL, treat Repeatable Read as equivalent to Read Committed for lost-update purposes.

End-to-end outcome

After working through the stages, you should be able to explain:

• Silence is the most dangerous symptom.
• Lost updates produce no errors, no warnings, and no log entries at the database or application layer.
• • A lost update occurs when two read-modify-write transactions overwrite each other — one write silently discards the other, with no error raised by either the database or the application.
• • The root cause is the read-modify-write gap : T2 reads the same committed value T1 already read, and T2's write lands after T1's — overwriting T1's result using a stale starting point.

Alternatives considered

Choosing the Right Prevention Strategy: A Decision Guide

The following flowchart captures the practical decision logic for selecting a lost-update prevention strategy.

Failure and degraded modes

Trade-offs: What Each Prevention Strategy Costs in Throughput and Complexity

• Detection: The two most common failure patterns in production: (a) under-protection — using Read Committed with a read-modify-write cycle and no locking, assuming "the database handles it" — it does not; and (b…
• User or operator impact: The two most common failure patterns in production: (a) under-protection — using Read Committed with a read-modify-write cycle and no locking, assuming "the database handles it" — it does not; and (b…