A Purchase Is a Transaction

A student with $5.00 on their account taps their card at the register for a $3.00 lunch. The application starts a transaction, reads the balance, sees that $5.00 covers $3.00, computes the new balance of $2.00, and then writes two things:

BEGIN;

SELECT balance_cents FROM accounts WHERE account_id = 1 FOR UPDATE;
-- application: 500 covers 300, so the new balance is 200

UPDATE accounts
SET balance_cents = 200
WHERE account_id = 1;

INSERT INTO ledger_entries (account_id, amount_cents, kind, location_id, occurred_at)
VALUES (1, -300, 'purchase', 1, now());

COMMIT;

Between BEGIN and COMMIT are the read and the two writes: the balance is set to 200 cents, and a ledger entry records the purchase.

These two writes need a transaction for the same reason publishing a post did. If the application crashes between them, the balance is lowered and there is no ledger row to show why. Inside a transaction, both writes take effect or neither does. That is atomicity, as described in chapter 4.

A refused purchase

The student taps again for another $3.00 lunch. The balance is now $2.00. That is not enough, so the application refuses the purchase without writing anything.

Suppose the application code skipped that check and wrote the new balance anyway, which would be negative. The database refuses the UPDATE, because the check constraint on balance_cents does not allow a negative value. The refusal happens at that statement, not at COMMIT. The application then issues ROLLBACK and ends the transaction.

So the balance cannot go negative, whatever the application does. The constraint is a rule about the data, and the database checks it on every write. Keeping the data within its rules is called consistency.