Mastering Race Conditions in E-Commerce Inventory: Locks, Redis & Atomic SQL
1. The High-Concurrency Inventory Dilemma
During flash sales or high-traffic product launches, hundreds of users click "Buy Now" for the last remaining item in stock.
If code reads the stock, checks if stock > 0, and then updates the database sequentially in separate statements:
User A: Reads Stock = 1
User B: Reads Stock = 1
User A: Deducts Stock -> New Stock = 0 -> Order Created
User B: Deducts Stock -> New Stock = -1 -> 💥 OVERSELLING DISASTER!
2. Concurrency Control Strategies
+---------------------+-------------------+-------------------+--------------------+
| Strategy | Throughput | DB Load | Implementation |
+---------------------+-------------------+-------------------+--------------------+
| Atomic SQL Update | High | Low | UPDATE ... WHERE > 0|
| Optimistic Locking | High (Low Conflict)| Moderate (Retries)| Version Column |
| Pessimistic Locking | Low (Queueing) | High (Row Locks) | SELECT FOR UPDATE |
| Distributed Lock | Very High | Offloaded to Redis| Redlock / Lua script|
+---------------------+-------------------+-------------------+--------------------+
3. Solution 1: Atomic Database Updates (Single Statement)
The simplest and most resilient relational database approach is conditional atomic execution:
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 'prod-123' AND stock >= 1;
If the update affects 0 rows, the stock is sold out. No explicit transaction locks needed!
4. Solution 2: Optimistic Locking with Versioning
async function purchaseWithOptimisticLock(productId: string, quantity: number) {
const maxRetries = 3;
for (let attempt = 0; attempt < maxRetries; attempt++) {
const product = await db.product.findUnique({ where: { id: productId } });
if (product.stock < quantity) throw new BadRequestException('Out of stock');
const result = await db.product.updateMany({
where: {
id: productId,
version: product.version, // Ensure version has not changed
stock: { gte: quantity },
},
data: {
stock: { decrement: quantity },
version: { increment: 1 },
},
});
if (result.count > 0) return { success: true };
// Wait briefly with random jitter before retrying
await new Promise((r) => setTimeout(r, Math.random() * 50));
}
throw new ConflictException('Transaction conflict. Please try again.');
}
5. Solution 3: High-Scale Redis Inventory Reservation (Lua Script)
-- Atomic check and decrement in Redis
local stock = redis.call('get', KEYS[1])
if not stock or tonumber(stock) < tonumber(ARGV[1]) then
return 0
else
redis.call('decrby', KEYS[1], ARGV[1])
return 1
end
6. Key Takeaways
- Never read stock and write stock in two disconnected non-atomic database queries.
- Use atomic SQL conditions for moderate loads.
- Offload extreme flash-sale spikes to Redis Lua scripts with asynchronous database synchronization.
