What Shopify's MySQL Inventory Reservation Migration Teaches

Context

Inventory reservation looks like a counter problem until payment, cancellation, expiration, and multiple fulfilment locations enter the picture. A checkout must temporarily hold stock, then either claim it permanently or release it.[1] If those state changes disagree, the system can oversell an item or reject a valid purchase.

Shopify recently described replacing a Redis-based reservation system with MySQL. The headline is surprising, but the useful lesson is not that MySQL is always better than Redis. It is that data ownership, lock behaviour, and observability matter more than choosing the component with the fastest reputation.[1]

The migration also operated at meaningful scale. Shopify reported record peak sales during Black Friday 2025, with inventory involved in every applicable checkout.[1] That makes this a useful production case rather than a synthetic database benchmark.

What the source says

The old design kept temporary reservations in Redis while the durable inventory ledger lived in MySQL. Reserving and releasing a quantity was simple, but claiming a paid order required coordinated changes across two independent systems. There was no single ACID transaction covering both operations, so a partial failure could leave the reservation and ledger inconsistent.[1]

Shopify moved both responsibilities into MySQL and changed the data model. Instead of one highly contended quantity row per item, it represented reservable units as individual rows. A transaction could select available unit rows with FOR UPDATE SKIP LOCKED, then move the selected units into the reserved state.[1]

SKIP LOCKED makes a locking read return immediately while excluding rows already locked by another transaction. MySQL documents it as appropriate for queue-like access patterns where an inconsistent view is acceptable, not as a general-purpose replacement for ordinary transactional reads.[2] Inventory units fit that constraint: a checkout does not need a specific unit row, only enough currently available units.

A literal row for every unit would make the table unbounded, so Shopify maintained a pool capped at 1,000 available rows for each item and location. A replenishment process refilled the pool from the ledger. If a hot item exhausted its pool, one transaction replenished it while competing reservations waited, preventing a thundering herd of inserts.[1]

What I would validate before adopting it

The design is more than one SQL clause. Four details determine whether it works under load.

1. Index shape is part of the concurrency model

Shopify's first prototype used an auto-increment primary key. InnoDB then locked the matching secondary-index entry and its clustered primary-key record. Moving the lookup columns into a composite primary key reduced the observed lock count from two locks per reservation row to one.[1]

The practical rule is to inspect the locks produced by the actual access path. A logically correct index can still create unnecessary lock work when its lookup must traverse a secondary index and then the clustered record.

2. Isolation level changes insertion behaviour

An empty pool created a subtle problem under MySQL's default REPEATABLE READ: the locking query could take gap locks that blocked the replenishment insert. Shopify used READ COMMITTED for these transactions.[1]

MySQL documents that locking reads under READ COMMITTED lock index records rather than the gaps before them, except for foreign-key and duplicate-key checks.[3] That improves insertion concurrency, but it changes transaction semantics. I would scope the isolation change to the reservation transaction and test empty-pool, refill, rollback, and retry cases explicitly.

3. Every path must acquire locks in the same order

Shopify encountered deadlocks when reserve and claim operations touched the unit and reserved-quantity tables in different orders. The fix was to standardise the sequence so concurrent paths could not form a circular wait.[1]

This is a design invariant, not a one-time patch. Any later cancellation, expiration, or reconciliation job must follow the same order.

4. Database connections can be the scarce resource

The team initially focused on query latency and CPU, yet production throughput plateaued while those metrics still looked healthy. They tagged SQL statements with the calling business process and measured connection hold time at ProxySQL. The data showed that other checkout work was retaining connections long enough to exhaust the shared pool.[1]

After cleaning up the wider checkout path, Shopify reported 50% fewer reads and 33% fewer transactions on the primary database. During high-volume flash sales, writer CPU remained below 50% and reader CPU below 16%.[1] The lesson is to measure connection occupancy by caller, not only query duration by statement.

How I would apply this in production

I would treat this pattern as a bounded allocation queue backed by the same database as the ledger:

1. Keep the durable inventory ledger as the source of truth.
2. Materialise only a bounded set of reservable unit rows per item and location.
3. Select the required number with FOR UPDATE SKIP LOCKED inside a short transaction.
4. Move those rows into a reserved table or state using a consistent lock order.
5. Replenish from the ledger, with one replenisher per pool.
6. Record reservation expiry and make release idempotent.
7. Measure lock waits, deadlocks, pool depletion, transaction duration, connection hold time by caller, and ledger-to-reservation discrepancies.

I would also copy Shopify's rollout shape rather than only its schema. They dual-wrote Redis and MySQL in shadow mode, kept Redis as the source of truth while comparing results, switched gradually, and retained a kill switch while both systems still had complete reservation state.[1] That approach validates correctness on real traffic without requiring an all-at-once migration of in-flight reservations.

Sources

[1] https://shopify.engineering/scaling-inventory-reservations (opens in a new tab) — We replaced Redis with MySQL for inventory reservations—and it scaled [2] https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html (opens in a new tab) — MySQL 8.0 Reference Manual: Locking Reads [3] https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html (opens in a new tab) — MySQL 8.0 Reference Manual: Transaction Isolation Levels