Non-Repeatable Read vs Phantom Read in Database Isolation Levels: Concepts and Practical Applications

Introduction

In database systems, transaction isolation is a core mechanism for ensuring data consistency and concurrency control. When multiple transactions execute concurrently, various read anomalies can occur, with non-repeatable read and phantom read being among the most common. Understanding their differences and mitigation strategies is crucial for designing reliable, high-performance database applications. This article starts with basic concepts, progressively analyzes these phenomena, and explores isolation level selection with practical examples.

Definitions of Non-Repeatable Read and Phantom Read

A non-repeatable read occurs when a transaction reads the same row twice but gets different results. This typically happens after another transaction performs an UPDATE on that row and commits. For instance, transaction A first reads a user's account balance as 5000; transaction B then updates it to 10000 and commits; when transaction A reads again, the balance changes to 10000, causing inconsistency.

A phantom read involves range queries: a transaction executes the same query condition twice, but the result set differs in the number of rows. This is usually caused by INSERT or DELETE operations from another transaction. For example, transaction A queries all users with balances greater than 5000, initially returning 5 rows; after transaction B inserts a new record (balance 6000) and commits, transaction A might return 6 rows upon re-querying, as if a "phantom" row appeared.

Key Differences and Example Analysis

The core distinctions between non-repeatable read and phantom read lie in operation type and scope:

• Non-repeatable read targets single-row data via UPDATE, causing value changes in the same row.
• Phantom read targets range data via INSERT or DELETE, causing changes in the result set row count.

Referring to the example in the Q&A: transaction A executes SELECT ID, USERNAME, accountno, amount FROM USERS WHERE ID=1 twice, while transaction B executes UPDATE USERS SET amount=amount+5000 where ID=1 AND accountno=29019892 and commits in between. In this case, if transaction A's two reads yield different results (e.g., amount changes from 5000 to 10000), a non-repeatable read occurs. Since the query condition is based on the primary key ID=1, involving only a single row, a phantom read cannot happen.

Role and Selection of Isolation Levels

Databases control these phenomena through isolation levels. Common levels include:

• READ_UNCOMMITTED: Lowest level, allows reading uncommitted data, potentially causing dirty reads, non-repeatable reads, and phantom reads.
• READ_COMMITTED: Prevents dirty reads, but non-repeatable reads and phantom reads may still occur.
• REPEATABLE_READ: Prevents dirty reads and non-repeatable reads, but phantom reads may occur.
• SERIALIZABLE: Highest level, prevents all phenomena via strict locking, but offers the lowest concurrency performance.

Choosing an isolation level requires balancing data consistency and performance. In the example, if the application must avoid non-repeatable reads, REPEATABLE_READ or higher should be used. However, increasing isolation levels may reduce concurrency; for instance, SERIALIZABLE can force serial execution of transactions, increasing latency.

Practical Applications and Optimization Tips

In real-world development, isolation levels can be selected flexibly based on specific scenarios. For example, in Oracle databases, beyond setting isolation levels, one can use the SELECT FOR UPDATE statement to explicitly lock rows, preventing modifications by other transactions and thus avoiding non-repeatable reads. Here is a code example:

-- Transaction A, using SELECT FOR UPDATE to lock rows
SELECT ID, USERNAME, accountno, amount FROM USERS WHERE ID=1 FOR UPDATE;
-- Perform other operations...
COMMIT;

This statement locks the qualifying rows until the transaction commits, ensuring data consistency. However, overuse may lead to lock contention, impacting performance.

Additionally, for phantom reads, the REPEATABLE_READ level partially prevents them in some databases (e.g., MySQL's InnoDB via Multi-Version Concurrency Control), but complete avoidance requires SERIALIZABLE or application-layer logic (e.g., using unique constraints).

Conclusion

Non-repeatable read and phantom read are key concepts in database concurrency control; understanding their differences aids in designing more robust applications. By judiciously selecting isolation levels and leveraging database-specific mechanisms (e.g., locking), one can optimize performance while ensuring data consistency. In practical projects, it is advisable to test the impact of different isolation levels based on business requirements to achieve an optimal balance.