All or Nothing

Move money from your left pocket to your right: take it out, put it in. Two steps — and between them, for one heartbeat, the money is in your hand and belongs to no pocket. Now make it real: a bank moving 500 birr between two accounts is two UPDATEs — subtract here, add there. What if the power dies between them?

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- ...the lights go out here...
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

Account 1 is lighter. Account 2 never got heavier. Five hundred birr has left the universe, and no single statement did anything wrong — the gap did. Constraints can't help; each write was individually acceptable. This failure lives between writes, and it needs a tool built for exactly that.

Transactions

A transaction wraps several statements into one indivisible package. You mark where the package starts and where it ends:

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

BEGIN tells the database "what follows belongs together." COMMIT says "seal it — make all of it real, permanently." And the database's promise in return is absolute: everything between BEGIN and COMMIT happens entirely, or none of it happens at all. Power cut after the first UPDATE? On restart, the database rolls the half-done work back — account 1 untouched, as if the transfer never began. The money is findable in every version of events.

There's a word for this property, and it's worth owning properly: atomic — indivisible, from the old Greek idea of a thing that cannot be cut. An atomic change has no halfway. The outside world gets to see before and after, and nothing in between exists for anyone.

One more door this opens: changing your mind. Inside a transaction, ROLLBACK instead of COMMIT undoes everything since BEGIN — the database equivalent of "actually, forget I said anything." Last lesson's advice to rehearse risky writes gets its professional upgrade here: careful operators BEGIN, run the dangerous UPDATE, look at the result, and only then COMMIT — or ROLLBACK and walk away whistling.

Seeing the packages everywhere

The skill to build is noticing which changes are secretly plural. A new loan in our library isn't one write — it's INSERT the loan and mark the book unavailable. An online order is a whole parade: create the order, decrease the stock, record the payment. Every one of those is several statements that are one event, and every gap between them is a place half-truths could live. The professional reflex: if two writes only make sense together, they travel in one transaction. The vault doesn't do halfway.

Constraints guard each card; transactions guard each change. But both assume the drawers themselves survive. Fires, floods, dying disks, and catastrophic Tuesdays are still out there — so next lesson is about the photocopy that saves the whole library: backups.