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 LOCKEDturns 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.
Engineering 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.