Learn / SQL Fundamentals / Transactions
Lesson 6 of 6

Transactions

Imagine transferring $100 from your checking account to your savings account. That's really two updates: subtract $100 from checking, add $100 to savings. Now imagine the power goes out right after the first update but before the second. Your money just vanished — not spent, not saved, just gone. A transaction is how databases guarantee that can't happen: a group of statements that succeed together, or fail together, with no in-between state left behind.

💡 The Bottom Line

Wrap related statements in BEGIN ... COMMIT, and if anything goes wrong partway through, ROLLBACK undoes everything since BEGIN — as if none of it ever happened. This guarantees atomicity: a multi-step operation either fully happens or fully doesn't, with no half-finished state visible to anyone else.

The bank transfer, done safely

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 'checking';
UPDATE accounts SET balance = balance + 100 WHERE id = 'savings';

COMMIT;

Until COMMIT runs, neither update is final — the database holds both changes together as one pending unit. If something fails in between (a crash, a constraint violation, an explicit check that the checking balance would go negative), you call ROLLBACK instead, and both updates are undone, restoring the exact state from before BEGIN:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 'checking';
-- suppose an application check now finds the resulting balance is negative
ROLLBACK;
-- both accounts are exactly as they were before BEGIN; the -100 update never happened

⚠️ Watch Out

Don't run several related statements outside a transaction and assume you can "clean up" manually if one fails partway through. By the time you notice the failure, other queries may already have read the half-updated data, and untangling exactly what got applied and what didn't is far harder than just wrapping the statements in BEGIN/COMMIT/ROLLBACK up front.

ACID, in plain terms

Transactions are usually described with the acronym ACID:

You don't need to memorize the acronym — just the intuition: a transaction is a promise that "this group of changes is all-or-nothing, and once I say it's done, it's really done."

🙋 There Are No Dumb Questions

Q: Do I need to wrap every single query in a transaction?
A: No — a single standalone statement (like one SELECT or one UPDATE) is already atomic on its own in most databases; there's nothing to coordinate. Transactions matter specifically when multiple statements need to succeed or fail as one unit — like the two-account transfer above, where succeeding at only one of the two updates would leave the data in a broken, inconsistent state.

🔧 Try It Yourself

Write the SQL for a transaction that moves a book from one library branch to another: it should decrease the copy count at the source branch and increase it at the destination branch, wrapped in BEGIN/COMMIT. Then write a one-sentence explanation of what could go wrong if you ran those two UPDATE statements without wrapping them in a transaction at all.

✅ Quick Check

Why does the bank transfer example need a transaction instead of two independent statements?

That's the whole core of everyday SQL — tables and relations, filtering, joining, summarizing, speeding up lookups, and keeping multi-step changes safe. Time for the recap.

← Back to
Next →