Non-Repeatable Read

Non-Repeatable Read is a concurrency problem in database systems that occurs when a transaction reads the same data twice and gets different results due to modifications made by another transaction in the meantime. This inconsistency arises when isolation levels do not guarantee stability for repeated reads of the same data.

Key Concepts

• Unstable Reads: The data read by a transaction changes during its execution because another transaction modifies it.
• Concurrency Issue: Non-repeatable reads are a type of Database Anomaly that occur when the isolation level does not prevent conflicting operations.
• Impact on Consistency: Can lead to inconsistencies in applications that rely on stable data for business logic.

Example of Non-Repeatable Read

Consider the following scenario in a banking application:

Step	Transaction A (T1)	Transaction B (T2)	Explanation
1	BEGIN TRANSACTION	-	T1 starts and reads the balance of Account 1.
2	SELECT balance FROM accounts WHERE account_id = 1;	-	T1 reads a balance of 1000.
3	-	UPDATE accounts SET balance = 800 WHERE account_id = 1;	T2 modifies the balance to 800.
4	-	COMMIT;	T2 commits the change.
5	SELECT balance FROM accounts WHERE account_id = 1;	-	T1 reads the balance again and gets 800.
Result	-	-	T1 observes a different balance (1000 → 800) for the same account within the same transaction.

Problems Caused by Non-Repeatable Reads

• Inconsistent Data: A transaction sees inconsistent snapshots of data during its execution.
• Incorrect Calculations: Calculations based on changing data may yield erroneous results.
• Logical Errors: Application logic relying on consistent reads can break when data changes mid-transaction.

Preventing Non-Repeatable Reads

Non-repeatable reads can be prevented by enforcing stricter isolation levels:

1. Repeatable Read:
   • Ensures that once data is read by a transaction, it cannot be modified by other transactions until the first transaction completes.
   • Prevents non-repeatable reads but allows phantom reads.
2. Serializable:
   • Provides the highest isolation level, ensuring complete consistency by preventing all anomalies, including non-repeatable reads.

Use Cases Where Non-Repeatable Reads Occur

Non-repeatable reads are common in systems with lower isolation levels:

• Read Committed Isolation Level: Allows other transactions to modify data between reads.
• High-Concurrency Systems: Environments prioritizing performance over consistency, such as logging or real-time analytics.

Example in SQL

To demonstrate non-repeatable reads, consider the following SQL code:

-- Transaction A
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1; -- Reads balance as 1000.

-- Transaction B
BEGIN TRANSACTION;
UPDATE accounts SET balance = 800 WHERE account_id = 1;
COMMIT;

-- Transaction A
SELECT balance FROM accounts WHERE account_id = 1; -- Reads balance as 800.
COMMIT;

In this example, Transaction A observes different values for the same data during its execution.

Advantages and Disadvantages of Allowing Non-Repeatable Reads

• Advantages:
  • Increased concurrency and performance in environments where strict consistency is not required.
  • Useful for workloads that involve frequent updates and low dependency between transactions.
• Disadvantages:
  • Data inconsistency can lead to logical errors in applications.
  • Risk of propagating incorrect data to downstream processes.

See Also

• Isolation Levels
• Dirty Read
• Phantom Read
• Serializable
• Repeatable Read
• Concurrency Control
• Transaction Management