Mastering MySQL Transactions: ACID, Isolation Levels, and Common Pitfalls

Transaction Concept and Necessity

In MySQL, a transaction is the smallest unit of work that consists of one or more SQL statements; all statements either succeed together or are rolled back together.

If a database lacks a transaction mechanism, what could happen?

Typical financial example (adapted from High Performance MySQL 4th edition): transferring 1,000 CNY from account A to account B requires at least three steps:

Check that account A's balance exceeds 1,000 CNY.

Subtract 1,000 CNY from account A.

Add 1,000 CNY to account B.

These steps must be wrapped in a single transaction; if any step fails, all steps must be rolled back.

Use START TRANSACTION to begin, then either COMMIT to persist changes or ROLLBACK to undo them.

<code>/* Start transaction */
START TRANSACTION;
/* Check balance of account A (123456) */
SELECT balance FROM acount WHERE customer_id=123456;
/* Subtract 1,000 from A */
UPDATE acount SET balance=balance-1000.00 WHERE customer_id=123456;
/* Add 1,000 to B */
UPDATE acount SET balance=balance+1000.00 WHERE customer_id=123457;
/* Commit */
COMMIT;
</code>

Interpretation of the script:

If the server crashes after the fourth statement, user A loses 1,000 CNY while user B receives nothing.

If another process consumes A's entire balance between the third and fourth statements, the bank may unintentionally give B 1,000 CNY.

Financial systems therefore require strict ACID testing. ACID stands for Atomicity, Consistency, Isolation, and Durability. A robust transaction system must satisfy all four.

Transaction's Four Properties (ACID)

The four essential properties of a transaction are:

Atomicity : All operations in a transaction are all‑or‑nothing; on error the transaction rolls back to its initial state.

Consistency : The database’s integrity constraints remain intact before and after the transaction.

Isolation : Concurrent transactions do not interfere with each other; isolation levels (read uncommitted, read committed, repeatable read, serializable) control this.

Durability : Once a transaction commits, its changes are permanently stored, surviving system failures.

How to Ensure Transaction Isolation

3.1 Phenomena Under Concurrency

3.1.1 Dirty Read

Reading uncommitted data from another transaction.

A transaction A modifies data but has not committed; transaction B reads that uncommitted data and uses it, leading to inconsistency.

T1 | A: start transaction | B:
T2 |                     | start transaction
T3 | A: SELECT balance FROM acount WHERE customer_id=123456; | 
T4 | A: UPDATE acount SET balance=balance+1000.00 WHERE customer_id=123457; (uncommitted) | 
T5 |                     | B: SELECT balance FROM acount WHERE customer_id=123457; (reads dirty data)
T6 | A: ROLLBACK (undo 1000) | 
T7 | A: COMMIT |

3.1.2 Non‑Repeatable Read

Multiple reads of the same data return different results.

Transaction A reads a row, transaction B modifies and commits that row, then A reads the same row again and sees a different value.

T1 | A: start transaction | B:
T2 |                     | start transaction
T3 |                     | B: SELECT balance FROM acount WHERE customer_id=123456;
T4 | B: UPDATE acount SET balance=balance+1000.00 WHERE customer_id=123457; (uncommitted)
T5 | A: COMMIT | 
T6 |                     | B: SELECT balance FROM acount WHERE customer_id=123457; (sees 1100)

3.1.3 Phantom Read

Repeated queries return a different number of rows.

Transaction A runs a query that returns N rows; transaction B inserts a new row that matches A’s query condition and commits; A runs the same query again and now sees N+1 rows.

T1 | A: start transaction | B: start transaction
T2 |                     | B: SELECT COUNT(*) FROM account; (returns 2 rows)
T3 | B: INSERT INTO account (pay_id=3, ...) | 
T4 | A: COMMIT | 
T5 |                     | B: INSERT INTO account (pay_id=3, ...) fails due to key conflict

These phenomena illustrate why isolation is crucial for data consistency.

Dirty read – reading uncommitted data.

Non‑repeatable read – reading changed data after another transaction commits.

Phantom read – reading a different set of rows after another transaction inserts or deletes rows.

Isolation levels mitigate these issues:

Read Uncommitted

Read Committed

Repeatable Read

Serializable

3.2 Transaction Isolation Levels

The SQL‑92 standard defines four isolation levels and specifies which phenomena each level prevents.

Isolation Level

Dirty Read

Non‑Repeatable Read

Phantom Read

Read Uncommitted

✔

✔

✗

Read Committed

✗

✔

✗

Repeatable Read

✗

✗

✔

Serializable

✗

✗

✗

Lower isolation levels offer higher concurrency but weaker consistency; higher levels provide stronger guarantees at the cost of performance.

3.2.1 Read Uncommitted

Allows reading uncommitted data, leading to dirty reads and non‑repeatable reads. SELECT statements are non‑locking, giving the highest concurrency but the poorest consistency.

3.2.2 Read Committed

Most databases use this as the default (except MySQL). It prevents dirty reads but still permits non‑repeatable reads. Regular SELECTs use snapshot reads; locked statements use row‑level locks.

3.2.3 Serializable

The strictest level; all SELECTs are implicitly converted to SELECT ... IN SHARE MODE , blocking reads of rows being modified until the transaction finishes. It eliminates dirty reads, non‑repeatable reads, and phantom reads but greatly reduces concurrency.

<code>SELECT ... IN SHARE MODE;</code>

3.2.4 Repeatable Read (RR)

InnoDB’s default level. SELECTs use snapshot reads (MVCC) for consistent non‑locking reads. Updates, deletes, and SELECT … FOR UPDATE acquire row‑level locks when operating on unique or range indexes. RR prevents dirty and non‑repeatable reads but may still allow phantom reads.

Summary

Understand ACID (Atomicity, Consistency, Isolation, Durability) and its implementation.

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

Know the four SQL‑92 isolation levels and how InnoDB implements them.

InnoDB defaults to Repeatable Read; the most commonly used level in web applications is Read Committed, which prevents dirty and phantom reads while balancing performance.