Lesson 39Scale and concurrencyAdvanced

Double-spend prevention

Two concurrent withdrawals can each pass the same balance check. Lock or constrain.

By Solomon Ajayi · Free to read, no signup

User has ₦100 in their wallet. They open two tabs and click 'Withdraw ₦60' in each tab at the same time. Both requests read balance = ₦100. Both pass the sufficient-funds check (100 >= 60). Both post withdrawal entries. The user now has ₦100 - ₦60 - ₦60 = -₦20 in their wallet, and ₦120 of withdrawals succeeded against ₦100 of actual balance. They double-spent ₦20 of money that doesn't exist. This bug is the most common production fintech outage. Three fixes exist: pessimistic locks (SELECT FOR UPDATE), optimistic concurrency (version column), and database CHECK constraints. All three solve it; engineers building serious ledgers use at least two.

A sufficient-funds check is two operations: read the balance, then deduct from it. The bug lives in the gap between them. If two withdrawals run that gap at the same time, both read ₦100, both conclude 100 is enough for 60, and both proceed. Neither did anything wrong on its own; the failure is that they were allowed to interleave. The result is ₦120 of approved withdrawals against ₦100 that actually exists.

The fix is to stop the two requests from running that read-then-deduct in parallel on the same wallet. A pessimistic lock, SELECT FOR UPDATE, makes the second request wait at the read until the first commits, so it sees the post-deduction balance of ₦40 and correctly fails the 60 check. Optimistic concurrency takes the other route: let both proceed, but stamp a version on the row and refuse the second UPDATE if the version moved underneath it. Either way you have forced the operations to happen one at a time.

Then add a backstop the application cannot talk its way around: a CHECK (balance >= 0) constraint on the wallet row. Even if a future code path forgets the lock, the database refuses any UPDATE that would drive the balance negative and aborts the transaction. Serious ledgers run the lock and the constraint together, because the lock handles the common race and the constraint catches the bug you have not written yet.

Worked example, step by step

Set the stage: user has ₦100 in wallet

Standard starting balance.

Seed wallet ₦100
Account	Debit	Credit
FBO at Sponsor Bank (1300)	₦100.00
User Wallet (2000)		₦100.00

FBO ₦100. User Wallet ₦100 credit. Just the baseline.

Withdrawal ₦60, succeeds correctly under SELECT FOR UPDATE

Code path: BEGIN; SELECT balance FROM wallet WHERE id = X FOR UPDATE; (returns 100); check >= 60 → yes; INSERT entry; UPDATE wallet SET balance = balance - 60; COMMIT. The FOR UPDATE clause holds a row lock for the duration of the transaction, any concurrent SELECT FOR UPDATE on the same row BLOCKS until this transaction commits.

Withdrawal ₦60 (lock-protected)
Account	Debit	Credit
User Wallet (2000)	₦60.00
FBO at Sponsor Bank (1300)		₦60.00

User Wallet DOWN ₦60. FBO DOWN ₦60. Wallet balance after: ₦40. The lock guaranteed serial execution: if a second tab fired the SAME withdrawal at the same time, its SELECT FOR UPDATE would have WAITED until this commit finished. When the lock released and the second tab proceeded, it would read balance = ₦40 (the post-commit value), and 40 >= 60 fails. Second withdrawal correctly rejected.

Pessimistic lock: serialise concurrent withdrawals on a single row

BEGIN;

-- Acquires a row-level lock. Any concurrent transaction trying to
-- SELECT ... FOR UPDATE the same row will BLOCK here until we COMMIT.
SELECT balance
  FROM wallet
 WHERE id = $1
   FOR UPDATE;

-- Application code now checks: balance >= 60. If false, ROLLBACK.

INSERT INTO journal_entry (description) VALUES ('Withdrawal NGN60');
INSERT INTO journal_line (entry_id, account_code, debit, credit) VALUES
  (currval('journal_entry_id_seq'), '2000', 6000, 0),
  (currval('journal_entry_id_seq'), '1300', 0, 6000);

UPDATE wallet
   SET balance = balance - 60
 WHERE id = $1;

COMMIT;  -- lock releases here; tab 2's SELECT FOR UPDATE unblocks

Same pattern in Python. The lock is the FOR UPDATE on the SELECT.

def withdraw(conn, user_id: str, amount: int) -> None:
    with conn.transaction():
        row = conn.execute(
            "SELECT balance FROM wallet WHERE user_id = %s FOR UPDATE",
            (user_id,),
        ).fetchone()
        if row is None or row[0] < amount:
            raise InsufficientFunds()

        post_entry(conn, {
            "description": f"Withdrawal NGN{amount / 100:.2f}",
            "lines": [
                {"account_code": "2000", "debit": amount, "credit": 0},
                {"account_code": "1300", "debit": 0, "credit": amount},
            ],
        })

        conn.execute(
            "UPDATE wallet SET balance = balance - %s WHERE user_id = %s",
            (amount, user_id),
        )

Same pattern in Go. Lock acquired inside BeginTx; released on Commit.

func Withdraw(ctx context.Context, db *sql.DB, userID string, amount int64) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    var balance int64
    err = tx.QueryRowContext(ctx,
        `SELECT balance FROM wallet WHERE user_id = $1 FOR UPDATE`,
        userID,
    ).Scan(&balance)
    if err != nil {
        return err
    }
    if balance < amount {
        return ErrInsufficientFunds
    }

    if err := postEntry(ctx, tx, withdrawalEntry(amount)); err != nil {
        return err
    }

    if _, err := tx.ExecContext(ctx,
        `UPDATE wallet SET balance = balance - $1 WHERE user_id = $2`,
        amount, userID,
    ); err != nil {
        return err
    }
    return tx.Commit()
}

Second withdrawal attempt rejected by app + DB constraint

The second tab's withdrawal attempt for ₦60 is rejected at the app layer (balance now ₦40, insufficient). Even if app code had a bug and let it through, the DB CHECK constraint `CHECK (balance >= 0)` on the wallet row would have fired and aborted the transaction. Belt AND suspenders. No journal entry posts for the rejected attempt.

Marker: second ₦60 attempt rejected
Account	Debit	Credit
User Wallet (2000)	₦0.01
FBO at Sponsor Bank (1300)		₦0.01

No new entry. The point of this 'step' is to show what does NOT happen. Apply this entry as a no-op marker (a tiny dummy entry just so the lesson advances). The real lesson is in the commentary: double-spend prevention is enforced at TWO layers: app-level sufficient-funds check after acquiring the row lock, AND a DB CHECK constraint as a backstop. If either layer is missing, you eventually ship the bug.

The DB-level backstop. Even if app logic is buggy, the DB refuses.

-- Numeric CHECK constraint: any UPDATE that would make balance
-- negative aborts the transaction with check_violation (23514).
ALTER TABLE wallet
  ADD CONSTRAINT wallet_balance_nonneg
  CHECK (balance >= 0);

-- Optimistic-concurrency alternative: bump a version column on every
-- UPDATE, and refuse the write if the version changed underneath you.
ALTER TABLE wallet ADD COLUMN version int NOT NULL DEFAULT 0;

-- Then in your app:
-- UPDATE wallet
--    SET balance = balance - 60, version = version + 1
--  WHERE id = $1 AND version = $expected_version;
-- If rowCount = 0, someone else won the race. Retry or fail.

Takeaway

Concurrent requests can each pass the same balance check if you don't serialize them. Three fixes: (1) SELECT FOR UPDATE on the wallet row inside the transaction, pessimistic lock, simple, works; (2) optimistic concurrency with a version column, fail the second commit if the version changed underneath; (3) DB CHECK constraint balance >= 0, last-line defense even if app logic is buggy. Serious systems use the lock + the constraint. Skip both and you ship double-spend bugs that take years to discover and never fully resolve.

The code behind it

Reference solution

CREATE TABLE wallet (
  user_id  uuid PRIMARY KEY,
  balance  bigint NOT NULL DEFAULT 0 CHECK (balance >= 0)
);

-- App-side withdrawal: lock, check, deduct.
BEGIN;

-- Row lock held for the rest of this transaction.
-- A concurrent SELECT ... FOR UPDATE on the same user_id will BLOCK here.
SELECT balance
  FROM wallet
 WHERE user_id = $1
   FOR UPDATE;

-- App checks: balance >= withdrawal_amount, otherwise ROLLBACK.

-- Even if app logic is buggy, the CHECK constraint aborts the
-- transaction with check_violation (23514) before negative-balance
-- corruption hits the table.
UPDATE wallet
   SET balance = balance - $2
 WHERE user_id = $1;

COMMIT;

Practice this on a real ledger

Reading is half of it. Open this lesson in the lab to post the entries yourself against a real Postgres-backed double-entry ledger, with the validation on. Free, your sandbox is yours.

Open this lesson in the lab