Shopify用MySQL替换Redis实现库存预留并成功扩展

blog|Infrastructure

We replaced Redis with MySQL for inventory reservations—and it scaled

How we used SKIP LOCKED, composite primary keys, and connection visibility to hit our scale targets.

Published on May 12, 2026

During 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 from the inventory ledger.

Why 1,000? The cap needs to be large enough to absorb bursts without running dry, but small enough to keep the table compact and the SKIP LOCKED scan fast. We sized it based on observed peak reservation rates per item/location during flash sales: 1,000 gives us enough headroom that replenishment can keep up under sustained load without the table growing to a point where query performance degrades.

What happens if the pool empties? During an extreme flash sale, the pool for a hot item can be temporarily exhausted. When that happens, the reserve path triggers replenishment inline. A lock ensures only one transaction replenishes at a time; other concurrent reserves for the same item wait for it to finish rather than all racing to insert rows, avoiding a thundering herd. Once replenishment completes, the waiting transactions proceed with a full pool. The buyer never sees the item as unavailable (unless it truly is). This adds latency to that specific reservation, but it preserves correctness: a buyer with available inventory is never turned away.

Key technical decisions

1. Composite primary key: fewer locks per row

Our first prototype used an auto-increment ID as the primary key. When we observed lock behavior (e.g. with SHOW ENGINE INNODB STATUS), we saw two row locks per reservation instead of one.

With an auto-increment primary key, InnoDB was locking both the secondary index used in the WHERE clause and the clustered index (primary key). We switched to a composite primary key (shop_id, inventory_item_id, inventory_group_id, id) so the columns we filter on are part of the primary key. That reduced to one lock per row, which was important when running many reservations per second.

Takeaway: at this scale, index and primary key design directly affect lock count and throughput.

2. READ COMMITTED: avoiding gap (supremum) locks

When we ran SELECT ... FOR UPDATE SKIP LOCKED on an empty table that needed replenishment, we saw gap locks (including on the "supremum" pseudo-record). Those locks blocked the replenishment transaction from inserting new rows and could lead to deadlocks.

We changed the transaction isolation level from REPEATABLE READ (MySQL default) to READ COMMITTED for these transactions. Under READ COMMITTED, InnoDB doesn't take gap locks in the same way, so replenishment could proceed. Jahfer Husain's guide to InnoDB locking was very helpful for understanding this. This was our first use of a non-default isolation level in this codebase; it required small framework support for setting isolation per transaction.

3. Consistent lock ordering: avoiding deadlocks

We hit deadlocks when reserve and claim touched two tables in different orders. Reserve was doing INSERT into reserved_quantities then DELETE from reservation_units; claim was doing DELETE from reserved_quantities. Different transactions could lock the two tables in different orders and form a cycle.