Shopify Replaced Redis with MySQL for Inventory Reservations. The Interesting Part Is Not What They Replaced.

Shopify Replaced Redis with MySQL for Inventory Reservations. The Interesting Part Is Not What They Replaced.

Shopify published a post-mortem this week on how they migrated their inventory reservation system from Redis to MySQL, using SELECT ... FOR UPDATE SKIP LOCKED to handle concurrent checkouts at scale. The HN thread lit up immediately — 260+ points, hundreds of comments, mostly people rediscovering that MySQL can do things they assumed required specialized infrastructure.

I’ve been in rooms where this exact argument plays out. “We need Redis for this.” “We need Kafka.” “We need a distributed coordination service.” The framing is almost always wrong in the same direction: teams reach for specialized systems before they’ve exhausted what their existing database can do. Shopify’s post is a good corrective, but the real story buried in it is more interesting than the Redis-versus-MySQL debate. The real bottleneck wasn’t the storage engine at all. It was connection attribution — and they didn’t find it until they built tooling to see it.

What Shopify Was Actually Solving

The problem is classical: prevent overselling during concurrent checkouts. Two buyers, one unit of inventory, both clicking “Complete purchase” at the same millisecond. Get it wrong in one direction and you’ve sold the same item twice. Get it wrong in the other direction and you’ve told a buyer something is unavailable when it isn’t.

At Shopify’s scale — they cited $5.1 million in sales per minute at Black Friday 2025 peak — any systemic error compounds brutally. A 0.01% oversell rate across millions of transactions is still thousands of cancelled orders and support tickets per day.

The previous Redis implementation handled concurrency fine. DECR and INCR on quantity keys are atomic. Redis is fast. The problem was architectural: reservations lived in Redis, the inventory ledger lived in MySQL, and the claim step — when payment succeeds, permanently deduct inventory — required updating both systems. Those two writes couldn’t be wrapped in a single transaction. That’s not a Redis limitation, that’s a two-system consistency problem. You can build distributed transactions around it (2PC, sagas, outbox patterns), but every solution adds operational complexity and new failure modes.

Moving reservations into MySQL alongside the inventory ledger eliminates the consistency problem at the source. Now reserve and claim are in the same ACID transaction. No distributed coordination needed.

That’s the architectural insight, and it’s correct. But making MySQL handle the throughput required solving three separate problems, and the third one was the one nobody expected.

The SKIP LOCKED Design: One Row Per Unit

The naive MySQL implementation for this problem fails immediately. One row per item with a quantity column creates a single point of contention: every concurrent checkout tries to update the same row. Under high load you get a queue of waiting transactions, and the effective throughput drops to near-zero.

MySQL 8.0’s SKIP LOCKED clause, added in 2018, enables a different pattern. Instead of one row per item, you maintain one row per sellable unit. An item with 10 units has 10 rows. Reserving three units means locking and moving three rows in a single transaction. If another transaction has locked some rows, SKIP LOCKED simply skips them and returns other available rows — no waiting, no contention on the same row.

Shopify’s implementation adds a bounded pool per item/location combination, capped at 1,000 rows. The reasons for the cap are practical: scan time grows with table size, and SKIP LOCKED scans through available rows. If you let the pool grow unbounded, your scan eventually becomes the bottleneck. 1,000 rows gives enough headroom for burst absorption while keeping the scan fast.

I have some opinions about the pool design. The 1,000-row cap is a tuning parameter that will be wrong for some workload shapes. A viral product with 50,000 concurrent buyers in a flash sale will drain a 1,000-row pool faster than replenishment can keep up. Shopify acknowledges this: when the pool empties, the reserve path triggers inline replenishment with a lock to prevent thundering herd. That adds latency to that specific reservation. Whether that’s acceptable depends on your SLA. For most of Shopify’s long tail of merchants it’s fine; for a major sneaker drop it might not be.

The schema discipline required here is also non-trivial. Maintaining one row per sellable unit means your reservation table scales linearly with your pool size across all items and locations. For a merchant with 10,000 SKUs across 5 locations, capped at 1,000 rows each, you’re looking at 50 million rows in the reservation_units table before any actual reservations exist. InnoDB handles that fine, but it requires thinking carefully about table partitioning, index design, and the replenishment process that keeps the pool populated. This is not a drop-in replacement for your existing Redis usage.

The Three Technical Decisions That Made It Work

Shopify’s post documents three specific decisions worth examining in detail, because each one reveals something about how InnoDB locking actually works — and most engineers don’t learn this until they’re debugging a production crisis at 2am.

Composite Primary Keys Reduce Lock Count

Their first prototype used an auto-increment integer primary key. Under load they observed two row locks per reservation instead of one. The reason: InnoDB maintains both a clustered index (keyed on the primary key) and secondary indexes. When your WHERE clause filters on a secondary index column, InnoDB locks the entry in that secondary index and then follows the pointer to lock the corresponding clustered index row. Two locks per row.

Switching to a composite primary key (shop_id, inventory_item_id, inventory_group_id, id) that includes the filter columns means the columns in the WHERE clause are part of the clustered index itself. InnoDB can satisfy the query with one lock per row instead of two.

At hundreds of thousands of reservations per second, halving lock count matters. This is the kind of thing that looks like over-engineering until it’s the difference between hitting your throughput target or not. It also means your insert performance changes: composite primary keys with high-cardinality prefixes distribute inserts well, but if shop_id has low cardinality and is the first component, you can get page-level hotspots. Worth testing your specific distribution before committing to the schema.

READ COMMITTED Isolation Eliminates Gap Locks

The default MySQL isolation level is REPEATABLE READ. Under REPEATABLE READ, SELECT ... FOR UPDATE SKIP LOCKED on an empty or near-empty table takes gap locks — locks on the “gaps” between rows to prevent phantom reads. This is correct behavior for the isolation level, but it creates a problem: when the pool needs replenishment (inserting new rows), those gap locks block the insert. Deadlocks follow.

The fix is straightforward: use READ COMMITTED for these transactions. Under READ COMMITTED, InnoDB doesn’t take gap locks in the same way, so replenishment can insert rows without contending with the reserve queries.

What I find notable about this is that Shopify had apparently never used a non-default isolation level in this codebase before. That implies the framework (Rails/ActiveRecord) had been defaulting to REPEATABLE READ for everything, which is the standard choice but not always the right one. The fact that they needed “small framework support for setting isolation per transaction” suggests this was a first. Most Rails applications never touch isolation levels and are fine; the edge cases only appear under sustained high concurrency with specific access patterns. This is worth knowing before you encounter it in production rather than during the incident.

One thing Shopify doesn’t mention: READ COMMITTED in MySQL uses statement-level binlog format by default, which means if you’re using row-based replication (which you should be), you need to verify your replication configuration handles the isolation level change cleanly. In practice, modern MySQL setups with binlog_format=ROW are fine, but it’s a gotcha worth checking before rolling out to production.

Consistent Lock Ordering Kills Deadlocks

Deadlocks in relational databases almost always follow the same pattern: Transaction A holds lock 1 and wants lock 2; Transaction B holds lock 2 and wants lock 1. The solution is always the same: ensure all transactions acquire locks in the same order.

Shopify hit deadlocks because reserve and claim were acquiring locks across two tables in different orders. Reserve was doing INSERT into reserved_quantities then DELETE from reservation_units. Claim was doing DELETE from reserved_quantities. A reserve transaction and a claim transaction could each hold one lock the other wanted, forming a cycle.

The fix — reserve always DELETEs from units first, then INSERTs into reserved_quantities — is standard deadlock prevention. What’s interesting here is that this is the kind of bug that’s invisible until you’re at scale. In a development environment with low concurrency, you might run this code for months without seeing a deadlock. In production during Black Friday, it surfaces within minutes.

The rule “always acquire locks in the same order” is in every database textbook, but it’s hard to enforce systematically when different engineers write different parts of the codebase independently. Shopify discovered this through production observation, which is unfortunately typical. The more robust prevention is making lock ordering explicit in code comments and enforcing it in code review, but this requires discipline that’s hard to maintain across team boundaries and time. Automated deadlock detection in integration tests with high concurrency is the right long-term answer, but most teams don’t have those tests until after they’ve hit the deadlock in production.

The Real Bottleneck: Connection Attribution

Here’s the part of the article that most HN commenters glossed over, but which I think is the most operationally valuable lesson.

After implementing all of the above — SKIP LOCKED, composite primary keys, READ COMMITTED, consistent lock ordering, UNION ALL batching — Shopify hit a throughput ceiling well below their target. Reservation latency was acceptable. CPU wasn’t maxed out. Queries were optimized. But they couldn’t scale past a certain point.

The symptom was connection exhaustion: threads queuing in MySQL, CPU spiking when queued work ran, connection pools draining on the ProxySQL layer. Standard diagnosis would point at “reservations are slow” or “need more database connections.” Neither was true.

What they did next is what separates good infrastructure teams from great ones: they built per-caller attribution.

On the application side, they annotated every SQL statement with a comment tag identifying the business process: /* conn_tag:checkout_completion */. On the ProxySQL layer, they added tracking that parses the tag and measures how long each caller holds a connection — not query execution time, but total connection hold time including application-side processing between queries within a transaction.

This immediately revealed that reservations weren’t the primary consumer of connection time. Other parts of the checkout path — cart updates, payment processing steps, order creation — were holding connections longer than necessary. They hadn’t been optimized because they hadn’t been the obvious bottleneck. Reservations were the straw that broke the camel’s back, not the camel’s weight.

The cleanup of the checkout path removed 50% of reads and 33% of transactions on the primary database. Combined with re-evaluating InnoDB thread concurrency settings that hadn’t been touched in years, this removed the ceiling entirely. During peak flash sales, writer CPU stayed under 50% and reader CPU under 16%.

I’ve seen this pattern many times. A team optimizes System X to near-perfection, then discovers the bottleneck was System Y that shared a resource with X. The shared resource — in this case, MySQL connection pool — was the limiting factor, and the team didn’t have the instrumentation to see who was consuming it. They had query metrics. They had table-level lock metrics. They didn’t have per-business-process connection hold time. That’s the gap that cost them weeks of mis-directed optimization.

Connection Hold Time vs. Query Time: The Missing Metric

The distinction between query execution time and connection hold time deserves its own section because it’s almost universally absent from standard database monitoring setups.

Query execution time is what most monitoring tools measure. It’s the time from when the query hits the database to when results are returned. This is what shows up in slow query logs, in SHOW PROCESSLIST, in most APM tools.

Connection hold time is different. When an application starts a transaction, it holds a database connection for the duration of that transaction — which may span multiple queries, application-side computation, external API calls, and other work. A transaction might execute three queries that each take 5ms, but hold the connection for 800ms while the application processes results, calls a payment API, processes the response, then executes the final commit. That 800ms of connection hold time is largely invisible to query-level metrics.

At low throughput, this doesn’t matter. Your connection pool has plenty of spare capacity. At high throughput, long-hold transactions starve fast transactions of connections, and the system appears to be “slow” when it’s actually “contended.” The slow query log shows nothing. CPU is fine. But checkouts are timing out because they can’t get a database connection.

The SQL comment annotation approach Shopify used is the right implementation. ProxySQL parses comments and can attribute connection time per tag. The same technique works with pgBouncer for Postgres, though the stat collection requires more custom work. MySQL’s Performance Schema has some built-in attribution, but it doesn’t break down by arbitrary business-process tags — that requires the comment-tagging layer.

If you’re running any high-throughput OLTP system with connection pooling, you should have this instrumentation. You will need it eventually. Building it before you need it takes an afternoon. Building it during a production incident takes significantly longer and produces much worse results.

What “MySQL Is Enough” Actually Means

The conclusion Shopify draws — “MySQL can now handle workloads we used to assume required specialized infrastructure” — is true, but it undersells the difficulty of getting there. They spent weeks on primary key design, isolation levels, lock ordering, batching, and connection attribution before MySQL could handle their workload. None of that is free.

The counter-argument to “just use MySQL” is that Redis would have scaled horizontally with far less database-internal tuning. DECR/INCR on Redis are trivially scalable across cluster nodes. The cost is the two-system consistency problem. Shopify judged the consistency benefit worth the MySQL tuning cost. That’s the right call for their architecture. It might not be the right call for yours.

The cases where Redis is still clearly the right answer:

• You genuinely don’t need cross-system ACID. Pure cache invalidation, ephemeral session storage, rate limiting with tolerable over-counting — Redis is correct for these.
• Your access pattern is purely key-value with no relational structure. No joins, no foreign key constraints, no need to query across multiple attributes.
• Your throughput requirements exceed what a single MySQL primary can handle even with good tuning, and horizontal scaling is more important than ACID consistency.
• Your operations team has deep Redis expertise and limited MySQL/InnoDB internals knowledge. The “correct” architecture for your team depends partly on what your team can operate reliably.

The cases where this analysis suggests MySQL (or Postgres) instead:

• You need atomic operations that span multiple tables or need to be durable across restarts.
• The state you’re storing in Redis needs to be consistent with state in your relational database, and you’re currently building complex synchronization logic to maintain that consistency.
• You’re already running a capable RDBMS and the Redis cluster is operational overhead without a clear throughput justification.

The Shadow Mode Cutover: How to Migrate Without Downtime

The technical architecture is interesting, but Shopify’s deployment strategy deserves equal attention because most teams get storage migrations wrong in ways that are preventable.

The naive migration playbook: pick a maintenance window, drain in-flight state, switch storage backends, validate, go live. This fails at scale because “drain in-flight state” is harder than it sounds. Inventory reservations are time-bounded holds — typically a few minutes — but at Shopify’s throughput there are always active reservations. Waiting for all of them to expire or be claimed means your maintenance window grows proportionally to your reservation timeout. And the moment you’re done draining and ready to switch, new checkouts are already creating new reservations in the old system.

Shopify instead ran Redis and MySQL in parallel — every reservation was written to both systems, with Redis remaining source of truth. This shadow mode approach has three properties that make it the correct strategy for this class of migration:

First, it eliminates the in-flight state migration problem entirely. Because both systems are always live, there are no reservations to migrate. Redis has complete state because it’s still being written to. MySQL has complete state because it’s receiving every write. When you flip the source-of-truth flag, both systems are current.

Second, it lets you validate MySQL correctness on real production traffic rather than synthetic load tests. Load tests can approximate your workload, but they miss the long tail of edge cases that only appear in production — unusual SKU configurations, multi-currency checkouts, merchant-specific inventory rules. Running shadow mode for weeks against real traffic gives you confidence that MySQL handles these cases correctly before it becomes the system of record.

Third, it gives you a fast rollback path. If MySQL shows unexpected behavior after the source-of-truth flip, you revert the flag and Redis is immediately authoritative again — with complete, up-to-date state because dual writes never stopped. No data recovery, no state reconciliation, no emergency dump-and-restore.

The cost of shadow mode is the additional write load on both systems and the complexity of maintaining dual-write consistency. Every write must succeed in both systems or you have a divergence problem. Shopify presumably handled this by treating dual-write failures as either acceptable divergence (Redis is still source of truth, MySQL shadow state can drift) or by alerting on divergence and investigating. They don’t detail this in the post, which is the most significant omission.

The pod-by-pod rollout — starting with low-traffic pods, working up to highest-volume merchants — is also correct. It limits blast radius at each stage, gives you per-cohort metrics, and lets you catch merchant-specific edge cases before they affect your largest customers. The alternative, a global flag flip, risks taking down your entire checkout if something unexpected surfaces. At Shopify’s scale, even a 1% checkout degradation during peak is a material revenue event.

Why This Analysis Matters Beyond Shopify

Shopify is an outlier in throughput. Most engineering teams will never see $5.1 million in transactions per minute or the Black Friday traffic spikes that make this level of MySQL tuning necessary. So why should you care about this architecture?

Three reasons.

First, the connection attribution insight scales down. It’s relevant at 1,000 requests per minute, not just at Shopify’s peak. Connection exhaustion is a class of failure that appears whenever your transaction hold time exceeds your connection pool capacity relative to your throughput. That can happen at surprisingly modest scale if your transactions are long. A Rails app with a few hundred concurrent users, some complex transactions, and a connection pool of 20 can hit this ceiling. Most teams don’t have the instrumentation to diagnose it correctly and end up adding more servers or increasing the connection pool limit — which treats the symptom rather than the cause.

Second, the SKIP LOCKED pattern is useful at far smaller scales than Shopify’s. Any system with mutual exclusion requirements — preventing double-booking in a scheduling system, preventing duplicate processing in a background job system, preventing concurrent redemption of a single-use promo code — benefits from this pattern. And most of those systems are already running a relational database. The question of whether to add Redis for these use cases is exactly the question Shopify answered: if you need ACID consistency between the reservation and its associated business data, you probably don’t need Redis.

Third, the “revisit old decisions” lesson is universally applicable. Shopify’s innodb_thread_concurrency setting was wrong for years before they looked at it. Every long-running system has configuration decisions that made sense at the time but have decayed against changing hardware, changed workload, and improved software defaults. The organizational discipline to periodically audit these decisions — rather than treating configuration as settled once it “works” — is valuable regardless of what database you’re running or what scale you’re at.

The specific numbers (1,000-row pool cap, READ COMMITTED isolation, composite primary key design) are Shopify-specific tuning. The underlying insights — minimize lock scope, match index structure to access patterns, measure what actually limits you before optimizing, instrument before you need the instrumentation — are general engineering principles that apply across scales and systems.

The InnoDB Thread Concurrency Insight

One detail in Shopify’s post that deserves more attention: they increased InnoDB’s thread concurrency setting after discovering it had been configured conservatively years ago and never revisited.

The innodb_thread_concurrency parameter controls how many threads can be inside InnoDB concurrently. The default is 0 (unlimited), but many MySQL deployments set it to a conservative value — often 8 or 16 — based on old guidance that predates modern hardware and modern workloads. On a 32-core server with NVMe storage, a limit of 16 concurrent InnoDB threads is actively harmful. Threads queue outside InnoDB, waiting for a slot, while your hardware has substantial idle capacity.

The reason this setting was conservative and never revisited is a common organizational failure: configuration decisions made by one team years ago, optimized for hardware that no longer exists, never questioned because the system “works.” This is true of every long-running database deployment. The right cadence for reviewing MySQL configuration against current hardware and workload is roughly annually, or whenever you upgrade hardware or your workload profile changes significantly. Most teams do it never.

The tooling to identify misconfigured settings exists. MySQL’s Performance Schema and the sys schema expose thread concurrency utilization. If you’re seeing thread queuing while CPU is under-utilized, innodb_thread_concurrency is one of the first places to look. The fact that Shopify discovered this only after building connection attribution tooling suggests they didn’t have this metric in their existing dashboard. It should be standard.

Predictions

Based on this post and the direction of database infrastructure in 2026:

• Within 12 months: At least two major open-source job queue or reservation libraries will add first-class SKIP LOCKED backends for MySQL 8+ and Postgres 9.5+, explicitly marketed as Redis-free alternatives. The 37signals Solid Queue model will be widely copied and extended.
• Within 18 months: ProxySQL or a commercial competitor will ship per-caller connection attribution as a built-in feature requiring no custom comment tag parsing. The demand signal from posts like this one is clear enough that it will show up in product roadmaps.
• Within 24 months: A cloud database provider will publish a benchmark showing SKIP LOCKED throughput competitive with Redis for inventory-class workloads and use it to market “database-native coordination” positioning. This benchmark will be accurate for their specific test conditions and misleading in how it generalizes.
• Falsifiable: If connection hold time attribution tooling is not shipped as a built-in feature by at least one major database proxy (ProxySQL, pgBouncer, RDS Proxy) within 18 months, adoption of this pattern will remain slow. Abstract descriptions of the technique don’t spread; concrete built-in features do. The Shopify post alone will not move the needle on widespread adoption.
• Falsifiable: The 1,000-row pool cap will prove insufficient for at least one public case of a high-velocity product launch within 12 months. Someone will write a post-mortem about pool exhaustion during a viral product drop, and the discussion will clarify the tuning parameters required for extreme-burst workloads that Shopify’s post leaves underspecified.

The core architecture Shopify describes is sound and will be replicated widely. The connection attribution insight is the most immediately actionable piece for any team running high-throughput OLTP. The teams that instrument first will find their real bottleneck. The teams that don’t will spend weeks optimizing the wrong layer, just as Shopify did before they built the tooling. The only question is whether you build the instrumentation before or after your next production ceiling.