Transactions & ACID

Reviewed & published by Brayan K

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.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

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

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.

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.

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.

-- The table this lesson uses. Run this block first;
-- every later block queries it.
CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT, balance INTEGER);
INSERT INTO accounts VALUES (1, 'Alice', 1000), (2, 'Bob', 500);

-- 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

-- A transaction returns no rows of its own, so read the table back
-- to see what it did:
SELECT id, name, balance FROM accounts ORDER BY id;

-- The £200 moved across, and 800 + 700 is still 1500 — the same
-- total as before. Money is conserved.

-- ✅ Expected result:
-- id | name | balance
-- 1 | Alice | 800
-- 2 | Bob | 700

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 — fill in the three blanks, then press "Try it Yourself".
-- Uses the accounts table from the first example.
UPDATE accounts SET balance = 1000 WHERE id = 1;
UPDATE accounts SET balance = 500  WHERE id = 2;

-- Goal: safely move £100 from Alice (id 1) to Bob (id 2).

___;   -- 👉 replace ___ with BEGIN

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + ___ WHERE id = 2;  -- 👉 replace ___ with 100

___;   -- 👉 replace ___ with COMMIT

SELECT id, name, balance FROM accounts ORDER BY id;

-- 900 + 600 is still 1500, the same total as before — money conserved.

-- ✅ Expected result:
-- id | name | balance
-- 1 | Alice | 900
-- 2 | Bob | 600

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.

-- Uses the accounts table from the first example.
-- Put the balances back to 1000 and 500 so this example starts
-- from the same place the lesson does, whatever ran before it:
UPDATE accounts SET balance = 1000 WHERE id = 1;
UPDATE accounts SET balance = 500  WHERE id = 2;

-- 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

SELECT id, name, balance FROM accounts ORDER BY id;

-- Both balances are UNCHANGED. Because you never COMMITted,
-- nothing was made permanent.

-- ✅ Expected result:
-- id | name | balance
-- 1 | Alice | 1000
-- 2 | Bob | 500

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 — fill in the one blank to UNDO a mistaken change.
-- Uses the accounts table from the first example.
UPDATE accounts SET balance = 1000 WHERE id = 1;
UPDATE accounts SET balance = 500  WHERE id = 2;

-- A bad UPDATE wiped Alice's balance to 0. Cancel the whole transaction.

BEGIN;

UPDATE accounts SET balance = 0 WHERE id = 1;   -- a mistake!

___;   -- 👉 replace ___ with ROLLBACK

-- Read the table back, and let the query mark its own homework:
SELECT id, name, balance,
       CASE WHEN balance = 0 THEN 'the mistake stuck — did you COMMIT?'
            ELSE 'rolled back cleanly' END AS verdict
FROM accounts
ORDER BY id;

-- The balances are UNCHANGED. Because you never COMMITted, the
-- database forgets the bad UPDATE.

-- ✅ Expected result:
-- id | name | balance | verdict
-- 1 | Alice | 1000 | rolled back cleanly
-- 2 | Bob | 500 | rolled back cleanly

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.

-- Uses the accounts table from the first example.
UPDATE accounts SET balance = 1000 WHERE id = 1;
UPDATE accounts SET balance = 500  WHERE id = 2;

-- 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

SELECT id, name, balance FROM accounts ORDER BY id;

-- Nothing crashed here, so the end state looks identical to the
-- COMMITted version above. That is the danger: autocommit only
-- differs when something goes wrong halfway, and then there is no
-- undo. BEGIN ... COMMIT makes the pair atomic so a crash in the
-- middle takes the first UPDATE back with it.

-- ✅ Expected result:
-- id | name | balance
-- 1 | Alice | 800
-- 2 | Bob | 700

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 UPDATE in between.
• Phantom read — you run the same WHERE filter twice and new matching rows have appeared, because someone committed an INSERT in 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).

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 a BEGIN but 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 with COMMIT (or ROLLBACK).
• 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 a SAVEPOINT) before any further statements will run.
• ROLLBACK with nothing to undo: calling ROLLBACK outside a transaction is a no-op or warning — there's no open BEGIN to cancel. Make sure your BEGIN actually ran first.

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: 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.
--    If Alice only had 300, the ROLLBACK leaves both balances untouched.

-- your transaction here

🎉 Lesson Complete

• ✅ A transaction is an all-or-nothing group of statements
• ✅ BEGIN opens it, COMMIT saves it, ROLLBACK undoes 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

Practice quiz

Continue this course

• Previous: Views & Stored Procedures
• Next: Advanced Queries — CTEs, window functions, and advanced SQL patterns for real-world data
• Quick reference: SQL cheat sheet › Views & Transactions