Two checkouts for the last item both succeed and stock ends at 0. What happened?

Both transactions read stock = 1 before either wrote. Each computed 0 in application code and wrote it back; the second write overwrote the first. That's a lost update caused by read-modify-write without locking or conditions.

How does `UPDATE products SET stock = stock - 1 WHERE id = ? AND stock >= 1` prevent overselling?

The check and decrement happen atomically inside the database. Row locking serializes concurrent updates on that row, and the second update re-evaluates `stock >= 1` against the new value, matches zero rows and reports 0 affected rows — the application treats that as sold out.

Does MySQL's default REPEATABLE READ prevent lost updates in read-modify-write code?

No. The plain SELECT reads a snapshot without locking, and the later UPDATE overwrites the current row. You need an atomic update, `SELECT … FOR UPDATE`, or a version check.

Lost Updates: The Read-Modify-Write Bug — Transactions, Locking & Concurrency in Code

Scenario

Example app: an online shop runs a flash sale of 500 sneakers at 10:00. About 3,000 checkout requests per second hit POST /checkout in the first minute.

What you see in production

The sale "sells" 537 pairs. Stock in the database ends at 0, never negative, and nothing errored — yet 37 customers have confirmed orders the warehouse can't fill. In quiet hours the same code is always correct.

The naive code

Read the stock into the application, check it, write the new value back:

def checkout(product_id, qty):
    with transaction.atomic():
        product = Product.objects.get(pk=product_id)       # SELECT stock …
        if product.stock < qty:
            raise SoldOut()
        product.stock -= qty                               # computed in Python
        product.save(update_fields=["stock"])              # UPDATE … SET stock = <old - qty>
        Order.objects.create(product=product, qty=qty)

@Transactional
public Order checkout(long productId, int qty) {
    Product p = products.findById(productId).orElseThrow();
    if (p.getStock() < qty) throw new SoldOutException();
    p.setStock(p.getStock() - qty);           // dirty checking writes the computed value
    return orders.save(new Order(p, qty));
}

DB::transaction(function () use ($productId, $qty) {
    $product = Product::findOrFail($productId);
    if ($product->stock < $qty) throw new SoldOut();
    $product->stock = $product->stock - $qty;
    $product->save();
    Order::create(['product_id' => $productId, 'qty' => $qty]);
});

await prisma.$transaction(async (tx) => {
  const p = await tx.product.findUniqueOrThrow({ where: { id: productId } });
  if (p.stock < qty) throw new SoldOut();
  await tx.product.update({ where: { id: productId }, data: { stock: p.stock - qty } });
  await tx.order.create({ data: { productId, qty } });
});

Reproduce it

Two requests for the last pair, interleaved the way they are under load:

Two requests, one pair left: both succeedTry it yourself

db = {"stock": 1}
orders = []

def checkout(name):
    stock = db["stock"]                          # SELECT stock FROM products …
    yield f"{name}: read stock = {stock}"
    if stock < 1:
        yield f"{name}: sold out"; return
    db["stock"] = stock - 1                      # UPDATE products SET stock = <computed>
    orders.append(name)
    yield f"{name}: wrote stock = {stock - 1}, order confirmed"

a, b = checkout("A"), checkout("B")
for step in (a, b, a, b):                        # both read before either writes
    print(next(step))
print(f"stock = {db['stock']}, orders confirmed = {len(orders)}  <- one pair, two orders")

Output

A: read stock = 1
B: read stock = 1
A: wrote stock = 0, order confirmed
B: wrote stock = 0, order confirmed
stock = 0, orders confirmed = 2  <- one pair, two orders

The same with an atomic conditional update, run on a real SQLite database (the tutorial sample shop — product 6 is a 4K monitor with stock 8):

Atomic conditional update: the database decidesTry it yourself

UPDATE products SET stock = 1 WHERE product_id = 6;          -- one left
UPDATE products SET stock = stock - 1 WHERE product_id = 6 AND stock >= 1;
SELECT changes() AS request_a_rows;                           -- 1: A got it
UPDATE products SET stock = stock - 1 WHERE product_id = 6 AND stock >= 1;
SELECT changes() AS request_b_rows;                           -- 0: B is told "sold out"
SELECT stock FROM products WHERE product_id = 6;

Output

request_a_rows
1

request_b_rows
0

stock
0

Root cause

The check and the write are separate steps on a stale copy. Between SELECT and UPDATE, another transaction can change the row; the second writer overwrites the first's result with a value computed from old data — a lost update. Neither Read Committed (Postgres, MySQL) nor MySQL's Repeatable Read prevents it, because the SELECT doesn't lock anything. Postgres Repeatable Read does detect it — the second UPDATE fails with a serialization error that you must retry.

The fix

Four correct options, from simplest to most flexible:

Atomic conditional update — the database does the check and the write in one statement.
Pessimistic lock — SELECT … FOR UPDATE before reading (lesson).
Optimistic version check — write only if the version hasn't changed (lesson).
Serializable/Repeatable Read + retry (Postgres) — let the database abort the loser.

The atomic update in each stack:

with transaction.atomic():
    updated = (Product.objects.filter(pk=product_id, stock__gte=qty)
               .update(stock=F("stock") - qty))          # one UPDATE … WHERE stock >= qty
    if updated == 0:
        raise SoldOut()
    Order.objects.create(product_id=product_id, qty=qty)

@Modifying
@Query("update Product p set p.stock = p.stock - :qty where p.id = :id and p.stock >= :qty")
int reserve(@Param("id") long id, @Param("qty") int qty);

@Transactional
public Order checkout(long id, int qty) {
    if (products.reserve(id, qty) == 0) throw new SoldOutException();
    return orders.save(new Order(id, qty));
}

DB::transaction(function () use ($productId, $qty) {
    $updated = Product::whereKey($productId)->where('stock', '>=', $qty)->decrement('stock', $qty);
    if ($updated === 0) throw new SoldOut();
    Order::create(['product_id' => $productId, 'qty' => $qty]);
});

await prisma.$transaction(async (tx) => {
  const { count } = await tx.product.updateMany({
    where: { id: productId, stock: { gte: qty } },
    data: { stock: { decrement: qty } },
  });
  if (count === 0) throw new SoldOut();
  await tx.order.create({ data: { productId, qty } });
});

UPDATE products SET stock = stock - $1
WHERE id = $2 AND stock >= $1
RETURNING stock;            -- Postgres; MySQL: check ROW_COUNT()

Trade-offs

Atomic updates are the fastest and simplest, but only work when the rule fits in a WHERE clause on one row.
Locks handle multi-step logic but serialize requests for the same row (a hot row during a flash sale).
Optimistic checks avoid blocking but cause retries under heavy contention.
A CHECK (stock >= 0) constraint is a cheap last line of defence: a bug becomes an error instead of an oversell.

Detect it in production

Invariant checks: SELECT product_id FROM products WHERE stock < 0; and "orders per product > initial stock".
Load tests that fire concurrent requests at the same item — single-user tests never find this.
Code review: any ORM pattern of load → modify field → save on a shared counter or balance.

Key points

Read-modify-write in application code loses updates under concurrency.
Prefer one atomic UPDATE … SET x = x - n WHERE x >= n and check the affected rows.
Use locks or version checks when the logic can't fit in one statement.
Add CHECK constraints and concurrent load tests.