Inventory Reservation Checkout

Giriş

Açıklaması şöyle

Shopify replaced Redis with MySQL for one of their most critical checkout workloads. The trick was surprisingly simple.


Every time a buyer clicks "Complete purchase," Shopify has milliseconds to answer one question: is that last unit still available?


If Shopify says yes when the item is already gone, two people could buy the same item, leading to a cancelled order, an apology email, and a loss of trust.


If they say no when the item is actually available, they lose a genuine sale.


For years, Shopify used Redis to handle inventory reservations during checkout. When a customer started buying an item, Shopify would reserve that item in Redis so another customer couldn't buy the same unit at the same time.


But the actual inventory lived in MySQL, so reservations and inventory updates happened in two different systems with no shared transaction. A payment could succeed while the inventory update failed, causing an oversell, or the inventory could be deducted while the Redis reservation remained, causing an undersell.


The obvious fix was to move reservations into MySQL too, so everything could happen in one atomic transaction.


But MySQL had already failed at this once. Their first attempt used a single row with a quantity column, and at checkout scale, every buyer fought over that one row. Locking it, waiting, retrying. It buckled.


This is where the magic comes in.


Instead of using one row per item with its available quantity, they used one row for each individual unit. So an item with 10 units gets 10 rows. To reserve three units, they simply grab any three available rows.

And then the key: MySQL 8's 𝗦𝗞𝗜𝗣 𝗟𝗢𝗖𝗞𝗘𝗗.


Normally, if a query wants rows another transaction has locked, it waits, everyone queues behind the same rows, and throughput collapses. SKIP LOCKED flips that: if some rows are locked, MySQL simply skips them and hands back other free ones. No waiting. No queue on a hot row.


So when 500 buyers check out the same product at once, they're not fighting over one row, they each quietly grab a different free unit and move on. Contention, which killed the naive design, basically disappears.


To keep it fast at huge scale, they cap the pool at ~1,000 rows per item and refill it from the ledger as it drains. Same idea, bounded.


That's the whole trick: turn "everyone fights over one number" into "everyone grabs a different row." A single database feature made MySQL viable for a workload people assumed needed specialized infrastructure.


The boring database you already run is often more capable than you think. MySQL didn’t just handle Shopify’s scale, it also made their system much simpler.