Preventing Double-Spend Disasters with Strict Row-Level Locking in PostgreSQL
A double spend happens when two requests read the same balance, both decide there is enough money, and both debit it. It is a classic race condition, and it shows up the moment a wallet, micro-ATM or payout system handles concurrent traffic. PostgreSQL gives you precise tools to prevent it. This article covers the ones we rely on.
The race condition in one query pair
The unsafe pattern is read-then-write in application code: SELECT the balance, check it in Node.js or Java, then UPDATE. Between the two statements another transaction can change the same row. Under READ COMMITTED, which is the PostgreSQL default, nothing stops both transactions from succeeding.
Lock the row before you decide
SELECT ... FOR UPDATE takes a row-level lock. A second transaction that tries to lock the same row waits until the first commits or rolls back, then sees the updated value. The check and the debit become one serialised step per wallet.
BEGIN;
SELECT balance
FROM wallets
WHERE id = $1
FOR UPDATE;
-- application checks balance >= amount, then:
UPDATE wallets SET balance = balance - $2 WHERE id = $1;
INSERT INTO ledger_entries (wallet_id, amount, direction, ref)
VALUES ($1, $2, 'DEBIT', $3);
COMMIT;You can also fold the check into the statement: UPDATE wallets SET balance = balance - $2 WHERE id = $1 AND balance >= $2, and treat zero affected rows as insufficient funds. That is atomic without an explicit SELECT.
Keep the database as the last line of defence
- Add a CHECK (balance >= 0) constraint so a bug can never produce a negative wallet.
- Make writes append-only: record every movement in a double-entry ledger and derive balances from it or reconcile against it.
- Add a unique idempotency key per request so a retried API call cannot debit twice.
Avoid deadlocks with consistent lock ordering
When a transfer touches two wallets, always lock them in the same order, for example by ascending id. If one transaction locks A then B while another locks B then A, they deadlock and PostgreSQL aborts one of them.
SELECT id, balance
FROM wallets
WHERE id IN ($1, $2)
ORDER BY id
FOR UPDATE;Choosing an isolation level
Row locks under READ COMMITTED are enough for most single-row balance updates. For logic that spans several rows or aggregates, use SERIALIZABLE and be prepared to retry on serialization failures (SQLSTATE 40001). REPEATABLE READ avoids non-repeatable reads but still needs explicit locks for write-skew cases.
Operational details that matter
- Keep transactions short: no network calls to banks or gateways while holding a lock.
- Set lock_timeout and statement_timeout so a stuck transaction fails fast instead of piling up requests.
- Use NOWAIT or SKIP LOCKED for queue-style workloads where waiting is the wrong behaviour.
- Retry deadlock and serialization errors with a small, jittered backoff.
- Test with a concurrent load script that fires many simultaneous debits at one wallet and asserts the final balance.