Architecture/CaseStudies/shopify-inventory-reservations-mysql

Shopify: Replacing Redis with MySQL for Inventory Reservations

Problem + constraints

Shopify reserves inventory the moment a customer starts checkout, so two shoppers can't both buy the last unit of an item at the same time. That reservation system historically ran on Redis: each item had a quantity key, reserving decremented it (DECR), releasing incremented it back (INCR) — fast, and good enough for raw throughput. The catch is that reservations living in Redis while orders live in MySQL means two separate systems have to agree with each other, and any drift between them shows up as overselling or underselling. That failure mode is worst exactly when it's most expensive to debug — during a flash sale or Black Friday peak, under the heaviest concurrent load the system ever sees. Shopify's question was whether MySQL alone, tuned correctly, could absorb the same reservation throughput while collapsing reservations and orders into one transactionally-consistent system.

Solution

The naive MySQL translation of the Redis model — one row per item with a quantity column, guarded by SELECT ... FOR UPDATE — doesn't scale: every reservation for a given item serializes behind that one row's lock, so a viral product or flash-sale item becomes a single point of contention. Shopify's redesign changes the unit of locking instead of avoiding MySQL: one row per sellable unit, not one row per item. An item with 10 units in stock has 10 rows; reserving 3 units means selecting and moving 3 of those rows into a reserved state in a single transaction, using SELECT ... FOR UPDATE SKIP LOCKED. SKIP LOCKED (MySQL 8+) lets a transaction skip rows another in-flight transaction already has locked instead of queuing behind them — so concurrent reservations against the same hot item run in parallel across different rows instead of serializing on one. To keep row counts bounded for high-stock items, Shopify caps the materialized pool at roughly 1,000 rows per item-location combination rather than eagerly representing every unit. They also switched from a secondary index plus clustered-index lookup to a composite primary key (shop_id, inventory_item_id, inventory_group_id, id) so the columns the query filters on are the primary key itself — that halved the lock count per reservation.

Result: on Black Friday 2025, with merchants hitting a record $5.1 million in sales per minute at peak, the system processed millions of reservation requests per minute without overselling — with reservations and orders now living in the same ACID-transactional store.

What to steal

Row-per-unit plus SKIP LOCKED turns one hot, contended row into many independently lockable rows — you're trading row count for lock parallelism, which is a good trade when the row count is boundable.
A composite primary key that matches your query's filter predicate removes a secondary-index hop and cuts lock count directly — cheap to design in up front, expensive to retrofit later.
Consolidating a bolted-on side system (Redis as a fast counter) into your source-of-truth transactional store trades a small amount of raw single-operation speed for eliminating an entire class of cross-system consistency bugs — worth it when that bug class (overselling) is the expensive one.
Cap the materialized row pool instead of representing every unit eagerly — it keeps the technique's benefit where contention actually happens without letting row count blow up for high-stock items.

Principal Engineer Lens

This is a "don't assume the specialized store is automatically faster" story. Redis-for-hot-counters, database-for-source-of-truth is a common enough pattern that it's easy to reach for reflexively — but it always costs you a consistency gap between the two systems, and that gap becomes a real bug precisely under the peak-load conditions you're least equipped to debug live. The move worth defending in an architecture review isn't "we picked the specialized technology" or "we picked the general-purpose one" in the abstract — it's showing you measured whether the general-purpose system, tuned with the right row design and indexing, can absorb the same load while removing a whole failure mode. "We deleted a system and got both faster and more correct" is a much stronger review narrative than an abstract simplification pitch, and it's the kind of concrete, load-tested claim a Principal Engineer should be able to back with numbers, not intuition.

Reel Script

Setup: Shopify has to reserve inventory the instant checkout starts, so two customers can't both buy the last unit. That reservation system used to run on Redis, separate from the MySQL database holding orders — which raises the obvious question: can MySQL just do this itself?

Concept walkthrough: Walk through why the naive MySQL translation (one row per item, SELECT ... FOR UPDATE) fails — it serializes every reservation for that item behind one lock. Then the fix: one row per sellable unit instead of per item, SKIP LOCKED so concurrent reservations skip past rows already claimed instead of queueing, and a composite primary key matching the query's filter columns to cut lock count in half.

Real example tie-in: Black Friday 2025 — $5.1M/minute in sales at peak, millions of reservation requests per minute processed with zero overselling, reservations and orders now in one transactionally-consistent system instead of two systems that had to be kept in sync.

Tradeoffs & alternatives: Redis DECR/INCR is simpler and was plenty fast — the cost was a permanent consistency gap between two systems that could drift under load. The MySQL redesign required real schema and query work (row-per-unit, composite keys, capped pools) to hit comparable throughput, but it collapses reservations and orders into a single transaction boundary, so the class of bug caused by two systems disagreeing simply can't happen anymore.

Principal Engineer takeaway: Before defaulting to a specialized fast-path store for a hot operation, measure whether your general-purpose source-of-truth store can be tuned to absorb the same load. When it can, you get the throughput and you delete an entire consistency-bug class — a strictly better outcome than optimizing throughput and living with the gap.

Sources: