Transactions & ACID

The unit of all-or-nothing

A transaction is a logical unit of work — a set of closely related database changes that must all complete or none of them take effect, so the database stays valid. The textbook example is a funds transfer: subtract from one account, add to another. If only the first half runs, money has vanished. The transaction makes the pair indivisible.

Transaction boundaries mark its limits:

- BEGIN TRANSACTION (or the implicit start of a statement group) opens the unit and starts logging changes without making them permanent; - COMMIT makes every change since BEGIN permanent and visible; - ROLLBACK aborts the unit and undoes every change since BEGIN, restoring the pre-transaction state.

Without an explicit boundary, each statement is usually its own one-statement transaction (autocommit).

ACID: the four guarantees

A correctly processed transaction has four properties, abbreviated ACID:

- Atomic — the transaction cannot be subdivided; it runs entirely or not at all (commit or abort). No half-finished order. - Consistent — any database constraints that were true before the transaction are true after it. It moves the database from one valid state to another. (This is *not* “runs the same way every time.”) - Isolated — changes are not revealed to other users until committed; concurrent transactions see the database as it was before yours started. (Isolation is what concurrency control, Lessons 7.4–7.5, enforces.) - Durable — once committed, changes are permanent and survive a later crash (achieved with write-ahead logging — the change hits a durable log before the data files).

Keep transactions short: a transaction holds resources (locks) until it ends, so a long one blocks other users from “hot” data. Collect all user input *before* BEGIN, check for errors early so you can abort quickly, and commit as soon as the unit of integrity is complete.

Worked example: transfer, then prove atomicity by rolling it back

Project 1 (“Registrar Rebuild”) has a budget of 400000 and project 2 (“Analytics Platform”) 250000. We move 50000 from project 1 to project 2 as one atomic unit. The two UPDATEs are wrapped between a savepoint and a ROLLBACK TO so you can see the change happen *and then completely vanish* — atomicity is enforced by the DBMS, not your application code.

before : project 1 = 400000   project 2 = 250000
after the two UPDATEs (inside the unit): 350000 / 300000
after ROLLBACK: 400000 / 250000   <- the half-done transfer left no trace

The commit version (the next exercise) keeps the result: 350000 / 300000. Either way the *pair* moves together — never one without the other.