Transferring money between two bank accounts needs two UPDATE statements: subtract from one, add to the other. If the first succeeds and the second fails, money has vanished. A transaction is how SQL guarantees that never happens, either every statement in the group succeeds, or none of them do.
Learning Objectives
- Group multiple statements into a transaction with BEGIN, COMMIT, and ROLLBACK.
- Explain what the four ACID guarantees mean in practice.
- Recognize situations where wrapping statements in a transaction actually matters.
BEGIN
Start a transaction
Multiple Statements
INSERT, UPDATE, DELETE
COMMIT
Success, changes saved
or ROLLBACK
Failure, every change undone
A Basic Transaction
BEGIN starts the transaction. Every statement after it is provisional, not permanently saved, until COMMIT. If both UPDATEs succeed, COMMIT makes them permanent together, atomically, as a single unit.
BEGIN; UPDATE accounts SET balance = balance - 500 WHERE id = 1; UPDATE accounts SET balance = balance + 500 WHERE id = 2; COMMIT;
BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 500 WHERE id = 1; UPDATE accounts SET balance = balance + 500 WHERE id = 2; COMMIT TRANSACTION;
ROLLBACK: Undoing a Transaction
ROLLBACK discards every change made since BEGIN, restoring the data to exactly how it was before the transaction started. This is what makes it safe to run multiple related statements, if anything goes wrong partway through, nothing is left half-applied.
BEGIN; UPDATE accounts SET balance = balance - 500 WHERE id = 1; -- Something goes wrong here, insufficient funds, a failed check, an application error ROLLBACK; -- The balance update above is completely undone, as if it never ran
The Four ACID Guarantees
| Guarantee | What it means |
|---|---|
| Atomicity | All statements in a transaction succeed together, or none of them take effect at all |
| Consistency | A transaction can only move the database from one valid state to another, constraints are never violated |
| Isolation | Transactions running at the same time don't see each other's uncommitted changes |
| Durability | Once COMMIT succeeds, the change survives, even a crash immediately after |
ACID is the formal name for the guarantees transactions provide. MySQL (with the InnoDB engine, the modern default), PostgreSQL, SQL Server, and SQLite are all fully ACID-compliant databases.
When Transactions Actually Matter
- Any operation involving money, inventory counts, or anything where a partial update would be actively wrong, not just inconvenient.
- Multiple related INSERTs that only make sense together, like an order and its line items.
- Any bulk UPDATE or DELETE you're not fully confident in, since a transaction can be rolled back if the affected-row count looks wrong before committing.
Frequently Asked Questions
Is every single SQL statement automatically its own transaction if I don't write BEGIN?
Yes, this is called auto-commit mode, and it's the default in MySQL, PostgreSQL, SQL Server, and SQLite. Each individual statement commits immediately on its own unless you explicitly start a transaction with BEGIN first.
Can I read data mid-transaction, before committing?
Yes, SELECT statements inside the same transaction see its own uncommitted changes. What other, separate transactions see of your uncommitted changes depends on the isolation level, a more advanced topic beyond this lesson.
What happens if the connection drops in the middle of a transaction, before COMMIT?
The transaction is automatically rolled back, nothing is saved. This is part of what Durability and Atomicity guarantee together, a transaction only ever fully applies or fully doesn't.
Common Beginner Mistakes
Forgetting to COMMIT, and assuming changes were saved
A transaction's changes aren't permanent until COMMIT runs. Code that BEGINs a transaction and never explicitly commits (or rolls back) can leave changes pending, or lose them entirely if the connection closes.
Wrapping a single simple statement in a transaction out of habit
A lone statement is already atomic by default under auto-commit, wrapping it in BEGIN/COMMIT adds no safety, just extra code. Transactions earn their keep specifically when multiple statements need to succeed or fail together.
Leaving a transaction open for a long time while doing unrelated work
An open transaction can hold locks on rows it's touched, blocking other connections from updating them until it commits or rolls back. Keep transactions as short as the related statements actually require.
Best Practices
- Use a transaction whenever multiple statements must succeed or fail together as one logical unit, not for single standalone statements.
- Keep transactions short, don't hold one open across slow, unrelated work like an external API call.
- Always have an explicit COMMIT or ROLLBACK, don't leave a transaction open and hope it resolves itself.
- Test the ROLLBACK path deliberately, not just the success path, before trusting a transaction in production.
Interview Questions
Write a transaction that transfers 500 from account 1 to account 2.
BEGIN; UPDATE accounts SET balance = balance - 500 WHERE id = 1; UPDATE accounts SET balance = balance + 500 WHERE id = 2; COMMIT; — both updates succeed together under one transaction, so the money is never left in a half-transferred state.
Explain Atomicity and Durability in your own words, and how they differ.
Atomicity is about the moment of execution: every statement in a transaction takes effect together, or none do, there's no partial state. Durability is about after commit: once COMMIT has succeeded, that change is permanent and survives even a crash immediately afterward. One guarantees all-or-nothing, the other guarantees it sticks once it happens.
Why is wrapping every single standalone UPDATE in BEGIN/COMMIT unnecessary?
Under auto-commit mode (the default in every major SQL database), each individual statement is already its own implicit transaction, atomic and durable on its own. Explicit transactions add value specifically when multiple statements need to be treated as one unit, not for a single statement that's already atomic by itself.
Summary
This lesson covered grouping statements into a transaction with BEGIN and COMMIT, undoing one with ROLLBACK, the four ACID guarantees (Atomicity, Consistency, Isolation, Durability), and when a transaction actually earns its keep versus adding unnecessary complexity.
That completes Module 3, Database Design: primary/foreign keys, constraints, indexes, and transactions, on top of the SELECT and data-modification fundamentals from the first two modules.
What's Next?
Module 4, Advanced Queries, is next: window functions, common table expressions, set operations, and views.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.