Inventory reservation in MySQL: Shopify's row-lock design

7.9 relevance

Deep dive into Shopify's MySQL inventory reservation design, highly technical and applicable.

General dev.to

Answer the board on AI readiness

Arrive with evidence, not opinion. See how your foundation benchmarks against your peers, get a clear read, and leave with a ranked place to start.

Why It Matters

Demonstrates a concrete, battle-tested pattern for moving stateful coordination from a cache (Redis) to the primary database (MySQL) using `SKIP LOCKED`, directly addressing dual-write consistency and hot-spot contention in high-throughput checkout systems.

Key Takeaways

Evaluate your allocation systems for hot-lock contention and dual-write risks, then consider replacing counter-based locks with row-per-unit pools using `SKIP LOCKED` in your primary database.

Inventory reservation in MySQL: Shopify's row-lock design

Summary

Shopify replaced a Redis-based inventory reservation with a MySQL design using `SELECT ... FOR UPDATE SKIP LOCKED` on a bounded pool of 1,000 rows per item/location. Each row represents a single available unit, letting concurrent checkouts acquire distinct locks instead of queuing on a shared counter. This architecture eliminates dual-write coordination failures between Redis and MySQL, enforces location-aware fulfillment, and cleanly separates the temporary reserve (payment hold) from the permanent claim (ledger deduction).

Author

Nikoloz Turazashvili (@axrisi)