Scenario: The Flash Sale Race Condition
An e-commerce platform is hosting a flash sale for a limited-edition smartphone. There is exactly one unit left in the database. User A and User B click “Checkout” at the exact same millisecond.
Q:If the backend application simply executes a `SELECT` to check inventory, sees '1', and then executes an `UPDATE` to decrement it, what goes wrong here? Reveal â–¾
SELECT statements concurrently before either executes their UPDATE, they both read the inventory value as ‘1’. Both applications assume the item is available, proceed to process the order, and decrement the database. The inventory drops to ‘-1’, and the company has sold an item they do not possess.Q:How can you solve this purely at the relational database level without locking the entire inventory table and freezing all other customers? Reveal â–¾
SELECT ... FOR UPDATE. When User A’s transaction reads the row, the database locks that specific row. When User B attempts to read it, their transaction is forced to wait until User A commits or rolls back. User A buys the phone, the inventory becomes 0, the lock is released, and User B’s read now correctly returns 0.Q:Locking is slow. The lead architect suggests using 'Optimistic Concurrency Control' instead. How does that work, and what happens to User B? Reveal â–¾
Optimistic Concurrency Control assumes conflicts are rare and avoids database locks entirely. Instead, every row gets a version column.
When User A and B read the row, they both see version = 1.
The application sends the update command: UPDATE inventory SET count = count - 1, version = version + 1 WHERE id = 123 AND version = 1.
User A’s query executes first, modifying the row and changing the version to 2. When User B’s identical query hits the database, it fails because version = 1 is no longer true. The database rejects User B’s update, and the application must catch this exception and inform User B the item is sold out.
Q:The company shifts to a microservices architecture. Inventory and Billing are now separate databases. User A buys the item, the Inventory database decrements via optimistic concurrency, but then the Billing microservice crashes before taking payment. How do you roll back the Inventory? Reveal â–¾
This breaks standard ACID compliance because we cross database boundaries. We must abandon standard transactions and use a distributed transaction protocol like the Saga Pattern.
In a Saga, each microservice executes a local transaction and publishes an event. If Billing fails, it publishes a “PaymentFailed” event. The Inventory microservice listens for this event and executes a Compensating Transaction—a completely new transaction that increments the inventory back by 1 to reverse the initial action.
Q:What happens if the Inventory service receives the 'PaymentFailed' event, attempts to execute the compensating transaction, but the database disk is full and it fails? Reveal â–¾
The system is now in an Inconsistent State. The item is reserved, the customer wasn’t charged, and the automated rollback failed.
This is the fundamental trade-off of Eventual Consistency (BASE) vs ACID. To mitigate this, engineers must implement a Dead-Letter Queue (DLQ). The failed compensating event is pushed to the DLQ, triggering high-priority alerts for the DevOps team. The system remains partially broken until an engineer manually intervenes to fix the disk space and replay the event, or manually corrects the database entry.
Variations & Real-World Impact
- Financial Tech (FinTech): In banking ledgers, optimistic concurrency is heavily preferred over locking to maintain high throughput. However, compensating transactions are highly regulated. You cannot simply “delete” a failed transaction; you must append a new, mathematically inverted ledger entry to maintain a strict audit trail.
- Dirty Reads: If the database isolation level is set too low (e.g., Read Uncommitted), analytic dashboards might query the inventory while User A is halfway through checkout, displaying artificially deflated stock levels before a potential rollback occurs.
Discussion & Comments