MySQL SKIP LOCKED at Scale

High-throughput checkout paths are unforgiving. A reservation service has to be fast, correct, and observable enough for on-call engineers to see the real blocker.

That is why Shopify's engineering writeup, We replaced Redis with MySQL for inventory reservations and it scaled, is useful beyond ecommerce. It is a case study in retiring extra infrastructure only after the new path proves it is safer.

What Changed

Shopify previously used Redis to hold short-lived inventory reservations during payment processing. Redis handled concurrent counters well, but reservation state and the inventory ledger lived in different systems, outside one ACID transaction.

The replacement moved reservations into MySQL 8. Instead of one row with a quantity counter, Shopify modeled reservable units as rows and used SELECT ... FOR UPDATE SKIP LOCKED to let concurrent transactions claim available rows without waiting on locked rows.

The design is bounded. A naive row-per-unit model can explode for large inventory counts, so Shopify keeps a capped pool per item and location, then replenishes hot pools when they run low.

SRE Lessons

• SKIP LOCKED turns one hot row into many claimable rows.
• Composite primary keys can reduce lock work.
• READ COMMITTED can avoid gap locks that block replenishment.
• Consistent table access order reduces deadlock risk.
• Batching line items with UNION ALL cuts database trips.

The Bottleneck Was Connections

The most useful part of the story is how the team found the actual ceiling. Query latency looked acceptable. CPU was not maxed. The reservation path had already been optimized. The failure signal was elsewhere: MySQL threads were queuing and ProxySQL backends were out of connections.

Shopify added business-process tags to SQL comments and tracked connection hold time at the proxy layer. That showed which checkout processes held database connections longest. The result was more useful than slow-query lists alone: which workflows consumed scarce connection time.

For SRE teams, that pattern is reusable. If a shared database is close to exhaustion, tag callers at the application layer and aggregate at the proxy or driver layer.

Rollout Pattern

The migration was not a blind cutover. Shopify ran Redis and MySQL in parallel, compared behavior on production traffic, and preserved a kill switch.

That is the right bar for stateful reliability work: observable before trust, reversible while risk is highest, and boring before peak traffic arrives.

Conclusion

This is not a claim that every Redis workload belongs in MySQL. It is a reminder to re-check old architecture decisions when database features, hardware, and traffic patterns change. Sometimes the best scaling move is removing a state split and instrumenting the path you already own.

Akmatori helps SRE teams keep critical services reachable with global TCP and UDP load balancing, health checks, and failover automation. Pair it with disciplined database observability and reversible rollout plans so stateful changes can ship without turning peak traffic into an incident.