Ensuring Inventory Consistency Under Concurrent Operations: Locking Pitfalls and Solutions

Working in JD retail supply‑chain inventory, where stock quantities are increased and decreased constantly, any mistake in concurrent operations can cause front‑end sales losses, making consistency under concurrency a critical concern.

While locking often resolves concurrency problems, it does not guarantee safety; lock failures can still occur. This article uses a real online case to dissect common mistakes in applying locks and offers corresponding remedies.

When multiple processes or threads access the same resource simultaneously, classic concurrency problems arise—for example, two bank tellers modifying the same account at the same time, leading to an incorrect final balance.

The same issue appears in retail inventory: concurrent increments/decrements can produce wrong stock numbers, affecting sales.

01 Introduction

The core problem is that locking is placed incorrectly, causing dirty reads and lock ineffectiveness.

Consider the following workflow and images; ask whether concurrency issues exist.

1. Before locking, the box detail data is fetched outside the lock, leading to dirty reads.

2. After locking, batch‑wise box shelving data is processed.

3. After locking, the box shelving quantity is updated using the pre‑lock (dirty) data plus the incoming batch data, directly updating the row.

02 Problem – Lock Failure

The root causes are:

Distributed lock misuse: a lock is acquired but the code still reads data before the lock, causing dirty reads.

MySQL lock failure: the database update occurs without any row‑level lock, allowing dirty data to overwrite the current row.

Idempotency codes (anti‑duplicate codes) do not help here because they are meant for the same request repeated, whereas the conflicting operations are from different requests with different codes.

03 Cause – Incorrect Lock Placement

The core issue is that the data fetch happens outside the critical section, so even with a lock, stale data is used.

04 Solutions

1. Code level: Use proper locks (mutex, read‑write, distributed) and ensure all data retrieval happens after acquiring the lock, serializing the batch queries and updates.

The refactored code (shown in the next image) moves the box‑detail query inside the lock.

2. Database level: Implement transaction management with appropriate isolation levels to prevent dirty reads, and consider optimistic or pessimistic locking for concurrent updates. Design queries efficiently to reduce lock contention.

Database locking complements application‑level locking, acting as a safety net when business code cannot guarantee consistency.

Examples of optimistic and pessimistic lock updates are illustrated below:

Extended Solutions

Application design: Avoid long‑lived DB connections or transactions, use AI‑assisted code review to spot potential concurrency hotspots, set appropriate lock granularity, and prevent lock loss.

Network load layer: Apply rate limiting, use distributed databases with sharding, and employ load balancers to spread traffic and reduce node‑level contention.

Request layer: Implement front‑end anti‑duplicate mechanisms and idempotent APIs to minimize repeated requests that could cause consistency issues.

By applying these measures across multiple layers, systems can effectively prevent concurrency problems and maintain data consistency.

END