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.
The fix was to standardize the order: reserve always DELETEs from the units table first, then INSERTs into reserved_quantities. Claim only touches reserved_quantities. Because both paths now acquire locks in the same order, neither can hold a lock the other is waiting for—no more circular waits.
4. Batching with UNION ALL
Each round trip to the database has a cost. For carts with multiple line items, we batch reservation queries using UNION ALL so we fetch all needed units in one round trip:
That cut total round trips and helped latency under load.
The real bottleneck: connections, not CPU
In production we hit a throughput ceiling well below our target. Reservation latency (e.g. P90) was acceptable, CPU wasn't maxed out, and the queries were already optimized. So we looked elsewhere.
We tried batching reservations across multiple checkouts in a single SKIP LOCKED query to use fewer connections. That helped in load tests but added complexity. We also moved some read load to replicas. Still, something didn't add up.
Following the symptoms
During load tests we saw:
- Threads queuing in MySQL
- CPU spiking when queued work ran
- Connection exhaustion to MySQL backends on ProxySQL layer
So we added visibility into which business processes were holding database connections and for how long. Knowing that connections are exhausted doesn't tell you who is holding them. We needed per-caller attribution.
On the application side, we annotated every SQL statement with a comment tag identifying the business process, like /* conn_tag:checkout_completion */. On the ProxySQL layer, we added tracking that parses the tag and measures how long each caller holds a connection. The result: total connection hold time, broken down by business process.
This immediately showed which callers were consuming the most connection time. Not which queries were slow, but which processes were holding connections across long transactions. If you're hitting connection limits and can't tell why, this pattern (tag at the app layer, aggregate at the proxy) is straightforward to implement and immediately actionable.
What we found
Once we could see connection usage, we learned that reservations weren't the only heavy user. Other parts of the checkout path were holding connections longer than necessary. They hadn't been optimized because they hadn't been the first to hit the limit. Connections are finite: at high throughput we need many short transactions per second. When other code held connections longer, reservations were the straw that broke the camel's back—not because reservations were slow, but because the pool was already near depletion.
The cleanup of the checkout path removed 50% of reads and 33% of transactions on the primary database. We also revisited MySQL configuration. InnoDB thread concurrency had been set conservatively years earlier and never re-evaluated. Our workload had changed. After increasing thread concurrency where we had headroom, we removed a bottleneck we hadn't seen until we had connection and CPU metrics side by side.
Combined, the cleanup and configuration changes removed the ceiling. We could scale beyond our previous limit and meet our targets. During high-volume flash sales, writer CPU stayed under 50% and reader CPU under 16%, with headroom to spare.
The cutover
We didn't flip a switch from Redis to MySQL. We ran both systems in parallel in what we called "shadow mode": every reservation was written to both Redis and MySQL, with Redis remaining the source of truth. This let us compare the two systems side by side, validating that MySQL produced the correct business outcomes and met our performance requirements on real production traffic. Because both systems were live, there were no in-flight reservations to migrate. Redis reservations continued to be honored while MySQL built up its own state.
Once we were satisfied with correctness and performance, we switched the source of truth to MySQL. If anything went wrong, we could revert to Redis with a kill switch; the dual-write path was still active, so Redis had a complete view of reservations at all times. The rollout was gradual, pod by pod, starting with low-traffic pods and working up to our highest-volume merchants.
What we learned
We learned so much from this project but the two main takeaways:
1. Revisit old decisions
What wasn't possible five years ago (e.g. MySQL for this workload) can be possible today with new features like SKIP LOCKED. The same goes for configuration: thread limits and other "rule of thumb" settings are worth re-checking when workload and hardware evolve. If the numbers don't add up (e.g. low CPU but queuing), dig in.
2. Start small and observe
We got a lot of value from a minimal prototype: a small ruby script and MySQL, no full framework like Rails. Observing the database (e.g. lock behavior in a second terminal) taught us more than theory alone. Simple tooling and a tight feedback loop beat big, opaque systems when exploring.
MySQL can now handle workloads we used to assume required specialized infrastructure. If you're reaching for Redis, Kafka, or a custom coordination layer for high-throughput mutual exclusion, your existing database might already be enough.
The bottleneck wasn't where we expected. We optimized queries and locks for weeks; the real limit was connection usage in code we weren't even looking at. If the numbers don't add up—low CPU but high queuing—instrument the full path. The answer is often in the plumbing, not the engine.
Crucially, this wasn't about making reservations fast. It was about making them safe neighbors. Reservations share a database with cart updates, payment processing, and order creation. A system that saturates connections or holds locks too long jeopardizes all of them. The real bar was sustaining throughput without degrading database health for everything else.
For us, the payoff is concrete: more reliable reservations mean no oversells and more successful purchases from our merchants.
