Two Buyers, One Item!

One unit left. Two people press Buy within the same millisecond. Exactly one of them should get it, and the other should be told (cleanly!).

It sounds like a counting problem. lol! But it isn't. The count is obv correct the whole time. What breaks is time: two processes look at the same number, both believe it, and both act on a belief that only one of them can make true.

So let's start with the problem, then look at three ways to fix it!

The Problem

A product page says Only 1 left. Here is the checkout code most of us write first:

stock = db.fetchval("SELECT stock FROM items WHERE id = $1", item_id)
if stock >= 1:
    db.execute("UPDATE items SET stock = stock - 1 WHERE id = $1", item_id)
    create_order(user_id, item_id)

It reads stock, decides, then writes. Run it once and it's correct. Run it twice at the same moment and this happens:

Two orders, one item. Every individual statement did exactly what it was told.

Result: overselling! Refunds! Loss! Extra effort!

Fix 1: Redis (WATCH / MULTI / EXEC)

Redis's own tutorial on real-time inventory reservation is a clean version of this approach. Instead of decrementing stock at purchase and hoping, it moves units between three pools: available (any cart can claim), reserved (held for one cart while it pays) and confirmed (paid for, permanently gone).

WATCH   inventory:sku-42        # tell Redis to watch this key
HGETALL inventory:sku-42        # available = 1
# app checks: 1 >= 1, ok
MULTI
  HINCRBY inventory:sku-42 available -1
  HINCRBY inventory:sku-42 reserved   1
  # create a reservation record
  HSET reservation:r-901 cart c-7 sku sku-42 qty 1 state active expiresAt ...
  # track active reservations
  SADD inventory:sku-42:active r-901
  # record an audit event
  XADD inventory:audit * type hold reservation r-901 sku sku-42
EXEC                            # nil if the key changed since WATCH

Quick cheat sheet:

• WATCH = check whether someone changed the data.
• MULTI = queue commands for a transaction.
• EXEC = execute the queued commands, provided no watched key has changed.

This is the same read → decide → write flow from the bug, with one difference: EXEC refuses to run if anyone touched inventory:sku-42 after the WATCH. Both buyers can read available = 1, but only one EXEC goes through. The other gets nil, rereads, sees 0, and gets insufficient inventory. The tutorial retries up to five times.

A smaller example:

WATCH tickets
GET tickets
MULTI
DECR tickets
EXEC

If another client changes tickets after WATCH and before EXEC, the transaction gets aborted. The client can retry after checking the latest value.

One thing that tripped me up: MULTI/EXEC does not roll back if one command fails. And WATCH only tells you something changed. You still have to check the value yourself before queuing commands if you want a rule like "only buy if tickets are available."

But… but…

• Two sources of truth. Confirming a hold in Redis and writing the order to Postgres are two writes in two systems. No transaction spans both. Crash between them and you either sold something the database never recorded, or recorded a sale while Redis still thinks the unit is held.
• Durability. Redis replication is asynchronous by default. If the primary fails right after acknowledging a hold, the replica that takes over may never have seen it, and that unit can be sold again.
• Optimistic locking on a hot key. WATCH watches the whole hash. In a flash sale, every buyer of that SKU is writing the same key, so most EXECs fail and retry. After five retries a buyer is turned away even if stock remains: an undersell caused purely by contention.

Fix 2: Just Use Your Database (FOR UPDATE)

You don't need a second system to fix the race. The database you already store stock in can do it with one extra clause:

BEGIN;
SELECT stock FROM items WHERE id = 42 FOR UPDATE;  -- lock the row
-- app checks: stock >= 1?
UPDATE items SET stock = stock - 1 WHERE id = 42;
COMMIT;                                            -- lock released

FOR UPDATE locks the row until the transaction ends. When Buyer B runs the same query, they wait for Buyer A to commit, then read the updated stock: 0. Nobody can modify the row in between.

Or fold the check into the write with a conditional UPDATE. If no row comes back, the item was already gone:

UPDATE items
SET stock = stock - 1
WHERE id = 42 AND stock >= 1
RETURNING stock;

What happens if two requests arrive at the same time? If the answer relies on "check, then update" as two separate steps, that gap is where the race lives. The condition needs to live inside the write, not before it.

For a monolithic application, this works well. You get one source of truth and transactional durability, but buyers competing for the same item queue up. Keep transactions short and never call the payment gateway while holding a lock.

Things get trickier with microservices. Inventory, payments, and orders become independent services without one shared transaction.

The inventory reservation succeeds, but payment fails. What happens now? Well, that's the tradeoff!

Fix 3: One Row per Unit

Shopify used to track available and reserved quantities in Redis. But Redis and MySQL each held part of the inventory state, so updating both atomically was basically impossible.

Their big idea: store one row per available unit, instead of one row with a quantity. So if 3 units are available, the table looks like this:

• One row per sellable unit. An item with 10 units has 10 rows. Reserving means grabbing rows, not decrementing a number.
• FOR UPDATE SKIP LOCKED. If another checkout already holds a row, MySQL skips it and hands you a different free one. Buyers stop queuing behind each other.
• A capped pool of 1,000 rows per item and location, refilled from the ledger. If a flash sale drains it, the next reservation refills it on the spot, with a lock so only one refill runs at a time.
• Reserve and claim commit in one ACID transaction, because they finally live in the same database.

The reservation itself, roughly:

-- Start transaction with READ COMMITTED isolation
BEGIN;

-- Select N units, skipping any locked by other transactions
SELECT id
FROM available_units
WHERE shop_id = ?
  AND inventory_item_id = ?
  AND inventory_group_id = ?
ORDER BY shop_id ASC, inventory_item_id ASC, inventory_group_id ASC, id ASC
LIMIT 3
FOR UPDATE SKIP LOCKED;

-- Move selected units to reserved_units table
INSERT INTO reserved_units (unit_id, cart_token, expires_at, ...)
VALUES (...);

-- Remove from available pool
DELETE FROM available_units
WHERE (shop_id, inventory_item_id, inventory_group_id, id) IN (?, ?, ?, ?);

COMMIT;

Further Reading

• PostgreSQL docs: Transaction Isolation
• Redis: Inventory Reservation in Real Time
• Shopify Engineering: Scaling Inventory Reservations

Thanks for reading!