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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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.

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.

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.

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.

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.

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

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.

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.

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:

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.

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.

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.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary