Why should you care?

Ten checkout pods updating the same promo counter need a strategy — row locks alone cause queues at Big Billion scale.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

UPDATE products
SET stock_qty = stock_qty - 1,
    version = version + 1
WHERE product_id = 10
  AND version = 5;
-- If 0 rows affected, another session won — retry or abort

What happened?

• Optimistic lock checks version unchanged since read.
• Concurrent update bumps version first — second updater gets 0 rows and retries.
• Avoids long FOR UPDATE waits.

Practice next

1. Add version INT DEFAULT 0 to products.
2. Two sessions read version=5; both try UPDATE ... AND version=5.
3. Only one succeeds; other retries with fresh read.
4. Combine SKIP LOCKED for worker queue pattern.
5. Partition orders by tenant to shrink lock contention.

Remember

Keep transactions short. Optimistic versioning for low collision hot rows. Monitor deadlocks and lock wait timeouts.

DataFlow promo stock

Flash coupon uses version column; app retries 3 times then shows “sold out”.

Outcome: Fair allocation without table-level lock.