A checkout request can race with another buyer, a cancellation or an expiry job. Each may have read valid data, yet their combined changes can still be wrong.
In the platform I maintain, the important boundaries are product availability, the paying account’s balance and the state that authorizes a reservation to be consumed. The examples here are simplified and use generic names.
Lock first, then read availability
Available stock is derived from physical stock and unpaid orders. Two concurrent buyers must not both reserve the same remaining unit.
The checkout path locks product rows in a stable order, then queries availability in a separate statement. Under PostgreSQL’s READ COMMITTED isolation, the second statement obtains a new snapshot after any lock wait. It can see the pending order committed by the previous transaction.
Begin transaction
Lock requested product rows in a stable order
Read stock minus current pending-order reservations
Validate quantities and recompute prices
Reserve funds and create the order
CommitThe pending order represents the reservation. Payment finalization deducts physical stock and changes order state in the same local transaction, so the units stop counting as pending at the point stock is consumed.
Make the balance check part of the write
An application-level balance check can become stale. The balance module locks the relevant row and uses a guarded update, recording the movement in the same transaction.
A simplified example:
UPDATE account_balances
SET available = available - $2,
held = held + $2
WHERE id = $1 AND available >= $2
RETURNING id;No returned row means the caller did not acquire the funds. The caller must stop rather than continue with the result of an earlier read.
The balance identity includes its owner, channel and unit. For B2B orders, the paying account may differ from the person placing the order; both responsibilities must be resolved before changing money.
Claim a pending hold once
For orders funded by a balance reservation, payment, cancellation and expiry compete to resolve that hold. Each path conditionally changes its pending state. The winning transition and the associated financial changes belong in the same transaction; a losing path must not independently move the funds.
stateDiagram-v2
[*] --> Pending
Pending --> Consumed: payment
Pending --> Released: cancellation or expiryA later refund is a separate financial event. It does not turn the original payment into a request that can run again.
Know where the database guarantee ends
A database transaction coordinates local records. It does not make an external payment provider part of that transaction. The application verifies payment status, amount and currency with the provider before finalizing payment; the browser’s success message is not sufficient evidence.
Retries must also be considered separately for order creation, payment confirmation and fulfilment. A duplicate check at one stage does not prove that the entire workflow executes once.
I use targeted database tests and reconciliation to verify the local guarantees: guarded balance movements, correct wallet selection, and records that agree after completion or reversal.