Why should you care?

Seat booking or flash sale inventory must serialize conflicting updates — otherwise two users buy the last unit.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

START TRANSACTION;
SELECT stock_qty FROM products WHERE product_id = 10 FOR UPDATE;
UPDATE products SET stock_qty = stock_qty - 1 WHERE product_id = 10;
COMMIT;

What happened?

• FOR UPDATE locks matching rows exclusively for this transaction.
• Other sessions block on same row until COMMIT.
• Prevents two checkouts reading “1 left” and both selling.

Practice next

1. Tab A: START; SELECT ... FOR UPDATE on product 10.
2. Tab B: try UPDATE same product — waits.
3. Tab A COMMIT; Tab B proceeds.
4. NOWAIT / SKIP LOCKED (MySQL 8+) for queue workers.
5. SHOW PROCESSLIST when B is waiting — State shows lock wait.

Remember

FOR UPDATE = exclusive row lock intent. Locks release at transaction end. Design hot paths to hold locks briefly.

DataFlow flash sale SKU

Checkout uses FOR UPDATE on stock row before decrement.

Outcome: Only one buyer gets the last unit.