Common Concurrency Interview Question: Handling High-Traffic Purchases and Key Approaches

In technical and system design interviews, one of the most frequently asked questions is: "How do you handle thousands of users attempting to purchase the same product at the exact same second without overselling or crashing the database?"

This article walks through the common approaches to this problem—including patterns you might already be using in production—evaluating the advantages and trade-offs of each, and presenting an optimal solution using the Reservation Pool Pattern.

(Note on Redis: You could introduce an in-memory Redis cluster to decrement stock counters using atomic operations. However, from my perspective, introducing additional infrastructure brings substantial operational overhead, higher hosting costs, and added maintenance burden. Furthermore, it introduces distributed dual-write complexity and difficult rollback handling between cache and database. For these reasons, this article focuses strictly on solving the problem end-to-end within a single relational database transaction).

---

1. Approach 1: DB Constraint (CHECK > 0)

In this approach, you avoid explicit locking (FOR UPDATE) and instead embed the condition directly into the UPDATE statement alongside a database-level integrity constraint:

-- Database-level integrity constraint
ALTER TABLE inventory ADD CONSTRAINT chk_stock_positive CHECK (stock >= 0);

-- Atomic update statement
UPDATE inventory 
SET stock = stock - 1 
WHERE item_id = 99 AND stock > 0;

The application checks the result: if affected rows > 0, the purchase succeeded; if 0, the item is out of stock.

Advantages:

• Simple to implement with no changes to table structure.
• Guarantees zero overselling: The CHECK constraint and WHERE stock > 0 condition enforce data integrity at the database engine level.
• Eliminates an initial SELECT round-trip.

Trade-offs:

• Implicit Row Lock: The UPDATE statement still acquires an exclusive lock on that row. Thousands of requests remain serialized.
• Connection Pool Saturation: While waiting for the row lock to release, waiting requests hold active database connections, leading to connection pool exhaustion during traffic spikes.
• Tail Latency on Stockout: If 100 items remain and 5,000 requests arrive, the last 4,900 requests queue sequentially just to eventually receive affected rows = 0.

---

2. Approach 2: Row-Level Lock (FOR UPDATE)

The traditional approach explicitly locks the inventory record before executing application logic:

START TRANSACTION;

SELECT stock FROM inventory WHERE item_id = 99 FOR UPDATE;

-- Application checks: if stock > 0, decrement inventory
UPDATE inventory SET stock = stock - 1 WHERE item_id = 99;

COMMIT;

Advantages:

• Enforces strict serializability.
• Allows application code to execute complex business rules and validations before committing the change.
• Well-suited for single-user idempotency (e.g., preventing duplicate orders when a user clicks the checkout button multiple times due to network delay).

Trade-offs:

• Severe Hot-Row Contention: When thousands of distinct users compete for the exact same record, all transactions queue sequentially behind a single row lock.
• Prolonged Connection Retention: Connection holding time grows linearly with queue depth, quickly depleting the connection pool and causing timeout errors across unrelated services.

---

3. Approach 3: Reservation Pool & SKIP LOCKED

Instead of directing all traffic to a single row, this approach partitions available stock into discrete records (a pool of slots) and allows transactions to claim slots concurrently using SKIP LOCKED.

3.1. Workflow & Architecture

3.2. Implementation

-- 1. Master ledger tracking total counts
CREATE TABLE inventory_ledger (
    product_id VARCHAR(50) PRIMARY KEY,
    total_stock INT NOT NULL,
    sold_count INT NOT NULL DEFAULT 0
);

-- 2. Reservation pool where each record represents an allocatable slot
CREATE TABLE reservation_pool (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id VARCHAR(50) NOT NULL,
    status ENUM('available', 'reserved', 'sold') NOT NULL DEFAULT 'available',
    reserved_by VARCHAR(100) NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_product_status (product_id, status)
);

Transaction execution:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

-- Claim 1 available slot, automatically bypassing slots locked by active transactions
SELECT id FROM reservation_pool 
WHERE product_id = 'SNEAKER-99' AND status = 'available' 
LIMIT 1 
FOR UPDATE SKIP LOCKED;

-- Update the claimed slot to reserved
UPDATE reservation_pool 
SET status = 'reserved', reserved_by = 'USER_101' 
WHERE id = :slot_id;

-- Increment sold counter in master ledger
UPDATE inventory_ledger 
SET sold_count = sold_count + 1 
WHERE product_id = 'SNEAKER-99';

COMMIT;

Advantages:

• Parallel Execution: Transactions do not block one another. Subsequent transactions automatically skip locked slots and claim the next available row.
• Minimal Connection Holding Time: Each transaction holds a connection for only 2ms to 5ms before returning it to the pool, preventing connection exhaustion.
• Instant Stockout Detection: When no slots remain, the query returns an empty result set immediately without queue delay.
• 100% ACID Consistency: All operations complete within a single database transaction.

Trade-offs:

• Requires schema adjustments and an additional pool table.
• Requires a replenishment mechanism (e.g., maintaining a buffer of 500 to 1,000 active rows from the ledger rather than pre-generating rows for the entire catalog).

---

4. Isolation Level: Why READ COMMITTED

When implementing SKIP LOCKED, configuring the transaction isolation level is mandatory: it must run under READ COMMITTED.

The key differences between database engines:

• MySQL (InnoDB): The default isolation level is REPEATABLE READ. Under this level, InnoDB places Gap Locks across index intervals. When multiple transactions scan for slots concurrently, overlapping Gap Locks block one another, neutralizing SKIP LOCKED and causing Deadlocks. Switching to READ COMMITTED disables gap locking for index scans.
• PostgreSQL: The default is already READ COMMITTED. If set to REPEATABLE READ, PostgreSQL uses Snapshot Isolation and aborts transactions with ERROR: could not serialize access due to concurrent update when a row is modified by another committed transaction. Under READ COMMITTED, PostgreSQL's internal EvalPlanQual mechanism automatically re-evaluates the latest row version and skips locked or committed rows cleanly without errors.

For an in-depth database engine analysis, refer to:

[

Deep Dive: Why SKIP LOCKED Requires READ COMMITTED in MySQL and PostgreSQL

Common Concurrency Interview Question: Handling High-Traffic Purchases and Key ApproachesIn technical and system design interviews, one of the most frequently asked questions is: “How do you handle thousands of users attempting to purchase the same product at the exact same second without overselling or crashing the database?” This article walks

Quang's Tech CornerQuangPV

](https://blog.quangpv.top/deep-dive-why-skip-locked-requires-read-committed-in-mysql-and-postgresql/)

---

5. Summary Comparison

Evaluation Metric	Approach 1: Constraint > 0	Approach 2: Row Lock (FOR UPDATE)	Approach 3: Pool Pattern + SKIP LOCKED
Locking Mechanism	Implicit lock on UPDATE	Explicit exclusive row lock	Row lock on individual slot; skips busy rows
Concurrency Model	Serialized (Blocking)	Serialized (Blocking)	Parallel (Non-blocking)
Connection Holding Time	Increases with queue depth	Increases with queue depth	Minimal (2ms – 5ms)
Connection Pool Risk	High during traffic surges	High during traffic surges	Very low
Out-of-Stock Latency	Must wait through queue	Must wait through queue	Instantaneous (empty result in ~2ms)
Implementation Complexity	Very low	Low	Moderate

---

6. Conclusion

Hopefully, these approaches give you broader perspectives on handling concurrency trade-offs and provide a clear edge in your technical interviews.

Being able to explain when a simple constraint is sufficient versus when an advanced pattern like a Reservation Pool with SKIP LOCKED is required demonstrates practical engineering maturity under real-world constraints.