Demo 6: Balance Transfer — The Stale-Validation Race and Its Fix

Isolation level: Read Committed Source: sql/06_balance_transfer.sql Interactive version: /start-6

The scenario

Two sessions transfer money from Alice to Bob. Each reads Alice’s balance, decides the transfer is affordable, and writes SET balance = balance - N. The expression is safe (Postgres re-evaluates it against the latest committed row), but the decision to proceed was based on a stale read.

• Part A — under Read Committed with no locking, both validate against Alice’s stale $100, both proceed, and the second transfer hits the CHECK (balance >= 0) constraint with an ugly error.
• Part B — SELECT ... FOR UPDATE shifts the serialization point to the read, so the second session waits, sees the true balance, and rejects cleanly.

Starting state: Alice = $100, Bob = $0. Session 1 transfers $60; Session 2 transfers $50. Only one can legitimately succeed ($60 + $50 = $110 > $100).

Part A: The Race (no locking)

Setup

./scripts/reset.sh

Session 1 — read and validate

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- EXPECT: 100.00
-- App: 100 >= 60? Yes. Proceed.

Session 2 — read and validate (before Session 1 writes)

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- EXPECT: 100.00  (Session 1's writes are uncommitted, invisible)
-- App: 100 >= 50? Yes. Proceed.

Session 1 — execute the transfer and commit

UPDATE accounts SET balance = balance - 60.00 WHERE id = 1;
UPDATE accounts SET balance = balance + 60.00 WHERE id = 2;
COMMIT;
SELECT * FROM accounts ORDER BY id;
-- EXPECT: Alice = 40.00, Bob = 60.00

Session 2 — execute its transfer

UPDATE accounts SET balance = balance - 50.00 WHERE id = 1;
-- EXPECT: ERROR  new row for relation "accounts" violates check
--         constraint "accounts_balance_check"
ROLLBACK;

Postgres re-evaluated 40 - 50 = -10, which violates the CHECK. The constraint prevented corruption, but the app got a surprise database error instead of a clean “insufficient funds” decision — and without the CHECK, the balance would have silently gone negative.

Either session — Part A outcome

SELECT * FROM accounts ORDER BY id;
-- EXPECT: Alice = 40.00, Bob = 60.00

Part B: The Fix (SELECT … FOR UPDATE)

Reset first

./scripts/reset.sh

Same two transfers, same ordering — but lock Alice’s row on read.

Session 1 — lock Alice’s row, then validate

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- EXPECT: 100.00  (row now locked for the rest of this txn)
-- App: 100 >= 60? Yes.

Session 2 — try to lock the same row; this BLOCKS

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- psql hangs — waiting for Session 1's lock on Alice's row.

Session 1 — finish the transfer and commit

UPDATE accounts SET balance = balance - 60.00 WHERE id = 1;
UPDATE accounts SET balance = balance + 60.00 WHERE id = 2;
COMMIT;

Session 2’s blocked SELECT now unblocks.

Session 2 — unblocks, sees the true balance, rejects cleanly

-- The SELECT returns the post-commit value:
-- EXPECT: 40.00  (not 100!)
-- App: 40 >= 50? No. Reject the transfer cleanly.
ROLLBACK;

Either session — verify

SELECT * FROM accounts ORDER BY id;
-- EXPECT: Alice = 40.00, Bob = 60.00

Same final numbers as Part A, but Session 2 was rejected by application logic — no constraint error, no surprise.

Takeaway

Even with expression-based updates (balance = balance - N), the validation logic is still vulnerable under Read Committed. In Part A both transactions read $100, both decided “affordable,” and the loser discovered the problem at write time via a CHECK error.

FOR UPDATE shifts the serialization point to the SELECT: the second transaction blocks before it reads, and when it unblocks it sees the true committed value — so the application makes the right call. This is why production ledgers and payment systems almost always SELECT ... FOR UPDATE the account row: it turns a race condition into orderly, sequential access.

That’s the full lab. Run ./scripts/reset.sh any time to replay a demo.