How Shopify Moved Inventory Reservations from Redis to MySQL

Shopify processes more than 14% of U.S. ecommerce, and every checkout that touches inventory passes through a system that keeps two buyers from purchasing the same last unit. When you start paying, it holds your items for a few minutes, and when payment succeeds, it deducts them from the inventory ledger for good. For years those holds lived in Redis as simple counters while the ledger lived in MySQL, and because no transaction can span two databases, a crash between "deduct the ledger" and "clean up the counter" left inventory wrong. Shopify rebuilt the whole thing inside MySQL, storing one row for every sellable unit so that concurrent buyers lock different rows instead of fighting over one, leaning on a MySQL 8 feature that lets a query skip past rows other transactions hold, and capping the row count with a small, refillable pool. The rebuilt system carried Black Friday 2025, where merchant sales peaked at a record $5.1 million a minute.

We picked this post because it's the seat-per-row move from ticket booking, where you store one row per seat so buyers contend on different rows, proven in production at enormous scale. It's also a demonstration that a relational database with the right table design can handle load most engineers assume needs specialized infrastructure. The original is worth your time too.

Picture a flash sale with one hoodie left and two buyers hitting the pay button in the same second. Something has to decide who gets it, and at Shopify that something is oversell protection, the system guarding inventory correctness at checkout. It does two things. When a buyer starts paying, it reserves their items, a short hold lasting a few minutes while the payment processes. When the payment succeeds, it claims them, a permanent deduction from the inventory ledger, the MySQL record of what each merchant actually has on hand.

Either failure costs the merchant money. If two buyers each get the last hoodie, the merchant cancels one order, writes an apology, and eats the support cost. A buyer told the hoodie is sold out while one sits in the warehouse is a sale that never happens. The system sits in the path of every inventory-backed checkout, so any failure it allows will happen at scale.

For years reservations lived in Redis, one quantity counter per item. Reserving decremented the counter, releasing incremented it, and since Redis executes commands one at a time, two simultaneous decrements never raced. As a concurrency mechanism the counter worked fine.

The counter lived in Redis, the ledger lived in MySQL, and a claim had to write both. Suppose a payment just succeeded. The service deducts the units from the MySQL ledger, and that write commits. Then, before it can decrement the Redis counter, the process dies. Redis still believes those units are reserved, the item shows less availability than reality, and the merchant loses sales. When the failure lands the other way, with the reservation released but the ledger write lost, the item sells without ever being deducted, and that's an oversell. Every checkout carried a small window where the two stores could disagree, and no amount of careful coding closes it, because there is no atomic commit across two databases. The usual patches don't close it either. Two-phase commit needs both sides to speak a coordination protocol Redis doesn't, and an outbox or a reconciliation job shrinks the window to however often it runs, which turns wrong inventory into wrong-for-a-while inventory. In the middle of a flash sale, wrong for a while is an oversell.

Redis also knew nothing about inventory locations, and it was a separate cluster to operate. The only real fix was to get reserve and claim inside one ACID transaction, which meant reservations had to move into the ledger's MySQL database.

The team had tried MySQL before, the obvious way, with one row per item holding a quantity.

-- The failed first attempt, simplified
UPDATE reservations
SET available = available - 1
WHERE inventory_item_id = 42 AND available >= 1;

Every checkout for a hot item has to write that one row. InnoDB, MySQL's default storage engine and the layer that owns physical storage, indexing, and locking, grants the row lock to one transaction at a time, so everyone else queues behind it. A flash sale is thousands of buyers converging on exactly this kind of hot row, and the design collapsed under the load it existed to handle.

The new design stores one row per sellable unit. Ten hoodies in stock means ten rows in a table called reservation_units, and reserving three means selecting three free rows and moving them, in a single transaction, into a table of active holds called reserved_quantities. MySQL 8 added the feature this depends on, SELECT ... FOR UPDATE SKIP LOCKED. An ordinary FOR UPDATE select that reaches a locked row stops and waits for whoever holds it. With SKIP LOCKED, the scan passes over rows other transactions hold and returns free ones instead, so two buyers reserving the same item at the same moment each lock different rows, neither waits on the other, and both commit in parallel. Shopify credits 37signals' work on database-backed load distribution for the idea.

BEGIN;
SELECT id FROM reservation_units
WHERE shop_id = 1
  AND inventory_item_id = 42
  AND inventory_group_id = 7
LIMIT 3
FOR UPDATE SKIP LOCKED;
-- delete the selected rows from reservation_units,
-- then insert one hold into reserved_quantities
COMMIT;

A literal row per unit stops working at catalog scale. A merchant with 50,000 units of an item across 10 locations would need 500,000 rows, and the reserve scan slows as the table grows. So the pool is bounded. Each item and location combination gets at most 1,000 rows, reservations consume them, and a replenishment process refills the pool from the ledger. The cap came from peak reservation rates observed per item and location during flash sales, a number big enough to ride out a rush before replenishment catches up and small enough that the reserve scan stays quick.

An extreme flash sale can still drain a pool faster than replenishment refills it. When a reserve finds the pool empty, it replenishes inline, and a lock guarantees exactly one transaction does the refilling while other reserves for that item wait behind it instead of racing to insert duplicates. The buyer whose request triggered the refill pays some extra latency, and a buyer with real inventory behind their request is never turned away. That latency lands exactly when the sale is hottest, since that's the only time pools run dry, and the post doesn't say how much of it buyers actually ate. We also wonder about big orders. A wholesale buyer taking 5,000 units of an item whose pool caps at 1,000 can't be served by one scan, and a single refill won't cover it either, so presumably large quantities take a different path, but the post never says.

Getting from that design to something production-safe took three specific decisions inside InnoDB. The prototype used an ordinary auto-increment primary key, which meant the reserve query filtered on a secondary index. InnoDB stores the actual table data in primary key order, so a locking scan through a secondary index locks two things per row, the index entry it matched and the clustered index record sitting behind it. Making the filter columns the primary key lets the scan walk the table data directly:

PRIMARY KEY (shop_id, inventory_item_id, inventory_group_id, id)

One lock per row instead of two, and half the lock traffic on the hottest path.

The second problem surfaced when a pool ran empty. Under REPEATABLE READ, MySQL's default isolation level, a locking scan doesn't just lock the rows it finds. It also takes gap locks, locks on the empty spaces between index records that block anyone else from inserting there, which is how InnoDB keeps phantom rows from appearing inside a range you already read. Scan an empty pool and there are no rows to lock at all, so the scan locks the one thing left, the gap at the end of the index where new rows would go (InnoDB marks it with a pseudo-record called the supremum). New rows at the end of the index are exactly what replenishment produces. Its inserts stalled behind reserve scans and could deadlock against them, so these transactions now run at READ COMMITTED, which doesn't take gap locks the same way, the first non-default isolation level anywhere in Shopify's codebase.

That last detail made us pause. Isolation level is set per transaction, so nothing in the schema enforces it, and a future refactor that drops the setting brings the gap locks and the deadlocks quietly back. It seems like the kind of thing you'd want a guardrail around, and we're curious what Shopify uses, a lint rule, a wrapper, or just vigilance.

The last was a classic ordering deadlock. Reserve originally inserted into reserved_quantities and then deleted from reservation_units, claim deleted from reserved_quantities, and the opposite acquisition orders could cycle. The fix was consistent lock ordering, where every path acquires locks in the same sequence. Reserve now deletes from reservation_units first and inserts second, which is the order the sketch above follows, and claim touches only one table.

The cutover didn't take any of this on faith. Both systems ran in parallel, every reservation written to Redis and MySQL with Redis staying the source of truth, so the new tables absorbed real production load while nothing rode on them. The rollout then went pod by pod, low-traffic merchants first, up through the highest-volume ones. It's the same move Discord made before cutting over to ScyllaDB, sending production traffic to both systems until the data said the new one was safe, and we suspect we'll keep seeing it, because at this scale there is no staging environment that resembles Black Friday. When Black Friday 2025 arrived, the row-per-unit design was underneath all of it, and merchant sales peaked at $5.1 million a minute.

The requirement was that reserve and claim commit together with the ledger, and only rows living in the same database as that ledger can deliver it. Redis counted concurrent decrements correctly the whole time and still had to go, because it couldn't take part in the ledger's transactions. The post closes on "If you're reaching for Redis, Kafka, or a custom coordination layer for high-throughput mutual exclusion, your existing database might already be enough," and the useful version of that advice starts with atomicity. If a hold and the record it guards must commit together, put them in the same database. If they don't need to, a Redis counter is still the simpler tool, and nothing in this story argues against it.

None of this was possible before MySQL 8 shipped SKIP LOCKED, which is why the conclusion that MySQL couldn't handle this workload was correct when Shopify first reached it and wrong by the time they revisited it. If your team ruled out the plain database years ago, the reasons behind that call are worth rechecking against what the database can do today.

The bounded pool deserves a close look before you copy it, because that's where the operational cost landed. Replenishment is a new process to run, monitor, and page on, and its natural failure mode is an empty pool in the middle of a flash sale. Shopify traded a Redis cluster for that process, one moving part for another, and the trade came out ahead because the new part lives inside the transaction boundary that mattered.