The "lost update" problem — one user's update silently vanishes

Question

Accepted Answer

105
   The "lost update" problem — one user's update silently vanishes
  Level: Very Hard — A subtle concurrency bug that corrupts data with no error; classic in inventory and counter systems.

  
     Scenario: An e-commerce product has 10 items in stock. Two customers buy simultaneously. Both should reduce stock, leaving 8. But after both orders, stock shows 9 — one decrement vanished. No error was thrown. This is the classic lost update. How does it happen and how do you prevent it?
  

   Sample Data — products
  
    
      
        product_idnamestock
      
      
        1Laptop10
      
    
  

   How the Update Gets Lost (read-modify-write race)
  
    
      
        TimeTxn A (customer 1)Txn B (customer 2)stock
      
      
        t1Reads stock = 1010
        t2Reads stock = 1010
        t3Computes 10 − 1 = 9, writes 99
        t4Computes 10 − 1 = 9, writes 99 ← should be 8!
      
    
  

  The root cause: Both transactions read the old value (10) before either wrote. Each calculated 9 in application memory and wrote it back. Txn B's write overwrote Txn A's — A's decrement was lost. The danger is the "read, then compute in app, then write" pattern.

   The Buggy Pattern
-- ANTI-PATTERN: read stock into the app, compute, write back
SELECT stock FROM products WHERE product_id = 1;   -- app reads 10
-- ... app computes new_stock = 10 - 1 = 9 ...
UPDATE products SET stock = 9 WHERE product_id = 1;  -- writes a stale value
-- Two concurrent runs both write 9 -> one decrement lost

   Fix 1 — Atomic Update (let the database do the math)
-- BEST for simple counters: never read-then-write; update in place
UPDATE products
SET stock = stock - 1
WHERE product_id = 1 AND stock > 0;   -- also prevents going negative

-- Check affected rows: if 0, stock was already 0 (out of stock)
-- Because 'stock - 1' is computed by the DB under a row lock,
-- concurrent runs serialize correctly -> final stock = 8

   Fix 2 — Pessimistic Locking (SELECT ... FOR UPDATE)
-- When you must read, compute complex logic, then write:
START TRANSACTION;

SELECT stock FROM products
WHERE product_id = 1
FOR UPDATE;              -- locks the row; Txn B waits here

-- ... app logic with the guaranteed-current value ...

UPDATE products SET stock = stock - 1 WHERE product_id = 1;
COMMIT;                  -- lock released; Txn B now reads the fresh value

   Fix 3 — Optimistic Locking (version column)
-- No locks; detect conflict at write time using a version column
-- 1) Read current value + version
SELECT stock, version FROM products WHERE product_id = 1;  -- stock=10, version=5

-- 2) Update ONLY if version is unchanged
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE product_id = 1 AND version = 5;

-- 3) If ROW_COUNT() = 0, someone else updated first -> retry the whole flow

   Which Fix to Use
  
    
      
        FixBest For
      
      
        Atomic updateSimple counters/increments (stock, likes, views)
        Pessimistic (FOR UPDATE)Complex logic between read and write; high contention
        Optimistic (version)Low contention, high concurrency, web forms
      
    
  

  
     The core lesson: The lost update happens because of read-modify-write in application code. Whenever possible, do the arithmetic inside the SQL statement (SET stock = stock - 1) so the database performs it atomically under a row lock — eliminating the race entirely.
  

  
     Interviewer follow-up: "Does REPEATABLE READ isolation prevent lost updates?" → No! REPEATABLE READ prevents non-repeatable/phantom reads, but a plain read + separate write can still lose updates because the read doesn't lock the row. You need an atomic update, FOR UPDATE (pessimistic), or a version check (optimistic) — isolation level alone isn't enough.

The "lost update" problem — one user's update silently vanishes

Answer with Explanation

The "lost update" problem — one user's update silently vanishes

Sample Data — `products`

How the Update Gets Lost (read-modify-write race)

The Buggy Pattern

Fix 1 — Atomic Update (let the database do the math)

Fix 2 — Pessimistic Locking (SELECT ... FOR UPDATE)

Fix 3 — Optimistic Locking (version column)

Which Fix to Use

Time	Txn A (customer 1)	Txn B (customer 2)	stock
t1	Reads stock = 10		10
t2		Reads stock = 10	10
t3	Computes 10 − 1 = 9, writes 9		9
t4		Computes 10 − 1 = 9, writes 9	9 ← should be 8!

Fix	Best For
Atomic update	Simple counters/increments (stock, likes, views)
Pessimistic (FOR UPDATE)	Complex logic between read and write; high contention
Optimistic (version)	Low contention, high concurrency, web forms