The Last Item Sold Twice Without a Traffic Spike
Tác Giả: OmniStack
Ngày đăng: 11/22/2026

On this page
- Start with the invariant, not the incident graph
- Show the unsafe read then decide then write shape
- Build the proposed stock invariant race timeline
- Compare three repair patterns without pretending they are equivalent
- Conditional atomic UPDATE
- SELECT FOR UPDATE with a locked decision
- SERIALIZABLE with complete transaction retry
- Handle the error path as a product decision
- Define the proposed acceptance template for correctness under contention
- Decide whether this is a repair, capacity or operating model investment
- FAQ
- Can PostgreSQL sell the last item twice without a traffic spike?
- Is SELECT FOR UPDATE enough to prevent inventory oversell?
- Should a serialization failure retry only the UPDATE?
- Does a CHECK constraint prove the inventory process is correct?
- What should the concurrency acceptance test assert?
- Sources and limits
Start with the invariant, not the incident graph
For a hypothetical SKU with one unit left and no backorders, two committed orders must not both reserve that unit. That is the invariant. It matters more than whether the API returned quickly, the database stayed available, or both buyers received successful HTTP responses.
A stock race is a correctness failure in the relationship between committed orders and remaining stock. It does not require unusual traffic. Two ordinary purchase attempts can overlap closely enough for both transactions to make a decision from stock that was visible at read time but no longer safe at write time.
Use this model:
- Starting available quantity: one.
- Backorders: not permitted.
- Committed reservations: zero before the race.
- Required invariant: committed reservations plus remaining stock must not exceed the starting available quantity.
Suppose both requests return success. That observation does not prove that both orders reserved a valid unit. The application may have acknowledged a purchase before the reservation was committed, or it may have treated an unconditional decrement as proof of availability. The database may have preserved a valid row state while the application recorded two accepted orders elsewhere. The invariant has to be evaluated against committed transaction state and the order state that the business treats as reserved.
This is why a green latency dashboard is weak evidence for inventory safety. Latency measures how long requests took. It does not show whether two transactions made mutually incompatible decisions. A PostgreSQL stock race condition can sit inside normal response times and only become visible when the last unit is contested.
PostgreSQL is not malfunctioning in this scenario. The PostgreSQL transaction isolation documentation explains that under Read Committed each statement sees a snapshot taken at the start of that statement. A later statement in the same transaction does not necessarily see the same snapshot as an earlier statement. The result follows from the selected isolation level and the shape of the statements.
For a multi-site operator, the operational question is precise: where does the system establish that the unit is still available, and what committed evidence proves that only one order won?
Show the unsafe read then decide then write shape
The unsafe pattern separates the availability decision from the write that must preserve it. Application code reads stock = 1, decides that the item is available, and later decrements the row without making the update conditional on stock still being positive.
The failure can be reduced to four facts:
- Session A reads stock as one.
- Session B reads stock as one before A's decision is committed.
- Both application paths now hold a local decision that the unit is available.
- The later write does not prove that the decision remains valid.
Under Read Committed, the two SELECT statements can each see a valid snapshot containing one available unit. Neither SELECT reserves the unit. Neither SELECT makes the other transaction wait. The application has learned what was visible, not what it is entitled to commit.
Assume A reaches an unconditional update first. PostgreSQL obtains a row-level lock for the UPDATE. B reaches the same row while A is still in progress and waits. When A commits, B's waiting UPDATE does not automatically become a business-level sold-out response. PostgreSQL continues according to the statement and transaction rules. If the update is written as an unconditional decrement, B can operate on the row state that is current for the write and decrement again. The application decision made after B's earlier SELECT remains detached from the state at the write boundary.
A conditional write changes the decision point. The predicate controlling availability belongs in the UPDATE itself, such as a write that changes the stock only where the row still has stock greater than zero. The application then interprets one affected row as a successful reservation and zero affected rows as sold out or unavailable. The exact SQL depends on whether inventory is split by warehouse, channel, hold, expiry, bundle or allocation pool. The mechanism does not change: the database write must enforce the predicate that makes the business decision valid.
This is the core of inventory oversell concurrency control. The goal is not to make the first SELECT more confident. The goal is to ensure that a transaction cannot commit a reservation unless the write still satisfies the availability rule.
For systems with more application logic around the decision, web application development for transactional products needs to preserve this boundary across the service, repository and database layers. A helper that returns availability from an earlier read is not sufficient evidence for an order service.

Build the proposed stock invariant race timeline
The proposed timeline below is a diagnostic template, not an OmniStack deployment. It records the point where visibility, locking, commit and product meaning diverge. Keep the scenario deliberately small: one stock row, one unit, two sessions, no backorders.
The tempting counter-argument is that a row-level lock acquired by UPDATE should make the second request safe. The PostgreSQL explicit locking documentation supports the mechanism: UPDATE acquires row-level locks, and SELECT FOR UPDATE can lock selected rows. A lock can make a competing writer wait. Waiting alone does not define the correct product outcome. The update predicate, affected-row handling, constraint behaviour and commit result still determine whether B becomes sold out, retries, or commits an invalid business decision.
Step | Session A | Session B | PostgreSQL mechanism to inspect | Invariant question |
|---|---|---|---|---|
1 |
| Statement visibility under the chosen isolation level | Has A reserved anything yet? | |
2 |
| Concurrent transaction sees its own statement snapshot | Have two buyers now been promised the same unit? | |
3 |
| Row lock acquired by | Does the write predicate prevent an invalid reservation? | |
4 |
| Lock wait, row recheck, constraint or serialization result | Is zero updated rows treated as sold out? | |
5 |
|
| Commit outcome and retry boundary | Can both orders commit while the invariant is false? |
The table forces a distinction that incident reviews often lose. At step two, two buyers may have received an application-level promise even though neither transaction has reserved the unit. At step four, B's database operation has a different context from B's earlier SELECT. A lock wait is a database event, not a product outcome.
A proposed race timeline should be extended with the order record and the external message state when those exist. If the order is marked accepted before the stock transaction commits, a later rollback creates a buyer-facing exception. If a payment capture or warehouse instruction leaves the database boundary before the transaction is known to have won, retrying the database transaction can create a separate side-effect problem. This article stays focused on the stock invariant, but the acceptance evidence must show where those boundaries sit.
Related:performance and scalability optimisation guidance, useful when a contention symptom is being mistaken for a capacity problem.

Compare three repair patterns without pretending they are equivalent
Conditional atomic UPDATE, SELECT FOR UPDATE with a locked decision, and Serializable transactions address different failure shapes. None removes the need to define the correct stock rows, the availability predicate, the buyer-facing result and the retry boundary.
Pattern | Failure it addresses | What the application must still check | Where it can fail or be the wrong choice |
|---|---|---|---|
Conditional atomic UPDATE | Separates the availability decision from the write by making the write succeed only while the predicate is true. | Affected row count, transaction commit, order state and any dependent database writes. | It is insufficient if the decision spans several rows or tables that are not covered by the predicate. |
| Controls interleaving for selected rows so the transaction reads and decides after obtaining the row lock. | Correct row selection, lock ordering, stock check after the lock, commit outcome and deadlock handling. | It can create waits and deadlocks, and it does not define a cross-row invariant by itself. |
| Can reject concurrent executions that would not be valid as a serial order. | Retry of the complete transaction, including the read and decision logic, with a defined retry policy and outcome. | Retries add load and complexity; external effects cannot be safely replayed merely because the database transaction is retried. |
Conditional atomic UPDATE
This is the narrowest repair for the one-row, one-unit example. The write carries the business predicate: decrement only when the available stock is still greater than zero. The service treats zero rows affected as a sold-out result, not as a successful update with an empty payload.
The application should not run a prior availability SELECT and use that value as the authority for the later update. A prior read can remain useful for display, but it cannot be the reservation proof. The affected-row result from the conditional write is the evidence that the database accepted the reservation attempt.
SELECT FOR UPDATE with a locked decision
A locked decision can be appropriate when the transaction must read a row, apply several rules, and write based on that locked state. The transaction first locks the relevant row, reads the current stock, decides, and updates before committing. A competing transaction waits for the lock and reads only after the first transaction has completed its decision path.
The design becomes harder when inventory spans multiple rows. A warehouse allocation may touch a SKU row, a location row and a reservation row. If concurrent transactions acquire those locks in different orders, deadlocks become possible. The team must choose the rows and lock order deliberately, and must treat deadlock errors as a defined transaction outcome rather than an unexplained 500 response.
SERIALIZABLE with complete transaction retry
Serializable asks PostgreSQL to reject some concurrent transaction patterns rather than allowing an unsafe serial interpretation. The PostgreSQL serialization failure handling documentation is explicit that applications may need to retry transactions. That is stronger than hoping the final UPDATE happens to see a safe state. It also changes the application contract: serialization failures are expected database outcomes that need deliberate handling.
The retry must include the complete transaction. Retrying only the final UPDATE is wrong when the earlier SELECT and business decision were part of the failed execution. A correct retry repeats the read, the decision, the reservation write and the dependent database changes as one unit. It must also stop after the configured policy and return a product-level result if contention persists.
For a team reviewing a production system, concurrency and performance optimisation work should begin with the invariant and transaction trace, not with connection pool expansion. More connections can increase the number of conflicting transactions without repairing the rule.
Handle the error path as a product decision
A constraint violation, a zero-row conditional update and a serialization failure are different signals. They must not collapse into one generic error because each says something different about whether the buyer's request can be accepted.
Imagine the hypothetical one-unit race again. A conditional update affects zero rows because another transaction consumed the unit. That is a normal sold-out outcome. A check constraint rejects a negative stock value because the schema caught an invalid row state. The PostgreSQL ALTER TABLE documentation supports adding table constraints, including checks, but a constraint is a guardrail for a class of row states. It does not decide which buyer should receive the unit. A serialization failure says the transaction must be retried or abandoned; it does not say the item is sold out.
Database result | What it means | Possible product outcome | Required handling |
|---|---|---|---|
Conditional UPDATE affects one row and commits | The reservation predicate held at the write and the transaction committed. | Reserved or accepted, according to the order model. | Persist the order state and release any downstream effect only from the committed outcome. |
Conditional UPDATE affects zero rows | The row no longer satisfied the availability predicate. | Sold out, unavailable, or release a temporary hold. | Do not report success based on the earlier SELECT. |
Check constraint violation | The database rejected a class of invalid row state, such as negative stock. | Failure requiring a safe user response and operational investigation. | Map the error deliberately; inspect why the application attempted the invalid write. |
Serialization failure | The transaction could not commit under Serializable. | Retry, sold out after re-evaluation, or an explicit temporary failure. | Retry the complete transaction, not only the final statement. |
Deadlock detected | Concurrent transactions acquired conflicting locks in an unsafe order. | Retry or fail according to the transaction policy. | Use consistent lock ordering and instrument the conflicting paths. |
A CHECK constraint is valuable for a simple column rule such as stock not becoming negative. It does not decide which buyer receives the unit, and it does not prove that an order, payment, message or warehouse instruction matches the committed reservation. A constraint can reject one invalid state after the application has made an invalid promise.
The external boundary needs its own decision. If payment has been captured before the stock transaction commits, a database retry cannot simply repeat the payment action. If an order confirmation has been sent, a rollback requires a correction path. If a warehouse instruction has been published, the system needs a durable relationship between the committed reservation and that instruction. Those are separate designs from the one-row stock invariant, but they determine whether the buyer sees a truthful outcome.
Define the proposed acceptance template for correctness under contention
The proposed acceptance test template is simple: with one unit and no backorder, after two concurrent purchase attempts, at most one committed order is marked as reserved and remaining stock is consistent with that result. The test oracle must inspect committed database state, not only HTTP status codes.
Use this proposed template:
- Reset the stock row to one available unit and remove prior reservations for the test SKU.
- Start two transactions through the same production code path, not through a simplified test-only repository.
- Synchronise the sessions so both attempts overlap at the availability decision.
- Record each transaction's SELECT, lock wait, write result, commit result and returned order state.
- Assert that no more than one order is committed as reserved.
- Assert that remaining stock plus committed reservations equals the starting available quantity.
- Repeat the test with the configured isolation level, retry policy and database constraint behaviour.
The test should distinguish outcomes. One request may reserve the unit while the other receives sold out. One may retry after a serialization failure and then receive sold out. A constraint violation may be an expected guardrail signal during a deliberately unsafe implementation, but it is not an acceptable buyer-facing success path.
Finite race tests increase confidence and catch regressions. They do not prove every possible schedule, lock order or failure at the external boundary. Production readiness depends on the actual schema, isolation level, write predicates, retry handling and side effects used by the system.
Instrument the evidence needed to answer the invariant question after deployment:
- Which transaction created the reservation?
- Which database write affected the stock row?
- Did the write use the availability predicate?
- Was the transaction retried, rolled back or committed?
- What order state was visible to the buyer?
- Which downstream effect was released from the committed result?
That evidence is more useful than a generic concurrency dashboard. It lets an operations leader distinguish a sold-out result from a transaction failure and a transaction failure from an oversell defect.
Decide whether this is a repair, capacity or operating model investment
Repair the transaction rule before buying capacity. Add engineering capacity only after the invariant is explicit, the write boundary is correct, and the remaining problem is implementation or sustained ownership rather than a missing concurrency design.
The common market answer is to add more developers or increase database capacity when an order system shows contention. That answer is wrong when two transactions can make incompatible decisions from the same stock value. More workers can create more competing attempts. A larger connection pool can make the race easier to reproduce. Neither changes the business rule.
Use this decision frame:
Observed condition | Decision | Reason |
|---|---|---|
The stock predicate is absent from the write, and the order state can commit independently. | Fund a database and application repair first. | The primary failure is correctness, not capacity. |
The rule is correct, but the team lacks time to implement tests, retries, observability and migration safely. | Add delivery capacity with explicit ownership of the acceptance evidence. | The constraint is implementation bandwidth and continuity. |
The rule spans warehouses, holds, bundles or channels and no owner can define the invariant. | Resolve domain ownership before selecting a delivery model. | No team can prove a transaction design for an undefined business rule. |
The internal team owns the domain rules, production authority and has sustained capacity for the change. | Hire in house or keep the work in house. | In-house ownership is right when the organisation can maintain the transaction boundary and operational evidence itself. |
The internal team has the authority but not the sustained engineering, QA or DevOps capacity. | Consider a dedicated engineering team aligned to the roadmap. | The delivery risk sits with implementation continuity and operational evidence, not with a hiring pipeline alone. |
This is where the distinction between renting headcount and buying delivered capability matters. Staff augmentation gives an organisation additional hands. A delivery-owning team includes the engineering, QA and DevOps capability needed to implement the conditional write, test the race, instrument the commit outcome and maintain the change. The decision should be based on who owns the result and remains accountable for the evidence, not on how many developers can be placed against a ticket.
OmniStack can be considered as a dedicated engineering team option when the internal team has authority over the business rule but lacks sustained implementation capacity. It is the wrong choice when the internal team already owns the domain rules and has the capacity and production change authority to carry the work itself.
For organisations that need the transaction repair to sit inside a broader application roadmap, dedicated engineering capacity aligned to the product roadmap is the relevant operating-model question. For teams extending deployment and observability around the change, cloud and DevOps engineering support may be relevant, but neither replaces the invariant or the acceptance test.
The next decision is concrete: assign an owner to write the one-unit race test, trace the current transaction from SELECT to commit, and choose the smallest change that makes the committed invariant observable. If that owner cannot be staffed from the existing team, buy continuity of delivery. If the owner exists and has authority, keep the repair in house.
FAQ
Can PostgreSQL sell the last item twice without a traffic spike?
Yes, a hypothetical PostgreSQL stock race condition can oversell one unit under ordinary traffic when two transactions read stock as available and the later write does not enforce the availability predicate. The issue is transaction design, not necessarily throughput.
Is SELECT FOR UPDATE enough to prevent inventory oversell?
No. SELECT FOR UPDATE can control interleaving for selected rows, but the transaction must lock the correct rows, read and decide after acquiring the lock, handle deadlocks, and commit the correct order state. It does not define a multi-row invariant by itself.
Should a serialization failure retry only the UPDATE?
No. The complete transaction must be retried, including the read and business decision logic. Retrying only the final statement can reuse a decision made by a transaction that PostgreSQL rejected.
Does a CHECK constraint prove the inventory process is correct?
No. A CHECK constraint can prevent a simple invalid row state such as negative stock, but it does not decide which buyer receives the unit or coordinate order, payment, message and warehouse side effects.
What should the concurrency acceptance test assert?
With one unit and no backorders, two concurrent attempts must produce at most one committed reserved order, and remaining stock plus committed reservations must equal the starting available quantity. The test should inspect committed state and record retry, rollback and sold-out outcomes.
Sources and limits
This article is limited to a hypothetical one-row PostgreSQL stock race with one unit remaining and no backorders. It does not prove a complete inventory, payment, warehouse or messaging design.
- PostgreSQL documentation: Transaction Isolation, used for Read Committed and Serializable isolation mechanisms.
- PostgreSQL documentation: Explicit Locking, used for row-level lock and SELECT FOR UPDATE behaviour.
- PostgreSQL documentation: Serialization Failure Handling, used for complete transaction retry guidance.
- PostgreSQL documentation: ALTER TABLE, used for constraint mechanisms such as CHECK constraints.

