Inventory reservations on MySQL: locking, pools, and a connection bottleneck
When a buyer clicks “Complete purchase,” Shopify has to make a binary call about inventory: is that unit still available? Get it wrong one way and two buyers purchase the same last item—the merchant eats support costs and sends apology emails. Get it wrong the other way and a legitimate sale is lost to a false “sold out.” At Shopify’s scale, either outcome compounds quickly. On Black Friday 2025, merchants hit a record $5.1 million in sales per minute at peak, and every transaction touched inventory.
Oversell protection handles this with a short reservation during payment processing. Previously, that system ran on Redis with a quantity key per item—reserving meant DECR, releasing meant INCR. It handled concurrency, but the reservation store and the inventory ledger (the source of truth in MySQL) lived in different systems. The claim step—permanently deducting inventory after payment—couldn't be atomic with the Redis cleanup. Depending on operation order, that caused overselling (sold but never deducted) or underselling (deducted but still reserved). The Redis model also lacked multi-location awareness and required running a separate cluster.
Moving reservations into the same MySQL database as the ledger would wrap everything in ACID transactions and eliminate those failure modes. Earlier attempts with a single row per item and a quantity column couldn't handle the contention. MySQL 8's SKIP LOCKED enabled a different design: one row per sellable unit. An item with 10 units has 10 rows; reserving three means selecting and moving three rows in one transaction.
Bounded pools and replenishment
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 slow scans. Instead, the system maintains a bounded pool of available rows per item/location, capped at 1,000. Reservations consume from this pool; a replenishment process refills from the inventory ledger.
The 1,000 cap is a balance: large enough to absorb flash-sale bursts without running dry, small enough to keep the table compact and the SKIP LOCKED scan fast. If the pool does empty during an extreme flash sale, the reserve path triggers inline replenishment. A lock ensures only one transaction replenishes at a time; concurrent reserves for the same item wait rather than racing to insert rows. That waiting adds latency but preserves correctness—a buyer with available inventory is never turned away.
Four key technical decisions
1. Composite primary keys reduce locks per row
The first prototype used an auto-increment ID as the primary key. Observing lock behavior with SHOW ENGINE INNODB STATUS revealed two row locks per reservation instead of one. With an auto-increment key, InnoDB locked both the secondary index used in the WHERE clause and the clustered primary key index. Switching to a composite primary key (shop_id, inventory_item_id, inventory_group_id, id) put the filtered columns into the primary key itself, reducing to one lock per row. At thousands of reservations per second, that matters.
2. READ COMMITTED avoids gap locks
Running SELECT ... FOR UPDATE SKIP LOCKED on an empty table needing replenishment caused gap locks, including on the “supremum” pseudo-record. Those blocked the replenishment transaction from inserting new rows and led to deadlocks. Changing the isolation level from REPEATABLE READ to READ COMMITTED for these transactions eliminated the issue—InnoDB doesn't take gap locks the same way under READ COMMITTED. It was the codebase's first use of a non-default isolation level and required small framework support for per-transaction settings.
3. Consistent lock ordering prevents deadlocks
Deadlocks occurred when reserve and claim touched two tables in different orders. Reserve inserted into reserved_quantities then deleted from reservation_units; claim deleted from reserved_quantities. Different transactions could lock the two tables in opposite orders, forming a cycle. The fix standardized the sequence: reserve always deletes from the units table first, then inserts into reserved_quantities. Claim only touches reserved_quantities. With both paths acquiring locks in the same order, circular waits became impossible.
4. Batched queries with UNION ALL
Each database round trip has a cost. For carts with multiple line items, reservation queries are batched using UNION ALL to fetch all needed units in a single trip, cutting total round trips and helping latency under load.
The real bottleneck: connections, not CPU
Production hit a throughput ceiling well below target despite acceptable P90 latency, unused CPU headroom, and optimized queries. Load tests showed threads queuing in MySQL, CPU spiking when queued work finally ran, and connection exhaustion to MySQL backends on the ProxySQL layer.
The problem: knowing connections are exhausted doesn't reveal who's holding them. The team added per-caller attribution. On the application side, every SQL statement was annotated with a comment tag identifying the business process, like /* conn_tag:checkout_completion */. On the ProxySQL layer, tracking parsed the tag and measured how long each caller held a connection. The result was total connection hold time broken down by business process—not which queries were slow, but which processes were holding connections across long transactions.
The findings were surprising. Reservations weren't the only heavy connection user. Other parts of the checkout path held connections longer than necessary; they hadn't been optimized because they hadn't hit the limit first. With a finite connection pool and high throughput demand for many short transactions per second, reservations were the last straw—not because they were slow, but because the pool was already near depletion.
Cleanup of the checkout path removed 50% of reads and 33% of transactions on the primary database. Revisiting MySQL configuration also helped: InnoDB thread concurrency had been set conservatively years earlier and never re-evaluated. After increasing it where headroom existed, the ceiling lifted. During high-volume flash sales, writer CPU stayed under 50% and reader CPU under 16%.
Shadow mode cutover
The migration wasn't a switch flip. Both systems ran in parallel in “shadow mode”: every reservation was written to both Redis and MySQL, with Redis remaining the source of truth. This allowed side-by-side validation that MySQL produced correct business outcomes on real production traffic. Because both were live, there were no in-flight reservations to migrate, and Redis reservations continued to be honored while MySQL built its own state.
Once correctness and performance were confirmed, the source of truth switched to MySQL. The dual-write path remained active, so reverting to Redis via kill switch was always possible. Rollout was gradual, pod by pod, starting with low-traffic pods and moving up to the highest-volume merchants.
Takeaways from the migration
Two lessons stood out from this project. The first is to revisit old decisions. What wasn't viable five years ago—like using MySQL for this kind of workload—can become practical thanks to newer features such as SKIP LOCKED. The same logic applies to configuration: thread limits and other rule-of-thumb settings deserve re-evaluation as hardware and workloads shift. When the numbers look inconsistent, such as low CPU usage alongside heavy queuing, that is a signal to investigate rather than accept the status quo.
The second lesson is to start small and observe. A minimal prototype—just a small Ruby script against MySQL, without a full framework like Rails—yielded substantial insight. Watching the database in action, including lock behavior from a second terminal, taught more than theoretical analysis. Simple tooling with a tight feedback loop outperforms large, opaque systems when exploring new ground.
MySQL is now capable of handling workloads that previously seemed to demand specialized infrastructure. Before reaching for Redis, Kafka, or a custom coordination layer for high-throughput mutual exclusion, consider whether the database you already run is sufficient.
The real bottleneck
The performance ceiling was not where the team initially looked. Weeks were spent optimizing queries and locks, but the actual constraint turned out to be connection usage in code that wasn't even under review. When metrics contradict expectations—low CPU but high queuing—trace the full request path. The culprit is often in the plumbing rather than the engine.
Speed was never the sole objective. Reservations share a database with cart updates, payment processing, and order creation, so the system needed to behave as a safe neighbor. Saturating connections or holding locks too long would degrade database health for every other operation. The true success criterion was sustaining throughput without compromising the overall health of the database.
The outcome is tangible: more reliable reservations mean fewer oversells and more completed purchases for merchants.
