Scaling Inventory Reservations: Why We Swapped Redis for MySQL

Published May 12, 2026 | Infrastructure Engineering

When a customer hits "Complete purchase" during checkout, the system must guarantee that the items in their cart are actually available. This is a high-stakes operation where there is no room for error.

The Risk Matrix:

• Under-counting: Two people buy the same final unit $\rightarrow$ Order cancellation $\rightarrow$ Apology emails $\rightarrow$ Increased support costs.
• Over-counting: A buyer is told an item is sold out when it isn't $\rightarrow$ Lost revenue for the merchant.

The Challenge of Massive Scale

Shopify powers a significant portion of U.S. ecommerce (over 14%). During the 2025 Black Friday peak, we witnessed a record-breaking throughput:

To prevent overselling, we use an oversell protection system that places a temporary "reservation" on inventory while payment is being processed. This prevents concurrent checkouts from claiming the same physical unit.

Core Operations

1. Reserve: A short-term hold placed when payment begins.
2. Claim: A permanent deduction from the inventory ledger (the source of truth) once payment succeeds.

The Old Goal: Use Redis for speed. The New Goal: Use MySQL for consistency and scale.

---

The Limitations of the Redis Model

Previously, reservations were handled in Redis using DECR to reserve and INCR to release. While Redis is incredibly fast, it introduced several critical issues:

• Distributed State: The reservation (Redis) and the ledger (MySQL) lived in separate systems.
• Lack of Atomicity: The Claim step required updating MySQL and cleaning up Redis. Because these couldn't be wrapped in a single transaction, we faced "ghost" inventory issues.
• Operational Overhead: Maintaining a separate cluster for reservations.
• Blindness to Location: The Redis model didn't natively handle multi-location inventory logic.

---

The Solution: Leveraging SKIP LOCKED

To unify our strategy, we moved reservations into MySQL. However, a standard "quantity column" in a single row creates massive lock contention. Instead, we adopted a one-row-per-unit design.

How it Works

Instead of updating a number, we select specific rows. By using the SKIP LOCKED feature in MySQL 8, concurrent transactions don't queue up behind a locked row; they simply skip it and find the next available one.

Simplified Reservation Logic:

BEGIN;
-- Find and lock available units, skipping those already locked by other users
SELECT id FROM inventory_reservations 
WHERE item_id = 'xyz' AND status = 'available' 
LIMIT 3 
FOR UPDATE SKIP LOCKED;

-- Mark these specific units as reserved
UPDATE inventory_reservations 
SET status = 'reserved' 
WHERE id IN (...);
COMMIT;

Managing the "Row Explosion" (The Bounded Pool)

If an item has 500,000 units, scanning that many rows would kill performance. To solve this, we implemented a bounded pool:

• The Cap: We limit the pool to 1,000 rows per item/location combination.
• The Flow: Reservations pull from this pool. A background process refills the pool from the main ledger.
• The Logic: 1,000 is the "sweet spot"—large enough to handle flash sale bursts, but small enough to keep SKIP LOCKED scans lightning-fast.

Handling Pool Exhaustion

If a flash sale drains the 1,000-row pool:

• Trigger inline replenishment.
• Use a lock to ensure only one transaction refills the pool.
• Force other concurrent requests to wait (preventing a "thundering herd").
• Resume reservations once the pool is replenished.

---

Fine-Tuning Performance: Composite Primary Keys

During prototyping, we noticed an inefficiency. We were using an auto-increment ID as the primary key. By analyzing the system with SHOW ENGINE INNODB STATUS, we discovered that MySQL was creating two row locks per reservation instead of one.

By switching to a composite primary key, we reduced the locking overhead, allowing us to hit our high-throughput targets for the 2025 peak.

Summary of Improvements

By rethinking the data model from "quantity as a value" to "quantity as a set of rows," we successfully migrated our most critical path to MySQL without sacrificing the performance required for the world's biggest shopping days.