How to Fix a Race Condition Double Charge With SELECT FOR UPDATE

Why the race happens

The buggy handler runs two separate database round-trips:

# Round-trip 1: read
cur.execute("SELECT balance FROM wallets WHERE user_id = %s", (user_id,))
balance = float(cur.fetchone()[0])

# Round-trip 2: write (only if balance is sufficient)
if balance < amount:
    return 400
cur.execute("UPDATE wallets SET balance = balance - %s WHERE user_id = %s",
            (amount, user_id))
conn.commit()

Between the SELECT and the UPDATE, nothing prevents another request from reading the same balance. If two $80 charges arrive concurrently against a $100 wallet, both threads read 100.00, both pass the >= 80 check, and both decrement - final balance: -60.00.

The fix: SELECT ... FOR UPDATE inside one transaction

Postgres's SELECT ... FOR UPDATE acquires a row-level lock on the matched rows. Any concurrent transaction that tries to lock the same row blocks until the first one commits or rolls back. Put the lock, the check, and the write all inside a single transaction:

conn = psycopg2.connect(DATABASE_URL)
try:
    cur = conn.cursor()
    # Locks the row. Concurrent callers block here until we COMMIT.
    cur.execute(
        "SELECT balance FROM wallets WHERE user_id = %s FOR UPDATE",
        (user_id,),
    )
    row = cur.fetchone()
    if not row:
        conn.rollback()
        raise HTTPException(status_code=404, detail="user not found")

    balance = float(row[0])
    if balance < amount:
        conn.rollback()        # release the lock on the error path
        raise HTTPException(status_code=400, detail="insufficient balance")

    cur.execute(
        "UPDATE wallets SET balance = balance - %s WHERE user_id = %s",
        (amount, user_id),
    )
    conn.commit()              # lock releases here
    return {"user_id": user_id, "charged": amount, "new_balance": balance - amount}
except HTTPException:
    raise
except Exception as e:
    conn.rollback()
    raise HTTPException(status_code=500, detail=str(e))
finally:
    conn.close()

The second concurrent request blocks at the FOR UPDATE line, then after the first commits it reads the decremented balance (20.00), fails the check, and returns 400. No double charge.

Testing concurrent requests

Fire several requests in parallel and inspect the final balance:

for i in 1 2 3 4 5; do
  (curl -s -X POST -H 'Content-Type: application/json' \
      -d '{"user_id":1,"amount":80}' http://localhost:8000/api/charge) &
done
wait

curl http://localhost:8000/api/balance/1
# should be 20.00, not -60.00

When to use alternatives

SELECT ... FOR UPDATE is the right first choice for row-level concurrency in Postgres. It serializes competing transactions without application-side coordination. Heavier alternatives exist for higher throughput or cross-service scenarios:

• Optimistic locking - add a version column; fail the UPDATE if the version changed since the read. Avoids holding a lock but requires retry logic.
• Advisory locks - pg_try_advisory_lock(id) for cases where the lock must span multiple tables or procedures.
• Distributed locks (Redis) - necessary when the critical section spans multiple database instances or services.

Start with FOR UPDATE; reach for a distributed lock only when the database cannot be the coordinator.