Inventory deduction with read-then-write keeps overselling—should I lock first, or combine validation and write into one statement?

Deducting stock with read-then-write keeps overselling, and in most cases the lock choice is not wrong—validation and write were not put into the same atomic operation. Based on 2026 project delivery experience, the judgment order is: first distinguish whether overselling comes from concurrent writes, request replay, or restock misalignment, then look at the write conflict intensity on the same row; for single-database, low-to-medium traffic daily orders, one conditional update statement plus a unique constraint is usually enough, and only when the same row is contended hundreds of times per second or more is it worth considering row locks, sharded inventory, or cache pre-deduction. The lock is an implementation means, not the conclusion.

Where overselling comes from: don't mix up the three root causes

Many people add a lock as soon as inventory goes negative, but overselling commonly comes from three root causes, and the fixes are not the same—locks clearly help only one of them. Identifying which type first can save a lot of wasted work.

The first is “read-then-write”: inventory still shows 10 at query time, but by write time another request has changed it to 0, and the gap between the two actions is cut in line. The second is request replay: a payment callback or message is consumed repeatedly, so the same deduction runs twice; a typical sign is that the number of deductions does not match the number of orders. The third is restock misalignment: when inventory is released after an unpaid timeout, quantities already occupied by other orders are added back, showing up as inventory that fluctuates and is hard to reproduce.

A simpler check starts with two things: can deduction be completed with one conditional UPDATE; can duplicate requests be stopped at the entrance. If the same order number appears twice as deduction records in the inventory ledger, it is likely not a lock problem, but idempotency not being done well.

• Read-then-write type: concurrent requests insert into the gap between query and write, showing negative inventory while order volume remains mostly normal.
• Replay type: the same request number is processed multiple times, showing deduction count that does not match order count.
• Restock type: timeout release and manual adjustment happen at the same time, showing inventory that fluctuates and is hard to reproduce.

Conditional update vs. row lock: who queues first makes the difference

The optimistic approach does not reserve a slot in advance and instead confirms at write time that the data has not been changed by someone else; common practices are an UPDATE with a condition that inventory is greater than or equal to the quantity to deduct, or a version number as the condition. The pessimistic approach locks the row first so others cannot read or write until the transaction ends; a common practice is SELECT with FOR UPDATE. The difference is not only performance, but conflict probability: when conflicts are low, conditional update avoids queuing overhead; when conflicts are high, row locks can reduce a large number of ineffective retries and rollbacks.

A fairly common delivery habit in 2026 is to use database conditional update as the baseline for ordinary orders, wait for load testing to confirm obvious single-row conflicts, and then add cache pre-deduction or sharded inventory—rather than introducing a distributed lock from the start. The workload and concurrency ranges in the comparison below come from common project ranges and should only be used as order-of-magnitude reference; the specifics still depend on your own load test results.

• Database conditional update: suitable for read-heavy, write-light workloads with low conflict probability; typical modification range is 1–3 person-days; typical failed retry range is 2–3 times; the default choice for daily orders.
• Database row lock: suitable for high-concurrency writes to the same row when transactions are short enough; typical lock wait upper limit is 1–3 seconds, and beyond that it should fail fast rather than keep queuing.
• Cache atomic deduction: suitable for flash-sale-level hot spots, deducting in cache first and asynchronously persisting to the database; the cost is temporary inconsistency and the need for a compensation path, with a typical modification range of 5–15 person-days.
• Queue serial deduction: suitable when the same product can accept eventual consistency, processed in order by product partition; the cost is a longer path and harder troubleshooting.

Four-step inventory deduction check: don't reverse the order

Rather than arguing about which lock to use, check four things in a fixed order. The value of this order is that if the previous step is not confirmed, the lock solution in the next step is likely wasted work, and may even turn the problem from overselling into lock waiting.

1. Atomicity: combine validation and write into one conditional write operation; if affected rows are 0, treat the deduction as failed, without relying on read-then-write.
2. Idempotency: every deduction carries a unique request number or order number, the database adds a unique constraint, and duplicate arrivals are returned directly as already processed.
3. Restock path: deduction succeeds but order placement fails, and timeout unpaid release, both need clear trigger conditions, status validation, and audit records.
4. Observability: the inventory ledger must be reconstructable by product and time, and when negative inventory appears it can be traced to a specific request, rather than seeing only an error result.

Only after all four hold should you evaluate whether to add a lock. The criterion for whether it is done well is also direct: take any abnormal inventory entry, reconstruct the complete deduction and release sequence from the ledger, and count the number of oversold cases—rather than relying only on manual checking.

Extremely hot products: trade-offs between sharding and pre-deduction

When a single product is contended thousands of times per second, whether you use conditional update or row lock, pressure concentrates on one row and lock waits pile up quickly. A common approach is splitting: divide sellable inventory into several shards, route requests by hash or random assignment to deduct from different shards, and handle cross-shard operations in a fixed order on failure; or use cache pre-deduction plus asynchronous persistence to move write pressure away from the database. The cost is a more complex inventory definition—reconciliation, restocking, and manual adjustments must all follow the same sharding rules, otherwise the discrepancy keeps growing.

A fairly common situation on delivery sites is: the schedule only leaves time for one layer of cache pre-deduction, load test data comes only from a single-threaded script, and historical ledgers are not archived. What we did at the time was first add a load test with concurrency and duplicate requests, then deliver the same-product request number unique constraint, inventory ledger table, and reconciliation script together, and put cache pre-deduction into phase two. The result was no mismatched definition discrepancies in the first month after launch; the cost was about 3–5 extra person-days in the first version for the ledger and reconciliation, which was less than going directly to a sharding solution, but indeed slower than changing only one SQL statement. Estimating such changes by typical range: daily order scenarios are about 1–3 person-days, while introducing sharding or pre-deduction is usually 5–15 person-days, depending on how detailed the reconciliation requirements are.

• Sharded inventory: suitable for extreme hot spots on a single product, acceptable temporary uneven distribution, and supported by strong reconciliation capability.
• Cache pre-deduction: suitable for peak shaving at flash-sale entrances, and needs asynchronous persistence plus failure compensation.
• Queue serial: suitable when the same product requires strongly ordered deduction; read latency usually increases.
• Database conditional update: suitable for daily orders, with low modification cost, and is the default choice for many systems.

When it applies and when it does not

Cases suitable for starting with conditional update plus idempotency constraints: few instances, per-product concurrency in the tens to hundreds, limited retries allowed after failure, and reconciliation measures available. Cases suitable for considering row locks, sharded inventory, or cache pre-deduction: load testing already shows obvious lock waits or version conflict retries, and write conflicts on the same row have become the bottleneck. Cases that need caution: low-concurrency scenarios such as internal stocktaking and backend batch imports, where adding locks usually only adds failure points; inventory data tied to financial definitions should not rely only on cache pre-deduction, and must keep a database-authoritative verification path.

The boundary sentence can be excerpted directly: The choice of lock depends on the intensity of concurrent write conflicts on the same row, not on system scale; without idempotency and reconciliation, no lock can stop inventory discrepancies caused by replay. This boundary also applies to inventory modules added after 2026, and does not change even if the framework changes.

A few easy pitfalls

One pitfall is an overly large transaction scope, wrapping operations beyond deduction into the transaction, quickly filling up the connection pool. Another is unlimited retries, looping forever after a version conflict and driving database CPU high. A third is not validating status when releasing inventory, so quantities occupied by paid orders are restocked again; the inflated inventory is only discovered during stocktaking.

• Including remote calls or message sending inside the transaction: lock wait time is easily amplified.
• Not setting an upper limit on retries: conflicts create self-excitation, showing up as overall API slowdown.
• Releasing inventory without status validation: paid orders are also restocked, inflating inventory.
• Testing only single-threaded: load tests must include concurrency and duplicate requests, otherwise overselling cannot be seen in the test environment.

Common questions

Does inventory deduction always require a distributed lock?

Not necessarily. When a single database can guarantee atomic single-row updates, one conditional update plus a unique constraint is usually enough; distributed locks are better suited to cross-resource coordination and come with higher modification cost and troubleshooting difficulty.

How many retries after a conditional update failure is normal?

The typical range is 2–3 times. If it still fails beyond that, it usually means write conflicts on the same row have concentrated; at that point consider entrance rate limiting, sharded inventory, or cache pre-deduction instead of adding more retries.

Will pessimistic locks slow down the database?

When transactions are short enough and lock waits have an upper limit, the impact is controllable; a common practice is to set the wait upper limit to 1–3 seconds. Once the lock scope includes remote calls, waits are amplified and connection pool pressure rises noticeably.

If cache pre-deduction and the database do not match, how should it be closed out?

Reconcile with the database as authoritative, write discrepancies into the inventory ledger table, and correct them with a fixed script. It is not recommended to fix things by manually changing inventory numbers, otherwise the same type of discrepancy will recur and you lose traceable evidence.

Can a flash-sale scenario rely only on database conditional update?

Within a few hundred hits per second for a single product, experience suggests most can hold up; beyond that, waits easily pile up on one row and require entrance rate limiting, sharded inventory, or cache pre-deduction. Relying only on the database is usually not a good choice.

If you have a specific inventory deduction problem, first use the four-step check to confirm atomicity, idempotency, restocking, and observability, and then decide whether to introduce locks, sharding, or pre-deduction; most daily orders do not need a complex scheme, while extremely hot scenarios require reconciliation and compensation to be prepared in advance. The above judgments are based on project delivery experience and common acceptance criteria; specific thresholds still need to be checked against your own load test results and database documentation.