Wallet double-spend under READ COMMITTED: a lost update between SELECT and UPDATE

Our nightly reconciliation flagged 23 wallets with a negative balance. The product does not allow overdrafts: a withdrawal or purchase checks the balance first and rejects it if funds are short. Every flagged wallet had two debits within the same 50 ms, usually from a mobile client that retried a purchase while the first request was still running, or from a user tapping two different checkout buttons quickly.

The reconciliation output looked like this:

wallet_id=77120 ledger_sum=-4500 stored_balance=500 debits_in_window=2 window_ms=31
wallet_id=80455 ledger_sum=-1200 stored_balance=800 debits_in_window=2 window_ms=12

Two separate problems were visible: the wallet was overspent, and the stored balance did not even match the sum of its ledger rows.

Reproducing it

The debit code, simplified:

def debit(conn, wallet_id: int, amount: int, ref: str) -> None:
    with conn.transaction():
        balance = conn.execute(
            "SELECT balance FROM wallets WHERE id = %s", (wallet_id,)
        ).fetchone()[0]
        if balance < amount:
            raise InsufficientFunds()
        conn.execute(
            "UPDATE wallets SET balance = %s WHERE id = %s",
            (balance - amount, wallet_id),
        )
        conn.execute(
            "INSERT INTO ledger (wallet_id, amount, ref) VALUES (%s, %s, %s)",
            (wallet_id, -amount, ref),
        )

Two psql sessions show the race without any application code. Start with a wallet holding 100:

-- session A                                  -- session B
BEGIN;                                        BEGIN;
SELECT balance FROM wallets WHERE id = 1;     SELECT balance FROM wallets WHERE id = 1;
-- 100                                        -- 100
UPDATE wallets SET balance = 40 WHERE id = 1;
                                              UPDATE wallets SET balance = 30 WHERE id = 1;
                                              -- blocks on A's row lock
COMMIT;
                                              -- unblocks, overwrites A's write
                                              COMMIT;
SELECT balance FROM wallets WHERE id = 1;     -- 30

A spent 60, B spent 70, the user spent 130 from 100, and the stored balance says 30. A's update was lost entirely.

What we ruled out

• Missing transaction. The code was inside a transaction. Under READ COMMITTED a transaction does not prevent this pattern.
• Idempotency only. The mobile retry made the race more likely, but two different purchases from the same wallet hit it too, so request deduplication alone would not fix it.
• Replica reads. The SELECT ran on the primary in the same transaction as the update.

Root cause

The code read the balance, decided in Python, and wrote back an absolute value. Under READ COMMITTED each statement sees data committed before it started, and the plain SELECT takes no lock. Both transactions read 100, both passed the check, and the second UPDATE waited for the first only at the row lock, then overwrote it with a value computed from stale data. This is the classic lost update anomaly.

What is happening under the hood

PostgreSQL's default isolation level is READ COMMITTED. Its guarantees are narrow: no dirty reads, and each statement sees a consistent snapshot. It says nothing about consistency between two statements in the same transaction.

When an UPDATE finds a row that a concurrent transaction has already modified, PostgreSQL waits for that transaction to finish. If it committed, the UPDATE re-reads the latest version of the row and re-evaluates its WHERE clause against it. That re-check is why SET balance = balance - 60 would be safe: the expression is evaluated on the newest row. Our code instead sent SET balance = 40, a constant computed from the old read, so the re-check had nothing to correct.

How the levels treat this exact pattern in PostgreSQL:

Other engines differ. MySQL InnoDB at its default REPEATABLE READ does not raise an error here; a plain SELECT reads the snapshot and the UPDATE still overwrites. Do not assume one engine's behaviour carries over.

Environment

Minimal reproduction

A runnable script that reproduces the double-spend with threads:

import threading
import psycopg

DSN = "postgresql://app:secret@localhost:5432/wallet_demo"

def setup():
    with psycopg.connect(DSN, autocommit=True) as c:
        c.execute("DROP TABLE IF EXISTS ledger, wallets")
        c.execute("CREATE TABLE wallets (id int PRIMARY KEY, balance bigint NOT NULL)")
        c.execute("CREATE TABLE ledger (id bigserial PRIMARY KEY, wallet_id int, amount bigint, ref text)")
        c.execute("INSERT INTO wallets VALUES (1, 100)")

def debit_unsafe(amount, ref, barrier):
    with psycopg.connect(DSN) as conn:
        with conn.transaction():
            bal = conn.execute("SELECT balance FROM wallets WHERE id = 1").fetchone()[0]
            barrier.wait()  # both threads have read before either writes
            if bal >= amount:
                conn.execute("UPDATE wallets SET balance = %s WHERE id = 1", (bal - amount,))
                conn.execute("INSERT INTO ledger (wallet_id, amount, ref) VALUES (1, %s, %s)", (-amount, ref))

setup()
barrier = threading.Barrier(2)
threads = [threading.Thread(target=debit_unsafe, args=(a, r, barrier)) for a, r in ((60, "a"), (70, "b"))]
for t in threads: t.start()
for t in threads: t.join()

with psycopg.connect(DSN) as c:
    print(c.execute("SELECT balance FROM wallets WHERE id = 1").fetchone()[0],
          c.execute("SELECT sum(amount) FROM ledger").fetchone()[0])

Output on every run, with the balance showing 30 or 40 depending on which thread commits last:

30 -130

Who is affected

Any read-modify-write on shared state under READ COMMITTED: balances, inventory counts, seat reservations, coupon usage limits, rate limit counters. ORMs make it easy, because loading an object, changing a field and saving it is exactly this pattern.