SQL · Chapter 37 of 42

SQL Transactions

A TRANSACTION groups multiple statements into an atomic unit. Either all succeed (`COMMIT`) or none apply (`ROLLBACK`).

ACID properties: Atomicity, Consistency, Isolation, Durability.

Example 1 (sql)

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

Output

Transferred 100 atomically

Money transfer with no half-updates.

Example 2 (sql)

BEGIN;
DELETE FROM users WHERE id = 42;
-- oh no, wrong row
ROLLBACK;

Output

Nothing was actually deleted

Undo before commit.

Key points

BEGIN / COMMIT / ROLLBACK.
All-or-nothing behaviour.
Isolation levels control concurrent visibility.
Great for multi-step updates.

💡 Note: Default isolation is usually READ COMMITTED (Postgres) or REPEATABLE READ (MySQL InnoDB). Raise to SERIALIZABLE for the strongest guarantee.

SQL Transactions

Key points

📝 Quick Quiz