Shopify moved Redis inventory holds into MySQL with one row per unit and SKIP LOCKED

Two moments in a checkout

Shopify's engineering account, as summarised in a dev.to article, splits reservation into two distinct moments. Reserve begins when a customer starts payment and creates a temporary hold. Claim happens after payment succeeds and permanently deducts units from the ledger, which remains the source of truth. Payment takes time, and during that interval no other checkout should be able to sell the held unit — yet an abandoned attempt must eventually release its stock.

The previous design kept reservation state in Redis as a per-item quantity counter: decrementing reserved a unit, incrementing released it. According to the dev.to article, Redis handled the concurrency itself adequately; the difficult boundary was between the reservation and the ledger. A paid order updated MySQL and cleaned up Redis through separate writes, and an interruption between them could oversell stock or leave it stranded as unsellable. The counter was also location-blind, and a unit is only useful if it comes from a location that can fulfil the order.

One row per unit, inside a bounded pool

Shopify had tried MySQL reservations before using a single quantity row, which became a hot lock: competing checkouts queued on the same row, and adding workers only meant more requests waiting there. The new design changes the independently lockable object. Each available unit gets its own row in an eligible pool, and a reservation selects the rows it needs, so concurrent transactions can acquire separate units instead of fighting over one shared counter.

To keep the schema practical, the pool is capped at 1,000 rows per item and location combination. The cap bounds the representation, not the merchant's actual stock: the ledger can describe inventory beyond the active pool, and replenishment brings units in as required. A background process handles normal replenishment, and an inline path covers the moment the pool empties — that path takes a lock so only one replenisher does the work while competing requests for the same item wait.

What SKIP LOCKED does and does not guarantee

MySQL's locking-read documentation describes SKIP LOCKED: a locking read excludes rows whose locks it cannot immediately acquire and returns other rows rather than waiting. Two workers selecting from the same pool illustrate the effect — one holds a unit's row, and the other bypasses it and takes a different unlocked unit.

The caveats matter as much as the mechanism. MySQL warns that queries skipping locked rows return an inconsistent view, so an empty result cannot establish that stock is sold out: rows may be held by other transactions, or the pool may simply need replenishment. There is no documented fairness or FIFO guarantee, and the manual flags these statements as unsafe for statement-based replication. The surrounding application, not the database, remains responsible for the availability decision.

Short transactions carry the hold through payment

In Shopify's published flow, reserve deletes the selected pool rows and inserts reservation records within one transaction. Committing releases the database locks, and the stored reservation persists while the customer completes payment. After payment succeeds, claim updates the ledger and removes the corresponding reservation atomically — both operations live in MySQL, so a single local transaction suffices. No row lock is held for the duration of the customer's interaction with a payment form.

The dev.to article flags a trap in the published example: the embedded SQL uses different table names and shows insertion before deletion, while the closing prose corrects the order to deleting from reservation_units before inserting into reserved_quantities. Expiry is similarly left open — the example includes an expires_at field and describes holds lasting several minutes, but the cleanup algorithm, cancellation handling, retry policy and late-payment-success behaviour are unspecified. The article also notes that connection hold time elsewhere in checkout became a scaling constraint even when the reservation queries themselves were fast.

Why it matters

This is a concrete architecture lesson for anyone building high-concurrency checkout or allocation systems. The interesting move is shaping the schema around lock contention: choosing per-unit rows as the independently lockable object instead of a hot counter, using SKIP LOCKED for queue-like allocation, bounding the pool so row-per-unit stays practical, and keeping transactions short so locks never span human-scale waits such as payment. Equally useful are the documented limits — inconsistent reads, no fairness guarantee, replication restrictions, and the ambiguity of an empty result — which any implementation copying this pattern has to design around explicitly.