Transactions

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

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.

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.

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.

Your Turn: undo a mistake with ROLLBACK

One blank this time — add the keyword that cancels the transaction so Alice's balance is restored.

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.

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

Level

Dirty Read

Non-Repeatable

Phantom

READ UNCOMMITTED

⚠️ possible

READ COMMITTED

✅ prevented

REPEATABLE READ

SERIALIZABLE

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.

📘 Quick Reference

Syntax

Purpose

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.

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.

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 ROLLBACK s if she can't, then copy it into a playground to confirm.

🎉 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