Concurrency Control on Stock Quantities
The 3-Line Engineering Rule
Never check stock with a simple `SELECT` and decrement with a separate `UPDATE`; use atomic database updates or distributed Lua scripts.
During flash sales, 1,000 concurrent requests reading remaining stock = 1 will all believe stock is available, leading to massive overselling.
Use atomic SQL `UPDATE stock = stock - 1 WHERE id = :id AND stock >= 1` or Redis atomic Lua scripts for flash-sale throughput.
Architectural Blueprint & Sequence
Hover over nodes to inspect state transitions❌ Check-Then-Act Race
Read qty=1 -> Wait -> Write qty=0 (Oversold!)
✅ Atomic SQL Conditional
UPDATE items SET qty=qty-1 WHERE qty>=1
⚡ Redis Atomic Lua
if redis.call('get', key) >= qty then decr
Key Engineering Takeaways
- Avoid check-then-act race conditions; the check and mutation must be atomic.
- Optimistic locking (`@Version`) is best for low contention; it fails fast under high contention.
- Pessimistic locking (`SELECT FOR UPDATE`) holds database locks, which bottlenecks throughput under flash sales.
- Redis Lua script (`DECRBY` with check) offloads thousands of QPS from the primary database.
The Problem & Production Reality
Inventory overselling is one of the most common critical bugs in software engineering. When thousands of users race to purchase the last 5 concert tickets, a naive application checking `if (stock > 0)` will suffer from a classic time-of-check to time-of-use (TOCTOU) race condition.
Anti-Pattern: Check-Then-Act in Application Memory
Fetching the inventory entity, checking the quantity in Java/Node, and updating it in a separate SQL query.
// ❌ ANTI-PATTERN: Fatal race condition
Inventory inv = repo.findById(productId); // Thread A & B both read qty = 1
if (inv.getQuantity() > 0) {
inv.setQuantity(inv.getQuantity() - 1);
repo.save(inv); // Both threads write qty = 0, item sold twice!
}Production Pattern: Atomic Conditional SQL or Redis Lua
Execute atomic conditional decrements in SQL, checking affected rows, or handle ultra-high traffic with atomic Redis Lua scripts.
// ✅ PRODUCTION PATTERN 1: Atomic SQL with Affected Row Check
@Modifying
@Query("UPDATE Inventory i SET i.quantity = i.quantity - :qty " +
"WHERE i.productId = :productId AND i.quantity >= :qty")
int atomicDeductStock(@Param("productId") String productId, @Param("qty") int qty);
// Service implementation
public boolean purchase(String productId, int qty) {
int rowsUpdated = inventoryRepo.atomicDeductStock(productId, qty);
if (rowsUpdated == 0) {
throw new OutOfStockException("Insufficient stock for product: " + productId);
}
return true; // Deduction succeeded atomically!
}
// ✅ PRODUCTION PATTERN 2: High-throughput Redis Lua Script
-- Atomic Stock Deduction Lua Script
local key = KEYS[1]
local qty = tonumber(ARGV[1])
local current = tonumber(redis.call('get', key) or '0')
if current >= qty then
redis.call('decrby', key, qty)
return 1 -- SUCCESS
else
return 0 -- OUT OF STOCK
endTrade-off Analysis & Staff Recommendations
| Aspect | Pros / Strengths | Cons / Hazards | Staff Recommendation |
|---|---|---|---|
| Atomic SQL vs Redis In-Memory Reservation | Atomic SQL is 100% durable in PostgreSQL without data sync lag. | High contention on a single row saturates database row locks. | Use Atomic SQL for standard traffic (< 500 QPS/item); use Redis Lua scripts for flash-sale spikes (> 10,000 QPS). |
| Pessimistic vs Optimistic Locking | Optimistic locking (`@Version`) never locks the DB row during reads. | Under heavy contention, 99% of transactions fail and retry, spiking CPU. | Use Atomic conditional UPDATEs rather than optimistic retry loops. |