Going Deeper: Professional SQL›Pro· 35 min read

Transactions: COMMIT, ROLLBACK & ACID

Group several statements into one all-or-nothing unit — so a money transfer can never leave your data half-finished.

What you will learn

Group statements in a transaction with START TRANSACTION and COMMIT
Undo a transaction with ROLLBACK
Explain the ACID guarantees and why they matter

The half-finished disaster

Some real-world actions need several SQL statements that must all succeed or none of them. The classic example is a bank transfer: subtract 500 from Asha, add 500 to Ravi. If the server crashes between those two statements, Asha has lost 500 and Ravi never received it — money has vanished. A transaction prevents this by treating a group of statements as one indivisible unit: either every statement sticks, or they are all undone together.

START TRANSACTION, COMMIT, ROLLBACK

A transaction has three keywords. Read the steps, then the code:

Begin with START TRANSACTION; — from here, your changes are held in a pending state, not yet permanent.
Run your statements — the UPDATEs that move the money.
Finish with COMMIT; to make everything permanent at once, or ROLLBACK; to throw away all of it as if it never happened.

A money transfer as one all-or-nothing transaction

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

Line by line: START TRANSACTION opens the unit. The first UPDATE takes 500 from account 1 (Asha); the second adds 500 to account 2 (Ravi). Until COMMIT, neither change is visible to other users — they are pending. COMMIT then applies both at the exact same moment. If anything had gone wrong before COMMIT, you could type ROLLBACK instead and both updates would be cancelled, leaving balances exactly as they started.

Note: Output: Query OK, 1 row affected. -- first UPDATE Query OK, 1 row affected. -- second UPDATE Query OK, 0 rows affected. -- COMMIT makes it permanent After COMMIT, Asha is down 500 and Ravi is up 500 — together, atomically. There was never a moment visible to others where the money existed in neither account.

Using ROLLBACK to undo a mistake

ROLLBACK is your safety net. Imagine you start a transaction, run an UPDATE, and then realise the condition was wrong:

ROLLBACK cancels everything since START TRANSACTION

START TRANSACTION;

UPDATE products SET price = price * 0.5;   -- oops, no WHERE — halved EVERY price!

ROLLBACK;

The UPDATE accidentally halved every product’s price (no WHERE). But because we are inside a transaction and have not committed, ROLLBACK wipes the change completely — prices snap back to what they were. Outside a transaction, that UPDATE would have been permanent immediately. This is why careful developers wrap risky changes in a transaction first.

Note: Output: Query OK, 200 rows affected. -- the bad UPDATE Query OK, 0 rows affected. -- ROLLBACK undoes all 200 All 200 price changes were reversed. After ROLLBACK the table is byte-for-byte what it was before START TRANSACTION.

ACID — the four guarantees

Transactions give four guarantees, remembered by the word ACID. These are why banks and shops trust relational databases:

Letter	Means	In plain words
A — Atomicity	All or nothing	Every statement sticks, or none do
C — Consistency	Rules stay true	Constraints/keys are never left broken
I — Isolation	No half-views	Concurrent transactions don’t see each other’s pending changes
D — Durability	Survives crashes	Once committed, data is safe even if power fails

Isolation has levels that control how strictly concurrent transactions are kept apart (READ COMMITTED, REPEATABLE READ — MySQL’s default, SERIALIZABLE). Higher levels prevent more subtle bugs (like reading data another transaction later rolls back) but allow less concurrency. For most apps the default REPEATABLE READ is exactly right; you only tune it when you hit specific concurrency problems.

Watch out: Transactions only fully work on storage engines that support them — MySQL’s default InnoDB does. The older MyISAM engine ignores transactions, so ROLLBACK does nothing there. Always use InnoDB (the default in modern MySQL) for any data that matters.

Tip: A simple rule: whenever two or more writes must succeed together to keep your data correct — transfers, placing an order that also reduces stock, anything money-related — wrap them in START TRANSACTION … COMMIT, and ROLLBACK on any error.

Q. You ran two UPDATEs inside a transaction but the second failed. What do you do to leave the data unchanged?

Answer: ROLLBACK cancels every change made since START TRANSACTION, so the data returns to its original state. COMMIT would instead make the partial changes permanent.

✍️ Practice

Wrap a two-step balance transfer in START TRANSACTION … COMMIT and confirm both rows changed.
Start a transaction, make a change, then ROLLBACK and verify the data is back to how it was.

🏠 Homework

Write a transaction that places an order AND decreases the product’s stock, committing only if both succeed.