Shopify replaced Redis with MySQL for inventory reservations–and it scaled blog|InfrastructureWe replaced Redis with MySQL for inventory reservations—and it scaledHow we used SKIP LOCKED, composite p
By Coderz Club · 2026-08-09 · Tags: coding
Shopify replaced Redis with MySQL for inventory reservations–and it scaled
blog|InfrastructureWe replaced Redis with MySQL for inventory reservations—and it scaledHow we used SKIP LOCKED, composite primary keys, and connection visibility to hit our scale targets.Published on May 12, 2026During checkout, when a buyer clicks "Complete purchase," we need to guarantee the items they're buying are still available. If we get this wrong in one direction, two buyers purchase the same last unit: the merchant has to cancel an order, send an apology email, and eat the support cost. If we get it wrong in the other direction, we tell a buyer something is sold out when it isn't, and the merchant loses a sale they should have made. At Shopify's scale, either failure compounds fast. On Black Friday 2025, merchants on our platform hit a record $5.1 million in sales per minute at peak. Every one of those transactions touches inventory. Our oversell protection system handles this by reserving inventory during payment processing—a short hold that prevents two concurrent checkouts from claiming the same unit. For years, this ran on Redis. When we moved toward a unified database strategy, we had to answer a hard question: could MySQL handle the same scale? Earlier attempts had failed. A single row with a quantity column couldn't handle the contention. MySQL 8's SKIP LOCKED feature introduced a different design: one row per inventory unit instead of one row per item. Inspired by 37signals' approach to database-backed load distribution, we rebuilt reservations on MySQL and hit our high-throughput targets during peak 2025 traffic. But the hardest lesson wasn't about database design. It was discovering that the real bottleneck wasn’t what we were observing and measuring. This post walks through the solution and what we found along the way. The challenge What is oversell protection? Oversell protection has two main operations: Reserve: When payment starts, we mark items as reserved (a short hold, e.g. several minutes). Claim: When payment succeeds, we permanently deduct quantity from the inventory ledger (source of truth). Checkout completion depends on this being fast and correct. Slow reservations trigger throttling and a worse buyer experience. Mistakes mean overselling (angry customers) or underselling (lost revenue). Scale and correctness requirements Scale here is not abstract: Shopify powers over 14% of U.S. ecommerce, and on Black Friday 2025 we saw an 11% increase in sales per minute at peak over the prior year. Reservations run on every checkout that touches inventory, so the system must handle that burst without dropping requests or breaking consistency. We needed to: Support the platform's high-performance throughput targets during peak traffic Respect multi-location inventory (only reserve from locations that can fulfill) Keep ACID guarantees between reservations and the inventory ledger Prioritize correctness: no overselling and no lost reservations The Redis model and its limits The previous system stored reservations in Redis. Each item had a quantity key, and reserving meant DECR, releasing meant INCR. Redis handled concurrency fine, but reservations and the inventory ledger lived in two different systems. The claim step (payment processed, permanently deduct inventory) required updating MySQL and cleaning up Redis, and those two operations couldn't be wrapped in a single atomic step. Depending on the order, this could cause overselling (item sold but was never deducted from the ledger) or underselling (item deducted and still marked reserved). On top of that, the Redis model had no multi-location awareness and added the operational cost of a separate cluster to maintain. Moving reservations into the same MySQL database as the ledger meant we could wrap everything in ACID transactions and eliminate these failure modes entirely. The solution: SKIP LOCKED Core idea: one row per unit, bounded by design Instead of one row per item with a quantity column, we use one row per sellable unit. An item with 10 units has 10 rows. Reserving three units means selecting and moving three rows in a single transaction. By keeping reservations and the inventory ledger in the same database, we get ACID across reserve and claim—fixing classes of bugs that were possible with Redis (e.g. payment succeeds but inventory isn't claimed, or the reverse). A simplified reserve flow looks like this: SKIP LOCKED is what makes this scalable: if another transaction has locked some rows, MySQL skips them and returns other available rows. No waiting on the same row, less contention. But one row per unit for all inventory would break down at scale—an item with 50,000 units across 10 locations would mean 500,000 rows, and the reserve query would slow as it scans through them. Instead, we maintain a bounded pool of available rows, capped at 1,000 per item/location combination. Reservations consume rows from this pool; a replenishment process refills it f
blog|InfrastructureWe replaced Redis with MySQL for inventory reservations—and it scaledHow we used SKIP LOCKED, composite primary keys, and connection visibility to hit our scale targets.Published on May 12, 2026During checkout, when a buyer clicks "Complete purchase," we need to guarantee the items they're buying are still available. If we get this wrong in one direction, two buyers purchase the same last unit: the merchant has to cancel an order, send an apology email, and eat the support cost. If we get it wrong in the other direction, we tell a buyer something is sold out when it isn't, and the merchant loses a sale they should have made. At Shopify's scale, either failure compounds fast. On Black Friday 2025, merchants on our platform hit a record $5.1 million in sales per minute at peak. Every one of those transactions touches inventory. Our oversell protection system handles this by reserving inventory during payment processing—a short hold that prevents two concurrent checkouts from claiming the same unit. For years, this ran on Redis. When we moved toward a unified database strategy, we had to answer a hard question: could MySQL handle the same scale? Earlier attempts had failed. A single row with a quantity column couldn't handle the contention. MySQL 8's SKIP LOCKED feature introduced a different design: one row per inventory unit instead of one row per item. Inspired by 37signals' approach to database-backed load distribution, we rebuilt reservations on MySQL and hit our high-throughput targets during peak 2025 traffic. But the hardest lesson wasn't about database design. It was discovering that the real bottleneck wasn’t what we were observing and measuring. This post walks through the solution and what we found along the way. The challenge What is oversell protection? Oversell protection has two main operations: Reserve: When payment starts, we mark items as reserved (a short hold, e.g. several minutes). Claim: When payment succeeds, we permanently deduct quantity from the inventory ledger (source of truth). Checkout completion depends on this being fast and correct. Slow reservations trigger throttling and a worse buyer experience. Mistakes mean overselling (angry customers) or underselling (lost revenue). Scale and correctness requirements Scale here is not abstract: Shopify powers over 14% of U.S. ecommerce, and on Black Friday 2025 we saw an 11% increase in sales per minute at peak over the prior year. Reservations run on every checkout that touches inventory, so the system must handle that burst without dropping requests or breaking consistency. We needed to: Support the platform's high-performance throughput targets during peak traffic Respect multi-location inventory (only reserve from locations that can fulfill) Keep ACID guarantees between reservations and the inventory ledger Prioritize correctness: no overselling and no lost reservations The Redis model and its limits The previous system stored reservations in Redis. Each item had a quantity key, and reserving meant DECR, releasing meant INCR. Redis handled concurrency fine, but reservations and the inventory ledger lived in two different systems. The claim step (payment processed, permanently deduct inventory) required updating MySQL and cleaning up Redis, and those two operations couldn't be wrapped in a single atomic step. Depending on the order, this could cause overselling (item sold but was never deducted from the ledger) or underselling (item deducted and still marked reserved). On top of that, the Redis model had no multi-location awareness and added the operational cost of a separate cluster to maintain. Moving reservations into the same MySQL database as the ledger meant we could wrap everything in ACID transactions and eliminate these failure modes entirely. The solution: SKIP LOCKED Core idea: one row per unit, bounded by design Instead of one row per item with a quantity column, we use one row per sellable unit. An item with 10 units has 10 rows. Reserving three units means selecting and moving three rows in a single transaction. By keeping reservations and the inventory ledger in the same database, we get ACID across reserve and claim—fixing classes of bugs that were possible with Redis (e.g. payment succeeds but inventory isn't claimed, or the reverse). A simplified reserve flow looks like this: SKIP LOCKED is what makes this scalable: if another transaction has locked some rows, MySQL skips them and returns other available rows. No waiting on the same row, less contention. But one row per unit for all inventory would break down at scale—an item with 50,000 units across 10 locations would mean 500,000 rows, and the reserve query would slow as it scans through them. Instead, we maintain a bounded pool of available rows, capped at 1,000 per item/location combination. Reservations consume rows from this pool; a replenishment process refills it f