Watch the video, transcript and sources
Shopify moved inventory reservations into MySQL using one row per unit in a bounded pool. Why choose MySQL? How do you skip locks safely? Shopify is a commerce platform for selling online and in person. Redis is an in-memory data store. MySQL is a relational database, and Shopify already kept its inventory ledger there. A reservation briefly holds stock while a buyer pays.
Their Redis model decremented an item counter. Claiming a paid order meant updating the MySQL ledger and cleaning up Redis. Those separate writes could leave stock sold twice or unavailable when it should be sellable. The old model also lacked location awareness. The replacement must choose stock from somewhere that can fulfill the order. A warehouse on the wrong continent makes an excellent database entry and a terrible delivery promise. Earlier MySQL attempts used a quantity row, so competing checkouts queued at the same lock. Think of a velvet rope around a spreadsheet cell. Adding more workers just lengthens the queue.
Shopify changed the lockable object. Each available unit gets its own row. A locking read skips units another transaction holds and selects other eligible units. Different workers can acquire different rows, while the hot counter stays out of their way. That pool is bounded at a thousand rows per item and location. Replenishment draws from the ledger. If it empties, the reserve path replenishes inline, with competing requests waiting behind a replenishment lock. Empty pool does not mean empty warehouse. Reserve deletes selected pool rows, then inserts reservation records in a transaction. Commit releases the database locks. Rollback undoes the changes. The reservation survives payment processing as stored state. Successful payment claims the ledger and removes the reservation atomically. Database locks never need to babysit the payment form.
Their composite primary key is (shop_id, inventory_item_id, inventory_group_id, id). Matching the lookup reduced index locking in their prototype. They also use READ COMMITTED to avoid the gap locks that blocked pool replenishment, and a consistent table order to prevent circular waits. The published example records an expiry time. Abandoned payments need stock released eventually, or the shopping cart becomes a landlord. Shopify's post leaves that cleanup algorithm unspecified. The expiry field marks a lifecycle requirement. Here's the catch. SKIP LOCKED excludes locked rows, so the manual calls its result an inconsistent view. It provides neither a complete stock count nor fair turn-taking. Keep the availability decision and replenishment rules around it.
And that ceiling? Other checkout code was holding database connections too long. Shopify tagged callers and measured connection hold time, then cleaned up the checkout path and revisited thread concurrency. Fast queries can still queue outside the query. They shadow-wrote both systems with Redis authoritative, compared outcomes, then switched gradually with a kill switch. My verdict is ship it. I'd ship the shared transaction boundary and that rollbackable rollout, with the whole checkout path instrumented. Got a question about this? Put it in the comments.
Verdict: SHIP IT — Shared state. Observable rollout.
Sources
https://shopify.engineering/scaling-inventory-reservations
https://gist.github.com/CourtneySymons/cb5ecbe86331047aae166d5b1f1d555c
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
https://www.shopify.com/blog/what-is-shopify
https://redis.io/docs/latest/develop/get-started/
https://dev.mysql.com/doc/refman/8.0/en/what-is-mysql.html
Viewer-supplied inspiration: https://www.youtube.com/watch?v=Hbwlg0RO-ig. The factual explanation above comes from Shopify and the official database documentation.
And that's the diff for today. I'm Niko from Axrisi. Merge responsibly.
YouTube · Newsletter · thedailydiff.dev · forward this to the intern who deployed on Friday.