How to prevent overselling the last item

One item remains. Ada and Ben click Place Order at nearly the same time. Two application instances receive the requests and run the same logic:

const product = await getProduct(sku);
if (product.available > 0) {
  await setAvailable(sku, product.available - 1);
  await createOrder(customerId, sku);
}

Both requests can read available = 1 before either one updates it. They both pass the check and both write 0, so the system accepts two orders for one physical item.

Each request looks correct when you read it on its own. The bug only appears when their operations overlap.

“Check, then act” crosses a race window

The application code treats the read and write as one decision, but the database receives two separate operations with a gap between them.

Ada: read stock = 1
Ben: read stock = 1
Ada: write stock = 0
Ben: write stock = 0

Wrapping this code in a transaction is not enough by itself. Under common isolation levels, both transactions may still read a version where the application-side check succeeds.

We need one place that decides which request wins.

Make the condition part of the write

For a simple counter, an atomic conditional update is often the cleanest solution:

update inventory
set available = available - 1
where sku = $1
  and available > 0
returning available;

PostgreSQL checks the condition and performs the update as one statement. Its row locking and condition recheck ensure that two concurrent updates cannot both take the same final unit.

The application checks the number of affected rows:

const result = await db.query(sql, [sku]);
if (result.rowCount === 0) {
  throw new OutOfStockError(sku);
}

The available count never has to leave the database for the application to decide who wins.

Lock the row when the workflow needs several steps

If the decision requires more data and several database operations, the transaction can explicitly lock the inventory row:

begin;

select available
from inventory
where sku = $1
for update;

-- validate the result, create the order, decrement stock

commit;

FOR UPDATE prevents another transaction from acquiring a conflicting lock and changing the row until the first transaction finishes. The second buyer waits, then observes the new state.

The lock protects correctness by forcing other work on that row to wait. Keep that locked section small. If you call a payment provider while holding the row lock, a four-second network delay becomes four seconds where every competing inventory update is blocked.

Serializable isolation detects unsafe outcomes

PostgreSQL’s serializable isolation level aims to make committed transactions behave as if they ran in some serial order. When concurrent activity would create an impossible serialization, PostgreSQL aborts one transaction with a serialization failure.

The application has to expect that abort and retry the whole transaction with a fresh snapshot. The retry still needs a limit and jitter so several failed transactions do not all restart together.

Serializable isolation can simplify reasoning across complex invariants, but it does not remove the need to understand contention, retries, and external side effects. Never charge a card inside a transaction that may be retried unless the payment call is independently idempotent.

Constraints are the final guardrail

If a business invariant can be expressed as a database constraint, I prefer to enforce it there. Unique constraints, foreign keys, checks, and exclusion constraints continue protecting the data even when a new code path forgets the application-side validation.

A simple nonnegative check helps:

alter table inventory
add constraint inventory_nonnegative
check (available >= 0);

The check constraint does not decide which buyer wins, but it prevents negative inventory from becoming committed state. The application can still validate first to return a better error, while the database remains the final guardrail.

Deadlocks are possible and survivable

Imagine one transaction locks inventory then customer credit, while another locks customer credit then inventory. Each waits for the other.

PostgreSQL detects the deadlock and aborts one transaction. The durable fix is consistent lock ordering: every workflow acquires shared resources in the same order. The application should also recognize deadlock errors and retry only safe, bounded units of work.

A deadlock is not the same as long lock waiting. Monitoring should expose both lock wait time and deadlock count.

The client can submit the same order twice

Correct inventory locking still does not prevent the same order from being delivered twice. The server may commit successfully and lose the response on its way back to the client. From the client’s side, that looks the same as a failed request, so it retries.

An idempotency key gives both attempts one business identity:

Idempotency-Key: checkout_8a10d2

The server stores the key with the operation result under a uniqueness constraint. A repeated request with the same key returns the original result instead of creating a second order.

The idempotency key needs a clear scope and lifetime. The server should also remember which payload belongs to it and reject a retry that uses the same key with different order details.

Treat inventory as a reservation

For slow payment flows, decrementing permanent inventory before payment can strand stock. Waiting until after payment can charge a customer for an item another buyer took.

A reservation model makes the intermediate state explicit:

available → reserved with expiry → sold
                         ↘ expired → available

Each reservation has an owner and an expiry time. A background process releases it if payment never completes. This is more work than maintaining a counter, but those pending and expired states already exist in the real checkout process whether we model them or not.

Expiry is another race, so the releaser must use a guarded transition. It should release only a reservation that is still reserved, still owned by the expected operation, and still expired. A payment or fulfillment transition that already won must prevent a late expiry worker from putting that unit back into available stock.

Choose the invariant before the mechanism

Ask what must always be true:

committed available stock must never be negative;
one idempotency key creates at most one order;
one reservation owns a unit during its validity window;
payment and order state eventually agree;
retries do not duplicate irreversible effects.

Once those rules are clear, choose the smallest combination of database operation, lock, constraint, and state transition that can enforce them.

The requests will overlap in production, so the decision has to sit at a boundary they cannot cross inconsistently.

We can now decide who gets the last item without overselling it. Next, the payment provider takes four seconds to answer.

Two people try to buy the last item