During the peak of Black Friday Cyber Monday (BFCM), Shopify's platform handles astronomical volumes of traffic. In 2025, merchants on the platform hit a record peak of $5.1 million in sales per minute. Every single one of those transactions interacts with inventory.

To prevent two buyers from purchasing the same last unit of an item, Shopify relies on an oversell protection system. This system places a temporary reserve on items during checkout. For years, this critical infrastructure ran on Redis. However, in an effort to unify their database architecture and eliminate cross-system consistency issues, Shopify successfully migrated this high-throughput system to MySQL.

Here is how they did it, the technical hurdles they overcame, and why the ultimate bottleneck wasn't the database engine itself, but connection management.

The Challenge of Oversell Protection

Oversell protection consists of two primary steps:

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

If the reserve step is slow, it triggers checkout throttling and degrades the buyer experience. If it fails or is inconsistent, it leads to either overselling (apologizing to customers and canceling orders) or underselling (falsely claiming an item is out of stock).

The Limitations of Redis

In Shopify's legacy architecture, reservations lived in Redis while the inventory ledger lived in MySQL.

• The Redis Flow: Reserving an item used a simple decrement (DECR), and releasing it used an increment (INCR).
• The Flaw: Because Redis and MySQL are separate systems, the "Claim" step could not be wrapped in a single atomic ACID transaction. If a network partition occurred after payment succeeded but before the Redis reservation was cleaned up, inventory counts would fall out of sync, triggering either over- or under-selling.

To solve this, Shopify engineers set out to move reservations into the same MySQL database as the ledger, allowing them to wrap both actions in atomic ACID transactions.

The Solution: SKIP LOCKED and Bounded Row Pools

Historically, managing inventory reservations in a relational database suffered from extreme lock contention. If multiple users tried to buy the same item, they would all queue to update the exact same row.

Shopify bypassed this limitation by using MySQL 8's SKIP LOCKED feature and flipping their data model: one row per unit instead of one row per item.

If an item has 10 units in stock, the database contains 10 individual rows. Reserving 3 units means locking and claiming 3 rows. By using SELECT ... FOR UPDATE SKIP LOCKED, MySQL automatically skips already-locked rows and grabs the next available ones without waiting. This entirely eliminates transaction queuing on highly contested items.

Keeping Table Sizes Bounded

If a hot item has 50,000 units in stock, scanning 50,000 rows would slow down the SKIP LOCKED query. To prevent this, Shopify maintains a bounded pool of available reservation rows, capped at 1,000 per item/location combination.

• Normal Path: Reservations consume rows from this compact pool.
• Replenishment Path: A background process refills the pool from the main ledger when it runs low.
• Flash Sale Safety Valve: If a massive surge empties the pool instantly, the reservation path triggers an inline replenishment. A lock ensures only one transaction performs the replenishment, avoiding a "thundering herd" of concurrent inserts.

Four Critical Engineering Decisions

Migrating to MySQL at Shopify's scale required precise optimization of InnoDB's locking behavior and transaction mechanics.

1. Composite Primary Keys to Reduce Locks

The initial prototype used an auto-incrementing ID as the primary key. However, this forced InnoDB to acquire two locks per reservation: one on the secondary index used in the WHERE clause, and another on the clustered index (the primary key).

By switching to a composite primary key consisting of (shop_id, inventory_item_id, inventory_group_id, id), the filtered columns became part of the primary key. This cut lock overhead in half to exactly one lock per row.

2. READ COMMITTED Isolation to Stop Gap Locks

Under MySQL's default REPEATABLE READ isolation level, querying an empty reservation table during replenishment triggered gap locks (including on the "supremum" pseudo-record). These locks blocked concurrent inserts, leading to deadlocks.

Switching the transaction isolation level to READ COMMITTED eliminated gap locking in this path, allowing replenishment queries to execute smoothly without blocking parallel transactions.

3. Consistent Lock Ordering

Deadlocks also arose when the "Reserve" and "Claim" paths touched tables in different sequences.

• Reserve inserted into reserved_quantities and then deleted from reservation_units.
• Claim only deleted from reserved_quantities.

By standardizing the operations so both paths acquire locks in the exact same order, circular waits (and subsequent deadlocks) were eliminated entirely.

4. Batching with UNION ALL

To optimize checkout performance for multi-item carts, Shopify batched reservation queries using UNION ALL. This allowed them to fetch and lock all required reservation units across different items in a single database round trip, substantially lowering overall transaction latency.

Finding the Real Bottleneck: Connection Visibility

During initial load testing, performance hit an unexpected ceiling well below Shopify's target. Oddly, database CPU utilization was low, and queries were highly optimized, yet connection exhaustion was occurring at the proxy layer.

To diagnose this, Shopify implemented application-level SQL tagging and combined it with proxy-level tracking:

SELECT ... FROM reservation_units 
WHERE ... 
LIMIT 3 FOR UPDATE SKIP LOCKED /* conn_tag:checkout_completion */;

By parsing these comments on the ProxySQL layer, they tracked exactly how long different business processes held database connections open.

The Revelation

What they discovered was eye-opening: reservations weren't the bottleneck.

Instead, entirely separate processes in the checkout path were holding database connections open far longer than necessary. Because connections are a finite resource, these sluggish sibling transactions starved the reservation system of database connections.

By optimizing those adjacent checkout paths, Shopify removed 50% of reads and 33% of primary database transactions, freeing up the necessary connection pool capacity to scale their MySQL reservation engine past their peak performance targets.

Key Takeaways for System Architects

Shopify's migration proves that modern relational databases are capable of handling high-throughput coordination tasks historically delegated to Redis or ZooKeeper.

1. Revisit Assumptions: Databases evolve. Features like MySQL's SKIP LOCKED open the door to design patterns that were impossible five years ago.
2. Instrument the Full Path: When a system fails to scale despite low CPU usage, look at the plumbing. The bottleneck is often connection hold-times in surrounding, unoptimized code.
3. Isolate with Prototypes: Shopify developed their entire SKIP LOCKED prototype using raw Ruby and MySQL scripts first. Minimizing framework overhead allowed them to observe pure InnoDB behavior in real time, accelerating their feedback loop.

By moving reservations to MySQL, Shopify achieved strict ACID consistency, simplified their infrastructure stack, and ensured that their merchant platform remains resilient under the most demanding retail events on earth.