Lost Updates in Warehouse Stock

PostgreSQL concurrency lab · labs/postgres/concurrency

Mental model

A product has one mutable availability number, but every successful reservation also leaves an immutable stock movement. Under concurrency, naive writers can overwrite each other's decrement. The reservation history remains complete while the availability number drifts.

Trigger the Race

The warehouse starts with 10 widgets. A normal reservation decrements stock once. The race simulates two workers reading the same starting value before either writes.

products.available_stock

mutable value used by the application

initial_stock + Σ movements

expected value from immutable history

movement	order	product	change

No reservations yet.

What gets lost

Both workers read available_stock = 10. Both calculate 9. Both write 9. Two orders and two movements commit, so history says 8 widgets remain, but the mutable row says 9. PostgreSQL executed both transactions successfully; the application used an unsafe transaction shape.

The Three Tables

products: the contested row

CREATE TABLE products (
  id              text   PRIMARY KEY,
  name            text   NOT NULL,
  initial_stock   bigint NOT NULL CHECK (initial_stock >= 0),
  available_stock bigint NOT NULL CHECK (available_stock >= 0),
  version         int    NOT NULL DEFAULT 0
);

orders: one accepted reservation

CREATE TABLE orders (
  id              uuid   PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id      text   NOT NULL REFERENCES products(id),
  quantity        bigint NOT NULL CHECK (quantity > 0),
  idempotency_key text   UNIQUE
);

stock_movements: immutable reservation history

CREATE TABLE stock_movements (
  id              bigserial PRIMARY KEY,
  order_id        uuid      NOT NULL REFERENCES orders(id),
  product_id      text      NOT NULL REFERENCES products(id),
  quantity_change bigint    NOT NULL CHECK (quantity_change < 0)
);

The Invariants

#	Rule	Purpose
I1	`available_stock == initial_stock + Σ quantity_change`	Detects lost decrements by comparing mutable state with reservation history.
I2	`available_stock >= 0`	Prevents accepting more reservations than the warehouse can fulfil.

Four Transaction Shapes

Mode	Behavior	Trade-off
`naive`	Read, validate, calculate in Go, then write.	Incorrect because the read is not protected.
`locked`	Read with `SELECT ... FOR UPDATE`.	Correct. Conflicting reservations wait and hot products form a queue.
`atomic`	Validate and decrement in one conditional `UPDATE`.	Correct and compact for this single-row invariant.
`serializable`	Keep the naive shape, let PostgreSQL abort unsafe executions, then retry.	Correct when committed, but contention can exhaust bounded retries.

Smallest reliable statement

UPDATE products
SET available_stock = available_stock - $1
WHERE id = $2
  AND available_stock >= $1
RETURNING available_stock;

A returned row means the reservation succeeded. No returned row means there was not enough stock. Validation and mutation occur under the same row lock.

Run the Real Experiment

# Start PostgreSQL, migrate, and seed
cd labs/postgres/concurrency
make up

# Demonstrate drift, then prove the lock-based fix
make break
make test

The browser simulation makes the interleaving visible. The Go driver creates the same race against PostgreSQL with concurrent workers and prints measured stock drift.

Measured Results

One run on 2026-06-11 used 8 workers, 250 reservations per worker, and a 2 ms delay between read and write. Throughput varies by machine; the correctness and contention behavior are the important parts.

Mode	Committed	Failed	Retries	Rate	Drift
naive	2,000	0	0	1,698/s	+1,750
locked	2,000	0	0	162/s	0
atomic	2,000	0	0	1,577/s	0
serializable	1,552	448	3,087	167/s	0

Serializable protects correctness, not completion

Every committed reservation was correct, but 448 attempts still failed after five retries. Under heavy contention, an application needs bounded retries and a defined failure response.

Atomic oversubscription check

With 12,000 attempts against 10,000 units, exactly 10,000 committed and 2,000 returned insufficient stock. Availability stopped at zero with no drift.