In March last year, one of us spent a week arriving at the client's warehouse at 4:50 every morning. Not to move vegetables — to watch orders being taken. Fresh produce runs on a different rhythm from other e-commerce: the miniprogram opens at 5:00 sharp, orders close at 6:30, picking and loading finish before 7:00. Half an hour late, and the truck misses the morning market.
V1: Deduct Stock at Checkout
The most straightforward logic: on order submission, run UPDATE stock SET num = num - qty. It broke on day three: someone added vegetables to the cart but never paid, and the stock was already deducted — everyone else saw 'sold out.' By the 6:30 cutoff, the system held a pile of 'reserved but unsold' quantities. The warehouse stocked by system numbers, for nothing.
The owner put it bluntly: this produce won't keep until afternoon.
V2: Reserve for 15 Minutes
We changed it so that creating an order writes a reservation record, with a frozen field added to the stock table — sellable quantity equals num minus frozen. Unpaid after 15 minutes? A cron job scans every minute and releases the reservation. This version was mostly right, but we hit one case: two orders racing for the last 3 units in the same second. Both read sellable as 3, both thought they had enough, and we oversold by 2.
The cause was read-then-write with no lock in between. Two people looked at the same time; both saw 3.
V3: Let the Database Decide
In the end we didn't add a queue or a distributed lock — just one conditional update:
UPDATE goods SET num = num - 1 WHERE id = 10086 AND num >= 1
The PHP backend reads rowCount() via PDO; a return of 0 means you didn't win the race, and the user immediately sees 'just sold out.' One statement handles both the check and the deduction, and MySQL's row lock guarantees they can't both succeed. The reservation record is still there, but it's only written after you've actually won.
After that change, we ran ab 50 times under concurrency watching for negative numbers. None.
How the 15 Minutes Was Chosen
The reservation window wasn't pulled out of thin air. The client went through half a month of order records: the median time from order creation to payment was a little over 3 minutes; the longest was 11 minutes — someone stepped out to take a call at the warehouse door. Fifteen minutes covers the vast majority without tying up produce in the system too long.
For the cron job, we deliberately wrote it to release 'only unpaid and uncanceled' reservations. The first version missed 'uncanceled': someone canceled an order, and the release job processed that reservation a second time. An extra line in the log, no actual damage, but it looked sloppy — adding a status check cleaned it up.
How Those 2 Oversold Units Settled
That day the warehouse shipped as usual; the owner covered the extra 2 units himself and didn't ask the customer to pay. At the retrospective we raised whether to add an 'oversell alert.' The owner waved it off: it won't happen twice a year, and if it does, he'd rather eat the cost than complicate the ordering flow.
We took note. On later e-commerce projects, when facing 'should we add another check for a tiny-probability event,' we often ask one question: if it does happen, does the system absorb it or does a person? In some scenarios, a person is cheaper.
That Old Computer
The order-entry machine at the warehouse was replaced once; when we visited in 2018, the labels on F1 through F12 were still worn blank. The new keyboard had stickers, but the operator said she didn't need to look — her hands remembered. Across the 40-plus orders we watched her enter, she never once looked up for the mouse.
The morning-market logic has been revised several times since — cold-chain time windows, category summaries — but that rowCount line never changed. The comment reads: win the race first, then write the reservation. The canopy over the warehouse door went up later; before that, rain meant sheltering under the eaves. We got lucky with rain during our stakeout days, but did catch the scale breaking twice.
The Morning Routine
Ten minutes before cutoff, the warehouse clicks 'export picking list' in the backend — an Excel summarized by category, two or three A4 pages. We deliberately built that export on final numbers after reservation releases, not on sellable quantities, in case someone canceled midway.
The picking lady clips the paper list to a board and pushes her cart along. At 6:30 the system automatically closes ordering: the frontend button goes gray, and the backend checks the time too. Both layers, always.
One morning at 5:40 there was a light drizzle, the ground outside the warehouse wet, and a few of us stood under the eaves watching the order count climb on a phone screen. Nothing profound about it — just the feeling that that rowCount line was worth writing.
