Dirty Read (Database)

IT 위키

Dirty Read is a concurrency problem in database systems that occurs when a transaction reads uncommitted changes made by another transaction. This can lead to inconsistent or incorrect data being used in the reading transaction, especially if the changes are later rolled back.

1 Key Concepts[편집 | 원본 편집]

  • Uncommitted Data: Data modified by a transaction that has not yet been committed to the database.
  • Concurrency Issue: Dirty reads are a type of Database Anomaly that arise in systems with inadequate isolation levels.
  • Impact on Data Integrity: Reading uncommitted data can lead to inconsistencies if the data is subsequently rolled back.

2 Example of Dirty Read[편집 | 원본 편집]

Consider the following scenario in a banking application:

Step Transaction A (T1) Transaction B (T2) Explanation
1 BEGIN TRANSACTION - T1 begins and updates the account balance.
2 UPDATE account_balance SET balance = 500 WHERE account_id = 1; - T1 modifies the balance but has not committed yet.
3 - SELECT balance FROM account_balance WHERE account_id = 1; T2 reads the uncommitted balance of 500.
4 ROLLBACK; - T1 rolls back the change, reverting the balance to its original value.
Result - T2 reads an invalid balance of 500 T2 used dirty data that no longer exists after T1's rollback.

3 Problems Caused by Dirty Reads[편집 | 원본 편집]

  • Inconsistent Data: Transactions may rely on temporary data that is later invalidated.
  • Erroneous Decisions: Applications might make decisions based on incorrect or transient data.
  • Compounded Errors: Subsequent transactions may propagate the effects of dirty reads, causing larger inconsistencies.

4 Preventing Dirty Reads[편집 | 원본 편집]

Dirty reads can be prevented by enforcing appropriate isolation levels:

  1. Read Committed:
    • Ensures that a transaction can only read committed data.
    • Prevents dirty reads but allows other anomalies like non-repeatable reads and phantom reads.
  2. Repeatable Read:
    • Ensures consistent reads by preventing both dirty reads and non-repeatable reads.
  3. Serializable:
    • Provides the highest level of isolation, preventing all types of anomalies.

5 Use Cases Where Dirty Reads Occur[편집 | 원본 편집]

Dirty reads can occur in systems that prioritize performance over strict consistency:

  • Low Isolation Levels: Isolation levels like Read Uncommitted allow dirty reads to maximize concurrency.
  • High-Performance Systems: Applications where fast response times are prioritized, such as real-time analytics or logging systems.

6 Example in SQL[편집 | 원본 편집]

To demonstrate the effect of dirty reads, consider the following SQL code:

-- Transaction A
BEGIN TRANSACTION;
UPDATE accounts SET balance = 500 WHERE account_id = 1;
-- Transaction A has not committed yet.

-- Transaction B
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE account_id = 1;
-- Transaction B reads the uncommitted balance of 500.

-- Transaction A
ROLLBACK;
-- The balance reverts to its original value, but Transaction B already read dirty data.

7 Advantages and Disadvantages of Allowing Dirty Reads[편집 | 원본 편집]

  • Advantages:
    • Improved performance in scenarios where consistency is less critical.
    • Suitable for systems with minimal dependencies between transactions.
  • Disadvantages:
    • Increased risk of data inconsistencies.
    • Potential for downstream errors in dependent processes.

8 See Also[편집 | 원본 편집]