How Shopify stopped selling the last hoodie twice

Picture a store with one hoodie left. Two people press Pay in the same second. Only one of them can get it, and the store has to decide who, every time, even when servers crash halfway through.

Shopify has this problem at a scale few systems see: on Black Friday 2025, merchants on the platform peaked at $5.1 million in sales per minute. For years the part that stops the last item being sold twice ran on Redis. Then they moved it into plain MySQL, and it got more correct without getting slower.

This post walks through why they moved, the problem the move created, and the trick that solved it: storing one row per hoodie instead of one row that says how many hoodies there are.

The job: hold it while they pay

Paying takes time. The card has to be checked, a bank might ask for a code, and that can take a minute. If the hoodie stayed on the shelf during all that, a second buyer could grab it, and one of them would get an apology email instead of a hoodie.

So when you start paying, Shopify puts the item on hold for you. Think of a shop assistant keeping it behind the counter with your name on it for a few minutes. Shopify's system for this is called oversell protection, and it has two main moves:

• Reserve: when payment starts, the items in your cart are marked as held for several minutes.

• Claim: when payment succeeds, the held quantity is taken off the inventory for good.

If the payment fails, the hold is released and the hoodie goes back on the shelf. If nothing happens at all, the hold runs out on its own.

The old setup: two notebooks

For years, Shopify kept this information in two places. Think of them as two notebooks.

• Notebook one was Redis: an in-memory store, extremely fast, used as a scratchpad for the holds. Reservations were counters that went up and down with INCR and DECR, which Redis does in a single step.

• Notebook two was MySQL: the inventory ledger, the official record of how much stock is left.

It's a sensible design, and you'll find it in plenty of systems. Redis is very good at counting things fast under heavy traffic. The catch is in how a sale moves between the two notebooks.

The bug: a crash between two writes

When a payment succeeds, two things have to happen: the ledger in MySQL loses one hoodie, and the hold in Redis goes away. Those are two writes in two different databases, so they can't commit together. One always happens first.

Now imagine the server crashes, or the network drops, after the first write and before the second. The two notebooks disagree, and what goes wrong depends on which write went first:

• Ledger first, then the hold: the hoodie is counted as sold and still held. The store shows "sold out" while the stock is really there. That's lost sales, quietly.

• Hold first, then the ledger: the hold is gone but the sale was never recorded. The store thinks it has a hoodie it already sold, and it can sell it again. That's an oversell: an angry customer and a refund.

Crashes like this are rare. But at Shopify's volume, rare things happen every day, and every one of them is a real order for a real merchant.

Why the usual patches don't close it

This part is my own reasoning, not something from Shopify's post. When you first see a bug like this, a few fixes come to mind, and none of them makes it go away:

• Retry the second write. That only works if the process that knew a retry was needed is still alive. A crash takes that knowledge with it.

• Two-phase commit. Both stores would have to take part in one distributed transaction protocol. Redis doesn't, and it would add a coordinator to the busiest path in checkout.

• An outbox and a background fixer. This makes the two stores agree eventually. Until then they disagree, and during that window you can still sell the same hoodie twice.

the gap is the bug, not the code.

The fix: one transaction, one database

Databases already have a tool for "these changes must happen together": a transaction. A transaction is all or nothing. Think of a bank transfer: the money leaves one account and lands in the other, or nothing happens at all. If the server dies in the middle, the database rolls the half-finished work back when it restarts.

The catch is in the name: a transaction only covers one database. MySQL can't roll back a write that happened in Redis. So Shopify moved the holds out of Redis and into MySQL, right next to the stock they protect. One notebook, so there's no gap between notebooks anymore.

After the move, every step of a hold's life is one MySQL transaction. Two tables matter here. reservation_units holds the stock you can still reserve (more on its shape soon). reserved_quantities holds the active holds, each with a quantity and a deadline.

The new problem: one hot row

Moving the holds into MySQL fixed correctness, but it created a speed problem. The obvious way to store stock is one row per item and location, with a number in it:

-- The obvious design: one row that says how many are left
START TRANSACTION;

UPDATE inventory
SET available = available - 1
WHERE item_id = 42 AND location_id = 7 AND available >= 1;

INSERT INTO reserved_quantities
  (checkout_id, item_id, location_id, quantity, expires_at)
VALUES (9001, 42, 7, 1, NOW() + INTERVAL 5 MINUTE);

COMMIT;

That works, and it's correct. The trouble is the UPDATE. To change a row safely, the database locks it, and the lock is held until the transaction commits. A lock is like an "occupied" sign on a door: one person inside, everyone else waits outside.

With a single row per item, every checkout for that hoodie queues at the same door. A flash sale sends thousands of buyers at one product in the same minute, so they all wait for one row, one at a time.

The trick: one row per hoodie

Here's the clever part. Instead of one row that says "6", store six rows that each say "1". Like six tokens in a bowl: to reserve a hoodie, you take a token out of the bowl.

On its own that isn't enough. If every buyer asks for "the first free row", they all try to lock row 1 and you're back to one queue. The second half of the trick is a MySQL 8 feature called SKIP LOCKED.

Normally, when a query wants a row that someone else has locked, it waits. With FOR UPDATE SKIP LOCKED it doesn't wait. It skips the locked row and takes the next free one. Buyer one takes row 1. Buyer two finds row 1 locked and takes row 2. Buyer three takes row 3. Nobody waits, and all three commit at the same time.

Here's a sketch of a reservation in this design. The table and column names are from Shopify's post; the exact statements are my simplification:

-- Reserve 2 hoodies (a sketch of the idea, not Shopify's code)
START TRANSACTION;

-- 1. Lock 2 free rows. Rows other checkouts hold are skipped, not waited on.
SELECT id FROM reservation_units
WHERE shop_id = 1 AND inventory_item_id = 42 AND inventory_group_id = 7
LIMIT 2
FOR UPDATE SKIP LOCKED;            -- say it returns ids 4 and 5

-- 2. Take them out of the pool.
DELETE FROM reservation_units
WHERE shop_id = 1 AND inventory_item_id = 42 AND inventory_group_id = 7
  AND id IN (4, 5);

-- 3. Write the hold, with a deadline.
INSERT INTO reserved_quantities
  (checkout_id, inventory_item_id, inventory_group_id, quantity, expires_at)
VALUES (9001, 42, 7, 2, NOW() + INTERVAL 5 MINUTE);

COMMIT;  -- all three steps happen, or none do

Why is this safe? A row can only be deleted by the transaction holding its lock, and once it's deleted nobody else can take it. Two checkouts can never walk away with the same token. If the first query returns fewer rows than you asked for, the pool is running low, and that's the next section.

If this pattern looks familiar, it's the same one database-backed job queues use to hand jobs to workers. Shopify credits 37signals' Solid Queue as the inspiration, and the job queue in my own project Namestead (pg-boss on Postgres) works the same way. Booking systems that keep one row per seat use it too. PostgreSQL has had SKIP LOCKED since 9.5, and MySQL since 8.0.

don't fight over one number. hand out tokens.

Keep the bowl small: a pool of 1,000

One row per unit raises an obvious question: what about a product with 100,000 in stock? That's 100,000 rows for one product, across millions of products. The table would be enormous, and almost all of it would sit there unused.

So the bowl is small on purpose. Shopify keeps a bounded pool of available rows, capped at 1,000 per item and location. The ledger still holds the real count. A replenishment process keeps topping the pool up from the ledger as buyers take rows out.

In a flash sale, buyers can drain the pool faster than the background process refills it. When the pool is empty, the reserve path refills it inline: one transaction takes a lock and adds rows, and the others wait for it to finish, then take their rows. It's slower for a moment, but nobody is told "sold out" while the ledger still has stock. Correctness wins over a few milliseconds.

Four things that only show up under load

The design above is the headline. Getting it to hold up at Shopify's peak took four more fixes, and they're the most useful part of the write-up if you ever build something like this.

1. Two locks per reservation, so: a composite primary key

The first version gave each row an auto-increment id as its primary key and found rows through a separate index on the shop and item columns. In MySQL's InnoDB engine, the table is stored in primary-key order, and a lookup through a secondary index locks two things: the index entry and the row itself. That's twice the locks per reservation.

The fix was to make the filter columns part of the primary key: (shop_id, inventory_item_id, inventory_group_id, id). Now the query goes straight to the rows it wants, in key order, and takes one lock per row, half as many.

2. Refills blocked on an empty pool, so: READ COMMITTED

MySQL's default isolation level is REPEATABLE READ. At that level, a locking read that finds nothing doesn't just come back empty: it locks the gap where matching rows would go (when the range runs to the end of the index, that's the "supremum" lock). On an empty pool that's a real problem, because the gap is exactly where the replenishment INSERT needs to write. The refill waits on the reservations, and the reservations wait for the refill. That's how you get deadlocks.

Under READ COMMITTED, InnoDB doesn't take gap locks for reads like this, so the refill can insert. Shopify switched these transactions to READ COMMITTED, which needed a small change in their framework to set the isolation level per transaction.

3. Deadlocks, so: one lock order

Reserve and claim touched the two tables in different orders. Reserve inserted into reserved_quantities and then deleted from reservation_units; claim deleted from reserved_quantities. Two transactions taking the same locks in opposite orders can each end up waiting for the other, and MySQL has to kill one.

The fix is an old rule: every code path takes locks in the same order. Reserve now always deletes from reservation_units first, then inserts into reserved_quantities, and claim only touches reserved_quantities.

4. A ceiling with low CPU, so: look at the connections

This one is the best lesson in the post. Load tests hit a throughput ceiling, but the database CPU was low and individual queries were fast. The bottleneck wasn't the queries. It was connections.

To find out who was holding them, Shopify tagged every SQL statement with the business process it belonged to, as a comment like /* conn_tag:checkout_completion */. Their ProxySQL layer read the tags and measured how long each caller held a connection. The culprits weren't the reservations at all: other steps in checkout were holding connections open across long transactions.

Cleaning up the checkout path removed 50% of the reads and 33% of the transactions on the primary database. They also found an InnoDB thread concurrency limit that had been set conservatively years earlier and never revisited. Raising it removed the last ceiling. They also batched multi-item carts into one round trip with UNION ALL, so a cart with five products doesn't make five separate trips to the database.

Shipping it: shadow, switch, roll out

You don't swap the system behind every checkout in one deploy. Shopify did it in three steps:

1. Shadow mode. Every reservation was written to both Redis and MySQL, with Redis still the source of truth. That proved MySQL was correct and fast enough on real production traffic, with no need to migrate holds already in flight.

2. Switch, with a kill switch. MySQL became the source of truth, with a way to flip back instantly.

3. Pod by pod. Shopify splits shops across "pods", and the rollout went one pod at a time, starting with the quiet ones, so any surprise would hit a small slice of shops first.

Notice that shadow mode is itself a dual write, the very thing this whole project removes. It's fine here because nobody trusts the second copy yet. A gap during shadow mode costs a mismatch in a report, not an oversold hoodie.

Did it work?

Yes. The MySQL design carried Black Friday 2025, when merchants peaked at a record $5.1 million in sales per minute, 11% higher than the year before. In high-volume flash sales after the move, the database writer stayed under 50% CPU and the readers under 16%, which leaves room for next year.

What I'm taking from it

• If two things must change together, keep them in one database. Speed was never the bug. The bug was a gap between two systems, and no amount of speed closes a gap.

• Change the shape of the data before you add infrastructure. A hot row is a contention problem. Splitting one number into many rows spread the contention without adding a new system.

• Re-check your old "can't". "MySQL can't handle this" was true before SKIP LOCKED existed. Databases move on, and so should the reasons we pick our tools.

• Measure the plumbing. The final ceiling was connections held by unrelated code, not the clever part.

• Count the trade-offs. The replenisher is a new moving part to run and watch, the isolation level isn't the default, and the inline refill makes a few checkouts wait during the worst spikes. Shopify chose those costs knowingly, and that's the part I'd copy.

Summary

Sources and further reading

• Scaling inventory reservations, Shopify Engineering (Emilie Noel, May 2026). The original write-up, and the source of every number here.

• Locking reads: NOWAIT and SKIP LOCKED, MySQL 8.0 reference manual.

• InnoDB locking: record, gap and next-key locks, MySQL 8.0 reference manual.

• Introducing Solid Queue, 37signals. The database-backed job queue Shopify credits as inspiration.

• Shopify inventory reservations, Hello Interview. A system design interview view of the same case.