Understanding the Root Cause: Inventory Over-Selling

A classic inventory over-selling issue arises when multiple conucrrent requests decrement stock without proper synchronization. Consider a scenario where product inventory is stored in MySQL, and the stock reduction logic is implemented as follows:

@Service
public class StockService {

    @Autowired
    private StockMapper stockMapper;

    public void checkAndLock() {
        // Step 1: Query current stock
        Stock stock = this.stockMapper.selectById(1L);

        // Step 2: Reduce stock if sufficient
        if (stock != null && stock.getCount() > 0) {
            stock.setCount(stock.getCount() - 1);
            this.stockMapper.updateById(stock);
        }
    }
}

This pattern is inherently non-atomic—two operations separated by a gap make it vulnerable to race conditions under high concurrency.

1. JVM-Level Locking: Local Synchronizasion

Single Instance, Non-Distributed Environment

To ensure thread safety within a single JVM instance, you can apply Java-level locking mechanisms:

Using synchronized:

@Service
public class StockService {

    @Autowired
    private StockMapper stockMapper;

    public synchronized void checkAndLock() {
        Stock stock = this.stockMapper.selectById(1L);
        if (stock != null && stock.getCount() > 0) {
            stock.setCount(stock.getCount() - 1);
            this.stockMapper.updateById(stock);
        }
    }
}

The synchronized keyword ensures only one thread can execute this method at a time, preventing concurrent modifications. This works well for single-instance applications.

Using ReentrantLock:

@Service
public class StockService {

    @Autowired
    private StockMapper stockMapper;

    private final ReentrantLock lock = new ReentrantLock();

    public void checkAndLock() {
        lock.lock();
        try {
            Stock stock = this.stockMapper.selectById(1L);
            if (stock != null && stock.getCount() > 0) {
                stock.setCount(stock.getCount() - 1);
                this.stockMapper.updateById(stock);
            }
        } finally {
            lock.unlock();
        }
    }
}

Both approaches work correctly in monolithic, single-node environments. However, they fail in distributed or multi-instance setups because each JVM instance maintains its own lock state—locks are not shared across instances.

With Transactional Context

Adding @Transactional does not resolve the problem. Consider this sequence:

Result: Two purchases, but only one stock deduction. The root cause is that the transaction isolation level allows dirty reads during the window between read and write.

2. Atomic Operation via Single SQL

A more robust solution is to perform the check-and-decrement operation atomically using a single UPDATE statement:

UPDATE db_stock 
SET count = count - 1 
WHERE product_code = '1001' AND count > 0;

This eliminates race conditions because the database handles both conditions in one atomic step. InnoDB locks the affected row(s), ensuring no other transaction can modify them concurrently.

Advantages:

• Eliminates need for application-level locks
• Works across multiple instances and clusters
• High reliability under load

Limitations:

• Only valid for products with a single global stock entry
• Cannot track pre-update and post-update states
• May escalate to table-level locks if product_code lacks an index

Even with indexing, full table scans can occur due to query plan issues. Thus, this approach is limited to simple scenarios.

3. MySQL Pessimistic Locking: SELECT ... FOR UPDATE

Use FOR UPDATE to acquire a row-level exclusive lock during the read phase:

SELECT * FROM tb_stock WHERE product_code = '1001' FOR UPDATE;

If product_code has an index, this applies a row lock; otherwise, it escalates to a table lock.

Implementation:

@Service
public class StockService {

    @Autowired
    private StockMapper stockMapper;

    @Transactional
    public void checkAndLock() {
        Stock stock = this.stockMapper.selectStockForUpdate(1L);
        if (stock != null && stock.getCount() > 0) {
            stock.setCount(stock.getCount() - 1);
            this.stockMapper.updateById(stock);
        }
    }
}

This ensures that subsequent requests block until the current transaction commits. It’s effective but introduces performance overhead.

Drawbacks:

• Lower throughput compared to atomic UPDATE
• Risk of deadlocks when acquiring locks in inconsistent order

Example deadlock scenario:

4. MySQL Optimistic Locking: Version-Based Control

Optimistic locking assumes conflicts are rare. It uses a version number field to detect stale updates.

Schema enhancement: Add a version column to the db_stock table.

Logic:

public void checkAndLock() {
    Stock stock = this.stockMapper.selectById(1L);
    if (stock == null || stock.getCount() <= 0) return;

    Long expectedVersion = stock.getVersion();
    stock.setCount(stock.getCount() - 1);
    stock.setVersion(expectedVersion + 1);

    int updatedRows = this.stockMapper.update(
        stock,
        new UpdateWrapper<Stock>()
            .eq("id", stock.getId())
            .eq("version", expectedVersion)
    );

    if (updatedRows == 0) {
        // Conflict detected — retry
        checkAndLock();
    }
}

Important Notes:

• Do not use @Transactional on this method. Without explicit rollback control, failed retries keep holding database connections.
• High contention leads to repeated retries, increasing CPU usage and potentially causing stack overflow.
• In distributed databases (e.g., MySQL replication, MyCAT), version checks may fail due to replication lag.
• Many middleware layers do not support pessimistic locks, making optimistic locking unreliable in read-write split architectures.

When to Use Each Approach:

In summary, prefer atomic SQL updates for simplicity and performance. Use pessimistic locking when writes are frequent and conflict risk is high. Reserve optimistic locking for low-contention, read-dominant workloads.