Mastering Database Concurrency: Dirty Read, Non‑Repeatable Read, and Phantom Read

1. Background

Interviewers often ask: what are dirty read, non‑repeatable read, and phantom read? To answer, we first recall the four ACID properties a DBMS must guarantee: Atomicity, Consistency, Isolation, and Durability.

Atomicity means a transaction’s operations either all succeed or all fail, with rollback restoring the state before the transaction. Consistency ensures the database remains valid before and after a transaction. Isolation allows concurrent transactions to execute without interfering, and is implemented through four isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Durability guarantees that once a transaction commits, its changes survive system failures.

A classic bank transfer illustrates a transaction: debit one account and credit another as a single atomic operation.

2. Scenario Analysis

2.1 Dirty Read

Dirty read occurs when a transaction reads data modified by another transaction that has not yet committed. If the uncommitted transaction later rolls back, the reading transaction has observed data that never existed.

In the diagram, Transaction B updates a row and then rolls back, while Transaction A reads the uncommitted value, resulting in a non‑existent record.

When the isolation level is Read Uncommitted , dirty reads can happen.

2.2 Non‑Repeatable Read

Non‑repeatable read means that a transaction reads the same row twice and gets different results because another transaction modified and committed the row in between.

In the example, Transaction A reads a value of 1, Transaction B updates it to 2 and commits, and Transaction A’s second read returns 2.

This phenomenon appears under Read Uncommitted and Read Committed isolation levels.

2.3 Phantom Read

Phantom read is similar but involves the appearance or disappearance of rows that satisfy a query condition.

Transaction A modifies a set of rows, then Transaction B inserts a new row and commits. When Transaction A re‑executes the same query, it sees an extra row that was not present before, giving the impression of a “phantom”.

Phantom reads can occur under Read Uncommitted , Read Committed , and Repeatable Read isolation levels.

3. How to Resolve

Databases use locking mechanisms to prevent these anomalies. In MySQL, locks are classified by object (row‑level vs. table‑level) and by mode (shared vs. exclusive).

Shared locks allow multiple readers but block exclusive locks; exclusive locks block both shared and other exclusive locks. Statements such as INSERT, UPDATE, DELETE, and SELECT FOR UPDATE acquire implicit row‑level exclusive locks, which may be escalated to table‑level locks under heavy contention.

Rather than managing locks manually, MySQL lets users set an isolation level; the engine then chooses appropriate locks automatically to eliminate dirty reads, non‑repeatable reads, and phantom reads.

Higher isolation levels reduce concurrency: the stronger the isolation, the lower the throughput.

4. Summary

The article introduced dirty read, non‑repeatable read, and phantom read, explained the isolation levels that cause them, and described MySQL’s lock‑based solutions, emphasizing the trade‑off between isolation strength and concurrent performance.