Transactions — All or Nothing

Suppose you're moving money between two accounts: subtract 100 from row A, add 100 to row B. Your program crashes between the two updates. The database now says the money vanished. No sequence of careful single-row operations can fix this class of problem — what you need is a way to make several changes act as one change. That's a transaction.

ACID, Honestly

Transactions promise four properties, remembered as ACID:

• Atomicity — all of the transaction's changes happen, or none do. No in-between state is ever visible, even after a crash.
• Consistency — the database moves from one valid state to another; invariants (like "accounts never go negative", or our invariant, "the index agrees with the table") hold before and after.
• Isolation — concurrent transactions don't see each other's half-done work. (We'll meet the machinery for this in the concurrency lesson.)
• Durability — once committed, the changes survive power loss. (You built this in the durability lesson — commit is exactly where fsync goes.)

Notice these aren't four independent features — they interlock. Atomicity without durability is pointless (all-or-nothing... until reboot). Isolation without atomicity is meaningless (isolated from which state?). This lesson adds the A; you already have the D; the C emerges from doing both right.

How Do You Un-Do? Three Strategies

Atomicity means the database must be able to undo work. There are three classic ways to keep the undo information, and they map directly onto real systems:

1. Snapshot: before the transaction starts, copy the entire state. To roll back, restore the copy; to commit, discard it. Dead simple, brutally expensive at scale — you copy everything to protect anything.

2. Undo log (rollback journal): before modifying each page/row, save just the old version of that piece to a side log. Rollback replays the log backwards; commit discards the log. Cost is proportional to what you changed, not what you have. Classic SQLite works this way: before touching a page, the original page is copied into <db>-journal; crash recovery means "if a journal exists, play it back".

3. Redo log (write-ahead log / WAL): invert the idea — write the new versions to an append-only log first, and only later apply them to the real database. Rollback is trivial (the main file was never touched); commit is one fsync of the log. Crash recovery replays committed log entries forward. This is SQLite's WAL mode, Postgres's WAL, and essentially every modern engine — because appending to one log file is the cheapest durable write there is (sequential I/O, one fsync).

We'll implement the snapshot strategy: our table is small and lives in memory, so "copy the rows array" is one memcpy — and it makes the semantics crystal clear before you meet the log-based versions in real engines. Notice the recipe is exactly the durability lesson's temp-file trick, relocated into memory: never modify the only copy; build the new state beside the old, then switch atomically. The same idea keeps reappearing at every layer of a database.

Semantics: The Fine Print

Getting transaction semantics right matters more than the mechanism:

• No nesting (for us): BEGIN inside a transaction is an error. Real systems layer savepoints on top for partial rollback; the flat version must work first.
• Commit/rollback without begin is an error, not a no-op. Silently accepting them hides bugs in the caller's transaction discipline.
• Rollback restores everything — including next_id in our version. Fun fact: real databases deliberately do not roll back their ID sequences (a rolled-back insert burns the ID forever). That's a concession to concurrency — making sequences transactional would serialize all inserters — and it's why production tables have gaps in their ID columns. Our single-threaded snapshot restores the counter for free, so we do.