Lesson 13 • Expert Track
Transactions & ACID
By the end of this lesson you'll be able to bundle several statements into one all-or-nothing unit — so a bank transfer either fully completes or fully undoes, never half-finishes. You'll use BEGIN, COMMIT, and ROLLBACK, understand the ACID guarantees behind them, and know which isolation level keeps concurrent users from corrupting each other's data.
What You'll Learn
- ✓Why a transaction is an all-or-nothing group of statements
- ✓Start, save, and undo work with BEGIN / COMMIT / ROLLBACK
- ✓Write the classic bank-transfer so money is never lost
- ✓Explain ACID — Atomicity, Consistency, Isolation, Durability
- ✓Set isolation levels (READ COMMITTED, SERIALIZABLE)
- ✓Recognise dirty, non-repeatable, and phantom reads
UPDATE. The in-browser editor lets you write and edit SQL; to run a multi-statement transaction, copy it into a free playground like db-fiddle.com (pick PostgreSQL/MySQL) or sqliteonline.com. Every example below shows the expected before/after balances so you can check yourself. Our Sample Table: accounts
Every example runs against this tiny accounts table. The balance column is money in pounds. Watch how the numbers change after a COMMIT — and how they don't change after a ROLLBACK.
Result — starting state:
| id | name | balance |
|---|---|---|
| 1 | Alice | 1000.00 |
| 2 | Bob | 500.00 |
| 3 | Carol | 250.00 |
Total across all accounts: 1750.00. A correct transfer never changes this total — it only moves money between rows.
1. What Is a Transaction?
A transaction is a group of SQL statements that the database treats as a single, indivisible unit of work. Either every statement in the group takes effect, or none of them do — there is no half-finished state. The word for that guarantee is atomic: "cannot be split".
You mark the boundaries yourself: BEGIN opens the transaction, COMMIT saves everything you did since then, and ROLLBACK throws it all away. Until you COMMIT, your changes are invisible to everyone else and can be undone instantly.
🏦 Real-world analogy
Moving £200 from Alice to Bob is really two steps: take £200 out of Alice, put £200 into Bob. Imagine the system crashes between those steps — Alice is £200 poorer and Bob got nothing; £200 has vanished into thin air. A transaction wraps both steps in a "do them as one" envelope: both happen, or neither does, so money is never created or destroyed.
2. BEGIN & COMMIT — The Bank Transfer
Here is the canonical example. You debit one account and credit another, wrapped in BEGIN ... COMMIT. BEGIN and START TRANSACTION mean the same thing; use whichever your database prefers.
Before the transfer:
Result — starting balances:
| id | name | balance |
|---|---|---|
| 1 | Alice | 1000.00 |
| 2 | Bob | 500.00 |
Transfer £200 (BEGIN … COMMIT)
Debit Alice, credit Bob, save it all atomically.
-- A money transfer is TWO updates that must both happen.
-- Wrap them in a transaction so it's all-or-nothing.
BEGIN; -- start the transaction (also: START TRANSACTION;)
-- 1) Debit Alice (id 1): take £200 out
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
-- 2) Credit Bob (id 2): put £200 in
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT; -- both updates are now saved permanently
-- After COMMIT: Alice 800.00, Bob 700.00 — the £200 moved across,
-- and the
...After COMMIT:
Result — £200 moved across; total still 1500 for these two:
| id | name | balance |
|---|---|---|
| 1 | Alice | 800.00 |
| 2 | Bob | 700.00 |
Notice the total of the two balances is the same before (1500) and after (1500). That conservation is the whole point: the transaction guarantees the two halves of the move never come apart.
Your Turn: wrap a transfer in BEGIN/COMMIT
Fill in the three blanks to move £100 from Alice to Bob safely. The expected balances are in the comments so you can check yourself.
🎯 Your Turn: safe £100 transfer
Replace the ___ blanks with BEGIN, the amount, and COMMIT.
-- 🎯 YOUR TURN — fill in the three blanks, then press "Try it Yourself".
-- Goal: safely move £100 from Alice (id 1) to Bob (id 2).
___; -- 👉 the keyword that STARTS a transaction
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + ___ WHERE id = 2; -- 👉 same amount you took out
___; -- 👉 the keyword that SAVES the transaction permanently
-- ✅ Expected after: Alice 900.00, Bob 600.00
-- (and 900 + 600 = 1500, the same total as before
...3. ROLLBACK — The Undo Button
ROLLBACK discards everything you've done since BEGIN, returning the data to exactly how it looked before. You'll use it when a mistake happens, a check fails, or your application hits an error partway through.
COMMIT. If you never commit — because you rolled back, disconnected, or crashed — the database behaves as though the transaction never happened.ROLLBACK undoes a mistake
A wrong UPDATE is thrown away — balances stay put.
-- ROLLBACK is the undo button for a transaction.
-- Everything since BEGIN is discarded, as if it never ran.
BEGIN;
-- You meant to give Bob £50, but typed 5000 by mistake...
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
-- ...and noticed before saving. Throw the whole thing away:
ROLLBACK; -- Bob's balance returns to exactly what it was
-- After ROLLBACK: Alice 1000.00, Bob 500.00 — UNCHANGED.
-- Because you never COMMITted, nothing was made permanent.After ROLLBACK:
Result — identical to the starting state — nothing was saved:
| id | name | balance |
|---|---|---|
| 1 | Alice | 1000.00 |
| 2 | Bob | 500.00 |
Your Turn: undo a mistake with ROLLBACK
One blank this time — add the keyword that cancels the transaction so Alice's balance is restored.
🎯 Your Turn: ROLLBACK the mistake
Throw the bad UPDATE away; balances unchanged.
-- 🎯 YOUR TURN — fill in the one blank to UNDO a mistaken change.
-- A bad UPDATE wiped Alice's balance to 0. Cancel the whole transaction.
BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 1; -- a mistake!
___; -- 👉 the keyword that throws the transaction away (the "undo")
-- ✅ Expected after: balances UNCHANGED — Alice 1000.00, Bob 500.00.
-- Because you never COMMITted, the database forgets the bad UPDATE.4. What Happens Without a Transaction?
By default the database runs in autocommit mode: each statement is saved the instant it finishes, as if it were its own one-line transaction. That's fine for a single UPDATE, but dangerous when two updates must agree with each other.
The danger of no transaction
A crash between two updates leaves money lost.
-- Without a transaction, every statement is its own mini-commit
-- ("autocommit"). Each line below is saved the instant it runs:
UPDATE accounts SET balance = balance - 200 WHERE id = 1; -- SAVED immediately
-- 💥 If the app crashes RIGHT HERE, Alice has lost £200...
UPDATE accounts SET balance = balance + 200 WHERE id = 2; -- ...and Bob never got it
-- The fix is to group them: BEGIN ... COMMIT makes the pair atomic,
-- so a crash in the middle rolls the first UPDATE back automatically.If the process dies after the first UPDATE, Alice is down £200 and Bob never received it — the books no longer balance. Wrapping the pair in BEGIN ... COMMIT makes them atomic, so an interruption rolls the first update back for you.
5. ACID — The Four Guarantees
Transactions give you four promises, remembered by the acronym ACID. In plain English:
🔒 Atomicity — "All or Nothing"
Every statement in the transaction succeeds, or the whole thing is rolled back. The transfer's debit and credit can never come apart.
✅ Consistency — "Rules Stay True"
A transaction moves the database from one valid state to another. Constraints, foreign keys, and checks (for example "balance cannot go negative") are all still enforced when it commits.
🔀 Isolation — "Don't Step On Each Other"
Concurrent transactions behave as if they ran one at a time. Your transaction won't see another's half-finished, uncommitted changes.
💾 Durability — "Commits Are Forever"
Once COMMIT returns, the data is safely on disk. It survives a power cut or crash a millisecond later.
6. Isolation Levels (a Brief Intro)
When many users hit the database at once, you choose how strictly the I in ACID is enforced. A stricter level prevents more anomalies but does more locking, so it can be slower. Three classic anomalies it guards against:
- Dirty read — you read another transaction's change that it hasn't committed yet (and might roll back). You acted on data that never officially existed.
- Non-repeatable read — you read the same row twice in one transaction and get different values, because someone committed an
UPDATEin between. - Phantom read — you run the same
WHEREfilter twice and new matching rows have appeared, because someone committed anINSERTin between.
The two levels you'll use most are READ COMMITTED (you only ever see committed data — the sensible default in most databases) and SERIALIZABLE (the strictest: transactions behave as if they ran one after another, so none of the three anomalies can occur).
| Level | Dirty Read | Non-Repeatable | Phantom |
|---|---|---|---|
| READ UNCOMMITTED | ⚠️ possible | ⚠️ possible | ⚠️ possible |
| READ COMMITTED | ✅ prevented | ⚠️ possible | ⚠️ possible |
| REPEATABLE READ | ✅ prevented | ✅ prevented | ⚠️ possible |
| SERIALIZABLE | ✅ prevented | ✅ prevented | ✅ prevented |
💡 Pro Tip
Stick with the default (READ COMMITTED in PostgreSQL and most engines) until you have a proven reason not to. Reach for SERIALIZABLE only on the few transactions that truly need it — for example a "check the balance, then deduct it" sequence — because the extra locking costs throughput.
Common Errors (and the fix)
- Forgetting to
COMMIT: you ran your updates inside aBEGINbut never committed. When the connection closes, the database rolls everything back and your changes vanish. Meanwhile the open transaction held locks, blocking other users. Always end withCOMMIT(orROLLBACK). - Partial update without a transaction: running the debit and credit as two separate autocommitted statements means a crash in between leaves the books inconsistent. Wrap related writes in
BEGIN...COMMIT. - Long transactions blocking others: keeping a transaction open while you do slow work (network calls, user input) holds locks the whole time and stalls everyone waiting on those rows. Keep transactions short — open late, commit early.
- "current transaction is aborted, commands ignored until end of transaction block" (PostgreSQL): one statement errored, so the transaction is poisoned. You must
ROLLBACK(or roll back to aSAVEPOINT) before any further statements will run. ROLLBACKwith nothing to undo: callingROLLBACKoutside a transaction is a no-op or warning — there's no openBEGINto cancel. Make sure yourBEGINactually ran first.
📘 Quick Reference
| Syntax | Purpose |
|---|---|
| BEGIN / START TRANSACTION | Open a new transaction |
| COMMIT | Save every change since BEGIN, permanently |
| ROLLBACK | Discard every change since BEGIN (undo) |
| SAVEPOINT name | Mark a checkpoint inside a transaction |
| ROLLBACK TO name | Undo back to a savepoint, keeping earlier work |
| SET TRANSACTION ISOLATION LEVEL … | Choose READ COMMITTED / SERIALIZABLE etc. |
| A — Atomicity | All statements apply, or none do |
| C — Consistency | Constraints/rules stay satisfied |
| I — Isolation | Concurrent transactions don't interfere |
| D — Durability | Committed data survives crashes |
Frequently Asked Questions
Q: What's the difference between BEGIN and START TRANSACTION?
Nothing meaningful — they're synonyms. PostgreSQL and MySQL accept both; use whichever reads best to you.
Q: If I never type BEGIN, am I in a transaction?
Yes, briefly. In autocommit mode each statement is its own one-statement transaction that commits the moment it succeeds. BEGIN just lets you group several statements before committing.
Q: Does ROLLBACK undo a COMMIT from earlier?
No. ROLLBACK only undoes work since the last BEGIN that hasn't been committed yet. Once you COMMIT, that data is permanent — to reverse it you'd run a new transaction with compensating statements.
Q: Which isolation level should I use?
Start with the default (usually READ COMMITTED). Upgrade specific transactions to SERIALIZABLE only when correctness under concurrency demands it, since stricter levels lock more and can slow things down.
Mini-Challenge: A Safe Transfer
Put it all together — a brief, a blank canvas, and the expected result in the comments. Write a transfer that checks Alice can afford it and ROLLBACKs if she can't, then copy it into a playground to confirm.
🎯 Mini-Challenge
Transfer £400 only if funds are sufficient, else ROLLBACK.
-- 🎯 MINI-CHALLENGE: a SAFE transfer that checks funds first
-- Move £400 from Alice (id 1) to Bob (id 2), but ONLY if Alice can afford it.
--
-- 1. BEGIN a transaction.
-- 2. SELECT Alice's balance (id 1) so your app can read it.
-- 3. In your application code, decide:
-- • if her balance >= 400 → run the two UPDATEs, then COMMIT
-- • else → ROLLBACK (transfer refused, nothing changes)
--
-- ✅ Expected (Alice has 1000): transfer succeeds → Alice 600.00, Bob 900.00.
...🎉 Lesson Complete
- ✅ A transaction is an all-or-nothing group of statements
- ✅
BEGINopens it,COMMITsaves it,ROLLBACKundoes it - ✅ The bank transfer (debit + credit) is the textbook reason transactions exist
- ✅ ACID = Atomicity, Consistency, Isolation, Durability
- ✅ Isolation levels trade speed for protection against dirty / non-repeatable / phantom reads
- ✅ Next: Advanced Queries — window functions and CTEs for serious analytics
Sign up for free to track which lessons you've completed and get learning reminders.