Transactions, Procedures and Interviews · Lesson 28 of 30
SQL Transactions, ACID and Isolation Levels
Transferring ₹1,000 from one account to another takes two updates: subtract from one, add to the other. If the server crashes between them, money vanishes. A transaction groups statements so they succeed or fail together. Transactions are what make databases trustworthy for banking, orders and bookings – and ACID is a guaranteed interview question.
In this lesson
START TRANSACTION,COMMITandROLLBACKSAVEPOINTfor partial rollback- The ACID properties
- Isolation levels, read anomalies, locking and deadlocks
COMMIT and ROLLBACK
CREATE TABLE accounts (id INT PRIMARY KEY, owner VARCHAR(30), balance DECIMAL(10,2) CHECK (balance >= 0));
INSERT INTO accounts VALUES (1, 'Asha', 5000.00), (2, 'Ravi', 1200.00);
-- A transfer that succeeds
START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT; -- both changes are now permanent
SELECT * FROM accounts;
-- A transfer we abandon
START TRANSACTION;
UPDATE accounts SET balance = balance - 3000 WHERE id = 1;
SELECT balance AS asha_inside_transaction FROM accounts WHERE id = 1;
ROLLBACK; -- undo everything since START TRANSACTION
SELECT * FROM accounts;+----+-------+---------+ | id | owner | balance | +----+-------+---------+ | 1 | Asha | 4000.00 | | 2 | Ravi | 2200.00 | +----+-------+---------+ +-------------------------+ | asha_inside_transaction | +-------------------------+ | 1000.00 | +-------------------------+ +----+-------+---------+ | id | owner | balance | +----+-------+---------+ | 1 | Asha | 4000.00 | | 2 | Ravi | 2200.00 | +----+-------+---------+
Life of a transaction
- START TRANSACTIONautocommit is paused
- Statements runchanges are visible to this session only (by default)
- COMMITall changes become permanent and visible to everyone
- or ROLLBACKall changes are undone, as if they never happened
Without START TRANSACTION, MySQL runs in autocommit mode: every statement is its own transaction and is committed immediately.
SAVEPOINT
CREATE TABLE cart (item VARCHAR(30));
START TRANSACTION;
INSERT INTO cart VALUES ('Mouse');
SAVEPOINT after_mouse;
INSERT INTO cart VALUES ('Keyboard'), ('Lamp');
ROLLBACK TO SAVEPOINT after_mouse; -- undo only what came after the savepoint
INSERT INTO cart VALUES ('Notebook');
COMMIT;
SELECT * FROM cart;+----------+ | item | +----------+ | Mouse | | Notebook | +----------+
ACID
The four guarantees of a transaction
- Atomicityall or nothing – no half-done transfer
- Consistencyconstraints hold before and after (e.g. balance ≥ 0)
- Isolationconcurrent transactions don't see each other's unfinished work
- Durabilityonce committed, changes survive crashes (written to the log)
Consistency in action – the CHECK constraint makes an overdraft fail, and the application then rolls back:
START TRANSACTION;
UPDATE accounts SET balance = balance + 9000 WHERE id = 2;
UPDATE accounts SET balance = balance - 9000 WHERE id = 1; -- Asha only has 4000ERROR 3819 (HY000): Check constraint 'accounts_chk_1' is violated.
The failed statement changes nothing, but Ravi's +9000 from the first statement is still pending inside the transaction – your code must issue ROLLBACK on any error. Application frameworks do this for you when you use their transaction helpers.
Isolation levels
When many users work at once, isolation decides what one transaction can see of another's changes. Weaker isolation is faster but allows read anomalies:
| Anomaly | What happens |
|---|---|
| Dirty read | reading another transaction's uncommitted change, which may be rolled back |
| Non-repeatable read | reading the same row twice and getting different values because someone committed in between |
| Phantom read | re-running a query and getting new rows that someone inserted in between |
| Level | Dirty | Non-repeatable | Phantom |
|---|---|---|---|
| READ UNCOMMITTED | possible | possible | possible |
| READ COMMITTED (default in PostgreSQL, SQL Server, Oracle) | no | possible | possible |
| REPEATABLE READ (MySQL InnoDB default) | no | no | mostly prevented in InnoDB |
| SERIALIZABLE | no | no | no |
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;Two sessions, side by side
| Session A | Session B |
|---|---|
START TRANSACTION;SELECT balance FROM accounts WHERE id = 1; → 4000 | |
UPDATE accounts SET balance = 3500 WHERE id = 1; (autocommit) | |
SELECT balance FROM accounts WHERE id = 1; → 4000 in REPEATABLE READ (same snapshot), 3500 in READ COMMITTED | |
COMMIT; |
Locking and deadlocks
Writers lock the rows they change until COMMIT. To read a row and then update it safely (stock checks, seat booking), lock it while reading:
START TRANSACTION;
SELECT stock FROM products WHERE product_id = 8 FOR UPDATE; -- others wait until we finish
UPDATE products SET stock = stock - 1 WHERE product_id = 8;
COMMIT;A deadlock happens when transaction A holds a lock B needs while B holds one A needs. InnoDB detects it and rolls one back with error 1213 – the application should simply retry. Keep transactions short and touch rows in a consistent order to make deadlocks rare.
Common mistakes
- Long transactions that wait for user input – they hold locks and block others.
- Forgetting that DDL (CREATE, ALTER, DROP, TRUNCATE) commits implicitly in MySQL.
- Not rolling back after an error, leaving a half-finished transaction open.
Practice
Quick check
What does Atomicity guarantee?
All statements in a transaction take effect, or none do.
What is MySQL InnoDB's default isolation level?
REPEATABLE READ.
What is a dirty read?
Reading another transaction's uncommitted change.
What should an application do after a deadlock error?
Retry the transaction.
Summary
- START TRANSACTION … COMMIT makes changes permanent together; ROLLBACK undoes them; SAVEPOINT undoes part.
- ACID: atomicity, consistency, isolation, durability.
- Isolation levels trade anomalies for speed; FOR UPDATE locks rows; retry on deadlock.
Interview practice: transactions, ACID and isolation levels.
Finished this lesson?
Mark it complete to track your progress, then carry on with the next lesson.