How to Stop Double-Allocated Stock in Logistics Management Solutions

Two warehouse workers confirm the last 12 units of a SKU within the same second, and both orders succeed. This happens in logistics management solutions whenever stock is a mutable counter updated by concurrent services. Retries from a flaky carrier API make it worse. This article shows a ledger-based reservation pattern in Python and PostgreSQL that prevents oversell. It follows the same principle behind how logistics management solutions work in enterprise systems.

Context and Setup

The scenario: an order service, a warehouse service, and a tracking service all write to one inventory store. The stack is Python 3.12, psycopg 3, and PostgreSQL 16. You need a table per warehouse-SKU pair and an order API that retries on timeout.

Data quality is the usual failure point. McKinsey’s 2023 supply chain survey reported that 52 percent of companies had adoption difficulties caused by the quality of their planning data. A counter that drifts under concurrency produces exactly that kind of bad data.
chainstoreage

Building a Reservation Ledger for Logistics Management Solutions

In logistics management solutions a reservation ledger replaces the stock counter with append-only rows. Available stock is the sum of all rows, so no update can overwrite another.

Step 1: Model Stock as Events

Store every change as a signed row. Receipts add units, reservations subtract them, and releases add them back.

sql
CREATE TABLE stock_ledger (
id BIGSERIAL PRIMARY KEY,
sku TEXT NOT NULL,
warehouse TEXT NOT NULL,
delta INT NOT NULL, -- Why: signed value makes SUM() the available stock
kind TEXT NOT NULL, -- receipt | reserve | release
idem_key TEXT UNIQUE, -- Why: a retried request cannot insert twice
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX ON stock_ledger (sku, warehouse); -- Why: keeps the SUM() scan narrow

Step 2: Reserve Under a Lock With an Idempotency Key

Take a per-SKU lock, check the key, check availability, then insert. All four actions run in one transaction.

python
import psycopg

def reserve(conn, sku, wh, qty, key):
with conn.transaction():
# Why: serializes writers per SKU without locking the whole table
conn.execute("SELECT pg_advisory_xact_lock(hashtext(%s))", (f"{sku}:{wh}",))
# Why: a retried request returns the first result instead of reserving twice
if conn.execute("SELECT 1 FROM stock_ledger WHERE idem_key=%s", (key,)).fetchone():
return "duplicate"
(avail,) = conn.execute(
"SELECT COALESCE(SUM(delta),0) FROM stock_ledger WHERE sku=%s AND warehouse=%s",
(sku, wh)).fetchone()
if avail < qty:
return "insufficient" # Why: reject before writing, so the ledger stays clean
conn.execute(
"INSERT INTO stock_ledger (sku,warehouse,delta,kind,idem_key) "
"VALUES (%s,%s,%s,'reserve',%s)", (sku, wh, -qty, key))
return "reserved"

Step 3: Pick a Locking Strategy for Logistics Management Solutions

Advisory locks suit hot SKUs because contention stays per key. Row locks on a counter table are simpler but block unrelated reads. Optimistic versioning avoids locks, yet it pushes retry logic into every caller.

The ledger has one cost: SUM() grows with history. Write a periodic snapshot row per SKU and sum only rows after it. Do this once a single SKU passes a few hundred thousand rows.

Real-World Application

In one of our logistics projects at Oodles, a Shanghai-based logistics provider needed inventory, order processing, and real-time tracking in one open-source ERP (enterprise resource planning) system. We gave all three modules a single stock model, so reservations and shipment updates read the same records.

Key Takeaways
Append-only rows remove lost updates because no writer overwrites another writer’s value.
A unique idempotency key turns retried API calls into harmless no-ops.

Per-SKU advisory locks limit contention to the item being reserved.
Snapshot rows keep the ledger sum fast once history grows.
Reject insufficient stock before the insert, so the ledger holds only true events.

Questions about this pattern? Share them in the comments, or contact us about Logistics Management Solutions for your stack.

What causes overselling in logistics management solutions?
Overselling occurs when two services read the same stock value and both write a reservation. Without a lock or atomic check, each sees enough units. Retried requests without idempotency keys create the same duplicate reservations.

Why use a ledger instead of a stock counter?
In logistics management solutions a ledger records each change as an immutable row, so concurrent writers never overwrite each other. It also gives an audit trail for reconciliation. The cost is a growing table, which snapshots keep manageable.

Are PostgreSQL advisory locks safe in production?
Yes, when used transaction-scoped with pg_advisory_xact_lock, because PostgreSQL releases them at commit or rollback. Session-level locks risk leaking under connection pooling, so avoid them with tools like PgBouncer in transaction mode.

How does an idempotency key prevent duplicate reservations?
The client sends a unique key per logical request. A unique constraint on that key means a retry either hits the stored row or fails the insert, so the reservation happens exactly once.

Should reservations expire automatically?
Yes. Unpaid or abandoned orders hold stock indefinitely otherwise. Insert a release row from a scheduled job when a reservation passes its time limit, so the ledger stays the single source of truth.