Two Users Try to Purchase the Last Product Simultaneously — How Will You Avoid Overselling?
Overselling is one of the most common concurrency problems in e-commerce, banking, ticket booking, and inventory management systems.
Real-Time Example
Suppose:
Available Product Quantity = 1
Scenario
User A clicks BUY User B clicks BUY Same Time
Problem
Without proper handling:
Both Orders Get Confirmed
Result
Inventory = -1
This Problem Is Called
Overselling / Race Condition / Concurrent Update Problem
Industries Where This Is Critical
- E-commerce
- Flight Booking
- Movie Ticket Booking
- Hotel Reservation
- Banking
- Stock Trading
Main Goal
Only One User Should Successfully Purchase
Production-Level Techniques to Prevent Overselling
- Pessimistic Locking
- Optimistic Locking
- Atomic Database Updates
- Distributed Locks
- Queue-Based Processing
- Inventory Reservation
- Event-Driven Processing
- Idempotency
- Caching with Synchronization
- Saga Pattern
Understanding the Core Problem
Without Locking
Stock = 1
User A Reads Stock
Stock = 1
User B Reads Stock
Stock = 1
User A Updates Stock
Stock = 0
User B Also Updates
Stock = -1
Result
Overselling Happened
1. Pessimistic Locking
Pessimistic locking assumes conflicts will happen.
Idea
Lock Row Before Update
SQL Example
SELECT * FROM products WHERE product_id = 101 FOR UPDATE;
Flow
User A Locks Product Row
↓
User B Waits
↓
User A Completes Purchase
↓
Lock Released
↓
User B Checks Updated Stock
Benefits
- Strong consistency
- Prevents overselling completely
Problems
- Reduced performance
- Thread blocking
- Deadlock risk
- Poor scalability under heavy traffic
Spring Boot JPA Example
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("SELECT p FROM Product p
WHERE p.id = :id")
Product findByIdForUpdate(Long id);
2. Optimistic Locking
Optimistic locking assumes conflicts are rare.
Idea
Use Version Column
Table Example
| Product | Quantity | Version |
|---|---|---|
| Laptop | 1 | 5 |
Flow
User A Reads Version 5 User B Reads Version 5
User A Updates Successfully
Version Becomes 6
User B Update Fails
Because version changed.
Benefits
- Better performance
- No DB row blocking
- Highly scalable
Problems
- Retry logic required
- Conflicts under high concurrency
Spring Boot Example
@Entity
public class Product {
@Id
private Long id;
private Integer quantity;
@Version
private Long version;
}
How It Works
UPDATE product SET quantity = ?, version = version + 1 WHERE id = ? AND version = ?
If Version Changed
Update Count = 0
Meaning
Another User Already Purchased
3. Atomic Database Update
This is one of the most common production solutions.
Idea
Decrease Stock Only If Quantity > 0
SQL Example
UPDATE products SET quantity = quantity - 1 WHERE product_id = 101 AND quantity > 0;
How It Works
- Only one transaction succeeds
- Second update affects zero rows
Benefits
- Very fast
- Simple implementation
- Highly scalable
- No explicit locking required
Best For
- E-commerce
- Flash sales
- Inventory systems
Production Java Example
@Modifying @Query( "UPDATE Product p " + "SET p.quantity = p.quantity - 1 " + "WHERE p.id = :id " + "AND p.quantity > 0" ) int reduceStock(Long id);
Flow
If Updated Rows = 1
↓
Purchase Success
If Updated Rows = 0
↓
Out Of Stock
4. Distributed Locking
In distributed microservices, multiple application instances run.
Problem
Instance 1 Instance 2 Instance 3
Need
Global Lock Across All Instances
Production Solution
- Redis Distributed Lock
- Zookeeper Lock
- Etcd Lock
Redis Lock Example
SET product_101_lock value NX EX 10
Meaning
| Command | Purpose |
|---|---|
| NX | Create only if not exists |
| EX 10 | Auto-expire after 10 seconds |
Flow
Acquire Lock
↓
Process Order
↓
Release Lock
Benefits
- Works across distributed systems
- Prevents concurrent updates
Problems
- Network latency
- Complexity
- Lock expiration handling
5. Queue-Based Sequential Processing
Instead of processing requests directly:
Process One By One Through Queue
Architecture
Users ↓ Kafka/RabbitMQ Queue ↓ Single Inventory Consumer ↓ Database Update
Benefits
- No race conditions
- Ordered processing
- Highly reliable
Problems
- Additional latency
- Complexity
Best For
- Flash sales
- Ticket booking systems
- High concurrency systems
6. Inventory Reservation Pattern
Temporary stock reservation before payment completion.
Flow
Reserve Product
↓
Payment Processing
↓
Confirm Order
If Payment Fails
Release Reservation
Benefits
- Prevents overselling
- Handles payment delays
- Improves consistency
Example
| State | Meaning |
|---|---|
| AVAILABLE | Can purchase |
| RESERVED | Temporary hold |
| SOLD | Purchase completed |
7. Saga Pattern in Microservices
Inventory update is part of distributed transaction.
Example Flow
Create Order
↓
Reserve Inventory
↓
Process Payment
↓
Confirm Order
If Payment Fails
Compensation Transaction
↓
Release Inventory
Benefits
- Handles distributed consistency
- Supports rollback logic
8. Idempotency
Prevent duplicate processing.
Problem
Same Payment Request Sent Twice
Solution
Use Unique Idempotency Key
Example
X-IDEMPOTENCY-KEY: txn-123
Benefits
- Prevents duplicate orders
- Ensures safe retries
9. Cache + Database Synchronization
High-traffic systems use Redis cache.
Architecture
Redis Stock Counter
↓
Database Synchronization
Benefits
- Very fast
- Handles huge traffic
Problem
- Cache consistency challenges
- Synchronization complexity
10. Event-Driven Inventory Management
Inventory changes published as events.
Flow
Inventory Reduced Event
↓
Kafka
↓
Update Other Systems
Benefits
- Loose coupling
- Real-time synchronization
Real Production E-Commerce Example
Problem
Flash sale with:
10,000 users 1,000 products
Challenges
- Huge concurrency
- Overselling risk
- Database overload
Production Solution Used
- Redis inventory counters
- Kafka queue
- Atomic DB updates
- Inventory reservation
- Idempotency keys
- Saga orchestration
Flow
User Request
↓
Redis Decrease Stock
↓
Push To Kafka
↓
Order Consumer
↓
Database Update
↓
Payment Processing
Result
- No overselling
- High scalability
- Millions of requests handled
Which Technique Is Best?
| Technique | Best Use Case |
|---|---|
| Pessimistic Lock | Low concurrency systems |
| Optimistic Lock | Moderate concurrency |
| Atomic Update | High-performance systems |
| Distributed Lock | Multi-instance systems |
| Queue Processing | Very high concurrency |
| Reservation Pattern | Payment workflows |
Production Best Practices
| Practice | Purpose |
|---|---|
| Atomic Updates | Prevent race conditions |
| Optimistic Locking | Scalable concurrency handling |
| Redis Locks | Distributed coordination |
| Kafka Queue | Sequential processing |
| Reservation Pattern | Temporary stock hold |
| Idempotency | Duplicate prevention |
| Saga Pattern | Distributed consistency |
Final Interview Answer
To avoid overselling when two users try to purchase the last product simultaneously, I would use concurrency control mechanisms such as optimistic locking, pessimistic locking, or atomic database updates depending on system requirements. In high-scale production systems, atomic SQL updates like reducing stock only when quantity is greater than zero are commonly used because they are fast and scalable. In distributed microservices environments, I would combine Redis distributed locks, Kafka-based sequential processing, inventory reservation patterns, and Saga orchestration to ensure consistency across services. Additionally, I would implement idempotency to prevent duplicate order processing and use Redis caching carefully for high-performance inventory management. The final goal is to ensure that only one transaction successfully purchases the last item while maintaining scalability, consistency, and resilience in production systems.