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.