Glonce

Shopify replaces Redis with MySQL for inventory reservations

infrastructureThe Glonce editorsHacker News🔥58Glonce rating58Impact35Novelty45Relevance65Surprise30Players55Credibility82
Shopify replaces Redis with MySQL for inventory reservations

Shopify's checkout relies on "oversell protection": a short reservation hold placed on inventory the moment a buyer starts paying, so two concurrent checkouts cannot claim the same last unit. For years this ran on Redis, with each item's stock tracked as a single quantity key that reservations decremented and releases incremented. The problem was that reservations and the permanent inventory ledger lived in two different systems, Redis and MySQL, so the final "claim" step at successful payment could not be wrapped in one atomic operation. Depending on timing, that gap could cause overselling (a sale never deducted from the ledger) or underselling (stock deducted but still marked reserved).

When Shopify moved toward a single, unified database strategy, the engineering team rebuilt reservations on MySQL using MySQL 8's SKIP LOCKED feature. Instead of one row holding a quantity per item, the new design uses one row per sellable unit: an item with 10 units gets 10 rows, and reserving three units means selecting and moving three rows in one transaction. SKIP LOCKED lets a query skip rows another transaction already has locked and grab other available rows instead, avoiding contention from everyone queuing on the same row. Because reservations and the ledger now share a database, both can be wrapped in a single ACID transaction, closing the overselling and underselling gap that Redis could not close. The approach was inspired by 37signals' database-backed load distribution pattern. To keep the row count from exploding (an item with 50,000 units across 10 locations would otherwise need 500,000 rows), Shopify caps a bounded pool of available rows at 1,000 per item/location combination, refilled by a replenishment process; if a hot item's pool empties during a flash sale, a lock ensures only one transaction replenishes it while other reserves wait rather than racing to insert rows.

The rebuilt system ran through Black Friday 2025, when merchants on Shopify hit a record $5.1 million in sales per minute at peak, an 11% increase in peak sales per minute over the prior year; Shopify says it powers over 14% of U.S. ecommerce.

Getting there took several specific fixes. A composite primary key on (shop_id, inventory_item_id, inventory_group_id, id) cut InnoDB row locks per reservation from two down to one, versus an earlier auto-increment primary key that locked both a secondary index and the clustered index. Running SELECT ... FOR UPDATE SKIP LOCKED against an empty table needing replenishment produced gap locks, including on the InnoDB "supremum" pseudo-record, that blocked replenishment inserts and risked deadlocks; switching those transactions from MySQL's default REPEATABLE READ isolation to READ COMMITTED, the codebase's first use of a non-default isolation level, removed the gap locks (the team credits Jahfer Husain's guide to InnoDB locking for the explanation). A separate deadlock, caused by reserve and claim touching two tables in different orders, was fixed by standardizing the order: reserve always deletes from the units table before inserting into reserved_quantities, and claim only touches reserved_quantities. Multi-item carts are batched into one round trip with UNION ALL.

Even after those fixes, production throughput hit a ceiling well below target, despite acceptable P90 reservation latency, CPU that wasn't maxed out, and already-optimized queries. Load tests showed threads queuing in MySQL, CPU spikes when queued work ran, and connection exhaustion on the ProxySQL layer, but knowing connections were exhausted didn't say who was holding them. The team tagged every SQL statement with a comment identifying the business process, such as /* conn_tag:checkout_completion */, and added tracking at the ProxySQL layer that parsed the tag and measured connection hold time per caller. That revealed reservations were not the only heavy user: other, unoptimized parts of the checkout path were holding connections longer than necessary, and reservations were simply the process that hit the already-depleted pool first.

Cleaning up the checkout path removed 50% of reads and 33% of transactions on the primary database. Shopify also raised InnoDB thread concurrency, a setting left at a conservative value for years without being re-evaluated against the current workload. Together, the cleanup and the configuration change removed the throughput ceiling: during high-volume flash sales, writer CPU stayed under 50% and reader CPU under 16%, with headroom left.

The cutover itself was gradual. Shopify ran Redis and MySQL in parallel in what it calls "shadow mode," dual-writing every reservation to both systems while Redis stayed the source of truth, to validate that MySQL produced correct outcomes under real production traffic without needing to migrate any in-flight reservations. Once satisfied, Shopify switched the source of truth to MySQL while keeping the dual write active as a kill switch back to Redis, and rolled out pod by pod, starting with low-traffic pods and working up to its highest-volume merchants.

Key facts

  • Shopify replaced its Redis-based inventory reservation system with one built on MySQL 8's SKIP LOCKED, switching from a single quantity row per item to one row per sellable unit, with a pool capped at 1,000 rows per item/location combination.
  • The system ran through Black Friday 2025, when merchants hit a record $5.1 million in sales per minute at peak, an 11% increase over the prior year; Shopify says it powers over 14% of U.S. ecommerce.
  • A composite primary key cut InnoDB row locks per reservation from two to one; switching to READ COMMITTED isolation eliminated gap locks blocking replenishment; standardizing lock order between reserve and claim removed a deadlock.
  • The actual throughput ceiling turned out to be MySQL connection exhaustion from other, unoptimized parts of checkout, not reservation query speed; tagging SQL statements with a conn_tag comment and aggregating hold time in ProxySQL exposed which processes were holding connections.
  • Cleaning up the checkout path cut primary-database reads by 50% and transactions by 33%; combined with raising InnoDB thread concurrency, writer CPU stayed under 50% and reader CPU under 16% at flash-sale peak, and the rollout used Redis/MySQL shadow-mode dual writes with a gradual, pod-by-pod cutover and a kill switch back to Redis.

Why it matters

This is a public account, from the company running the system, of replacing a widely used caching layer with a relational database for a hard concurrency problem, oversell protection at ecommerce checkout scale, and having it hold through a record Black Friday. The headline change is technical (SKIP LOCKED, one row per unit), but the piece's real point is that the team's biggest scaling problem was not the database redesign at all: it was MySQL connection exhaustion caused by unrelated, unoptimized code elsewhere in checkout, invisible until they built per-caller connection attribution. That is a broader lesson about where bottlenecks actually hide in high-throughput transactional systems.

Who it affects

Backend and database engineers building high-throughput transactional systems, particularly anyone dealing with lock contention, connection pool exhaustion, or migrating stateful logic off Redis onto a relational database. It is also relevant to engineers evaluating MySQL's SKIP LOCKED feature, InnoDB isolation levels, or connection-pool observability patterns for their own services.

How to use it

The concrete, reusable techniques described: model bounded, replenished row pools instead of one row per unit at full scale; use SKIP LOCKED to let contended reservation queries skip locked rows instead of waiting; design a composite primary key around the columns used in the WHERE clause to cut lock count; use READ COMMITTED instead of REPEATABLE READ where gap locks block concurrent inserts; standardize lock acquisition order across code paths that touch the same tables to avoid deadlocks; batch multi-row reservations with UNION ALL to cut round trips; and tag SQL statements with a per-process comment, then aggregate connection hold time at the proxy layer, to attribute connection exhaustion to the actual culprit rather than the process that happens to hit the limit first.

How solid is it

This is a first-party engineering account published by Shopify itself, describing a system the company says it ran in production through Black Friday 2025's real peak traffic, with specific figures for sales per minute, row-lock counts, database read/transaction reductions, and post-fix CPU utilization. There is no independent or third-party verification of these numbers; they are Shopify's own measurements. The captured text also cuts off mid-sentence in the closing "What we learned" section, right as it introduces the post's two main takeaways, so that concluding summary is not available here.

Risks and caveats

The source does not give exact dates for Black Friday 2025 or for when MySQL became the source of truth, only that the rollout was "gradual, pod by pod." It does not specify the length of a reservation hold beyond "a short hold, e.g. several minutes," and it does not name which other checkout-path processes were found to be holding connections too long, only that some existed. No author byline is given for the post. The design choices, such as the 1,000-row pool cap and the InnoDB thread-concurrency setting, were tuned to Shopify's own observed traffic patterns, so the specific numbers may not transfer directly to systems at a different scale.

“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.”

— Shopify engineering blog