What is non-repeatable read?

A non-repeatable read is a phenomenon that can occur in database transactions, specifically under certain isolation levels. It happens when, within a single transaction, a row is read multiple times, and the values returned differ between reads because another committed transaction has modified and committed changes to that same row.

Definition

Imagine a transaction (TxA) reads a specific row from a table. While TxA is still active, another transaction (TxB) updates that same row and commits its changes. If TxA then tries to read the same row again, it will retrieve the new, updated value, leading to inconsistency within TxA's view of the data. This change between reads is what defines a non-repeatable read.

Scenario Example

Consider a 'Products' table with 'product_id', 'name', and 'price'.

In this example, Transaction A reads the price for 'product_id = 1' as 100 at T2. Before Transaction A completes, Transaction B updates the price to 120 and commits at T5. When Transaction A reads the same row again at T6, it sees the new price of 120, resulting in a non-repeatable read because the value changed within a single transaction.

When it Occurs

Non-repeatable reads can occur in database systems configured with isolation levels lower than 'Repeatable Read', specifically:

• Read Uncommitted: This level allows transactions to read uncommitted changes made by other transactions (dirty reads), and thus also non-repeatable reads.
• Read Committed: While this level prevents dirty reads (a transaction only sees changes that have been committed), it does not prevent non-repeatable reads. Once a transaction commits, its changes become visible to other concurrent transactions, even if those transactions are still in progress and have previously read the same data.

How to Prevent It

To prevent non-repeatable reads, higher isolation levels are used:

• Repeatable Read: This isolation level ensures that if a transaction reads a row, it will see the same data every time it reads that row until the transaction commits or rolls back, even if other transactions modify and commit changes to that row. This is typically achieved by using shared locks on all data read by a transaction, which are held until the transaction commits, preventing other transactions from modifying that data.
• Serializable: This is the highest isolation level and prevents all concurrency anomalies, including non-repeatable reads. It ensures that transactions execute as if they were run sequentially, one after another.

While preventing non-repeatable reads improves data consistency within a transaction, it often comes at the cost of reduced concurrency, as more locks are held for longer durations, potentially increasing contention and blocking.