Shopify moved inventory reservation from Redis into the MySQL database that already stored its inventory ledger. The interesting part is the shape of the data: a bounded pool gives each available unit its own row, letting concurrent checkouts acquire different locks. For developers building checkout or allocation systems, this is a useful example of how schema, transaction boundaries and connection occupancy interact.
TL;DR
- Shopify's previous reservation state and inventory ledger lived in separate systems. Claiming an order required coordination between Redis and MySQL.
- The replacement uses one row per available unit, with a pool capped at 1,000 rows per item/location combination. The ledger can represent additional stock.
-
SKIP LOCKEDlets a locking read choose other eligible rows while some rows are busy. An empty result cannot establish that all stock is sold out. - Reserve and claim use short database transactions. Stored reservation state survives payment processing after the reserve transaction releases its locks.
- Connection hold time elsewhere in checkout became a scaling constraint even when the reservation queries were fast.
Inventory reservation has two different moments
Shopify's engineering account distinguishes reserve from claim. Reserve begins when a customer starts payment and creates a temporary hold. Claim happens after successful payment and permanently deducts inventory from the ledger, which remains the source of truth.
That distinction matters because payment takes time. During that interval, another checkout should not sell the same held unit. A completed purchase also needs a durable ledger change. The application therefore has to connect a temporary promise to a permanent accounting operation without leaving inventory permanently unavailable when an attempt is abandoned.
The old Redis design used an item quantity counter. Decrementing the counter reserved stock; incrementing it released stock. Redis handled concurrency adequately. The difficult boundary was between the reservation and the ledger: paid-order claim updated MySQL and cleaned up Redis through separate writes. An interrupted sequence could leave stock oversold or unavailable when it should have been sellable.
The previous model also lacked the location awareness that the replacement needed. A unit is useful only if it comes from a location that can fulfill the order. Counting all stock together can produce an attractive database number and an impossible delivery promise. The new selection path has to preserve that eligibility constraint throughout the reservation.
MySQL SKIP LOCKED depends on the rows you lock
Shopify had tried MySQL reservations before. A straightforward quantity row created a hot lock: competing checkouts updating the same row had to queue. Adding workers gave more requests a chance to wait at that row.
The replacement changes the independently lockable object. Each available unit gets a row in an eligible pool. A reservation chooses rows representing the quantity it needs. Concurrent transactions can choose separate units rather than repeatedly attempting to modify one shared counter.
MySQL's locking-read documentation explains the next piece. A locking read using SKIP LOCKED excludes rows whose row locks it cannot immediately acquire. It can return other rows instead of waiting for those busy rows. The option provides a mechanism for queue-like tables, and unit allocation benefits from that shape.
Imagine two workers selecting from the same eligible item/location pool. Worker A has locked one unit. Worker B can bypass that row and select another unlocked unit. This is a conceptual explanation of the mechanism; the four-unit diagram in the episode does not represent a measured Shopify inventory count.
The selection still has boundaries. It cannot cross into an ineligible group to make the diagram look faster. It also cannot treat skipped rows as absent stock. MySQL explicitly warns that queries which skip locked rows return an inconsistent view of the data. The result is useful for acquiring available work, while the surrounding application remains responsible for the availability decision.
There is no documented fairness or FIFO guarantee here. A row can be bypassed while busy. SKIP LOCKED applies to row-lock acquisition; other locks, connection queues or replenishment coordination can still involve waiting. The manual also flags these statements as unsafe for statement-based replication.
A bounded pool keeps the schema practical
One row per unit raises an obvious storage question. A merchant can have much more inventory than the number of rows a checkout should actively scan and manage.
Shopify caps the available pool at 1,000 rows per item/location combination. This bounds the pool representation rather than imposing a 1,000-unit limit on the merchant's actual stock. The inventory ledger can describe stock beyond the active pool, and replenishment brings units into the pool as required.
The article describes background replenishment and an inline path when the pool empties. Inline replenishment takes a lock so only one replenisher performs that work, while competing requests for the same item wait. That adds latency to this path, but prevents a depleted pool from being interpreted as an empty warehouse.
Two different empty cases deserve attention in a design review. The bounded pool might genuinely need replenishment. A locking read might also omit rows that other transactions currently hold. Neither observation alone establishes the global stock position. The surrounding reserve and replenishment rules have to decide how to proceed safely.
This is a good place to examine failure behavior before copying the pattern. How does a request distinguish temporary contention from a pool that needs refilling? Which operation coordinates replenishment? What does the caller receive when it cannot allocate enough eligible units? Shopify explains its broad replenishment design; this article supplies no additional production policy beyond that account.
Short transactions carry the hold across payment
In the final prose of Shopify's post, reserve deletes selected pool rows and then inserts reservation records in a transaction. Commit releases the database locks. Rollback undoes the transaction's changes. The committed reservation remains as stored state while the customer completes payment.
After successful payment, claim updates the ledger and removes the corresponding reservation atomically. Those operations share MySQL, so they can participate in one local transaction. The database locks belong to these short transactions. The payment form does not require a database row lock to stay held throughout the customer's interaction.
There is a source-detail trap worth retaining. The embedded simplified SQL uses different table names and shows insertion before deletion. The article's final lock-order explanation explicitly corrects the order to deletion from reservation_units followed by insertion into reserved_quantities. The episode follows that corrected prose. The incomplete gist is evidence of the published example, and should not be treated as runnable setup SQL.
Expiry is another boundary between a field and a complete design. The example includes expires_at, and the article describes a short hold such as several minutes. It leaves the MySQL cleanup algorithm, cancellation handling, retry policy and late-payment-success behavior unspecified.
An abandoned payment needs an eventual release path so stock does not remain held forever. Our dashed expiry branch labels that lifecycle requirement explicitly. It makes no claim about an actual Shopify cleanup worker, timeout cadence or release query. Any implementation of this idea needs to define those policies and how they interact with a concurrent successful claim.
Indexes, isolation and connections change the outcome
Shopify's composite primary key is (shop_id, inventory_item_id, inventory_group_id, id). The group identifier should retain its published name: the post does not fully define its mapping to physical locations. The key follows the access pattern and helps keep locking within the intended lookup.
In the prototype described by Shopify, an auto-increment design acquired locks through a secondary index and the clustered index. The composite key reduced that observed case from two row locks to one. That observation belongs to the disclosed prototype and query shape. It does not establish a universal lock count for every query using a composite primary key.
The team also used READ COMMITTED after observing gap and supremum locks under REPEATABLE READ interfere with replenishment inserts. MySQL's isolation-level documentation explains that under READ COMMITTED, locking reads generally lock index records rather than gaps. Foreign-key and duplicate-key checks retain gap-lock exceptions, and deadlocks remain possible.
Consistent table ordering helps prevent circular waits between related transaction paths. That is why the reserve-order correction matters: readers reproducing the idea need to inspect all interacting operations together. Isolation, indexes and ordering affect correctness and contention as a system.
The remaining scaling ceiling appeared outside the fast reservation query. Other checkout callers held database connections too long. Shopify tagged callers, measured connection hold time through ProxySQL, cleaned up the checkout path and revisited InnoDB thread concurrency where headroom existed. A connection occupied by surrounding application work is unavailable to the next request even after its SQL finishes.
For a practical review, measure the transaction's lock duration and the caller's connection occupancy separately. Inspect the lookup index, replenishment waits and the path after the query returns. A fast query benchmark can miss the queue that the application creates around it.
Verdict: SHIP IT
I stamped this SHIP IT for the shared transaction boundary and the observable, rollbackable rollout. Shopify initially kept Redis authoritative while shadow-writing both systems and comparing outcomes, then switched gradually with a kill switch. In-flight Redis reservations were honored during the transition.
The useful takeaway is a design method: choose an independently allocatable row, keep related state in a transaction, and observe the whole checkout path. Shopify's account provides no production reservation latency or independently replicated numerical speedup. The verdict evaluates the architectural choices and migration discipline described in the source.
FAQ
Did Shopify remove Redis everywhere?
The post concerns inventory reservations. It does not establish a company-wide removal of Redis or a judgment against its other workloads.
Does an empty SKIP LOCKED result mean sold out?
Busy rows are omitted, and Shopify's bounded pool can require replenishment while the ledger still has stock. An empty result alone cannot prove sold-out.
Are MySQL locks held while the customer pays?
The reserve transaction releases its locks at commit. The stored hold persists through payment, followed by a separate atomic claim transaction after successful payment.
How does Shopify expire abandoned MySQL reservations?
The published example contains an expiry field. The cleanup implementation is unspecified in the post, so the episode labels its release branch as a conceptual lifecycle requirement.
Sources
- Shopify Engineering: scaling inventory reservations
- Shopify's published simplified reserve SQL
- MySQL 8.0: locking reads and SKIP LOCKED
- MySQL 8.0: transaction isolation levels
This article expands on an episode of **The Daily Diff, a five-minute daily video on what shipped and what broke in tech.
Watch the episode · Subscribe on YouTube · the written diff lands in your inbox every morning at thedailydiff.dev.
Top comments (0)