Databases•22 min read•Advanced
Transactions & ACID
BEGIN, COMMIT, ROLLBACK — and why your bank really, really cares about isolation levels.
What's a transaction?
A transaction is a group of statements treated as a single, atomic unit. They all succeed (COMMIT) or none of them happen (ROLLBACK). The classic example is a bank transfer:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Without a transaction, a crash between the two updates means $100 just vanishes.ACID
- Atomicity — all or nothing.
- Consistency — the database ends in a valid state (constraints satisfied).
- Isolation — concurrent transactions appear to run sequentially.
- Durability — committed transactions survive crashes.
Isolation levels
Strict isolation costs performance. Most databases offer four levels — pick the weakest that's safe for your workload.
- READ UNCOMMITTED — can see other transactions' uncommitted writes ("dirty reads"). Almost never used.
- READ COMMITTED — only sees committed data. Default in Postgres and Oracle.
- REPEATABLE READ — within a transaction, the same row always reads the same value, even if others modify it.
- SERIALIZABLE — strongest. As if all transactions ran one at a time. Slowest.
⚠ Watch out
Long-running transactions are dangerous: they hold locks, bloat undo logs, and block other writers. Keep them short — open, do the writes, commit.