We replaced Redis with MySQL for inventory reservations and it scaled

我们用 MySQL 取代 Redis 处理库存预留，并成功实现了扩容

How we used SKIP LOCKED, composite primary keys, and connection visibility to hit our scale targets. 我们通过使用 SKIP LOCKED、复合主键和连接可见性，成功达到了预期的扩展目标。

Published on May 12, 2026 发布于 2026 年 5 月 12 日

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. 在 Shopify 的规模下，任何一种失败都会迅速恶化。在 2025 年的“黑色星期五”，我们平台上的商家在高峰期创下了每分钟 510 万美元的销售额记录。每一笔交易都涉及库存。我们的超卖保护系统通过在支付处理期间预留库存来解决这个问题——这是一种短暂的锁定，防止两个并发的结账请求抢占同一件商品。

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. 多年来，该系统一直运行在 Redis 上。当我们转向统一数据库策略时，必须回答一个难题：MySQL 能否处理同样的规模？早期的尝试都失败了。包含数量列的单行记录无法处理高并发竞争。MySQL 8 的 SKIP LOCKED 特性引入了一种不同的设计：每个库存单位对应一行，而不是每个商品对应一行。

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. 受 37signals 数据库负载分配方法的启发，我们在 MySQL 上重建了预留系统，并在 2025 年的高峰流量中达到了高吞吐量目标。但最深刻的教训并非来自数据库设计，而是发现真正的瓶颈并非我们所观察和测量到的。本文将详细介绍该解决方案以及我们在过程中发现的问题。

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).
预留 (Reserve)： 当支付开始时，我们将商品标记为已预留（短暂锁定，例如几分钟）。
扣减 (Claim)： 当支付成功时，我们从库存账本（事实来源）中永久扣除数量。

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. 这里的规模并非抽象概念：Shopify 支撑了美国超过 14% 的电子商务，在 2025 年的“黑色星期五”，我们观察到高峰期每分钟销售额比上一年增长了 11%。预留操作运行在每一个涉及库存的结账流程中，因此系统必须在不丢弃请求或破坏一致性的前提下处理这种突发流量。

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
在高峰流量期间支持平台的高性能吞吐量目标
支持多地点库存（仅从可履约的地点进行预留）
在预留和库存账本之间保持 ACID 保证
优先保证准确性：杜绝超卖，不丢失预留记录

The Redis model and its limits

Redis 模型及其局限性

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. 之前的系统将预留信息存储在 Redis 中。每个商品有一个数量键，预留意味着执行 DECR（减量），释放意味着执行 INCR（增量）。Redis 处理并发表现良好，但预留系统和库存账本位于两个不同的系统中。扣减步骤（支付处理完成，永久扣除库存）需要更新 MySQL 并清理 Redis，这两个操作无法封装在一个原子步骤中。

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. 根据执行顺序的不同，这可能导致超卖（商品已售出但未从账本中扣除）或少卖（商品已扣除但仍被标记为预留）。此外，Redis 模型没有多地点感知能力，且增加了维护独立集群的运营成本。将预留功能迁移到与账本相同的 MySQL 数据库中，意味着我们可以将所有操作封装在 ACID 事务中，从而彻底消除这些故障模式。

The solution: SKIP LOCKED

解决方案：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. 核心思想：每个单位一行，设计上进行限制。我们不再使用每个商品一行并包含数量列的设计，而是为每个可售单位使用一行。拥有 10 个单位的商品对应 10 行记录。预留 3 个单位意味着在单个事务中选择并移动 3 行。

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). 通过将预留和库存账本保存在同一个数据库中，我们实现了预留和扣减之间的 ACID 保证，修复了 Redis 模式下可能出现的各类 Bug（例如支付成功但库存未扣减，或反之）。

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. SKIP LOCKED 是实现可扩展性的关键：如果另一个事务锁定了某些行，MySQL 会跳过它们并返回其他可用的行。无需在同一行上等待，减少了竞争。

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. 但是，如果所有库存都采用“每个单位一行”的设计，在大规模下会崩溃——一个在 10 个地点拥有 50,000 个单位的商品意味着 500,000 行记录，预留查询在扫描时会变慢。因此，我们维护了一个有限的可用行池，每个商品/地点组合上限为 1,000 行。预留操作从该池中消耗行；补货流程则从库存账本中进行补充。

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. 为什么是 1,000？这个上限既要足够大以吸收突发流量而不耗尽，又要足够小以保持表紧凑并确保 SKIP LOCKED 扫描快速。我们根据闪购期间观察到的每个商品/地点的峰值预留率确定了这个数值：1,000 提供了足够的余量，使补货流程在持续负载下能够跟上，同时不会导致表增长到影响查询性能的程度。

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. 如果池耗尽了怎么办？在极端的闪购期间，热门商品的池可能会暂时耗尽。当这种情况发生时，预留路径会触发内联补货。通过锁机制确保同一时间只有一个事务进行补货；同一商品的其他并发预留请求会等待补货完成，而不是争相插入行，从而避免了“惊群效应”。一旦补货完成，等待中的事务就会在充满的池中继续执行。买家永远不会看到商品显示为不可用（除非它确实售罄）。这会增加该特定预留请求的延迟，但它保证了准确性：拥有可用库存的买家永远不会被拒之门外。