Race Conditions

Race Conditions

Farmers-market mornings are bursty: many shoppers reserve the same limited stock at the same moment. Every interleaving below is handled in code and covered by tests/Feature/RaceConditionTest.php. The long-form story, including the bug the stress test caught, is in the article The Last Mango Problem.

The rules

1. Lock what you're about to change — SELECT … FOR UPDATE inside one transaction, rows taken in id order so two checkouts can't deadlock.
2. Anything you decide on under a lock is read with a lock. Under MySQL's default REPEATABLE READ, a plain COUNT after waiting for a lock still reads the snapshot taken before the wait.
3. Prefer one atomic statement to read-modify-write. UPDATE … WHERE <still in the state I expect>, then check how many rows changed.
4. Let the database have the last word — unique indexes and CHECK constraints catch what a forgotten lock would let through.
5. Send nothing until the transaction commits — notifications are dispatched after commit.

Every race, and its fix

Race	What could go wrong	How it's prevented
Two shoppers buy the last items	Stock oversold	Products locked FOR UPDATE in id order; stock checked and decremented in one transaction
Same product twice in one basket	Each line passes, the total exceeds stock	Lines merged per product before the check
A single-use coupon, fifty buyers	Discount given twice	Coupon row locked, redemption recorded, counter incremented in the checkout transaction; CHECK (used_count <= usage_limit) as a backstop
A nearly full pickup window	Capacity exceeded — row locks on orders can't stop inserts into an empty range	The pickup-slot row is locked, and the booking count is itself a locking read
Double-click on Place order / two tabs	Duplicate orders	A per-customer atomic cache lock around checkout; the open-orders limit is checked inside it
Customer cancels while the farmer accepts	Stock released twice, or an accepted order silently cancelled	The order is re-read FOR UPDATE and its status re-checked for every transition
A farmer saves the stock form while customers buy	The form's stale number overwrites the sales (a lost update)	The form sends the stock it showed; the server subtracts what sold since, and leaves stock alone if the field wasn't changed
Monday restock during a checkout	A read-then-save loop overwrites a reservation	One atomic UPDATE … SET stock_quantity = weekly_quantity
Two restocks at once	Two "back in stock" e-mails	Each alert is claimed with UPDATE … SET notified_at = NOW() WHERE notified_at IS NULL; only the winner sends
Double tap on Pay	Charged twice	Payment row locked, pending → processing once, one-time idempotency key (see Payments)
Gateway confirms after the window	Money taken for released orders	Late capture detected and refunded automatically
Admin revokes a badge during the nightly job	Badge silently re-awarded	Badge rows locked during recompute; manual decisions are locked
Parallel OTP guesses	Extra guesses; the attempt counter lost on rollback	Code row locked; the error is thrown after commit so failed attempts persist
Double-tap on the heart	Duplicate favourites	Atomic DELETE, then INSERT IGNORE on a unique index
Two people claim the same username / e-mail / coupon code	A server error for the loser	The unique index decides; the exception is mapped to a normal form error
Deadlocks under load	Request fails	DB::transaction(..., attempts: 3) retries

Proving it

php tests/Stress/checkout-race.php 50

Fifty separate PHP processes hit checkout at the same instant against the real MySQL database: one item left, a coupon usable once, a pickup window with room for three, and one buyer double-submitting. Every run ends with exactly 1 sale, 1 coupon use, 3 bookings, 0 crashes and 0 negative stock, and the script removes its own test data. The first version of the pickup-window check let 20 orders into a 3-place window; making the count a locking read fixed it.

See also: Order Lifecycle · Database Design · Testing and CI