How Shopify moved inventory reservations from Redis to MySQL

// the short version

When a buyer clicks "Complete purchase", Shopify has to guarantee the item is

// what to take away

• Keeping inventory holds and sales in different databases left correctness gaps between them.
• A single quantity row serialized every checkout competing for a popular item.
• SKIP LOCKED let workers make progress on available reservations instead of waiting behind one lock.

// transcript

Shopify sells over 5 million dollars a minute at the peak of Black Friday, and every one of those checkouts has to be sure the item is still there. Get it wrong and two people buy the last unit, or a buyer is turned away from something in stock. For years that check was a number in Redis. Each item had a count, and starting a payment took one off. But the real inventory lives in MySQL, so the hold sat in one system and the sale in another, and if an order failed between the two, the item was either sold twice or stuck on hold. Putting that count in MySQL instead had been tried. One row with a quantity on it means every checkout for that item waits on the same row, and in a flash sale that's thousands of them behind one lock. So Shopify stopped storing the count and started storing the units. 10 in stock is 10 rows, and reserving 3 means picking 3 rows and moving them. A newer MySQL feature called SKIP LOCKED is what makes that fast. When a query reaches a row another checkout is holding, it steps over it and takes the next free one. Contention stops being a queue and becomes a scatter. Fifty thousand units across 10 locations would be half a million rows though, so each item keeps a pool of a thousand rows, topped up from the real ledger. It worked in testing, and then in production it hit a ceiling well below the target, with the CPU nowhere near busy. The problem was connections. Every query got a tag saying which part of checkout sent it, and the proxy in front of the database added up how long each one held a connection open. Other parts of checkout were holding connections through long transactions, and nobody had looked, because they'd never hit the limit first. Cleaning those up removed half the reads and a third of the transactions from the primary database, and the ceiling went with them. Through flash sales the database now sits under half its CPU, and the hold and the sale finally share one transaction. As they put it, the answer was in the plumbing, not the engine.