Preventing Overselling in Concurrent Inventory Reservations: PostgreSQL Conditional Updates, Idempotency and Release Tests

Define conservation before treating a reported balance as permission to write

Assume a service temporarily reserves inventory for an order and permits cancellation before dispatch is confirmed. A stock bucket is identified jointly by tenant, warehouse and SKU, the stock-keeping identifier for a material or product. This example excludes lot expiry, serial-number allocation, substitute materials and atomic commits across databases. Real requirements for those features must become part of object identity and transaction scope; a design based only on SKU is insufficient.

In a closed test without receipts, stock counts or dispatch, define initial quantity Q, current available quantity A and active reserved quantity R. Require A≥0, R≥0 and A+R=Q. Reserving q units reduces A and increases R by q; cancellation reverses that change. Repeating a call must not change the quantities again. Request quantities must be positive integers in individual units, without mixing boxes and pieces. Production receipts and adjustments require reconciliation against a complete inventory ledger instead of a permanent comparison with initial Q.

SQL multi-query snapshot consistency concerns whether readers combine different versions. Reservations additionally require control of competing writes. An AI assistant may query availability to support a decision, but that answer describes a previously visible state. The execution layer must repeat the protected check when accepting the reservation; a model's recent statement that stock exists is not permission to bypass database safeguards.

Consider two constructed orders against ten units: A requests seven and B requests six. If both sessions read ten and then write application-calculated balances of three and four, both can receive success promises while the final balance records only the last overwrite. Replacing that operation with an unconditional available=available-q can instead produce minus three. Lost updates and missing insufficient-stock conditions are different faults; fixing one does not fix the other.

Put availability into the actual UPDATE condition

For a single bucket, the core statement can be: UPDATE stock SET available = available - $4 WHERE tenant_id = $1 AND warehouse_id = $2 AND sku = $3 AND available >= $4 RETURNING available;. Parameters $1 through $4 are driver-bound values, with $4 already validated as a positive integer q. A model should not concatenate the SQL. The three identity columns must be unique. One returned row means the statement performed the decrement; zero means no row satisfied every condition.

PostgreSQL 18's Read Committed behavior provides the basis: an update encountering a concurrent writer waits, and after that writer commits it checks the updated row against its WHERE condition again.[1] Reading sufficient stock earlier does not permanently authorize a reservation. This argument concerns an existing single stock row and its own conditions; it does not establish safety for arbitrary cross-row predicates at this isolation level.

With ten units, session A conditionally subtracts seven, obtains three, and pauses before commit. Session B attempts the corresponding subtraction of six and waits for A's outcome. If A commits, B rechecks against three, fails the condition and returns zero rows. If A rolls back, B can subtract from the restored ten and obtain four. Which order succeeds depends on execution order; the service should not promise that the earliest network arrival always wins.

Do not translate every zero-row result into stock shortage. It can also indicate a wrong tenant or warehouse, an absent stock bucket, or an object outside visible business scope. An authorized follow-up read can help classify the result, but must not authorize an unconditional compensating decrement. Do not expose another tenant's SKU existence to an unauthorized caller. User-facing error categories may differ from internal diagnostic details.

The stock table should at minimum have a composite primary key over tenant_id, warehouse_id and sku, plus available bigint NOT NULL CHECK (available >= 0). Request quantity likewise needs non-null and positive-value validation. PostgreSQL's constraint documentation explains that a CHECK evaluating to NULL does not reject a row as FALSE would, so non-null requirements cannot be omitted.[2] A CHECK is a final guard, not a replacement for unique business identity, a correct transaction or the request-result ledger.

Record the entire request outcome under one business key

A conditional update prevents this concurrent operation from driving availability negative, but cannot stop a timed-out order from reserving again. We recommend a reservation_request table keyed uniquely by (tenant_id, request_key), retaining warehouse, SKU, quantity, operation type or a canonical parameter digest, status, reservation identifier and business response. The upstream caller creates and persists one stable key per business intention and reuses it for network retries. The same string with a different quantity must be rejected rather than treated as a new request.

One implementable single-database protocol is: BEGIN; insert a processing request with ON CONFLICT (tenant_id, request_key) DO NOTHING RETURNING request_key. One returned row grants this transaction ownership of new execution. Run the conditional decrement. On success, write the reservation detail and mark the request reserved; on insufficient stock, record rejected and its determined reason; then COMMIT. Report a completed reservation only after commit succeeds and the outcome is confirmed.

If the insert returns zero rows, issue a subsequent SELECT on the same primary database, compare all business parameters, and return the recorded outcome without decrementing again. The unique index underpins INSERT conflict handling.[3] At Read Committed, a conflicting row may not be visible in the insert statement's snapshot. Do not assume an ordinary query branch within that same statement must retrieve it; a later statement has a new visibility boundary.[1]

This protocol commits the initial processing row, decrement, reservation detail and final result in one transaction. It does not commit processing separately and start a second transaction for the decrement. Concurrent same-key inserts consequently wait for the original transaction: after a successful commit they read the persisted result; after a rollback they may obtain the insertion opportunity. If a subsequent lookup still cannot find the record, investigate an abnormal state rather than immediately generating another key.

Business rejection needs stable semantics too. This design persists insufficient-stock rejection and returns it on same-key retries. Later replenishment does not silently turn that old request into a success. A new identifier belongs to an explicitly new business attempt and should reference the previous one. This avoids an operation already reported as failed unexpectedly reserving stock during background retry. Products may choose other semantics, but implementation must not inconsistently alternate between caching rejection and attempting another decrement.

A reservation detail should include reservation_id, request key, stock bucket, quantity, state and created_at. Reserved, released and consumed are distinct states with controlled transitions. The request result may refer to the reservation identifier while current state is read from the detail. Preserving the original response must not make a canceled reservation appear active. Both historical outcome and current state need clear meanings and timing.

Use explicit row locks for more complex checks, with a defined lifetime

A second implementation uses SELECT ... FOR UPDATE to lock the stock row, reads current availability, and performs the check, decrement and detail write within the same connection and transaction. PostgreSQL row locks prevent conflicting modifications and generally remain until transaction end; ordinary unlocked readers are not all stopped by them.[4] Explicit locking allows several dependent row-level decisions after reading, at the cost of additional statements and a potentially longer lock-holding interval.

For a single available≥q condition, a conditional update is more compact. Additional properties such as a frozen flag or dispatch eligibility can be included in that UPDATE or examined after acquiring the lock, with reasons recorded individually. Both designs require cancellation, dispatch, adjustments and every other write path to follow a compatible protocol. One locked path cannot establish system safety if a separate script overwrites balances directly.

An explicit row lock cannot protect a row that does not exist. Creating stock buckets on demand requires separate unique-conflict handling and creation semantics; this example avoids that complication through pre-existing rows. Nor should SKIP LOCKED make an order silently skip its requested bucket and misreport the outcome as shortage. Queue workers skipping claimed jobs and customers requesting reservations for a specific SKU have different business contracts.

For an order involving several buckets, acquire locks or apply updates in a consistent order and place the whole reservation inside one transaction. Cross-row totals, dynamically selected combinations and substitute materials require a broader serialization design, potentially Serializable isolation with complete transaction retries. Stronger isolation brings conflict and retry costs rather than a free correctness switch; evaluate every writer participating in the invariant together.

Never hold inventory locks while waiting for model-generated explanations, human approval or an external payment API. Complete necessary controlled database actions and end the transaction before generating explanations. Subsequent business steps need explicit states and later transactions. Otherwise a slow call can queue work against a hot bucket and appear as timeouts and repeated requests.

An end-to-end timeline when commit succeeds but the reply is lost

Construct a reproducible timeline for tenant T, warehouse W and SKU S, starting with ten available units. A uses order-A-reserve-1 to request seven; B uses order-B-reserve-1 to request six. A inserts its request, decrements to three, writes a seven-unit active reservation and commits. B competes under a different key, gets zero rows from its conditional update, and records insufficient-stock rejection. Final state should be availability three, active reservations totaling seven, one successful request and one rejected request.

Now disconnect the network after A's database commit but before its response reaches the client. A timeout does not establish rollback. The caller retries with its original key and parameters. The insert encounters the unique conflict; the subsequent lookup returns the original reservation identifier. Availability stays three. There may be two successful calls from the application's perspective, but only one business reservation. Evidence comes from the request and detail tables, not the count of successful HTTP responses.

If the interruption instead occurs before commit and the transaction rolls back, a retry can obtain execution rights under the original key and reserve normally. Distinguish the branches through persistent records. When the client cannot determine COMMIT's outcome, query the primary by that key first. If the original transaction remains active, lookup and retry need bounded waiting and timeout policies. An empty read from a lagging replica does not prove that recreation is safe.

A local database transaction does not make external systems part of an atomic commitment. If a successful reservation must emit an event to a warehouse or messaging system, one option is to commit an outgoing event record alongside the business result, then deliver it through a separate sender with consumer deduplication by event identity. Delivery and consumption still need their own confirmation and recovery protocols. Sending a success message before database commit can trigger downstream work for an operation that later rolls back.

An authorized status query should return request status, reservation identifier, quantity, bucket and the detail's current state. Logs can retain the key, transaction stage, error class and duration without routinely copying the entire order or customer data. AI can explain the result, but the decision that an operation succeeded must come from committed evidence returned by the controlled execution layer.

Cancellation and expiry must restore stock only once

Canceling cannot simply execute available=available+q. Cancellation itself can repeat, and an expiry worker may compete with the user. A release transaction should first lock the reservation detail, validate tenant and bucket ownership, and inspect state. Only a reserved detail is eligible for release. Then increment the stock bucket by the quantity stored in that detail and change state to released, committing both changes together.

Take the quantity from the original reservation, not an arbitrary number supplied by a cancellation caller. A released detail returns an already-released outcome without another increment. A consumed detail requires a separately defined return or reversal process rather than ordinary cancellation. Confirm that restoration updates exactly one valid stock bucket; if it does not, roll back the transaction so state cannot say released while availability remains unrestored.

In the example, canceling A's seven units changes availability from three to ten and active reservations from seven to zero. A repeated cancellation must leave ten, not seventeen. Let a user cancellation and expiry worker compete for the same detail: the later lock holder should observe its released state. With no other stock operations, A+R remains ten; otherwise reconcile the relevant inventory movements too.

Lock order must cover the full lifecycle. One possible convention locks an existing reservation detail before its stock bucket; a new reservation acquires its request key, then updates stock and inserts a new detail. Another dispatch path must not lock stock first and then wait for that same detail, creating an inverse dependency. Multi-bucket orders also need consistent bucket ordering and an inspection of the actual call graph for hidden reversed acquisition.

An expiry scan merely identifies candidates; it does not release them. After locking a detail, a worker should recheck current state and expiry against database time, accounting for concurrent extensions or consumption. A failed worker must rerun the same state check instead of directly incrementing stock for every previously scanned identifier. Record expiry time and successful release time separately to explain why inventory may temporarily remain reserved.

Six failure tests with inputs, results and durable evidence

Test one is competing shortages. Start at ten and let two different keys request seven and six. Use two sessions to control order, pausing the first transaction before commit. At most one reservation should succeed; the other waits and then rejects. If the first rolls back, the second may succeed. Inspect returned-row counts, transaction completion order, availability and active detail totals. If both succeed, check the real write predicate, primary-database routing and bypass writers. After correction, repeat with the order reversed.

Test two covers same-key concurrency and parameter conflict. Two connections request seven under the same key: expect one detail, one decrement and the same reservation identifier. Change one request to eight under that key: expect parameter conflict without altering the original outcome. Inspect the composite unique key, bound values and detail count. For duplicate decrements, repair key persistence and the atomic transaction boundary, then arrange collisions before insertion, after update and before commit.

Test three interrupts the commit window. Separately simulate a rollback before commit and a lost response after commit. Recover only through the original key in both cases, finishing with exactly one active reservation or zero if the business outcome is rejection. Inspect the primary ledger rather than client exception text. If recovery generates a second key, stop that retry path and reconcile duplicate promises before repairing key lifecycle. Fault injection belongs in an isolated test environment.

Test four exercises release competition: first cancellation, repeated cancellation, simultaneous expiry and cancellation, and dispatch racing with cancellation. Only a valid transition out of reserved may restore stock once; consumed records cannot take the ordinary release route. Inspect state history, balance changes and affected-row counts. A balance of seventeen or released stock still held indicates missing transition protection or a broken transaction boundary. Repair and rerun with an exception injected mid-transaction.

Test five checks partial insufficiency across an order. It requests two units of S1 and four of S2, whose starting quantities are five and three. Under this example's all-or-nothing contract, failure on the second bucket must roll back the first decrement and its details, leaving five and three. An internal savepoint can roll back tentative decrements before storing stable rejection, or the whole transaction can roll back with a separately designed outcome-recording protocol. Never commit a partial order while reporting total failure. Check for caught exceptions followed by accidental commit; then test successful two-bucket orders and inverse-order competition.

Test six covers invalid values and deadlocks: zero, negative, NULL and out-of-range quantities, cross-tenant keys, and orders visiting multiple buckets in opposite order. Reject invalid input before decrement. Classify and roll back deadlocks or serialization conflicts. PostgreSQL requires retrying the whole transaction after a serialization failure and cautions against treating every other error as transient.[5] After repairing lock order, retry with the same business key and complete parameters under a bounded policy, checking for abandoned partial details.

Operate within hot-row capacity, reconciliation and rollback limits

This design does not make a popular stock row infinitely parallel. Updates still coordinate on the same row and bursts create waits, even with low average traffic. Separate successful reservations, business shortages, same-key hits, parameter conflicts, lock waits, deadlocks and unknown outcomes in monitoring. Not every unsuccessful request is a database failure. Load tests should preserve realistic hot-SKU concentration and duplicate-request rates; uniformly random access can overstate usable capacity.

Waiting and statement timeouts must fit the total request deadline, with bounded retries and backoff chosen for business timing. A timeout means the caller did not receive an answer on time, not necessarily that the database action was reversed. Uncertainty around commit still requires ledger lookup. Admission control and queues can protect the database, but the interface must explain that queued is not reserved and avoid promising success prematurely.

Periodically aggregate active reservations in a reconcilable view and compare them with inventory movement records. Look for orphan details, missing buckets, long-lived processing states, released records still counted as active, and reservations neither consumed nor handled after expiry. For discrepancies, restrict new promises in affected buckets, preserve evidence and investigate writer paths rather than erasing accountability through a bulk reset. Production reconciliation needs complete receipt, dispatch and adjustment definitions, not just a comparison of this example's two tables.

Deployment can start with these timelines in isolation, then controlled traffic through the real connection pool, transaction propagation and retry middleware. A rollback should stop new reservations while preserving original-key lookup and valid releases; switch write paths only after handling in-flight transactions. Rolling application code back does not justify deleting the idempotency ledger. Its retention must cover permitted retries and business traceability, because premature deletion lets an old request decrement inventory again.

This protocol follows PostgreSQL 18 documentation and checks conservation in constructed examples; database concurrency experiments were not executed. Reproduce the tests with your own driver, pool, triggers and permissions. The design provides neither a global cross-database no-overselling guarantee nor proof of physical stock accuracy. Availability conditions, request identity, state transitions and durable evidence must work together to form a reservation path that can be retried safely.