One ticket, two buyers

Your shop has one ticket left. Alice and Bob both click Buy. Each request reads “1 ticket available” before either request changes it.

How do you stop the shop from confirming two purchases?

Start with reading and changing rows and transactions if SQL, commit, or rollback are unfamiliar. We will follow the two requests, find the mistake, and repair it. A workload describes the requests the database handles. A correctness contract states what must remain true while it handles them.

Start with the rule

For this example, every order buys one ticket. There are no cancellations or new tickets added during the sale. The shop starts with one unsold ticket and no orders.

After any completed purchase, this must still be true:

Tickets left + tickets in confirmed orders = 1.

A rule that must remain true is called an invariant. Here it prevents the shop from selling the same ticket twice. Preventing a negative stock number is not enough: the shop could show zero remaining and still issue two orders.

The database has one inventory row for this ticket type, identified by product_id = 73. Think of an order as the shop’s record that it owes a buyer one ticket.

Where the simple approach goes wrong

A first attempt might read the stock, check it in application code, and then write the new number. Each buyer seems to follow sensible steps. The problem appears when we put the steps from both requests on the same timeline.

1 · Both requests read
Alice
Reads remaining = 1
Bob
Reads remaining = 1
2 · Alice finishes
Writes remaining = 0 and records her order.
3 · Bob uses his earlier answer
Also writes remaining = 0 and records his order.

Before
1 left + 0 ordered = 1

After: rule broken
0 left + 2 ordered = 2

Time runs downward. Bob's stock check was true when he made it. It was out of date when he used it to approve an order.

The two writes both set the value to zero, so the stock number looks plausible. The extra order reveals the error.

This is a race condition: whether the rule holds depends on how the requests overlap. If Bob had read only after Alice finished, he would have seen zero and stopped. Testing the requests one at a time would miss the problem.

Let one update make the decision

Move the availability check into the update itself. The database should reduce the stock only if stock is still available when it can perform the change.

Here are the first two statements of a PostgreSQL 18 transaction:

BEGIN ISOLATION LEVEL READ COMMITTED;

UPDATE inventory
SET remaining = remaining - 1
WHERE product_id = 73
  AND remaining > 0
RETURNING remaining;

WHERE remaining > 0 is the guard. RETURNING tells the application whether a row changed. We assume product_id uniquely identifies this row and every purchase uses this same guarded update.

If Alice updates the row first, Bob’s competing update waits while her transaction is unfinished. If she commits, PostgreSQL checks the condition against the updated row. It now contains zero, so Bob’s update returns no row. If Alice rolls back instead, her decrement is undone and Bob can attempt to buy the ticket. This behavior is specific to the stated PostgreSQL Read Committed example. PostgreSQL transaction isolation

The database has made “check and decrement” one operation. There is no application-side gap between checking stock and claiming it.

Finish the order in the same transaction

Claiming the ticket is only part of the purchase. The stock change and the order must succeed together. Otherwise an order-writing error could leave a ticket missing from stock with no buyer attached.

The application follows this sequence on the same database connection, inside the transaction already started:

1 · Inspect the update result
No returned row? Roll back and report sold out. Do not create an order.
2 · Record the order
One returned row? Insert the buyer's order. If insertion fails, roll back the transaction.
3 · Confirm the purchase
Commit both changes. Tell the buyer the purchase succeeded only after the database confirms the commit.

A transaction makes the stock change and order creation an all-or-nothing operation. A successful purchase ends with 0 tickets left and 1 confirmed order.

A transaction groups changes so they commit together or are rolled back together. Putting the guarded update and order insertion in the same transaction preserves our ticket count if either step fails. PostgreSQL transactions

There is still a separate problem if the commit succeeds but its reply is lost: the buyer cannot tell whether the purchase worked. A real checkout needs a stable purchase identifier and a way to retrieve its recorded result before retrying. The later lesson on remote calls develops that problem. Our example establishes the stock-and-order rule, not a complete checkout system.

Predict what happens

Alice changes the stock from 1 to 0. Before she commits, her order insertion fails. The application rolls back. Bob’s guarded update has been waiting on the same row.

Can Bob now buy the ticket, or has Alice used it up?

Show the reasoning

Bob can buy it, assuming no other request gets there first. Alice’s decrement was part of the transaction that rolled back, so it did not become a committed stock change. The remaining count is again 1. Bob’s guard can pass, after which he must record his order and commit.

If Alice had committed a valid order, the remaining count would be 0 and Bob’s update would return no row. The outcome depends on whether her transaction commits, not merely whether it once wrote a zero.

What this teaches us about a workload

The opening problem now has an answer: make the stock decision in a guarded update, and keep the order in that transaction. Two purchase requests can overlap without both claiming the one available ticket.

A shop serving many independent products has a different workload from a flash sale where everyone wants this same ticket. Both might receive the same number of requests, but the flash sale concentrates the decisions on one row. We will use that distinction when considering performance.

The next chapter examines locks: the coordination mechanism behind making one operation wait for another. Keep today’s rule in mind: a faster implementation is useful only if it still sells each ticket at most once.