A transaction is a group of SQL operations that execute as a single unit — either all of them succeed, or none of them do. If anything fails partway through, everything gets undone, leaving the database exactly as it was before.
Why transactions exist
Imagine transferring money between two bank accounts. It takes two steps:
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- withdraw from A
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- deposit to BIf the server crashes between these two statements, money vanishes — deducted from account 1, never added to account 2. A transaction guarantees this can't happen: either both updates apply, or neither does.
Without a transaction: With a transaction: Step 1 succeeds ✔ Step 1 succeeds ✔ ┐ Step 2 fails ✘ Step 2 fails ✘ ├─> entire group rolled back Result: money lost Result: nothing changed at all
Basic syntax
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;START TRANSACTION— begin the group (BEGINalso works in MySQL, same thing)COMMIT— save all changes permanentlyROLLBACK— undo everything sinceSTART TRANSACTION
Rolling back on failure
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Something went wrong (e.g. application detects insufficient funds,
-- a constraint violation, or any other error condition)
ROLLBACK;
-- The UPDATE above never happened, as far as the database is concernedAfter ROLLBACK, it's as if the transaction never ran at all — even though the UPDATE statement executed, nothing was actually saved.
Autocommit — what happens without a transaction
By default, MySQL runs in autocommit mode: every single statement is its own implicit transaction, committed immediately. That's why all your CRUD statements so far worked without ever typing COMMIT.
-- With autocommit ON (the default), this commits instantly on its own
UPDATE books SET price = 9.99 WHERE id = 1;START TRANSACTION temporarily suspends autocommit until you COMMIT or ROLLBACK — that's what lets you group multiple statements together.
SAVEPOINT — partial rollbacks
You can mark a point inside a transaction and roll back to just that point, without undoing everything:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT after_withdrawal;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Only undo the second update, keep the withdrawal
ROLLBACK TO after_withdrawal;
COMMIT;Example table for testing transactions
Create a simple accounts table with some initial data:
CREATE TABLE accounts (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
balance DECIMAL(10, 2) NOT NULL
);Insert some sample accounts:
INSERT INTO accounts (name, balance)
VALUES ('Alice', 5000), ('Bob', 2000);Check the initial balances:
SELECT * FROM accounts;+----+-------+---------+ | id | name | balance | +----+-------+---------+ | 1 | Alice | 5000.00 | | 2 | Bob | 2000.00 | +----+-------+---------+
Start a transaction to transfer money from Alice to Bob:
START TRANSACTION;
-- OR
-- BEGIN;These are commonly used to begin a transaction.
Perform the transfer:
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;At this point, the changes are part of the transaction.
Commit the transaction to save the changes:
COMMIT;The transaction is successfully completed.
COMMITmeans: Make the changes permanent.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;
COMMIT;START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;
ROLLBACK;
ROLLBACKmeans: Undo the changes made during the current transaction.
The basic transaction lifecycle
ACID
Once you understand the basic transaction, the next major concept is ACID.
A transaction is generally discussed in terms of four properties:
A → Atomicity C → Consistency I → Isolation D → Durability
1. Atomicity
Atomicity means all operations in a transaction succeed, or none of them do.
Transfer:
Alice -1000 Bob +1000
Either: Both happen ✔ or: Neither happens ✔
Not:
Alice -1000 Bob unchanged ✘
Atomicity = All operations succeed, or none of them are applied.
2. Consistency
Consistency means the transaction moves the database from one valid state to another valid state, preserving defined constraints and rules.
Suppose your business rule is: balance >= 0
Before:
Alice = 5000 Bob = 2000
Transfer:
Alice -1000 Bob +1000
After:
Alice = 4000 Bob = 3000
The database remains valid.
But consistency is broader than "the query didn't error." It includes things such as:
PRIMARY KEY constraints FOREIGN KEY constraints UNIQUE constraints CHECK constraints application/business invariants
Consistency = A transaction must preserve the rules and constraints that define a valid database state.
Atomicity is about whether the whole transaction happens. Consistency is about whether the resulting state obeys the rules.
3. Isolation
When multiple transactions run at the same time, one transaction should not improperly interfere with another transaction.
Imagine:
User A --> UPDATE balance --> MySQL <-- SELECT balance <-- User B
What happens if two transactions access the same data simultaneously?
For example:
Transaction A Transaction B
Can one transaction see another transaction's uncommitted changes?
Can two transactions update the same row?
Can one transaction read different values during the same transaction?
These are concurrency problems.
Isolation controls how transactions interact with each other.
Isolation = Concurrent transactions should not improperly interfere with each other's work.
4. Durability
Durability means once a transaction is successfully committed, its changes should survive subsequent failures such as a database/server crash, subject to the storage engine's durability configuration.
For example:
START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;
COMMIT;After COMMIT, you expect: Alice = 4000
Even if the database server restarts afterward, the committed change should not simply disappear.
MySQL's InnoDB storage engine provides transactional durability using mechanisms including its redo log.
Durability = Once a transaction commits successfully, its changes are persisted and should survive failures.
| ACID Property | How transactions provide it |
|---|---|
| Atomicity | COMMIT/ROLLBACK ensures all-or-nothing execution |
| Consistency | Constraints (NOT NULL, FOREIGN KEY, CHECK) are enforced even mid-transaction |
| Isolation | Other transactions don't see your changes until you COMMIT |
| Durability | Once COMMIT succeeds, the change survives even a crash |
Atomicity → all or nothing
Consistency → valid state → valid state
Isolation → concurrent transactions
Durability → committed means persistent
Isolation levels (brief intro)
Isolation controls how much one transaction can "see" of another transaction's in-progress changes. MySQL's default is REPEATABLE READ.
| Isolation Level | What it prevents | Trade-off |
|---|---|---|
| READ UNCOMMITTED | Nothing — can see uncommitted changes from other transactions ("dirty reads") | Fastest, least safe |
| READ COMMITTED | Dirty reads | Still allows some inconsistency between repeated reads |
| REPEATABLE READ (MySQL default) | Dirty reads + non-repeatable reads | Good balance of safety and performance |
| SERIALIZABLE | All concurrency anomalies | Safest, but slowest — transactions effectively run one at a time |
You'll rarely need to change this as a beginner — just know it exists and that it's a real lever for handling high-concurrency systems correctly.
Rule of thumb: wrap multiple related writes in a transaction whenever a partial failure would leave your data in a broken or inconsistent state — money transfers, order + inventory updates, or any "these things must happen together" scenario.
Quick Reference
| Command | Purpose |
|---|---|
START TRANSACTION | Begin a transaction, suspending autocommit |
COMMIT | Save all changes permanently |
ROLLBACK | Undo all changes since the transaction began |
SAVEPOINT name | Mark a point to roll back to, without undoing everything |
ROLLBACK TO name | Undo back to a specific savepoint only |
Interview Questions
1. What is a database transaction?
A transaction is a group of database operations treated as a single unit of work.
For example, transferring money from Alice to Bob requires two operations: deducting money from Alice and adding it to Bob.
If both operations succeed, we COMMIT the transaction. If something fails, we ROLLBACK so the database does not keep a partial update.
2. Why do we need transactions?
Transactions prevent a database from being left in an incorrect state when multiple related operations are involved.
For example, during a money transfer, if money is deducted from Alice but the application crashes before adding it to Bob, the database becomes inconsistent.
A transaction ensures that either all related operations succeed or all of them are undone.
3. What are ACID properties?
ACID describes four important properties of reliable database transactions:
- Atomicity — All operations succeed or none are applied.
- Consistency — The transaction keeps the database in a valid state.
- Isolation — Concurrent transactions should not improperly interfere with each other.
- Durability — Once a transaction is committed, its changes should survive failures.
An easy way to remember it is: All or nothing, valid state, safe concurrency, and committed data persists.
4. What is Atomicity?
Atomicity means a transaction is treated as one unit: either all of its operations succeed, or none of them are applied.
For example, a bank transfer has two operations: deduct money from Alice and add money to Bob.
If the second operation fails, the first operation is rolled back as well.
In simple terms: Atomicity means "all or nothing."
5. What is Consistency in ACID?
Consistency means a successful transaction moves the database from one valid state to another valid state while respecting its constraints and rules.
For example, if a database has a rule that an account balance cannot be negative, a transaction should not leave the database with an invalid negative balance.
In simple terms: valid state → transaction → valid state.
6. What is Isolation in a transaction?
Isolation controls how concurrent transactions interact with each other.
For example, if two users are modifying the same account at the same time, one transaction should not improperly see or interfere with another transaction's intermediate changes.
Isolation is important because real applications have many transactions running concurrently.
7. What is Durability in ACID?
Durability means that once a transaction is successfully committed, its changes should be persisted and survive failures such as a database or server crash, subject to the database's durability configuration.
In simple terms: COMMIT means the successful changes should not simply disappear after a failure.
8. What is the difference between COMMIT and ROLLBACK?
COMMIT permanently applies the successful changes made by the transaction.
ROLLBACK undoes the changes made during the current transaction.
For example:
COMMIT means "save these changes."
ROLLBACK means "undo these transaction changes."
9. What is the difference between Atomicity and Consistency?
Atomicity is about whether the transaction is applied completely or not at all.
Consistency is about whether the resulting database state follows the defined constraints and rules.
For example, Atomicity asks: "Did both sides of the money transfer happen?"
Consistency asks: "Is the resulting database state still valid?"
10. What is a Dirty Read?
A dirty read happens when one transaction reads data changed by another transaction before that change has been committed.
For example, Transaction A changes a balance from 5000 to 4000 but has not committed yet.
If Transaction B reads 4000 and Transaction A later performs a ROLLBACK, Transaction B has read data that was never committed.
That is called a dirty read.
11. What is a Non-Repeatable Read?
A non-repeatable read happens when a transaction reads the same row twice but gets different values because another transaction changed and committed that row between the two reads.
For example:
First read: balance = 5000
Another transaction changes and commits it.
Second read: balance = 4000
The same transaction read the same row twice but received different values.
12. What is a Phantom Read?
A phantom read happens when the same query is executed twice in a transaction and the set of matching rows changes because another transaction inserted, deleted, or otherwise changed rows that affect the query.
For example, the first query finds 2 accounts with a balance above 3000.
Another transaction inserts a new account with a balance of 4000 and commits.
The same query now finds 3 accounts.
The new matching row is called a phantom row.
13. What are the isolation levels in MySQL?
MySQL provides four standard transaction isolation levels:
READ UNCOMMITTEDREAD COMMITTEDREPEATABLE READSERIALIZABLE
They control how much one transaction can observe the effects of other concurrent transactions.
InnoDB's default isolation level in MySQL is REPEATABLE READ.
14. What is the difference between Dirty Read, Non-Repeatable Read, and Phantom Read?
The difference is what changes between reads:
- Dirty Read — You see another transaction's uncommitted data.
- Non-Repeatable Read — The value of an existing row changes between reads.
- Phantom Read — The set of rows returned by a query changes between reads.
A simple way to remember them:
Dirty = uncommitted data.
Non-repeatable = same row, different value.
Phantom = same query, different rows.
15. What is the default isolation level in MySQL?
The default isolation level for the InnoDB storage engine in MySQL is REPEATABLE READ.
You can check the current isolation level with:
SELECT @@transaction_isolation;The important point is that MySQL does not simply run every transaction completely separately. InnoDB uses mechanisms such as MVCC and locking to provide its isolation behavior.