Chapter 17: Transactions & Concurrency
Transactions are what make MySQL (and any good database) trustworthy — especially when multiple people or programs are using the database at the same time.
Today we’ll learn:
- What ACID really means (why your money doesn’t disappear!)
- How to use START TRANSACTION, COMMIT, ROLLBACK
- SAVEPOINT — like mini checkpoints
- Isolation levels — how much one user can see what others are doing
- LOCK TABLES — when you really need to stop everyone else
Let’s use a real-life bank example to make everything crystal clear.
Imagine two bank accounts:
- Account A (Priya) — balance = ₹10,000
- Account B (Rahul) — balance = ₹5,000
Priya wants to transfer ₹3,000 to Rahul.
This should happen in one atomic operation:
- Subtract ₹3,000 from Priya
- Add ₹3,000 to Rahul
If anything fails in the middle (power cut, crash, error), neither account should change — otherwise money would vanish or appear from nowhere!
1. ACID Properties – The 4 Golden Rules of Transactions
| Letter | Property | What it means | Bank Example |
|---|---|---|---|
| A | Atomicity | All or nothing — entire transaction succeeds or fails completely | Either both accounts update, or neither does |
| C | Consistency | Database remains in a valid state before & after transaction | Total money in bank stays ₹15,000 before & after |
| I | Isolation | Transactions don’t interfere with each other until committed | Another user can’t see Priya’s balance mid-transfer |
| D | Durability | Once committed, changes are permanent — even if server crashes | After commit, ₹3,000 is safely transferred forever |
MySQL’s InnoDB storage engine (default) fully supports ACID!
2. START TRANSACTION, COMMIT, ROLLBACK
Syntax:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
START TRANSACTION; -- or BEGIN; -- Your SQL statements COMMIT; -- Save changes permanently -- OR ROLLBACK; -- Undo everything |
Example – Safe bank transfer
First, create sample tables:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE accounts ( account_id INT PRIMARY KEY, account_holder VARCHAR(50), balance DECIMAL(10,2) ); INSERT INTO accounts VALUES (1, 'Priya Sharma', 10000.00), (2, 'Rahul Patel', 5000.00); |
Now the safe transfer:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
START TRANSACTION; UPDATE accounts SET balance = balance - 3000.00 WHERE account_id = 1; UPDATE accounts SET balance = balance + 3000.00 WHERE account_id = 2; -- Check midway (only you can see these changes!) SELECT * FROM accounts; -- If everything is fine → save forever COMMIT; |
If something goes wrong (simulate error):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
START TRANSACTION; UPDATE accounts SET balance = balance - 3000.00 WHERE account_id = 1; -- Oops! Internet crash or error — we ROLLBACK ROLLBACK; -- Now balances are back to original! SELECT * FROM accounts; |
Real power: If the server crashes after COMMIT → changes are saved (Durability). If crash before COMMIT → changes are automatically rolled back (Atomicity).
3. SAVEPOINT – Mini Checkpoints Inside a Transaction
Syntax:
|
0 1 2 3 4 5 6 7 8 |
SAVEPOINT savepoint_name; ROLLBACK TO savepoint_name; |
Example – Transfer with safety checkpoints
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
START TRANSACTION; UPDATE accounts SET balance = balance - 3000.00 WHERE account_id = 1; SAVEPOINT after_deduct; UPDATE accounts SET balance = balance + 3000.00 WHERE account_id = 2; SAVEPOINT after_add; -- Suppose error happens here (e.g., Rahul’s account is frozen) -- We rollback only the add part, keep the deduct ROLLBACK TO after_deduct; -- Now we can decide: COMMIT or ROLLBACK completely COMMIT; |
Result: Priya’s account is deducted, Rahul’s is not — we can fix and retry the add.
4. Isolation Levels – How Much One Transaction Sees Others
MySQL has 4 isolation levels (from strictest to loosest):
| Level | What it prevents | Dirty Read? | Non-repeatable Read? | Phantom Read? | Performance |
|---|---|---|---|---|---|
| READ UNCOMMITTED | Almost nothing | Yes | Yes | Yes | Fastest |
| READ COMMITTED | Dirty reads | No | Yes | Yes | Good |
| REPEATABLE READ | Dirty + Non-repeatable reads (MySQL default) | No | No | Yes | Balanced |
| SERIALIZABLE | All anomalies | No | No | No | Slowest |
Real-life example of problems:
Dirty Read (READ UNCOMMITTED): Priya sees Rahul’s balance as ₹8,000 mid-transfer — but if Rahul rolls back, she saw fake data!
Non-repeatable Read (READ COMMITTED): Priya reads her balance twice in one transaction → second time it’s changed by Rahul’s transfer!
Phantom Read (REPEATABLE READ): Priya counts customers with balance > ₹5000 → first time 10, second time 11 because Rahul added a new customer.
How to set isolation level:
|
0 1 2 3 4 5 6 7 |
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- or READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE |
Check current level:
|
0 1 2 3 4 5 6 |
SELECT @@transaction_isolation; |
5. LOCK TABLES – When You Need Exclusive Access
Syntax:
|
0 1 2 3 4 5 6 7 8 9 10 |
LOCK TABLES table_name [READ | WRITE]; -- Do your work UNLOCK TABLES; |
READ lock: Others can read, but no one can write WRITE lock: Only you can read/write — others wait
Example – Safe bulk update (prevent others from interfering)
|
0 1 2 3 4 5 6 7 8 9 10 |
LOCK TABLES accounts WRITE; UPDATE accounts SET balance = balance * 1.05; -- 5% bonus to everyone UNLOCK TABLES; |
Caution: LOCK TABLES can cause deadlocks or slow performance — use only when necessary. InnoDB usually handles row-level locking automatically — prefer SELECT … FOR UPDATE for row locks.
Modern way (row-level lock):
|
0 1 2 3 4 5 6 7 8 9 |
START TRANSACTION; SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE; -- Locks only this row -- Do your update COMMIT; |
That’s it for Chapter 17! 🎉 You now understand how to keep data safe, consistent, and isolated even when 1000 users are hitting your database at once!
Homework for today (do it right now – very important!) Run these commands and paste the output of the final SELECTs:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
-- 1. Create accounts table (if not done) CREATE TABLE accounts ( account_id INT PRIMARY KEY, account_holder VARCHAR(50), balance DECIMAL(10,2) ); INSERT INTO accounts VALUES (1, 'Priya Sharma', 10000.00), (2, 'Rahul Patel', 5000.00); -- 2. Safe transfer with transaction START TRANSACTION; UPDATE accounts SET balance = balance - 2000.00 WHERE account_id = 1; UPDATE accounts SET balance = balance + 2000.00 WHERE account_id = 2; SELECT * FROM accounts; -- midway check (only you see) COMMIT; -- 3. Check final balances SELECT * FROM accounts; -- 4. Try rollback START TRANSACTION; UPDATE accounts SET balance = balance - 1000.00 WHERE account_id = 1; ROLLBACK; SELECT * FROM accounts; -- should be back to previous |
Paste the results of the three SELECT * FROM accounts; — I’ll check if transactions worked correctly!
