Two buyers, one item

Three units of a product sit on one shelf. Two checkouts reach warehouse within the same millisecond, each asking for all three. Warehouse has to promise every unit at most once, and nothing in either request says the other one exists.

This lesson follows one gRPC call, ReserveStock, through warehouse’s Rails code and shows the line that makes it safe — and what goes wrong without it.

Play it

Run Without FOR UPDATE first, then switch to With FOR UPDATE. Every step shows the SQL each order sends, what it has read, and what it believes it holds.

Order A

not started

Read reserved_quantity: nothing read yet
Believes it reserved: 0 units

bin_inventories · SKU-C

On the shelf: 3
Reserved, committed: 0
Available: 3

Order B

not started

Read reserved_quantity: nothing read yet
Believes it reserved: 0 units

Three units of SKU-C sit in one bin. Orders A and B each want all three and arrive at the same moment. In this version the bin row is read without a lock.

0/6

Text version of this diagram

Without FOR UPDATE

Three units of SKU-C sit in one bin. Orders A and B each want all three and arrive at the same moment. In this version the bin row is read without a lock.

Order A
```
BEGIN;
SELECT * FROM bin_inventories
WHERE sku = 'SKU-C' ORDER BY id;
```
A opens a transaction and reads the bin. Nothing is reserved, so all three units look available.
Order B
```
BEGIN;
SELECT * FROM bin_inventories
WHERE sku = 'SKU-C' ORDER BY id;
```
B does the same a moment later. A has written nothing yet, so B sees exactly what A saw: three units available.
Order A
```
UPDATE bin_inventories
SET reserved_quantity = 3
WHERE id = 1;
```
A reserves its three units. The new value, 0 + 3, was computed in the application from what A read. Postgres takes the row lock for this write and keeps it until A commits.
Code: reserve_stock_service.rb:120
Order B
```
UPDATE bin_inventories
SET reserved_quantity = 3
WHERE id = 1;
```
B writes its own 0 + 3. Postgres makes B wait for A's row lock, so the two writes cannot interleave — but the number B is about to write was decided on a read that is already out of date.
Code: reserve_stock_service.rb:120
Order A
```
COMMIT;
```
A commits and releases the lock. B's write goes through at once and replaces A's 3 with B's 3. A's reservation has disappeared from the count, and nobody was told.
Order B
```
COMMIT;
```
B commits. Both orders were told they hold three units: six units promised, three on the shelf.

Outcome. A lost update. The row says three are reserved, each order believes it holds three, and three units are oversold. There was no error and no constraint violation: every write stored a legal value.

With FOR UPDATE

The same shelf and the same two orders. This time the bins are read with SELECT … FOR UPDATE, which is what warehouse does.

Order A
```
BEGIN;
SELECT * FROM bin_inventories
WHERE sku = 'SKU-C' ORDER BY id
FOR UPDATE;
```
A opens a transaction and reads the bin with FOR UPDATE. The row lock is taken at the read, so nothing can change the row between A's decision and A's write.
Code: reserve_stock_service.rb:86
Order B
```
BEGIN;
SELECT * FROM bin_inventories
WHERE sku = 'SKU-C' ORDER BY id
FOR UPDATE;
```
B asks for the same row FOR UPDATE and waits. It has not read anything yet, so it has nothing stale to act on. Warehouse bounds this wait with a lock_timeout that applies to this transaction only.
Code: idempotent_operation.rb:26–32
Order A
```
UPDATE bin_inventories
SET reserved_quantity = 3
WHERE id = 1;
```
A reserves its three units, computed from the value it read under the lock.
Code: reserve_stock_service.rb:119–120
Order A
```
COMMIT;
```
A commits and the lock passes to B. B's read completes now and returns the row as A left it: three reserved, none available.
Code: idempotent_operation.rb:85–96
Order B
```
ROLLBACK;
```
No bin can cover three units, so warehouse refuses with INSUFFICIENT_STOCK and rolls B's transaction back, taking B's ledger rows with it.
Code: reserve_stock_service.rb:107–116

Outcome. Three units on the shelf, three promised. B was refused on the true count instead of being given stock that no longer existed.

Prefer a terminal? Reproduce the race in psql walks through the same steps by hand, with two sessions against a real PostgreSQL.

The read is the bug, not the write

Reserving stock is a read-modify-write: read reserved_quantity, decide whether enough is available, write the new total. In warehouse the write is one line:

bin.update!(reserved_quantity: bin.reserved_quantity.to_i + @quantity)

The new total is computed in Ruby, from the value that was read. What reaches Postgres is UPDATE bin_inventories SET reserved_quantity = 3 WHERE id = 1 — a constant.

Postgres does protect the write. An UPDATE takes a lock on the row, and a second UPDATE of the same row waits until the first transaction ends. That is why, in the diagram, B’s write waits. But B waits in order to write a number it decided on before the wait began, and under PostgreSQL’s default Read Committed isolation nothing tells B that what it read has since changed.

That is a lost update, and it is silent:

Both transactions commit. Neither sees an error.
No constraint is violated. reserved_quantity = 3 never exceeds quantity = 3, so even a check constraint reserved_quantity <= quantity would have accepted both writes. bin_inventories does not define one; the row lock is the guard.
The row itself looks correct. Only the two reservations, three units each, show six units promised against three on the shelf.

Take the lock at the read

Warehouse reads the bins with a locking read:

bins = BinInventory.where(sku: @sku).order(:id).lock("FOR UPDATE").to_a

SELECT … FOR UPDATE takes the row lock when the row is read, not when it is written. B’s read now waits instead of B’s write. When A commits, B’s read completes and returns the row as A left it, so B decides on the true count and is refused with INSUFFICIENT_STOCK.

Three details keep this correct under real traffic:

Every bin of the SKU, always in id order. Two reservations for the same SKU lock the same rows in the same sequence, so neither can hold one row while waiting for a row the other holds.
Ledger rows before bin rows. Before it touches a bin, the call inserts an idempotency claim and a reservation ledger row. A release takes the same rows in the same order. When the two took them in opposite orders, a reserve racing a release deadlocked; a spec with two real database connections now holds that order in place.
One transaction around all of it. IdempotentOperation opens the transaction and the reservation runs inside it. A refusal returns commit: false, the transaction rolls back, and the claim and ledger rows it inserted disappear with it.

A lock turns a race into a queue

A queue needs a limit, or one slow transaction stalls every order for the same product. Warehouse sets lock_timeout for its own transaction only — the true in set_config('lock_timeout', …, true) scopes the setting to the current transaction. The limit comes from WAREHOUSE_STOCK_LOCK_TIMEOUT_SECONDS, defaults to 15 seconds and is clamped between 1 and 120.

When a wait runs out, nothing has changed, and the caller is told which row was busy:

Code	The wait was on
`RESERVATION_IN_PROGRESS`	The idempotency claim of an identical request still in flight
`STOCK_LEDGER_BUSY`	This order’s own reservation row, held by its own release
`STOCK_LOCK_TIMEOUT`	A bin row held by a different order

Each code is asserted against a real second connection, so a change that folds two of them together turns exactly one example red.

Takeaways

A read-modify-write is safe only if nothing can change the row between the read and the write. Either take the lock at the read with FOR UPDATE, or make the write conditional — for example SET reserved_quantity = reserved_quantity + 3 WHERE quantity - reserved_quantity >= 3, then check how many rows changed. Warehouse uses the locking read because it has to compare several bins and check who owns them before it writes.
Lock rows in one consistent order everywhere, or two correct transactions can deadlock each other.
Bound every wait, and when one runs out, say which wait it was.