Zero-Overselling Inventory: How Pessimistic Locking Eliminates Stock Drift

TL;DR: Optimistic concurrency fails at scale. Pessimistic SELECT ... FOR UPDATE at the warehouse bin level + double-entry stock ledger = mathematical guarantee of zero overselling. Aetheria achieves 0.000% overselling at 250,000 concurrent checkouts on the last unit of stock.

---

The Concurrency Problem

Optimistic Locking (What Everyone Else Does)

-- 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 20 write qty = 4 → 15 oversells.

Pessimistic Locking (Aetheria's Approach)

-- App locks the specific bin row
BEGIN;
SELECT bin_qty FROM inventory_bins WHERE bin_id = 'BIN-001' FOR UPDATE;
-- Row is now LOCKED. Other transactions WAIT.

-- App checks, decrements
UPDATE inventory_bins SET bin_qty = bin_qty - 1 WHERE bin_id = 'BIN-001';
COMMIT;

Result: Transactions serialize at the database level. Zero overselling. Guaranteed.

---

Why Bin-Level, Not SKU-Level?

Granularity	Problem
SKU-level lock	Locks ALL bins for that SKU → unnecessary contention
Bin-level lock (Aetheria)	Locks ONLY the specific physical location → maximum concurrency

Aetheria's inventory model:

Product (SKU)
  └── Warehouse
       └── Zone
            └── Aisle
                 └── Rack
                      └── Shelf
                           └── Bin (LOCKED HERE) ← Physical location

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

---

Double-Entry Stock Ledger (The Accounting Analogy)

Every stock movement = two entries (debit + credit), just like financial accounting.

Stock 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.

Sale (Bin → Customer)

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

Purchase Receipt (Supplier → Bin)

Dr. Bin A (Inventory)         +100 units
    Cr. Accounts Payable      $5,000

Cycle Count Adjustment

Dr. Bin A (Inventory)         +3 units (found)
    Cr. Inventory Variance    $150

Every transaction balances. Full audit trail. SOX-ready.

---

Benchmark: 250,000 Concurrent Checkouts

Test Setup

• SKU: Single product, 1 unit in Bin-001
• Clients: 250,000 simultaneous HTTP requests
• Endpoint: POST /api/pos/checkout (locks bin, decrements, commits)
• Database: PostgreSQL 16, 16 vCPU, 64GB RAM
• Network: 10 Gbps, <1ms latency

Results

Metric	Value
Total Requests	250,000
Successful Checkouts	1 (only 1 unit existed)
Oversells	0
Failed (Out of Stock)	249,999 (correct)
Avg Lock Hold Time	1.8ms
P99 Latency	12ms
Throughput	18,000 req/s

Comparison: Optimistic Would Have Been

Metric	Optimistic (Simulated)
Oversells	~180,000 (72%)
Correct Checkouts	~70,000
Data Corruption	Severe

---

Why Not Redis Locks?

Aspect	Redis Advisory Lock	PostgreSQL FOR UPDATE
Enforceability	Advisory (app must respect)	Mandatory (DB engine)
Direct DB Bypass	❌ Possible	✅ Impossible
Transaction Atomicity	Separate from DB txn	Same transaction
Crash Recovery	Lock leaks possible	Auto-release on rollback
Consistency Model	Eventual	Strong (ACID)

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

---

Handling Edge Cases

Deadlock Prevention

-- Always lock bins in consistent order (by bin_id ASC)
SELECT bin_qty FROM inventory_bins WHERE bin_id IN ('BIN-003', 'BIN-001') FOR UPDATE ORDER BY bin_id;
-- Application enforces global bin_id ordering

Long-Running Transactions

• Lock timeout: 5 seconds (configurable)
• Auto-retry: Exponential backoff (max 3)
• Circuit breaker: Opens after 10 consecutive timeouts

Distributed Transactions (Multi-Bin)

-- Two-phase commit via advisory lock coordinator
BEGIN;
SELECT pg_advisory_xact_lock(hashtext('TXN-12345'));
SELECT bin_qty FROM inventory_bins WHERE bin_id IN ('BIN-001', 'BIN-002') FOR UPDATE;
-- ... updates ...
COMMIT;

---

Comparison: Concurrency Strategies

Strategy	Oversell Rate (20 concurrent)	Complexity	Auditability
No Locking	95%	Low	None
Application Mutex	5–15% (leaks)	Medium	Low
Redis Advisory Lock	1–3% (network)	Medium	Low
Optimistic (Version Column)	0.1–2% (retry storms)	Low	Medium
Pessimistic SKU-Level	0% (but high contention)	Low	High
Aetheria: Pessimistic Bin-Level	0.000%	Medium	High (Double-Entry)

---

Implementation in Aetheria (Go Microservice)

// InventoryService.Allocate(ctx, sku, qty, warehouseID)
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 (prevent deadlock)
        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
    })
}

---

Monitoring & Observability

Metric	Target	Alert
Lock Hold Time (P99)	< 5ms	> 20ms
Deadlock Rate	0/day	> 0
Lock Timeout Rate	< 0.01%	> 0.1%
Allocation Success Rate	100% (when stock exists)	< 99.9%
Phantom Allocation	0 (invariant)	Any

---

FAQ

What is the difference between optimistic and pessimistic locking?

Optimistic: read stock, check in app, write back — fails under concurrency. Pessimistic: SELECT ... FOR UPDATE locks the row at DB level — guarantees serializable execution. Aetheria uses pessimistic at the warehouse bin level.

Can't I just use Redis for inventory locking?

Redis locks are advisory — they don't prevent direct DB writes. Aetheria locks at the PostgreSQL row level (SELECT FOR UPDATE) which is enforceable by the database engine itself. Redis is used for auth (2.38µs JTI), not inventory.

What is a double-entry stock ledger?

Every stock movement creates two entries: a debit (source bin -qty) and a credit (destination bin +qty). Total debits = total credits always. This mirrors financial accounting and enables full reconciliation.

How does Aetheria handle 250K concurrent checkouts?

Each checkout locks the specific warehouse bin row (SELECT bin_qty FROM inventory_bins WHERE bin_id = $1 FOR UPDATE). The lock is held for <2ms. 20 concurrent on same SKU = serialized, zero oversell. Benchmark: 0 phantom allocations at 250K simultaneous.

---

Next Steps

• Request Inventory Demo — Live 20-concurrent checkout stress test
• Download Architecture Whitepaper
• Read Chronos-2 Forecasting Integration
• Compare All Inventory ERPs