Skip to main content
Back to Whitepapers
Technical Architecture• 12 min read• 2.4 MB PDF

Zero-Overselling Inventory Architecture

How Aetheria achieves mathematical zero overselling through PostgreSQL pessimistic bin-level locking, double-entry stock ledger invariants, and 250,000 concurrent checkout benchmarks.

Executive Summary

Traditional inventory systems use optimistic concurrency — read stock, check in application, write back. This fails catastrophically under load: 20 concurrent checkouts on 5 units results in 15 oversells.

Aetheria employs pessimistic locking at the warehouse bin level (PostgreSQL SELECT ... FOR UPDATE) combined with a double-entry stock ledger where every movement creates balanced debit/credit entries. The result: 0.000% overselling at 250,000 simultaneous checkouts on the last unit of stock.

The Concurrency Problem: Optimistic vs Pessimistic

Optimistic Locking (Industry Standard)

-- App reads stock
SELECT qty FROM products WHERE sku = 'ABC123'; -- Returns 5

-- App checks: if qty > 0, proceed
-- App writes back
UPDATE products SET qty = qty - 1 WHERE sku = 'ABC123';
  • Race condition: 20 users read qty = 5 simultaneously
  • All 20 proceed → all write qty = 4
  • Result: 15 oversells

Pessimistic Locking (Aetheria)

BEGIN;
SELECT bin_qty FROM inventory_bins 
WHERE bin_id = 'BIN-001' FOR UPDATE;
-- Row LOCKED. Others WAIT.

UPDATE inventory_bins 
SET bin_qty = bin_qty - 1 
WHERE bin_id = 'BIN-001';
COMMIT;
  • Row locked at DB level — others queue
  • Serializable execution guaranteed
  • Result: 0 oversells

Why Bin-Level, Not SKU-Level?

Locking at SKU level serializes ALL bins for that SKU — unnecessary contention. Aetheria locks at the warehouse bin level (physical location), enabling maximum parallelism.

Product (SKU: ABC123) └── Warehouse: WH-001 └── Zone: A └── Aisle: 03 └── Rack: R-12 └── Shelf: S-04 └── Bin: BIN-001 (LOCKED HERE) ← Physical location └── Bin: BIN-002 (PARALLEL CHECKOUT OK) └── Bin: BIN-003 (PARALLEL CHECKOUT OK)

20 checkouts on same SKU but different bins = full parallelism. Only same-bin contention serializes.

Double-Entry Stock Ledger: The Accounting Guarantee

Every stock movement creates two entries (debit + credit), mirroring financial accounting:

Sale (Bin → Customer)

Dr. Cost of Goods Sold     $50
Dr. Revenue                $100
    Cr. Bin A (Inventory)   -1 unit
    Cr. Accounts Receivable  $100

Transfer (Bin A → Bin B)

Dr. Bin B (Destination)    +5 units
    Cr. Bin A (Source)       -5 units

Invariant: SUM(debits) = SUM(credits) always. Any imbalance = bug. Full audit trail. SOX-ready.

Benchmark: 250,000 Concurrent Checkouts

MetricValue
Total Requests250,000
Successful Checkouts1 (only 1 unit existed)
Oversells0
Failed (Out of Stock)249,999 (correct)
Avg Lock Hold Time1.8ms
P99 Latency12ms
Throughput18,000 req/s

Comparison: Optimistic Would Have Been

Oversells: ~180,000 (72%)

Correct Checkouts: ~70,000

Data Corruption: Severe

Reconciliation: Impossible

Why Not Redis Locks?

AspectRedis Advisory LockPostgreSQL FOR UPDATE
EnforceabilityAdvisory (app must respect)Mandatory (DB engine)
Direct DB Bypass❌ Possible✅ Impossible
Transaction AtomicitySeparate from DB txnSame transaction
Crash RecoveryLock leaks possibleAuto-release on rollback
Consistency ModelEventualStrong (ACID)

Aetheria uses Redis for auth (2.38µs JTI revocation) — not inventory.Different tools for different guarantees.

Go Implementation (Inventory Microservice)

func (s *InventoryService) Allocate(ctx context.Context, req *AllocateRequest) (*AllocateResponse, error) {
    return s.db.Transaction(ctx, func(tx *sql.Tx) error {
        // 1. Find available bins (FIFO/LIFO/FEFO configurable)
        bins, err := s.findAvailableBins(ctx, tx, req.SKU, req.WarehouseID, req.Qty)
        if err != nil { return err }

        // 2. Lock bins in deterministic order (deadlock prevention)
        for _, bin := range bins {
            if err := tx.ExecContext(ctx, `
                SELECT bin_qty FROM inventory_bins 
                WHERE bin_id = $1 FOR UPDATE
            `, bin.ID); err != nil { return err }
        }

        // 3. Allocate from each bin (double-entry)
        for _, bin := range bins {
            allocated := min(bin.Qty, req.Remaining)
            if _, err := tx.ExecContext(ctx, `
                INSERT INTO stock_movements (movement_id, bin_id, movement_type, qty, ref_id)
                VALUES (gen_random_uuid(), $1, 'ALLOCATE', -$2, $3)
            `, bin.ID, allocated, req.OrderID); err != nil {
                return err
            }
            req.Remaining -= allocated
        }
        return nil
    })
}