Transaction Boundaries

Grouping statements into one unit

Everything up to this chapter has treated each statement as its own independent event: an INSERT either happens or it doesn't, immediately. Real applications need more than that — "transfer money from account A to account B" is really two writes (debit A, credit B) that must succeed or fail together. That all-or-nothing grouping is what a transaction provides, and BEGIN / COMMIT / ROLLBACK are the commands that mark its boundaries.

Auto-commit: the implicit default

Without an explicit transaction, every statement is its own transaction — this is called auto-commit mode. INSERT INTO t VALUES (1) outside a BEGIN/COMMIT block takes effect immediately and permanently, as if it had been wrapped in an invisible BEGIN; INSERT ...; COMMIT;. This is the behavior every earlier lesson in this course has relied on. This lesson makes the transaction boundary explicit and controllable.

The three commands

• BEGIN — starts an explicit transaction. From this point, writes are staged as "pending" rather than taking effect immediately — the visible effects of the transaction shouldn't be considered permanent until COMMIT.
• COMMIT — makes every change since the matching BEGIN permanent, atomically, and ends the transaction. After this, the database is back in auto-commit mode.
• ROLLBACK — discards every change since the matching BEGIN, as if none of it ever happened, and ends the transaction.

function begin():
    if in_transaction:
        return ERR "nested transaction"
    in_transaction = true
    snapshot = deep_copy(current_state)   // or: start recording an undo log
    return OK

function commit():
    if not in_transaction:
        return ERR "no transaction"
    in_transaction = false
    snapshot = None                        // changes are already applied; just stop tracking them
    return OK

function rollback():
    if not in_transaction:
        return ERR "no transaction"
    current_state = snapshot               // restore pre-BEGIN state
    in_transaction = false
    return OK

Two implementation strategies

There are two common ways to make ROLLBACK actually undo changes, and either is reasonable for this exercise:

1. Snapshot/copy-on-begin. When BEGIN runs, deep-copy whatever state your engine tracks (tables, rows). Writes during the transaction mutate the live state directly; COMMIT simply discards the snapshot (nothing to restore), and ROLLBACK replaces the live state with the snapshot. Simple to reason about, but costs memory/time proportional to database size on every BEGIN — fine for a teaching exercise, not how real databases do it at scale.
2. Undo log. When BEGIN runs, start recording an undo entry for every write that happens (e.g., "this row didn't exist before" for an insert, or "this row had these old values" for an update/delete). COMMIT discards the undo log. ROLLBACK replays the undo log in reverse to restore prior state. This mirrors how real databases actually implement rollback, and connects directly to the WAL chapter — many engines log old values (undo records) right alongside the new values (redo records) in the same write-ahead log.

Either approach gets you correct BEGIN/COMMIT/ROLLBACK semantics for this exercise; the undo-log approach is worth understanding conceptually since it's the technique real systems use, and it's the natural extension point if this course's WAL work continues into transaction logging.

Nested BEGIN is an error, not a stack

The exercise spec is explicit: BEGIN while already inside a transaction is an error, not something that creates a nested/save-pointed sub-transaction. Real SQL does have SAVEPOINT for nested rollback points, but that's a separate, more advanced feature — for this lesson, one flat "am I in a transaction right now" boolean is exactly the right amount of state.

Edge cases

• COMMIT or ROLLBACK with no active transaction — should be a clean error, not a silent no-op and not a crash.
• Auto-commit statements interacting with an open transaction — while inside BEGIN...COMMIT, ordinary INSERT/UPDATE/DELETE statements should still each report their normal OK-style output, but their effects must only become permanent at COMMIT (or vanish at ROLLBACK) — don't let them auto-commit individually while a transaction is open.
• ROLLBACK correctly restoring reads — after rolling back, a SELECT/.count immediately after should reflect the pre-BEGIN state exactly, not a partially-undone state.
• A transaction with zero writes — BEGIN then COMMIT (or ROLLBACK) with nothing in between should both succeed trivially, changing nothing.