Isolation Level (Database)
IT 위키
Isolation Level is a property in database transaction management that defines the extent to which transactions are isolated from each other. It determines how and when changes made by one transaction are visible to other concurrent transactions, affecting the trade-off between consistency and concurrency.
Key Concepts
- Transaction Isolation: Ensures that concurrent transactions do not interfere with each other inappropriately.
- Anomalies: Lower isolation levels can lead to issues like dirty reads, non-repeatable reads, and phantom reads.
- Trade-offs: Higher isolation levels improve consistency but may reduce performance due to increased locking and reduced concurrency.
Types of Isolation Levels
The SQL standard defines four primary isolation levels:
| Isolation Level | Description | Prevented Anomalies | Allowed Anomalies |
|---|---|---|---|
| Read Uncommitted | Transactions can read uncommitted changes made by other transactions. | None | Dirty Reads, Non-Repeatable Reads, Phantom Reads |
| Read Committed | Transactions can only read committed data. | Dirty Reads | Non-Repeatable Reads, Phantom Reads |
| Repeatable Read | Ensures consistent reads for data accessed multiple times within a transaction. | Dirty Reads, Non-Repeatable Reads | Phantom Reads |
| Serializable | Provides the highest level of isolation by ensuring that transactions appear to execute serially. | Dirty Reads, Non-Repeatable Reads, Phantom Reads | None |
Explanation of Anomalies
- Dirty Reads: A transaction reads data modified by another transaction that has not yet committed.
- Non-Repeatable Reads: A transaction reads the same data twice and gets different results due to modifications by another transaction.
- Phantom Reads: A transaction reads a set of rows based on a condition, and another transaction inserts or deletes rows that meet the condition.
Examples
Read Uncommitted Example
-- Transaction A
BEGIN TRANSACTION;
UPDATE accounts SET balance = 500 WHERE account_id = 1;
-- Transaction B
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE account_id = 1; -- Reads uncommitted balance of 500.
-- Transaction A
ROLLBACK; -- Balance reverts, but Transaction B already used dirty data.
Repeatable Read Example
-- 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; -- Still reads balance as 1000 due to Repeatable Read.
COMMIT;
Serializable Example
-- Transaction A
BEGIN TRANSACTION;
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Transaction B
BEGIN TRANSACTION;
INSERT INTO orders (order_id, status) VALUES (123, 'pending');
-- Blocked until Transaction A completes to ensure serializability.
Trade-offs
The following table summarizes the trade-offs for each isolation level:
| Isolation Level | Performance Impact | Use Case |
|---|---|---|
| Read Uncommitted | Highest concurrency | Non-critical applications or logging systems |
| Read Committed | Balanced | General-purpose transaction management |
| Repeatable Read | Moderate performance loss | Financial systems requiring consistent reads |
| Serializable | Highest consistency | Critical systems with strict data integrity |
Applications
- Banking Systems: Use Repeatable Read or Serializable for financial transactions to avoid inconsistencies.
- E-Commerce: Often use Read Committed for balancing performance and consistency.
- Analytics Systems: May use Read Uncommitted for faster data ingestion and processing.
