Topic 27 of 52 · Expert
Transactions & ACID
COMMIT, ROLLBACK, isolation levels and locking.
Concept
A transaction groups statements so they all succeed or all fail. ACID: Atomicity, Consistency, Isolation, Durability.
Isolation levels trade safety for concurrency: READ UNCOMMITTED (dirty reads), READ COMMITTED, REPEATABLE READ (InnoDB default) and SERIALIZABLE.
SELECT ... FOR UPDATE locks rows you plan to change. Deadlocks happen when two transactions wait on each other; InnoDB rolls one back, so apps should retry.
Cheat sheet
| Level | Dirty read | Non-repeatable | Phantom |
|---|---|---|---|
| READ UNCOMMITTED | Yes | Yes | Yes |
| READ COMMITTED | No | Yes | Yes |
| REPEATABLE READ | No | No | Mostly no (InnoDB) |
| SERIALIZABLE | No | No | No |
Code (SQL · MySQL 8)
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT; -- or ROLLBACK on errorRemember
- InnoDB default isolation = REPEATABLE READ.
- Lock rows in a consistent order to avoid deadlocks.
- Keep transactions short.
Interview questions
Explain ACID.
Atomicity: all or nothing. Consistency: constraints stay valid. Isolation: concurrent transactions don't see each other's partial work. Durability: committed data survives crashes.
What is a phantom read?
Re-running a range query in the same transaction returns new rows inserted by another transaction.
Lesson test
Take an 8-question test on “Transactions & ACID” — concepts, output prediction, bug spotting and interview questions.