Shopify replaced Redis with MySQL for inventory reservations–and it scaled

Shopify replaced its inventory reservation system with MySQL, leading to improved performance and reliability. During checkout, Shopify ensures that items remain available to buyers, as an error in this process could result in overselling or missed sales. In the previous system, reservations were stored in Redis, with inventory handled separately.

This caused issues when combining reservation and payment updates, leading to overselling or underselling. The new MySQL-based system reserves inventory during payment processing, ensuring that two concurrent checkouts cannot claim the same unit. By moving reservations into the same MySQL database as the ledger, the company was able to use ACID transactions to eliminate failure modes.

Instead of storing one row per item, the system uses one row per sellable unit, with 10 rows for an item with 10 units. Reserving three units means selecting and moving three rows in a single transaction. The SKIP LOCKED feature in MySQL allows for scalable reservations by skipping locked rows and returning available ones. A bounded pool of 1,000 available rows per item/location combination is maintained, replenished from the inventory ledger when needed.

If the pool empties, the system triggers inline replenishment to avoid turning away buyers. The composite primary key design in MySQL reduces lock count and improves throughput, crucial at Shopify's scale.