Prevent ecommerce overselling with inventory reservations and concurrency control

Overselling is a concurrent decision, not merely an inaccurate number

Two shoppers can see the last available unit and complete checkout almost simultaneously. Even when the product page was accurate at load time, that read cannot guarantee the unit is still allocatable when the order is created. Marketplaces, physical stores, returns and warehouse systems add more concurrent writes. The realistic objective is not a promise of zero overselling. It is to reduce inconsistency windows, choose where a conflict must be rejected, and make each exception recoverable.

A reliable design separates customer messaging from the authoritative decision. Displayed availability may come from a cache or index that updates later; confirmation must recheck inventory, quantity and version within an atomic operation. If another request already allocated the unit, the flow offers a lower quantity, an alternative, or a backorder allowed by commercial policy instead of accepting an order the warehouse cannot fulfil.

Build an inventory model that keeps states distinct

Separate physical, available, reserved and committed quantities

For every SKU and location, define at least four concepts: on_hand for units physically present, available for sellable units, reserved for temporary holds, and committed for units belonging to a confirmed order. Shopify documents these as separate states and treats physical stock as a set of both sellable and unavailable quantities. Your domain may use different names, but it needs the same distinction and a testable formula.

Add SKU, location, version, reason, source document and timestamp to every movement. A stock correction, a reservation and an order commitment are different events. An append-only ledger or equivalent history shows why a quantity changed and which system requested it. Avoid one total field that several integrations overwrite: it conceals conflicts and makes a sale indistinguishable from a warehouse correction.

Choose reservation timing from the commercial process

There is no universal best moment. A digital catalogue may skip inventory checks; abundant products may only be tracked; a standard order may reserve at confirmation; scarce inventory may be reserved while it is in a cart. As a concrete platform example, commercetools offers None, TrackOnly, ReserveOnOrder and ReserveOnCart. These modes illustrate policy choices rather than rules that apply to every stack.

A cart reservation needs a business-defined expiry. Its duration should reflect checkout, payment, the cost of held stock and customer expectations, not a generic benchmark. On expiry, line removal or confirmed abandonment, return the quantity through an idempotent transition. Store reservation_id, cart, SKU, location, quantity, state and expiry. Order confirmation converts the reservation into a commitment without decrementing inventory twice.

Make allocation of the final unit atomic

Use optimistic locking when conflicts are uncommon

Under moderate contention, read quantity and version, then issue a conditional update. The write succeeds only if the version still matches and quantity covers the request. The DynamoDB optimistic-locking pattern detects a conflict at write time. After rejection, read fresh state and retry only a limited number of times. An unbounded loop increases contention and checkout latency instead of creating availability.

Use row locks when contention is concentrated

In a relational database, a short transaction can select the inventory row FOR UPDATE, validate quantity, and record the reservation and movement before commit. PostgreSQL documents that incompatible writers and lockers on the same row wait for that transaction. Keep network and payment calls outside the lock. For a cart containing several SKUs, acquire rows in a consistent order and retry a deadlock at the transaction boundary.

Both approaches require an invariant, such as sellable quantity never falling below the threshold allowed by policy. Enforce it in the authoritative system, not only in the storefront. A disabled button, browser-side quantity limit or fresh-looking availability message cannot replace an atomic condition on the data that decides allocation.

Coordinate checkout, payment and distributed services

Do not hold a database lock while a shopper completes payment. Create a durable reservation, start payment with an idempotency key, and convert the reservation after a verified result. Define explicit states such as active, committing, committed, released and expired, with allowed transitions. A duplicate event must return the same outcome without releasing or committing units again.

When orders, payments and inventory belong to different services, one ACID transaction may not cover all of them. Prepare compensating operations: release the hold after a failed payment, or open manual handling if payment succeeded but stock commitment cannot be recovered. Microsoft notes that compensation can itself fail and must resume. Make each step idempotent, record its progress, and retain enough context to explain the decision.

Synchronise channels without trusting caches blindly

Designate an authority for each SKU-location pair and specify who may write. ERP, WMS, POS and ecommerce services should exchange identified deltas or events rather than periodically overwriting the same total without a guard. Every message carries an idempotency ID, version and commercial document reference. Reject out-of-order updates or apply them through an explicit version policy.

The product-page view can intentionally be fast and slightly behind. Present it as guidance, then perform the authoritative check before reservation or order creation. If an external channel cannot support atomic reservations, use a prudent commercial buffer, frequent synchronisation and an exception process. A buffer reduces exposure but cannot repair an event pipeline that loses changes.

Reconcile the system and measure the operating outcome

A reconciliation job compares the movement ledger, active reservations, open orders, payments and warehouse quantities. It looks for expired holds that still block stock, orders without commitments, duplicate movements and versions that stopped advancing. Do not silently replace the total. Create a traceable correction, preserve before and after values, and quarantine ambiguous cases. Reconciliation is a safety net, not a substitute for atomic allocation.

• Accuracy: difference between recorded and physically counted units per SKU-location.
• Conflicts: rejected conditional updates and exhausted retries.
• Reservations: conversion, expiry, failed release and time in each state.
• Customer: accepted orders that cannot be fulfilled and required substitutions.
• Pipeline: event lag and anomalies detected during reconciliation.

Segment metrics by channel, SKU and location because an aggregate can conceal a hotspot. Derive thresholds from actual volume and business tolerance. Connect every alert to a runbook with an owner, diagnostic queries and a safe action.

Roll out through controlled evidence

Start with one flow and a limited SKU group. Map states, formalise the invariant, add versions and idempotency, then test two simultaneous checkouts for the last unit, payment timeouts, duplicate events, expiry and reconciliation. Exercise multiple locations and conflicting lock orders. Before expanding, require each failure to create an explainable state rather than a silent quantity change.

To design data models, transaction boundaries and integrations for your stack, explore our ecommerce and systems development services. A sound architecture does not remove uncertainty; it exposes it, limits the effect of concurrency and provides a repeatable recovery path. To assess overselling risk in your checkout, contact AE Digital Agency and define priorities, tests and measurable operating controls.