Prevent negative stock with a conditional MySQL update

Two requests try to reserve the last item. A separate SELECT followed by an unconditional UPDATE lets both requests make a decision from the same old stock value. For a simple decrement, combine the condition and change.

Make the stock condition part of the write

UPDATE products
SET stock = stock - 1
WHERE id = 204
  AND stock >= 1;

Check the affected-row count. One changed row means the decrement happened. Zero means this update did not reserve an item; the product might not exist or might not have enough stock.

This example assumes an indexed primary key, integer stock, and InnoDB. It does not implement a complete order system.

Include related records in a transaction

If the reservation also creates an order line, those database changes need a shared transaction. Otherwise the decrement could succeed while the order-line insert fails. Define what should happen on rollback and on a repeated request.

For multi-item orders, use a consistent locking order and reject the whole operation when any required item cannot be reserved. Keep network calls outside the locked section.

Test actual concurrency

Use two database connections against MySQL, start with one unit, and attempt two reservations. Exactly one should succeed. A sequential test against SQLite cannot establish MySQL's concurrency behavior.

Add a duplicate request test with a stable operation identifier. Atomic decrement prevents one overselling race, but it does not prevent the same legitimate request from being submitted twice.

Record failures using business-safe identifiers so you can distinguish unavailable stock from infrastructure errors without exposing customer details. MySQL's UPDATE reference explains update conditions and changed-row reporting. The order workflow still needs its own integrity design.