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:
- 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.
- Repeatable Read:
- Ensures consistent reads by preventing both dirty reads and non-repeatable reads.
- 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.