Why Transaction Isolation Is Needed and Understanding MySQL Isolation Levels

Author: Cup Uncle Book Link: https://www.jianshu.com/p/29b33f210c0f

Why Transaction Isolation Is Needed

Databases serve many clients simultaneously, so multiple requests can hit the database at the same time, even from the same client executing several concurrent transactions. When concurrent operations target the same data, problems such as dirty writes, dirty reads, non‑repeatable reads, and phantom reads can arise. Transaction isolation is designed to solve these concurrency issues.

Understanding Multi‑Transaction Concurrency Issues

Lost Update (Dirty Write) When two transactions modify the same row based on the original value without knowing about each other, the later update can overwrite the earlier one, causing lost updates.

In the illustration, both transactions update id=1 . The final persisted value belongs to only one transaction; the other update is lost, leading to confusion for the transaction that was overwritten.

Dirty Reads A transaction modifies a record but has not yet committed. Another transaction reads that uncommitted data and proceeds based on it. If the first transaction later rolls back, the second transaction has acted on “dirty” data.

Example: Transaction A updates a=50 . Transaction B reads a=50 and performs business logic, but later Transaction A rolls back. Transaction B’s work proceeds on invalid data, which is a dirty read.

Non‑Repeatable Reads A transaction reads a row, then later reads the same row again and finds that the data has changed or the row has been deleted.

In the diagram, each time Transaction A queries id=1 , the value of a differs, making it impossible for Transaction A to rely on a stable result.

Phantom Reads A transaction re‑executes a query with the same condition and discovers new rows inserted by another transaction that satisfy the condition.

Example: Transaction A initially finds two rows with id<10 . Transaction B inserts a third row that also satisfies id<10 . When Transaction A queries again, it sees three rows – a phantom read.

Understanding Transactions

A transaction is a logical unit composed of a group of SQL statements. It possesses four ACID properties:

Atomicity : All operations succeed together or none are applied.

Consistency : Data remains in a valid state before and after the transaction.

Isolation : Transactions operate independently without affecting each other.

Durability : Once committed, changes survive system failures.

Understanding Transaction Isolation Levels

MySQL defines four isolation levels:

Read Uncommitted : Allows reading uncommitted changes, leading to dirty reads, non‑repeatable reads, and phantom reads.

Read Committed : Only committed data is visible, preventing dirty reads but still permitting non‑repeatable reads and phantom reads.

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

Serializable : Eliminates dirty reads, non‑repeatable reads, and phantom reads by using strict locking, at the cost of performance.

MySQL’s default isolation level is Repeatable Read . You can view it with SHOW VARIABLES LIKE 'tx_isolation'; and change it using SET tx_isolation='REPEATABLE-READ'; .

Architect Guide

We are all architects!