SQL Practice 12/13

Transactions: all or nothing

Moving money between two accounts is never really one change - it's two: subtract from one balance, add to the other. Run them as two separate, independent statements and there's a gap between them where anything can go wrong: a crash, a dropped connection, an error in your application code. If the process dies after the first UPDATE but before the second, you've either deleted money or duplicated it.

A transaction closes that gap. BEGIN opens a bundle of changes; COMMIT makes every change in the bundle permanent at once. Nothing outside the bundle - including a mid-transfer crash - can see it half-done. Either both updates land, or neither does.

There's an accounts table: id, name, balance. Two rows - one with 100, one with 50.

Your task: move 30 from account 1 to account 2 atomically, using BEGIN/COMMIT around both updates, then select all accounts (ordered by id) so there's something to check.

You'll practice:

Wrapping multiple statements in BEGIN ... COMMIT
Seeing that only the final SELECT gets graded, so the transfer has to actually run first

-- Move 30 from account 1 to account 2, atomically, then show both accounts.
SELECT * FROM accounts;