Design an E-Commerce Platform (Amazon)

In this approach, when a checkout process begins, we place an exclusive lock on the inventory row for each product in the cart.

1. BEGIN TRANSACTION;
2. SELECT stock_quantity FROM Inventory WHERE product_id = 'X' FOR UPDATE;
3. The FOR UPDATE clause locks the row. No other transaction can read or write to this row until the current transaction commits or rolls back.
4. If stock_quantity is sufficient, we update the count: UPDATE Inventory SET stock_quantity = stock_quantity - 1 WHERE product_id = 'X';
5. COMMIT; Pros: Guarantees consistency. It's impossible to oversell. Cons: Can hurt performance and increase latency, as other checkout processes for the same popular item must wait for the lock to be released. This can lead to poor user experience, especially during flash sales.

This approach avoids long-lived locks. Instead, we add a version number to the inventory table.

1. Read the stock_quantity and version for the product.
2. In the application, check if the quantity is sufficient.
3. If it is, attempt to update the stock: UPDATE Inventory SET stock_quantity = stock_quantity - 1, version = version + 1 WHERE product_id = 'X' AND version = {read_version};
4. Check how many rows were affected by the update.
   • If 1 row was affected, the transaction was successful.
   • If 0 rows were affected, it means another transaction updated the row (and incremented the version) between our read and write. Our transaction has failed, and we must roll back and inform the user the item is out of stock. Pros: More performant and scalable than pessimistic locking as it doesn't hold locks. Cons: Can lead to higher rates of transaction failure under high contention, which might be frustrating for users.

In a microservices architecture, the inventory logic might be handled by multiple instances of an Inventory Service. In this case, a simple database row-level lock isn't enough, as the contention is happening at the service layer. A distributed lock is required.

1. Before processing a checkout, the service instance must acquire a lock for each product_id in the cart from a distributed locking service like Redis or Zookeeper.
2. A common implementation uses a Redis command like SET product:lock:{product_id} "locked" NX PX 30000. The NX option means the key is only set if it doesn't already exist, making the operation atomic. The PX sets an expiry time (e.g., 30 seconds) to prevent permanent deadlocks if a service instance crashes.
3. If the lock is acquired successfully, the service can safely perform the read-modify-write operations on the inventory database.
4. After the database transaction is complete, the service must explicitly release the lock by deleting the Redis key. Pros: Provides strong consistency across a distributed system, preventing race conditions between multiple service instances. Cons: Introduces a new dependency (Redis/Zookeeper) and adds network latency to the checkout process. It's also more complex to implement correctly, with risks like deadlocks or lock-release failures that need to be handled carefully.