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

1. 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.

2. 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.

3. 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

Choosing the Right Isolation Level

1. High Performance, Lower Consistency

   • Use Read Uncommitted or Read Committed.
   • Example: Analytics queries or reports where minor inconsistencies are tolerable.

2. Medium Consistency and Performance

   • Use Repeatable Read.
   • Example: Banking systems for transactions requiring consistent reads.

3. 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

1. 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.

2. 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.

3. 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.