MySQL Transaction Issues: Dirty Read, Non-Repeatable Read...

Dirty Read

Definition

Dirty read means one transaction can read data modifications not yet committed by another transaction. Since these data modifications may be rolled back, the read data may be invalid or inconsistent.

Causes

Dirty read typically occurs when database isolation level is set to “Read Uncommitted”.

Specific Manifestation

1. Transaction A modifies a record but has not yet committed
2. Transaction B reads the record modified by transaction A
3. If transaction A ultimately rolls back, then the data read by transaction B is invalid

Solutions

1. Raise transaction isolation level to “Read Committed” or higher
2. Set appropriate lock mechanism at database level

Non-Repeatable Read

Definition

Non-repeatable read means within the same transaction, due to modifications by other concurrent transactions, the results of reading the same data twice are inconsistent.

Detailed Example

1. Transaction A first query: Returns result 1000
2. Transaction B executes and commits: UPDATE account SET balance = 800
3. Transaction A queries again: Returns result 800

At this point, transaction A finds the two read results are inconsistent.

Solutions

1. Raise transaction isolation level to REPEATABLE READ or above
2. Use pessimistic lock (SELECT FOR UPDATE)
3. Implement optimistic concurrency control at application layer

Repeatable Read

Basic Concept

Repeatable read is the main characteristic of database transaction isolation level REPEATABLE READ, ensuring consistent results when reading the same data multiple times within a transaction.

Core Features

1. Prevents non-repeatable read: Through Multi-Version Concurrency Control (MVCC) technology
2. Based on snapshots: Based on data snapshot created when transaction starts

Problems Solved

• Dirty read problem
• Non-repeatable read problem

Phantom Read

Definition

Phantom read refers to within the same transaction, due to other concurrent transactions’ insert or delete operations, the same query executed twice returns different result sets.

Specific Manifestation

1. Transaction A first query returns N records
2. Transaction B inserts new data within that range and commits
3. Transaction A executes the same query again, returns N+1 records

Difference from Non-Repeatable Read

• Non-repeatable read: For existing rows whose data is modified
• Phantom read: For new or deleted rows causing result set changes

Solutions

1. Raise isolation level to SERIALIZABLE
2. Use Gap Lock or Next-Key Lock
3. Use pessimistic lock (such as SELECT…FOR UPDATE)

Summary

Problem	Occurring Isolation Level	Solution
Dirty Read	READ UNCOMMITTED	Raise isolation level
Non-Repeatable Read	READ COMMITTED	Use REPEATABLE READ
Phantom Read	REPEATABLE READ	Use SERIALIZABLE