The invariant belongs in the write operation
Two customers see one remaining item. Both submit checkout. If each request reads availability and later subtracts one, both can succeed. A faster server does not fix the race; it changes how often the race occurs.
Choose a precise invariant: available stock cannot become negative, and every successful reservation has a unique durable identity. The architecture in this article assumes one authoritative database for a SKU and location. A globally distributed inventory service needs an additional allocation or consensus design; this single-database pattern does not create global serialization.
Reserve with an atomic conditional update
The request should carry a stable operation ID, authenticated account context, SKU, location, and positive quantity. Validate bounds and authorization before attempting the transaction. Scope the operation ID to the account and reject reuse with different parameters.
UPDATE inventory
SET available = available - $3
WHERE sku_id = $1
AND location_id = $2
AND available >= $3
RETURNING available;
One returned row means the update won under the predicate; no row means no matching eligible inventory. A database check constraint on nonnegative availability provides a second line of defense. The application must distinguish an unavailable SKU from an authorization failure without leaking restricted inventory.
Under PostgreSQL Read Committed, a concurrent updater waits for the competing update and rechecks its condition against the updated row. That behavior supports this single-row conditional decrement. It does not make arbitrary multi-row read-then-write logic serializable. PostgreSQL isolation behavior
Commit the reservation and stock change together
In the same transaction, establish the request's idempotency record, perform the decrement, and insert the reservation with its expiry and parameter fingerprint. On duplicate request identity, return the original outcome. A unique constraint must arbitrate concurrent submissions; an application-level existence check is insufficient.
If the reservation insert fails, roll back the decrement. If the transaction commits and the response is lost, the client can safely retry the same operation ID. Persist the decision long enough to cover the business retry window.
For a basket containing several items, acquire inventory locks in a consistent order such as location plus SKU. PostgreSQL row locks block competing writers until transaction completion; inconsistent lock order can create deadlocks. Keep transactions short and retry the entire transaction when the database reports a retryable conflict. PostgreSQL explicit locking
Expiry is a competing state transition
A background sweeper and a payment callback can race for the same reservation. Both must lock or conditionally update its state. Only the worker that changes held to expired may return stock. Only a permitted transition can consume the hold.
| Current state | Requested transition | Stock consequence |
|---|---|---|
| Held | Confirm | Hold becomes a committed allocation |
| Held | Expire | Return reserved quantity exactly once |
| Held | Cancel | Return reserved quantity exactly once |
| Confirmed | Expire | Reject; the hold has already been consumed |
| Expired | Confirm | Enter recovery policy; do not assume stock exists |
Use the database's time consistently for expiry decisions. Define whether confirmation requires the hold to be unexpired at the moment of transition. A sweeper running late should not silently extend the contractual reservation window.
The state transition and stock return belong in one transaction. Repeated expiry jobs should observe an already expired reservation and do nothing. Deleting the reservation immediately would erase the evidence that prevents a second return.
Keep remote payment calls outside inventory locks
Reserve locally, commit, and then initiate payment with a stable provider operation key. Record uncertain payment outcomes rather than assuming a timeout means failure. A callback or reconciliation job can later complete the workflow.
Payment and inventory do not share an atomic transaction. Design compensation deliberately: release an unused hold, reacquire stock if policy permits, or refund a payment that cannot be fulfilled. Each compensation needs its own identity and state, because compensation requests can also time out or be retried.
Expose these states honestly to the customer. “Payment received; confirming availability” may be appropriate in a recovery case. A generic success screen should not promise fulfillment before the system has established it.
Test with barriers, not just parallel requests
Use a concurrency test that pauses two transactions immediately before the critical write, then releases them together. With one item available, assert that only one reservation commits and the stock never becomes negative.
Repeat the test for confirmation against expiry, duplicate checkout IDs, reversed lock order, worker crashes, and payment confirmation after expiry. Reconcile the stock ledger against active reservations and committed allocations. Measure conflict rate and lock wait time alongside successful checkout latency; rising contention is a capacity signal even while correctness holds.