SQL, Refreshed

Transactions & ACID

Lesson 5 of 5

What you'll learn

Group statements with BEGIN, COMMIT, and ROLLBACK
Understand atomicity as all-or-nothing
See how ACID protects data from partial updates

A transaction wraps several statements so they behave as a single unit. You open it with BEGIN, do your work, and finish with COMMIT to make it permanent — or ROLLBACK to discard everything since BEGIN as if it never ran.

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

If the second UPDATE fails — or you call ROLLBACK — the first one is undone too. Money is never debited from one account without being credited to the other. That all-or-nothing guarantee is atomicity.

ACID in one breath

Transactions give you four guarantees, ACID: Atomicity (all-or-nothing), Consistency (constraints always hold), Isolation (concurrent transactions don't see each other's half-done work), and Durability (a committed change survives a crash).

BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 50);
-- Something goes wrong:
ROLLBACK;
-- The order was never inserted. The table is exactly as before.

Forgotten transactions hold locks

An open transaction can hold locks that block other queries. Always finish what you BEGIN — commit it or roll it back. A long-running uncommitted transaction is a classic source of mysterious stalls.

The core idea: stage the writes, and only let them touch the real data on COMMIT. The challenge models a transaction that rolls back.

Model a ROLLBACK

Run it. Stage several writes in a transaction, then ROLLBACK and prove the committed data never changed.

Loading editor…

You now have the relational core: tables, queries, joins, indexes, and transactions.

PreviousIndexes & EXPLAIN