Practical guide

How to reserve stock correctly when placing an order

Check and change availability atomically. Two customers must not both buy the last item successfully.

30 minutes · E-commerce and PostgreSQL

In short

Reading and then writing is not enough

When two requests separately read available = 1, both can try to reserve the same item. The check and change must therefore be one atomic database operation or run under a lock.

A transaction protects the reservation and its state. An external payment does not belong inside; a reservation record with expiry handles customer waiting.

Prepare

What to define

Name the meaning of inventory numbers and the entire reservation lifecycle first.

An on_hand, reserved, and availability model such as available = on_hand − reserved for a specific SKU and warehouse.
Reservation states pending, confirmed, released, and expired with allowed transitions.
Expiry duration and behavior after successful payment, cancellation, and a mid-process failure.
An idempotency key for the order and line so a repeated request cannot create another reservation.

Steps 1 to 3

Reserve under database control

A conditional UPDATE is enough for one item. For several, lock rows in one order and decide the whole basket together.

1. Introduce an explicit reservation

Keep one inventory row per SKU and warehouse. Constraints enforce on_hand >= 0, reserved >= 0, and reserved <= on_hand unless backorders are allowed.
Store a separate reservation with order_id, sku_id, quantity, state, and expires_at. Add a unique key for order and SKU.
Calculate availability from the authoritative database. Cache or Elasticsearch may display a hint but must not decide the last item.
Audit inventory changes as business events or a ledger so reservation, sale, cancellation, and manual correction are explainable.

available = on_hand - reserved

Official PostgreSQL constraint documentation

2. Check and change stock atomically

For one SKU, use a conditional UPDATE with RETURNING. If no row returns, stock is insufficient and no reservation is created.
Create the reservation and increase reserved in one transaction. An idempotent retry finds the existing reservation first.
For multiple SKUs, load and lock all inventory rows with SELECT FOR UPDATE in ascending ID order. Save only after all items pass.
Do not call a payment gateway or another API while holding locks. A shorter transaction means less waiting and fewer deadlocks.

UPDATE inventory
SET reserved = reserved + :qty
WHERE sku_id = :sku AND on_hand - reserved >= :qty
RETURNING on_hand, reserved;

Official PostgreSQL row locking documentation

3. Confirm or release exactly once

After payment, move pending to confirmed and atomically decrease both on_hand and reserved. A repeated callback finds confirmed and changes nothing twice.
On cancellation or expiry, move pending to released or expired and decrease only reserved. A state predicate prevents double release.
An expiry worker selects small batches with FOR UPDATE SKIP LOCKED so multiple workers cannot process the same reservation concurrently.
Use database time or consistently synchronized clocks and store an absolute expires_at. Monitor the oldest pending reservation.

UPDATE reservation SET status = 'expired' WHERE id = :id AND status = 'pending' RETURNING quantity;

Official PostgreSQL FOR UPDATE documentation

Step 4

Test real concurrency

A sequential test cannot expose overselling. Use two database connections in a controlled race for one item.

Let two customers reserve the last item

Run both requests concurrently. Exactly one succeeds, the other receives unavailable, and the inventory invariant holds.
```
php bin/phpunit --filter ConcurrentStockReservation
```
Deliver payment confirmation twice

Both callbacks return a consistent result but on_hand and reserved change only once.
Run two expiry workers

Every pending reservation is released once and confirmed reservations remain untouched.

When it goes wrong

Common mistakes

Inventory fell below zero

The check and update are not atomic or a database constraint is missing. Use a conditional UPDATE or row lock in one transaction.

A cancelled reservation still blocks stock

Order state and release are not reliably connected. Process cancellation as an idempotent event and monitor pending reservation age.

The expiry worker released a paid order

The transition did not verify current state under a lock. UPDATE needs status=pending and the decision belongs in one transaction.

Multi-item orders deadlock

Requests lock SKUs in different orders. Sort all IDs first and acquire locks consistently.

Done

Only one order can claim the last item.

The transaction now protects reservation creation, confirmation, and release atomically. Availability remains explainable and repeated requests do not change stock twice.

Read database transaction in the glossary All guides