Two stock decrements, but only one takes effect

Fictional setting, real lab evidence. Fortex in this series is a fictional setting; Tom and Ben are characters. Their conversations illustrate the reasoning. Measurements and assertions come from the linked Docker lab, not Fortex production incidents.

The Fortex journey · Chapter 4/8

Two orders, one missing decrement

Ben brought Fortex's new inventory code to Tom. He had remembered transaction cleanup and error handling. The flow was readable: fetch stock, subtract the order quantity, save the new value.

“I check that it stays nonnegative too. One successful order decreases stock correctly.”

Tom read the whole function. “Can I let two requests reach this read together?”

“The database locks the row during UPDATE, doesn't it?”

“It does. Keep that hypothesis, and write down the values the application will send.”

Ben put two windows side by side. Starting from 100, each order taking 10, he wrote 90 in both. The lock could make them run one after another, but they were still two instructions to write 90. Tom let him run that branch in the lab before they changed how the decrement was expressed.

Two successful requests, one missing decrement

Ben wrote down the expected result before running anything: 80 after two distinct orders. He kept the one-request case as well, to explain why his earlier test had looked reassuring.

An item has 100 units in stock. Two orders each buy 10. Both API calls succeed, so the remaining stock should be 80.

A read–calculate–write implementation can leave 90 instead. Each request reads 100, subtracts 10 in application memory, and sends SET stock = 90. Both SQL statements are valid. There is no failed query to alert on: the database cannot infer what those two literal values were meant to represent.

This is a lost update. A controlled schedule makes the failure easier to understand than a load test that occasionally reproduces it.

A value you read is not a reservation

“So the database isn't ignoring its lock,” Ben said. “I'm sending a value I calculated earlier.”

“Exactly. Look at the gap between read and write,” Tom replied. “A lock acquired at the end can't protect a calculation already performed outside it.”

A reads 100. B reads 100 before A writes. A writes 90 and commits. B then writes its own previously calculated 90. One business operation has disappeared from the final state.

-- Both clients previously read stock = 100
UPDATE inventory SET stock = 90 WHERE id = 1; -- A
UPDATE inventory SET stock = 90 WHERE id = 1; -- B

The lab deliberately schedules two committed reads and retains their values in the driver, just as an application retains local variables. This is evidence that the schedule is possible, not a measurement of its frequency in production. The atomic comparison uses two concurrent database sessions.

PostgreSQL defaults to READ COMMITTED. When two updates target the same row, the second may wait for the first. After the first commits, PostgreSQL rechecks the condition against the updated row. That behavior helps when the update expression uses the row's current value; it cannot repair an already computed client-side literal. The distinction is described in the PostgreSQL 17 isolation documentation.

Put the operation and its precondition together

Ben changed the expression to stock = stock - 10, then stopped at the return handling. “If there isn't enough stock, the query succeeds but returns no row. The API has to understand that too.”

“Yes. The statement only helps if its caller handles a rejected update,” Tom said.

For a simple stock decrement, use one conditional statement:

UPDATE inventory
SET stock = stock - 10
WHERE id = 1 AND stock >= 10
RETURNING stock;

One request returns 90 and the other returns 80. If fewer than 10 units remain, no row is returned. The application must turn that into a business rejection; successful SQL execution alone does not mean the order was accepted.

A CHECK (stock >= 0) constraint is useful defense in depth, but it cannot detect this lost update. Ninety is nonnegative. The constraint checks a row invariant, not whether every accepted order was accounted for.

Order creation and inventory changes may also need one transaction so they commit or roll back together. If a timeout causes the client to retry the same order, an atomic decrement alone still permits two valid decrements for one intent. That requires a separate idempotency mechanism.

When the change needs an application-side calculation

“What about an edit that needs calculations in application code? I can't put every rule into one SQL expression.”

Tom didn't defend a single universal answer. He asked Ben to compare version checks with retained locks: where is a conflict detected, and who repeats the work?

An edit form may read several fields, run business logic, and submit a replacement value. Optimistic version checks can detect stale input:

UPDATE inventory
SET stock = 90, version = version + 1
WHERE id = 1 AND version = 0
RETURNING version;

A second writer using version 0 updates zero rows. It must reread and recalculate, retry within a budget, or show a conflict. Resubmitting the same 90 with a newer version defeats the purpose.

SELECT ... FOR UPDATE is another option for logic that must read before writing. Keep the lock, calculation, and update in the same transaction on the same connection. Locking in one transaction and writing in another leaves a gap. See PostgreSQL row locks.

What the Docker lab actually verifies

Ben added the insufficient-stock case himself. He wanted more than a chart with a better-looking branch: would the implementation reject a sale it couldn't fulfill? The assertions below record the actual outcomes.

The isolated environment uses PostgreSQL 17.6 pinned by image digest, with stock reset to 100 before each comparison. It never connects to the application database.

The optimistic branch also checks that a stale version changes zero rows, then retries against the current version and reaches 80. A separate insufficient-stock case starts at 5, attempts to subtract 10, and confirms that the conditional statement leaves 5 unchanged.

The published report and raw SQL and command logs are tied to the downloadable source. Run make reproduce, then make verify RUN_ID=<id> to check the assertions. These are correctness measurements, not latency or throughput results.

Choosing an approach

“What will you recommend?” Tom asked.

“The atomic update for this simple decrement. But it doesn't promise higher throughput: a hot row still makes writers wait. Creating the order belongs in the same commit boundary.”

Tom handed him the decision note and helped review the alternatives.

An invariant spanning two different rows may need more than this single-row example. Two doctors independently going off duty can be a write-skew problem even though neither overwrites the other's row. Likewise, an HTTP call made while holding a row lock turns external latency into internal contention.

Before writing a value derived from an earlier read, identify what makes that read safe to use now. An in-database expression, a version check, and a retained lock provide different answers, with different costs.

The page Ben takes into tomorrow

Ben added two lines to his notebook: “Successful SQL doesn't prove the business outcome. A previously read value needs a reason to remain trustworthy.” He also asked what happens if one order is submitted twice. Tom saved that question for the retry chapter; today's experiment concerns two different orders.

Meanwhile, Fortex's product page could still send a crowd of readers to the database. Ben had a Redis proposal ready.

Previous chapter: How a read-only transaction can keep a PostgreSQL table bloated · Next chapter: One expired key, twelve requests hitting the database · Start the journey