Code10x
Data & consistencyAdvanced

Checkout and inventory, without overselling the last one

Checkout touches three things that fail independently — stock, payment and the order — and the design is mostly about what happens when one of them succeeds and the next does not.

The brief

Customers add products to a cart and check out. Checkout reserves stock, takes payment and creates an order.

Stock is finite, and a flash sale sells a small quantity of one product to a very large number of people at once.

Requirements

Functional

  • Show a product's availability
  • Reserve stock at checkout, take payment, and create the order
  • Release reserved stock when payment fails or the customer abandons checkout
  • Never confirm more units than exist

Non-functional

  • Overselling is worse than showing out of stock too early; an order that cannot be fulfilled costs a refund, a support contact and trust
  • A customer must never be charged without an order, or have an order without a charge
  • Browsing and cart traffic must not be slowed by a flash sale on one product

Back-of-envelope

Assume

  • 1 million orders a day in ordinary trading, 3 items per order
  • Peak hour carries 10% of the day
  • A flash sale: 1,000 units of one product, 200,000 people trying in the first minute
  • Checkout-to-payment-result takes 5 seconds at p50 and 30 seconds at p99

Therefore

  • Ordinary peak: 100,000 orders an hour ≈ 28 orders a second ≈ 85 stock decrements a second, spread across many products. Any database handles that comfortably — ordinary checkout is not the problem.
  • Flash sale: 200,000 attempts a minute ≈ 3,300 a second, all on ONE row. The problem is not throughput, it is contention on a single counter.
  • 999 of every 1,000 people in the flash sale get no unit. As with ticketing, most of the design's work is saying no quickly.
  • A 30-second p99 payment means reserved units are unavailable to others for at least that long; if 10% of reservations are abandoned, about a hundred units sit reserved-but-unsold at the moment of sell-out.

The ordinary and flash-sale numbers point at different designs. The mistake is to design for one and assume it covers the other.

The interface

POST /checkouts { cartId } (Idempotency-Key) → 201 { checkoutId, reservedUntil } | 409 { outOfStock: [sku] }Reserves every line or none. The idempotency key matters here more than anywhere: a double-clicked checkout button must not reserve twice the stock.
POST /checkouts/{id}/pay { paymentMethod } (Idempotency-Key) → 202 { status: PENDING }202, because the payment result is not known when the call returns. The client polls or is told; it does not block a connection for a p99 of 30 seconds.
GET /orders/{id} → { status: PENDING | CONFIRMED | CANCELLED }The order is created in PENDING before payment, so there is always a record to reconcile against. An order row that appears only after payment has nothing to attach a lost payment to.

What is stored

stocksku · on_hand · reserved · CHECK (reserved <= on_hand)
A reservation is UPDATE stock SET reserved = reserved + n WHERE sku = ? AND on_hand - reserved >= n. The condition and the change are one statement, and the CHECK constraint refuses an oversell even if some other code path gets it wrong.
reservationsreservation_id · checkout_id · sku · quantity · expires_at · state · INDEX (state, expires_at)
One row per reserved line, so a release knows exactly how much to give back. The index exists for the expiry job and nothing else.
orders and outboxorders: order_id · checkout_id UNIQUE · status · total · outbox: event_id · type · payload · published_at
checkout_id is unique so a retried checkout cannot create a second order. The outbox row is written in the same transaction as the status change, so 'order confirmed' is never published for an order that rolled back.

The design

Catalogue and cartRead-heavy and cached. Availability shown here is advisory — 'in stock' on a product page is a hint, and the reservation is the truth.
The inventory serviceOwns the stock rows and the only code allowed to change them. Reservations, releases and the final decrement on fulfilment all go through it.
The checkout orchestratorRuns the saga: reserve stock, create the pending order, request payment, then confirm or compensate. It records each step so a crash halfway resumes rather than repeats.
The reconcilerPeriodically compares payments the provider recorded with orders this system recorded, and releases reservations that expired without an outcome. It is the component that turns 'eventually' into a number of minutes.

The decisions

Each of these could go the other way. The choice, the reason, and what it costs — a design that lists only what it chose teaches the choice; one that lists what it gave up teaches the judgement.

Decrement stock when the item is added to the cart, at checkout, or after payment?Chose — Reserve at checkout, with an expiry; decrement for good on paymentBecause — At add-to-cart, abandoned carts lock stock for hours. After payment, many people pay for the last unit and all but one are refunded. At checkout the window is minutes, and the customer is committing.Reserved-but-abandoned stock is invisible to other buyers until it expires, so a product can look sold out and then come back. The expiry length trades that against customers who pay slowly.
One distributed transaction across stock, order and payment, or a saga?Chose — A saga with compensating stepsBecause — The payment provider is an external system that will not take part in a two-phase commit, so there is no transaction that spans it. The only question is whether the compensation is designed or discovered.Intermediate states are visible: an order can be PENDING with stock reserved and no payment result. Every screen and every report has to handle that state instead of pretending it does not exist.
Serve a flash sale from the same stock row?Chose — No — pre-split the sale quantity into bucketsBecause — 3,300 conditional updates a second on one row serialise on its lock. Splitting 1,000 units into, say, 20 buckets of 50 lets 20 reservations proceed at once, each on its own row.A request routed to an empty bucket must try another, and the sale can report sold out while a few units remain in buckets nobody retried. The last units are sold by a sweep, not by the rush.

What breaks first

In order. Each names what you would actually observe, and each fix carries its cost.

The single hot stock row in a flash saleSymptom — Checkout latency for the sale product climbs into seconds while every other product checks out normally; the database shows lock waits concentrated on one key.Fix — Bucketed stock as described, or admit flash-sale checkouts through a queue at the rate the row can serve.Either false sell-outs from empty buckets, or customers waiting in a queue for a product they may not get.
Payment tail latencySymptom — Reserved counts grow during a sale while confirmed orders lag behind; the gap is the p99 of the payment provider, visible as reservations older than 30 seconds.Fix — Shorten the reservation during the sale and release on the first definitive failure rather than waiting for expiry.A payment that succeeds after its reservation lapsed needs a refund or a re-reservation attempt, and some will get the refund.
The flash sale spilling onto everything elseSymptom — Cart and browse latency rises for all products during the sale, because the sale shares connection pools and instances with ordinary checkout.Fix — Isolate the sale path: its own instance pool, its own connection pool — a bulkhead.Capacity reserved for an event that lasts minutes, idle the rest of the time.

When something fails

Payment succeeds and the orchestrator crashes before recording itThe order stays PENDING and the reservation eventually expires, releasing stock that was paid for. The reconciler must run before the reservation expiry, find the payment, and confirm the order — which makes its schedule a correctness parameter, not a tuning one.
The inventory service is unavailableCheckout fails closed: no reservation, no payment attempt. Browsing continues from the cache. Taking payment 'and reserving later' is how an oversell is manufactured.
A compensation step fails — the stock release does not go throughUnits stay reserved for a cancelled order. The release is retried from the saga log; the expiry on the reservation is the backstop that returns the stock even if every retry fails.

Scaling it

Each step is triggered by a number, not a feeling — and carries what it costs.

10× ordinary order volumeMove — Partition stock by SKU and orders by customer; the inventory service scales horizontally because each SKU's row is independent.Cross-SKU reports and 'total reserved right now' become aggregations across partitions rather than one query.
Multiple warehousesMove — Stock per (SKU, warehouse), with reservation choosing a warehouse by proximity and availability.An order can now be split across warehouses, and the reservation must be all-or-nothing across rows that may live in different partitions.
A flash sale ten times largerMove — More buckets and a waiting room in front of the sale product.More units stranded in empty-looking buckets at the end, and a longer tail of the sale selling its last units after the headline sell-out.

What gets probed

The design is the easy half. These are where the conversation goes, and each has a defensible answer above.

  • A customer's payment succeeds, and the stock was released a second earlier. What happens next?
  • Why is the stock check a WHERE clause rather than a SELECT followed by an UPDATE?
  • The product page said 'in stock' and checkout said 'out of stock'. Is that a bug?
  • What does the reconciler compare, how often does it run, and what decides that interval?