The "Overselling" Problem
You have 1 item left. 100 users buy it. If you rely on Reading the stock before Writing it without locks, you will oversell.
Golden Rule: Inventory is the source of truth. It requires Strict
Consistency. Do not use Eventual Consistency for specific stock counters (e.g., "Available to
Promise").
The Reservation Pattern (Two-Phase)
Do not decrement stock immediately. Reserve it first.
1. Add to Cart
→
2. Reserve Stock (TTL 15m)
→
3. Payment Success
→
4. Confirm (Hard Deduced)
If step 3 fails or times out, the reservation expires and stock returns to pool.
Data Modeling: SQL vs NoSQL
For inventory, Row-level Locking is your friend.
🐘 SQL (Postgres/MySQL)
Best for strict ACID compliance. Use `SELECT ... FOR UPDATE` or atomic updates.
UPDATE inventory
SET
available = available - 1,
reserved = reserved + 1
WHERE sku = 'A123' AND available > 0;
-- Returns Row Count: 1 (Success) or 0 (Oversold)🍃 NoSQL (DynamoDB/Redis)
Best for extreme scale. Use Conditional Writes or Compare-and-Swap.
# DynamoDB Conditional Update
table.update_item(
Key={'sku': 'A123'},
UpdateExpression="SET available = available - :val",
ConditionExpression="available >= :val",
ExpressionAttributeValues={':val': 1}
)Warehousing Reality
Inventory is physical. You need to model Locations (bins, shelves) and SKUs.
| Entity | Description |
|---|---|
| SKU (Stock Keeping Unit) | Unique identifier for a sellable product variant (e.g., T-Shirt Red Size M). |
| Bin Location | Physical spot in a warehouse (e.g., Aisle 4, Shelf B, Bin 12). |
| ATP (Available to Promise) | Virtual count showing what you can sell online (`Total On Hand - Reserved - Damaged`). |
Summary
- Consistency: Never compromise. Use Database constraints to prevent negative stock.
- Reservations: Temporarily hold stock during checkout to improve UX and accuracy.
- Reconciliation: Always have a background job to fix "drift" between physical counts and digital records.