BEGIN, COMMIT & ROLLBACK — Transaction Boundaries

A transaction is a group of operations that must either all succeed or all fail. This is the "A" in ACID (Atomicity).

Transaction Lifecycle

BEGIN;                              -- Start transaction
INSERT INTO accounts VALUES (1, 1000);
INSERT INTO accounts VALUES (2, 2000);
COMMIT;                             -- Make changes permanent

-- OR

BEGIN;
INSERT INTO accounts VALUES (3, 500);
ROLLBACK;                           -- Undo everything since BEGIN

Auto-Commit Mode

Outside an explicit transaction, each statement is its own transaction:

INSERT INTO users VALUES (1, 'Alice');   -- auto: BEGIN, INSERT, COMMIT
INSERT INTO users VALUES (2, 'Bob');     -- auto: BEGIN, INSERT, COMMIT

This is called auto-commit mode. Every statement implicitly wraps itself in BEGIN/COMMIT.

Why Transactions Matter

The classic example is a bank transfer:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- debit
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- credit
COMMIT;

If the system crashes between the debit and credit:

• Without transactions: Account 1 lost $100, account 2 didn't gain it. Money vanished.
• With transactions: ROLLBACK undoes the debit. Both accounts are unchanged.

Implementation Strategy

Track transaction state with a simple state machine:

States: IDLE → ACTIVE → (COMMITTED | ABORTED) → IDLE

BEGIN:    IDLE → ACTIVE (start recording changes)
COMMIT:   ACTIVE → COMMITTED → IDLE (make changes permanent)
ROLLBACK: ACTIVE → ABORTED → IDLE (undo all changes)

For ROLLBACK, keep a copy of modified data (or use the WAL) to restore the previous state.

Nested Transactions (SAVEPOINTs)

SQL supports partial rollbacks with SAVEPOINTs:

BEGIN;
INSERT INTO t VALUES (1);
SAVEPOINT sp1;
INSERT INTO t VALUES (2);
ROLLBACK TO sp1;          -- undoes only the second INSERT
COMMIT;                   -- commits only the first INSERT

We won't implement SAVEPOINTs, but it's important to know they exist.

How SQLite Handles Transactions

SQLite uses a simple locking-based model. In journal mode, BEGIN acquires a SHARED lock, and the first write upgrades to RESERVED, then EXCLUSIVE on commit. In WAL mode, readers never block writers — a huge advantage.

Error Handling

• BEGIN while already in a transaction → error
• COMMIT outside a transaction → error (or no-op, depending on database)
• ROLLBACK outside a transaction → error

Your Task

Implement BEGIN/COMMIT/ROLLBACK with auto-commit mode. ROLLBACK must undo all changes made since BEGIN.