Reserve the Last Lot Once: PostgreSQL Concurrency for Commodity Inventory

Two order screens show 100 bags available. Each operator reserves 70. Both screens passed their availability check, yet the warehouse now has commitments for 140 bags.

This is a concurrency bug, not a rounding problem. The availability read and the reservation write were separate decisions.

This article proposes a hypothetical inventory service. All quantities, identifiers and scenarios are synthetic; they do not describe Zenith Eclipse stock or software. The example uses whole bags of one agreed size. Bulk mass requires its own controlled unit and precision.

Put the invariant beside the quantity

For a single lot, define three stored quantities: physically recorded on-hand bags, active reserved bags, and a lot-level hold flag. Require nonnegative quantities and reserved bags no greater than on-hand bags.

The invariant is deliberately narrow. A reservation is an allocation record, not permission to dispatch. A later quality hold must block fulfillment even when reservations already exist.

Product eligibility is another separate decision. Zenith Eclipse's milling wheat requirements and feed wheat requirements illustrate why an inventory application needs an intended-use specification alongside the lot. In this hypothetical service, a successful reservation must not silently substitute one use category for the other.

An application can attempt allocation with one parameterized statement:

UPDATE lot_balance
SET reserved_bags = reserved_bags + $3
WHERE tenant_id = $1
  AND lot_id = $2
  AND $3 > 0
  AND NOT on_hold
  AND on_hand_bags - reserved_bags >= $3
RETURNING lot_id, reserved_bags;

Assume a composite primary key on tenant and lot, non-null integer quantities, nonnegative database checks and a check that reserved does not exceed on-hand. Validate quantity bounds and authorize the tenant before running SQL.

At PostgreSQL's Read Committed isolation level, a competing updater waits and re-evaluates its condition against the updated row after the first updater commits. The transaction isolation documentation explains this behavior. In the 100-bag example, one 70-bag reservation succeeds; the other finds only 30 available and updates no row.

A zero-row result is not automatically “out of stock”: the lot may be missing, held or inaccessible. Return a controlled application result without exposing another tenant's records.

Commit the reservation record with the balance

The statement is only the quantity guard. The same transaction must create a reservation containing its order reference, lot, quantity and lifecycle state. Roll back the balance update if that insert fails.

Require a client request identifier, unique within the authenticated tenant, and record the request's immutable parameters. On a retry, return the existing result only when those parameters match. A reused identifier with a different quantity is a conflict. Concurrent requests with the same identifier must be resolved through a database uniqueness constraint, not an application-only lookup.

Keep the transaction short. Do not hold its locks while requesting customer approval or calling another service.

Make cancellation and dispatch different transitions

Cancellation changes an active reservation to cancelled and releases its reserved quantity. Dispatch consumes both reserved and on-hand quantity. Perform each state transition and balance change in one transaction; a second cancellation must not release quantity again.

Use a consistent lock order across reserve, cancel, dispatch and hold operations. PostgreSQL's locking guidance describes both deadlocks and consistent acquisition order. If a deadlock or serialization failure requires a retry, retry the whole transaction with the same business request identity.

A hold changes dispatch eligibility, not physical quantity. A stock correction below existing reservations should create an explicit discrepancy workflow; do not silently erase commitments to make the database constraint pass.

Exercise the race deliberately

Use two database sessions and pause the first transaction before commit. Verify that the second allocation waits and then refuses the unavailable quantity. Repeat with the first transaction rolling back: the waiting allocation should then succeed.

Also test duplicate request IDs, double cancellation, dispatch racing with a hold, and an insert failure after the balance update. For multi-lot allocation, lock lots in a deterministic order and define whether partial allocation is allowed.

This design protects one database's recorded balance. It does not prove physical stock accuracy, coordinate independent inventory authorities or certify lot suitability. Those boundaries should remain visible in the user interface. A trustworthy allocation service promises only what its transaction can enforce.

---

About the publisher: Zenith Eclipse Co works across agricultural commodities, international logistics and machinery sourcing.