When two requests change the same data

The rule we need to preserve

Product P7 is the shop’s Blue mug. Its stock record has one available item. Requests A and B each want to reserve one mug and record a purchase. Each uses its own database session: a separate conversation with the database on its own connection. A will use order O14; B will use O15. Both can belong to customer C4, Ada: two browser tabs or two separate attempts can overlap even when they come from the same customer. We are treating these as distinct purchase requests, not retries of one operation.

The rule is that each committed purchase must consume one available item. If only one is available and nothing is restocked, at most one of these purchases may commit. The stock count must also stay non-negative, but that is only part of the rule. A wrong program can record two purchases while leaving a perfectly non-negative zero in the stock row.

To run the SQL yourself, start the shared practice setup. It explains opening PostgreSQL, loading the files and choosing a fresh database for this article.

CREATE TABLE stock (
  product_id text PRIMARY KEY REFERENCES products (product_id),
  available integer NOT NULL CHECK (available >= 0)
);
INSERT INTO stock (product_id, available) VALUES ('P7', 1);

These new purchases use an agreed €20 unit price. Each attempt must keep its stock change, order and line in the same transaction, as established in What belongs in one transaction?.

Two correct subtractions, one wrong result

Consider application code that reads availability, subtracts one in memory, then sends the new count back. An ordinary SELECT tells the application what it can see. It does not reserve that stock or prevent another transaction from changing it.

-- Each request starts its own transaction.
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT available FROM stock WHERE product_id = 'P7';
-- Each reads 1. Application code computes 1 - 1 = 0.

A reads one and computes zero. Before A writes, B also reads one and computes zero. Both now have a plan based on the same mug. The arithmetic is correct in each application; the plans cannot both describe separate successful purchases.

-- Unsafe: 0 was computed from an earlier ordinary read.
UPDATE stock SET available = 0 WHERE product_id = 'P7';
-- The application creates its order and line, then commits.

PostgreSQL does coordinate writes to this row. A’s update obtains a row lock: a claim that makes a conflicting writer wait until A’s transaction ends. B’s update cannot simply change the row under A’s unfinished write. But when A commits, B can proceed with the command it already sent: set availability to zero. That command contains no trace of why zero was chosen.

Both orders exist. Stock is zero. The non-negative check passes, and every order line has valid references and quantity one. The database kept the changes of each transaction together and made the writes wait for one another. It was never instructed to reconsider B’s permission to buy.

This is a race: the outcome depends on how the requests’ steps interleave. A test that runs A completely before starting B will miss it, because B then reads zero and declines. A useful concurrency test deliberately places both reads before either write. The schedule above is based on PostgreSQL’s documented behaviour; it is not a claim that these two sessions were run in a benchmark.

Make the test part of the change

For this one-row rule, the application can express the whole stock decision in an update: subtract one only from a row with at least one available. The database receives both the requirement and the change, rather than an answer calculated from a previous read.

BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE stock
SET available = available - 1
WHERE product_id = 'P7' AND available >= 1
RETURNING product_id, available;

A changes the row from one to zero and holds its lock. If B issues the same update while A is still active, B waits. At Read Committed, after A commits PostgreSQL considers the updated row and checks B’s WHERE condition again. Zero fails available >= 1, so B changes no row. If A rolls back instead, the available mug remains and B can reserve it. This recheck is the crucial Read Committed rule for this example.

RETURNING contains only rows actually updated. Here the primary key makes at most one row eligible, so one returned row means the reservation succeeded. An empty result is an ordinary outcome, not an SQL error. The application must inspect it. Otherwise it can still create a purchase without obtaining stock. UPDATE documents both its affected-row result and the fact that changing zero rows is not an error.

For request A, a successful reservation is followed by these statements. B uses O15 in both inserts instead. If the reservation returned no row, issue ROLLBACK and skip the inserts. If a later insert fails, roll back the reservation too.

-- Run only if the reservation returned exactly one row.
INSERT INTO orders (order_id, customer_id, placed_at)
VALUES ('O14', 'C4', '2026-10-04 11:00:00+00');
INSERT INTO order_lines
  (order_id, line_no, product_id, quantity, unit_price)
VALUES ('O14', 1, 'P7', 1, 20.00);
COMMIT;

Simply moving the subtraction into SQL is not the whole solution. Without the availability condition, a second subtraction would attempt minus one; our check constraint would refuse it, and the application would need to abandon that transaction. The guarded form makes “no stock reserved” a result the caller can handle directly. The constraint remains a useful defence against other incorrect writes.

Lock before making a longer decision

For our single yes-or-no stock check, the conditional update is enough. A different checkout might allow partial fulfilment: someone requests three mugs, permits a smaller purchase, and only two remain. Application code could inspect the available quantity, choose two, then use that quantity both to reduce stock and to create the order line. It needs the count to remain valid while it makes those related changes.

PostgreSQL offers a locking read for work like this: SELECT … FOR UPDATE retrieves the row while acquiring a lock that excludes competing updates and conflicting locking reads. The application can inspect the returned value while retaining that protection inside its transaction. The partial-purchase decision could also be written in SQL; the locking read gives us a way to make it in application code. It is not a reason to wait for a person or a remote service while holding the lock.

To compare the timing with the conditional update, we’ll keep the original one-mug purchase below. Only the way we read and reserve it changes:

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT available
FROM stock
WHERE product_id = 'P7'
FOR UPDATE;

-- The application now inspects the returned value.
-- Missing row or available < 1: ROLLBACK and stop.
-- Otherwise, while still holding the lock:
UPDATE stock SET available = available - 1
WHERE product_id = 'P7';
-- Create the order and line, then COMMIT.

These comments mark application branches, not SQL that performs the branching for you. The program waits for the read to return, checks that the row exists and availability is at least one, then either abandons the transaction or continues. Its update and purchase inserts must use the same transaction and connection. Committing between the read and the update would release the protection.

If A holds the row lock, B’s locking read waits before returning an answer. After A commits its purchase, B receives zero in this Read Committed example. B therefore declines before making its stock change. Acquiring a lock after an ordinary read, then using the old read to decide, would leave the original mistake intact.

A row lock does not make the row unreadable. Ordinary PostgreSQL reads can still use committed versions while the writer is active. Nor does this stock lock protect every fact the program might consult: a rule involving another stock row or another customer’s balance needs protection that covers those facts too. PostgreSQL’s row-lock documentation describes which operations conflict and how long the locks last.

Both approaches bring competing attempts to the same record before allowing an incompatible change. Their correctness depends on all relevant write paths following a compatible protocol; an administrative script can still defeat the business rule by writing an arbitrary stock value.

Change the first transaction’s ending

Waiting is not itself a rejection. While A is unfinished, B cannot know whether A will keep or abandon its reservation. If A commits, the protected decision sees stock zero. If A rolls back, that same decision can see stock one and proceed. The experiment keeps the request order fixed so that changing the method or A’s ending exposes this difference.

Notice that the committed stock counter stays at one while A’s reduction is pending. That does not grant B permission to buy. The coordinated methods wait for the row they need to change, then obtain a result that accounts for A’s ending. The unsafe method mistakes an earlier observation for that result.

When the rule grows

A cart containing a mug and a bowl can reserve each stock row with a conditional update inside one transaction. If either reservation fails, the application rolls back both. The records being changed have multiplied, but the requirement still decomposes into a per-product availability check plus a transaction that keeps the cart together.

Acquiring several locks introduces a new problem. One transaction might hold the mug row and wait for the bowl; another might hold the bowl and wait for the mug. Choosing a consistent product order for acquiring locks reduces these cycles. Applications still need to handle rejected transactions, which is the subject of Handling waits, deadlocks, and retries.

A different kind of rule cannot be reduced to independent checks. Suppose two bins each contain one display mug, and the shop must leave at least one mug across both bins. Two transactions can each observe a total of two and sell from a different bin. Each local quantity remains non-negative, but together they leave none. Locking only the row each transaction subtracts from does not make them meet at a shared decision.

That wider rule needs a design covering the shared condition: for example, a common reservation record, a locking protocol covering the relevant records, or serializable transactions with whole-transaction retries. Choosing among them requires understanding what a transaction can observe while others run. A stable view and a protected decision are related ideas, but they are not interchangeable.