Chapter 8: Stock Reservation and Concurrency

Why This Exists

In a digital store, physics doesn't apply. In a physical store, if there is one PlayStation left on the shelf, the person who picks it up has it. Nobody else can touch it. Online, 10,000 people can "look" at the last PlayStation and click "Add to Cart" at the exact same millisecond. Stock reservation and concurrency architectures exist to enforce the laws of physics in a digital world—ensuring that one physical item can only be bought by one person, preventing the catastrophic failure of overselling.

Real World Problem

A sneaker company releases a limited-edition shoe with only 50 pairs in stock. User A goes to checkout and enters their credit card. User B goes to checkout a second later. Because the database hasn't updated User A's purchase yet (waiting for the payment gateway), the system tells User B the shoe is in stock. Both payments process. The database drops to -1. The company has to email User B, cancel their order, and face a social media backlash.

Everyday Analogy

Think about buying concert tickets on Ticketmaster. When you select a seat, the system says, "These tickets are reserved for you for 5:00 minutes to complete your purchase." During those 5 minutes, nobody else in the world can select those seats. If you don't pay in time, the timer expires, the lock is removed, and the seats go back to the public pool.

Beginner Explanation

When two people try to change the same data at the same time, computers get confused. This is called a Race Condition.

To fix it, we use Locks. When User A starts checking out, we "lock" the item in the database. When User B tries to check out, the database says, "Wait, this item is locked by someone else." User B has to wait until User A finishes (or fails).

Intermediate Explanation

At the database level, there are two primary ways to handle concurrency:

Pessimistic Locking: The system assumes conflicts will happen. When User A queries the inventory, they run SELECT * FROM inventory WHERE sku='PS5' FOR UPDATE. This literally freezes that row in the SQL database. No other query can touch it until User A completes the transaction. It's incredibly safe but creates massive bottlenecks during high traffic.
Optimistic Locking: The system assumes conflicts are rare. We add a version column to the table. User A reads the row (Version 1). They try to update it: UPDATE inventory SET count=count-1, version=2 WHERE sku='PS5' AND version=1. If User B already bought it and the version is now 2, User A's update fails, and the application tells User A they missed out.

Advanced Explanation

At scale (like Amazon or Shopify), database locking is too slow. They use Distributed Locks utilizing high-speed, in-memory datastores like Redis.

When a user initiates checkout, the system issues a SETNX (Set if Not eXists) command in Redis for the specific SKU lock. Crucially, this lock requires a TTL (Time to Live). If the checkout service crashes mid-payment, we cannot leave the inventory locked forever. The TTL ensures that after 10 minutes, the Redis lock automatically evaporates, returning the inventory to the Available pool (Available to Sell).

Real World Example

Cart Reservation vs. Checkout Reservation:

Shopify (Checkout Reservation): Shopify generally does not reserve stock when you add it to your cart. They reserve it the moment you initiate the final payment. This is why you can have an item in your cart, take 10 minutes to find your credit card, and hit "Pay" only to see an "Out of Stock" error.
Airlines/Ticketing (Cart Reservation): Because inventory is highly specific (Seat 12A), they reserve stock the moment it enters the cart. This provides a better user experience for the winner, but creates "Inventory Hoarding" problems where bots add everything to carts just to grief the system.

Architecture Design

Here is the flow of a Redis Distributed Lock during checkout:

sequenceDiagram
    participant User
    participant CheckoutAPI
    participant Redis
    participant Payment
    participant DB

    User->>CheckoutAPI: Click "Pay Now" (SKU: PS5)
    CheckoutAPI->>Redis: SET lock:PS5 EX 300 NX (Lock for 5 mins)
    
    alt Lock Acquired
        Redis-->>CheckoutAPI: OK
        CheckoutAPI->>Payment: Charge Credit Card
        Payment-->>CheckoutAPI: Success
        CheckoutAPI->>DB: Deduct Inventory
        CheckoutAPI->>Redis: DEL lock:PS5 (Release Lock)
        CheckoutAPI-->>User: Order Confirmed
    else Lock Failed
        Redis-->>CheckoutAPI: Nil (Already Locked)
        CheckoutAPI-->>User: Error: Item out of stock or processing
    end

Database Design

Optimistic Concurrency Control (OCC) Schema:

CREATE TABLE inventory (
    sku VARCHAR(100) PRIMARY KEY,
    available_quantity INT,
    version INT DEFAULT 1 -- The key to Optimistic Locking
);

The application must always include the version it read in the WHERE clause when updating.

API Design

Reserve Endpoint: POST /api/checkout/reserve Payload: { "cart_id": "123", "items": [{"sku": "PS5", "qty": 1}] } Response: { "reservation_id": "res_888", "expires_in": 300 }

Commit Endpoint: POST /api/checkout/commit Payload: { "reservation_id": "res_888", "payment_token": "tok_xyz" }

Production Considerations

Deadlocks: If User A's cart has [Shirt, Pants] and User B's cart has [Pants, Shirt], and they checkout simultaneously using Pessimistic Locking: User A locks the Shirt. User B locks the Pants. User A waits for the Pants. User B waits for the Shirt. The database freezes permanently. Always sort SKUs alphabetically before acquiring locks.
Clock Drift: If using distributed locks across multiple Redis nodes, slight differences in server clocks can cause locks to expire prematurely (see: Redis Redlock algorithm).

Security Considerations

Inventory Hoarding (Denial of Inventory): Malicious actors can write bots to add all stock to carts, triggering the 10-minute reservation locks, preventing real customers from buying, and then dropping the carts. To mitigate this, require CAPTCHAs for high-demand items or use Checkout Reservation instead of Cart Reservation.

Common Mistakes

Forgetting the TTL: Building a locking mechanism without an automatic expiration timer. If the server crashes, the item is locked permanently and can never be sold.
Locking the Entire Table: Running LOCK TABLES inventory; instead of locking the specific row. This pauses the entire business across all products just to process one order.

Tradeoffs and Alternatives

Pessimistic vs. Optimistic: Pessimistic is safe but slow (database bottleneck). Optimistic is fast but requires the application code to handle retry logic when a conflict occurs.
Overselling vs. User Friction: You can completely avoid complex locking by just letting everyone buy and dealing with the fallout later. Some businesses calculate that refunding 1% of users is cheaper than building a highly concurrent Redis locking architecture.

Interview Questions

Explain the difference between Optimistic and Pessimistic locking. Which would you use for a flash sale?
What is a Deadlock, and how do you prevent it when a user buys multiple items in one cart?
What is a Distributed Lock, and why is a TTL (Time to Live) mandatory?

Hands-On Exercise

Write a pseudocode SQL query demonstrating an Optimistic Lock update.
Assume the read version was 3.
What should the application do if the UPDATE returns 0 rows affected?

Key Takeaways

Concurrency issues (Race Conditions) occur when multiple users attempt to modify the same inventory simultaneously.
Pessimistic locking freezes data; Optimistic locking uses versioning.
High-scale systems use Distributed Locks (Redis) with strict TTL expirations to manage reservations.
Reserving stock at the Cart stage risks Inventory Hoarding; reserving at Checkout risks user frustration.