Shopify Inventory Reservations
Shopify’s account describes replacing Redis reservations with MySQL.
Shopify’s account describes replacing Redis reservations with MySQL. It uses a bounded pool of rows representing available units and SKIP LOCKED to avoid waiting on the same locked rows. Keeping reservations and the inventory ledger together permits transactional updates. The post also emphasizes primary-key layout, lock behavior, consistent lock ordering and visibility into connection usage. The central lesson is a migration away from the earlier split-store design. Source publication: 2026-05-12. The walkthrough below is an original interview exercise, not an undocumented claim about the company.
The mechanism at a glance
Figure — Checkout → MySQL transaction (reserve request); MySQL transaction → Available unit pool (select unlocked units); MySQL transaction → Reservation rows (record reservation); MySQL transaction → Inventory ledger (atomic claim); Inventory ledger → Replenishment (bounded refill budget); Replenishment → Available unit pool (replenish)
The numbered components identify responsibilities. Follow the labeled arrows rather than treating the numbers as a global execution order. The scenario later in this lesson shows one concrete sequence.
Step-by-step reasoning
1. State the invariant
For an independent stock exercise, start with 100 units and require available + reserved + sold = 100. A reservation transfers units between states; it does not manufacture stock. Record reservation identity so an expiry callback can release each hold at most once.
2. Distinguish lock avoidance from availability
Suppose ten rows exist and another transaction currently locks eight. A SKIP LOCKED query may return only two even though more stock could become available shortly. Decide whether to retry briefly, report temporary contention or use a replenishment protocol. Do not interpret skipped rows as permanent out-of-stock evidence.
3. Bound work inside the transaction
Select a limited number of rows, validate quantity and move them to the reserved state in one short transaction. Lock shared tables in the same order across reserve, release and claim operations. Keep payment network calls outside that transaction; they have independent failure and latency behavior.
4. Measure the actual bottleneck
For this exercise, compare database CPU, lock-wait duration, connection occupancy and transaction time. Low CPU with exhausted connections suggests held connections or slow external work inside transactions. Increasing database size alone may not remove that constraint.
Contracts and state
The following sketch makes the decision boundary concrete. Field names and capacity assumptions are illustrative; adapt them to the stated product contract.
reservation(id, sku, quantity, state, expires_at)
unit_pool(sku, unit_id, location_id)
SELECT ... FOR UPDATE SKIP LOCKED LIMIT requested_quantity
Validate count; move selected units; commitWorked example
Original exercise: 20 requests each try to reserve one of 10 units. Allow ten commits and ten explicit unavailable or retry outcomes. Then expire three reservations and replay the same expiry events twice. The final available count must increase by exactly three, and a claimed reservation must never be released by an old expiry event.
Failure walkthrough
Original failure probe: payment succeeds but the response is lost. Persist an uncertain payment outcome and reconcile before releasing or confirming inventory. A conditional transition on reservation ID prevents a delayed expiry worker from undoing a completed sale. Keep a discrepancy report that compares ledger totals and reservation states.
Figure — Select available rows → Create hold transactionally → Call payment outside the transaction → Claim the matching active hold → Reconcile an uncertain payment
Decisions and trade-offs
| Decision | Useful when | Cost to explain |
|---|---|---|
| Adopt the mechanism | The same workload constraint is demonstrated | Validate with your own measurements |
| Keep a simpler design | Your scale and guarantees are already met | Monitor the trigger for changing it |
Check your understanding
Why is a per-unit row pool useful under contention, and what cost does it introduce?
Show answer and explanation
Answer: It offers multiple lockable units instead of one hot quantity row. It also increases row and replenishment work. A bounded pool and measured transaction behavior are necessary; the model is not automatically superior for every inventory workload.
Primary documentation
Read the first-party engineering account or official technical reference. Company engineering posts describe the scope and date of that publication; the interview reconstruction and scenarios here are original teaching examples.
Continue the connection
Study Ticketmaster and explain which guarantee from this lesson carries into that topic.