Mastering Race Conditions in E-Commerce Inventory: Locks, Redis & Atomic SQL

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!

+---------------------+-------------------+-------------------+--------------------+
| 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|
+---------------------+-------------------+-------------------+--------------------+

UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 'prod-123' AND stock >= 1;

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.');
}

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