Why Transactions Matter: Mastering MySQL ACID and Isolation Levels

Transaction Basics

In MySQL a transaction is the smallest unit of work that consists of one or more SQL statements; it either commits all changes or rolls back entirely, ensuring database consistency and integrity. Only storage engines that support transactions, such as InnoDB, can use this feature.

Financial Transfer Example

A classic banking scenario demonstrates why the three steps—checking account A balance, deducting from A, and crediting account B—must be wrapped in a single transaction. If any step fails, the whole operation is rolled back.

START TRANSACTION;
SELECT balance FROM account WHERE customer_id = 123456;  -- check A >= 1000
UPDATE account SET balance = balance - 1000.00 WHERE customer_id = 123456;  -- debit A
UPDATE account SET balance = balance + 1000.00 WHERE customer_id = 123457;  -- credit B
COMMIT;  -- persist changes
-- or ROLLBACK; if any statement fails

Script interpretation: If the server crashes before the fourth statement, user A loses 1000 while B receives nothing; if another process empties A’s balance between the third and fourth statements, the bank may over‑pay B.

ACID Properties

Atomicity : All statements succeed or none do; a failure rolls back to the state before the transaction.

Consistency : The database remains valid according to all defined rules after commit.

Isolation : Concurrent transactions do not interfere; isolation levels control the degree of visibility.

Durability : Once committed, changes survive crashes and power loss.

Concurrency Phenomena

Dirty Read

Transaction B reads data modified by transaction A before A commits. Example timeline shows B reading a 100‑unit balance that A later rolls back.

Non‑repeatable Read

Transaction A reads the same row twice and gets different values because transaction B committed an update in between.

Phantom Read

Transaction A executes the same query twice and sees a different number of rows because transaction B inserted or deleted rows between the two reads.

Isolation Levels (SQL‑92)

The standard defines four levels, each preventing a subset of the above anomalies:

Read Uncommitted : Allows dirty reads and non‑repeatable reads; prevents none of the anomalies.

Read Committed : Prevents dirty reads; still vulnerable to non‑repeatable reads.

Repeatable Read (InnoDB default): Prevents dirty and non‑repeatable reads; phantom reads may still occur.

Serializable : Eliminates all three anomalies by forcing transactions to execute as if serially, at the cost of high contention.

In practice, most internet services use Read Committed for a balance between consistency and concurrency, while InnoDB defaults to Repeatable Read for stronger guarantees.

Isolation Level Execution Sketches

Below are simplified timelines showing how each level handles overlapping statements.

-- Read Uncommitted example
START TRANSACTION;   -- T1 (A)
SELECT * FROM classes;   -- T2 (B reads uncommitted data)
-- ...
COMMIT;   -- T3 (A commits)

-- Read Committed example
START TRANSACTION;   -- T1 (A)
SELECT * FROM classes;   -- T2 (B reads only committed data)
UPDATE account SET balance = balance + 1000 WHERE customer_id = 123457;   -- T3 (B)
COMMIT;   -- T4 (A commits)

-- Serializable example (uses locking)
START TRANSACTION;   -- T1 (A)
SELECT * FROM classes IN SHARE MODE;   -- T2 (A holds shared lock)
-- B must wait until A commits
COMMIT;   -- T3 (A releases lock)

Conclusion

Understand the four ACID properties and why they matter.

Recognize dirty reads, non‑repeatable reads, and phantom reads.

Know how InnoDB implements the SQL‑92 isolation levels.

InnoDB defaults to Repeatable Read; most web applications prefer Read Committed for a good trade‑off between consistency and concurrency.