Jaimin Bariya

Posted on Nov 22

Isolation in ACID Transaction

Understanding Isolation in ACID Transactions

Isolation ensures that multiple concurrent transactions do not interfere with each other, preserving the consistency and integrity of the database. It guarantees that a transaction's intermediate states are invisible to other transactions until it is committed, preventing problems such as dirty reads, non-repeatable reads, and phantom reads.

Why Isolation is Important?

In multi-user environments where multiple transactions run concurrently:

Without isolation: One transaction may read or affect data that another transaction is modifying, leading to unpredictable or incorrect results.
With isolation: Each transaction operates as if it is the only one interacting with the database.

Common Problems Without Proper Isolation

Dirty Reads
- A transaction reads data that another transaction has modified but not yet committed. If the other transaction rolls back, the read data becomes invalid.
- Example:
  - Transaction A updates a user's balance from $1000 to $800 but hasn’t committed.
  - Transaction B reads the $800 balance.
  - If Transaction A rolls back, Transaction B used invalid data.
Non-Repeatable Reads
- A transaction reads the same data twice, but the data changes between reads because another transaction modified it.
- Example:
  - Transaction A reads a product price as $100.
  - Transaction B updates the price to $120 and commits.
  - Transaction A reads the price again and sees $120, leading to inconsistent results.
Phantom Reads
- A transaction retrieves a set of rows based on a condition, but another transaction inserts, updates, or deletes rows that affect the result set.
- Example:
  - Transaction A reads all products priced below $50.
  - Transaction B adds a new product priced at $45 and commits.
  - Transaction A re-executes the query and sees the new product, even though it wasn’t there initially.

Isolation Levels

Isolation levels determine how isolated a transaction is from others. The more isolation, the less concurrency, and vice versa. Different levels balance between data consistency and system performance.

1. Read Uncommitted

Definition: Allows transactions to read uncommitted changes from other transactions.
Use Case: Rarely used, suitable only when high performance is critical and occasional dirty reads are acceptable.
Problems Allowed: Dirty reads, non-repeatable reads, phantom reads.
Example:
- Transaction A updates an order status to "Shipped" but hasn’t committed.
- Transaction B reads the "Shipped" status and proceeds based on it.
- If Transaction A rolls back, Transaction B acted on incorrect information.

2. Read Committed (Default for many databases)

Definition: A transaction can only read committed changes from other transactions.
Use Case: Common in most systems; avoids dirty reads.
Problems Allowed: Non-repeatable reads, phantom reads.
Example:
- Transaction A updates the balance from $1000 to $800 and commits.
- Transaction B reads the updated balance only after the commit.

3. Repeatable Read

Definition: Ensures a transaction reads consistent data for the same query, even if other transactions modify the data.
Use Case: Used when data consistency during a transaction is important.
Problems Allowed: Phantom reads.
Example:
- Transaction A reads a product quantity as 50.
- Transaction B modifies the quantity to 30 and commits.
- Transaction A reads the quantity again and still sees 50, maintaining consistency for its duration.

4. Serializable (Highest Isolation Level)

Definition: Transactions are executed in a completely isolated manner, as if they were serialized (one after the other).
Use Case: Critical when absolute data consistency is required.
Problems Allowed: None (dirty reads, non-repeatable reads, and phantom reads are all prevented).
Example:
- Transaction A reads all orders above $100.
- Transaction B attempts to add a new order above $100 but is blocked until Transaction A completes.
- Ensures no new data interferes during Transaction A.

Comparison Table of Isolation Levels

Isolation Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads
Read Uncommitted	Allowed	Allowed	Allowed
Read Committed	Prevented	Allowed	Allowed
Repeatable Read	Prevented	Prevented	Allowed
Serializable	Prevented	Prevented	Prevented

Choosing the Right Isolation Level

High Performance, Lower Consistency
- Use Read Uncommitted or Read Committed.
- Example: Analytics queries or reports where minor inconsistencies are tolerable.
Medium Consistency and Performance
- Use Repeatable Read.
- Example: Banking systems for transactions requiring consistent reads.
Maximum Consistency, Lower Performance
- Use Serializable.
- Example: Financial systems for critical operations like fund transfers.

Example of Transaction -> With and without Isolaction

This line means that during a transaction's execution, any changes it makes to the database are not visible to other ongoing transactions until it is fully completed (committed).

In simpler terms:

A transaction operates as if it’s isolated from other transactions.
Other transactions cannot see or use the temporary (intermediate) changes made by a transaction until the transaction confirms its success and finalizes those changes by committing.

Breaking it Down

Intermediate State
- When a transaction updates data, it may not immediately finalize those changes. This is the "in-progress" or "intermediate state."
- Example: If a transaction is transferring money from Account A to Account B, the intermediate state could be after deducting money from Account A but before adding it to Account B.
Invisible to Others
- Other transactions cannot see or act upon these "in-progress" updates. They only see the data as it existed before the transaction began.
- This prevents other transactions from using incomplete or incorrect data.
Commit Finalizes Changes
- Once the transaction finishes and commits, its changes become permanent and visible to all other transactions.
- If the transaction fails or rolls back, none of its changes are visible or applied.

Example to Illustrate

Scenario: Money Transfer

Transaction 1: Transfers $200 from Account A to Account B.
- Step 1: Deduct $200 from Account A (Balance: $800).
- Step 2: Add $200 to Account B (Balance: $1200).
- Commit: Both steps are finalized together.
Transaction 2: Reads balances of both accounts during this process.

Without Isolation:

Intermediate State Visible:
- After Step 1, Transaction 2 reads Account A’s balance as $800 and Account B’s balance as $1000.
- This is inconsistent and could lead to errors, especially if Transaction 1 rolls back.

With Isolation:

Intermediate State Hidden:
- Transaction 2 sees the original balances: Account A = $1000, Account B = $1000.
- Once Transaction 1 commits, Transaction 2 will see the updated balances: Account A = $800, Account B = $1200.

Why Is This Important?

Hiding intermediate states ensures:

Consistency: Other transactions do not operate on incomplete or incorrect data.
Integrity: Avoids errors caused by transactions that interact with half-finished updates.

DEV Community