MySQL Basics: Understanding Isolation Level and Transaction Anomalies

Isolation Level & Transaction Anomalies

In relational databases, the isolation level of a transaction controls the visibility of data changes between transactions. Isolation levels define how one transaction is isolated from others in terms of read and write operations. By managing this isolation, you can prevent or allow certain types of transaction anomalies such as dirty reads, non-repeatable reads, and phantom reads.

Transaction Anomalies

Before we dive into isolation levels, let’s first understand some common transaction anomalies:

1. Dirty Read: This happens when a transaction reads data that has been modified by another transaction but not yet committed. If the other transaction rolls back, the read data becomes invalid.
2. Non-Repeatable Read: Occurs when a transaction reads the same row multiple times and gets different data each time because another transaction has modified the row in between the reads.
3. Phantom Read: Happens when a transaction reads a set of rows that match a condition, but when it reads again, new rows are added or deleted by another transaction, causing the result set to change.

SQL Isolation Levels

SQL defines four standard isolation levels that control how transactions interact with each other and the types of anomalies they allow. These are:

1. READ UNCOMMITTED
2. READ COMMITTED
3. REPEATABLE READ
4. SERIALIZABLE

Each level offers a trade-off between performance (concurrency) and consistency. The stricter the isolation level, the less concurrency, but the greater the consistency.

1. READ UNCOMMITTED

• Description: The lowest isolation level, where transactions can read data that has been modified but not yet committed by other transactions. This allows dirty reads.
• Anomalies
  • Dirty reads are possible.
  • Non-repeatable reads are possible.
  • Phantom reads are possible.

Example:

Transaction 1 (T1) updates the balance of an account but hasn't committed:

UPDATE accounts SET balance = 5000 WHERE account_id = 1;

Transaction 2 (T2) reads the balance before T1 commits:

SELECT balance FROM accounts WHERE account_id = 1;
-- Returns 5000 (dirty read)

If T1 rolls back, T2 has read an invalid balance.

2. READ COMMITTED

• Description: A transaction can only read data that has been committed by other transactions. This prevents dirty reads, but non-repeatable reads and phantom reads can still occur.
• Anomalies
  • Dirty reads are not possible.
  • Non-repeatable reads are possible.
  • Phantom reads are possible.

Example:

Transaction 1 (T1) reads a row:

SELECT balance FROM accounts WHERE account_id = 1; 
-- Returns 3000

Meanwhile, Transaction 2 (T2) updates and commits a new balance for the same account:

UPDATE accounts SET balance = 5000 WHERE account_id = 1;
COMMIT;

Now, T1 reads the balance again:

SELECT balance FROM accounts WHERE account_id = 1; 
-- Returns 5000 (non-repeatable read)

The data changed between T1’s reads, leading to a non-repeatable read.

3. REPEATABLE READ

• Description: This isolation level ensures that if a transaction reads a row, it will always read the same data throughout the transaction. Non-repeatable reads are prevented, but phantom reads can still occur.
• Anomalies
  • Dirty reads are not possible.
  • Non-repeatable reads are not possible.
  • Phantom reads are possible.

Example:

Transaction 1 (T1) reads a row:

SELECT balance FROM accounts WHERE account_id = 1; 
-- Returns 3000

Transaction 2 (T2) tries to update the same row, but T1 has locked it for repeatable reading:

UPDATE accounts SET balance = 5000 WHERE account_id = 1;
-- T2 waits for T1 to finish.

T1 can read the balance again and will still see the original value:

SELECT balance FROM accounts WHERE account_id = 1;
-- Returns 3000 (repeatable read)

However, phantom reads can still occur. If T1 runs a query that selects multiple rows based on a condition (e.g., WHERE balance > 1000), and T2 inserts a new row that matches the condition, T1 will see this new row in subsequent queries within the same transaction.

4. SERIALIZABLE

• Description: The strictest isolation level. Transactions are executed as if they were running serially (one after another). This level prevents all anomalies—dirty reads, non-repeatable reads, and phantom reads—but it severely limits concurrency.
• Anomalies
  • Dirty reads are not possible.
  • Non-repeatable reads are not possible.
  • Phantom reads are not possible.

Example:

Transaction 1 (T1) reads a row:

SELECT balance FROM accounts WHERE account_id = 1; 
-- Returns 3000

If Transaction 2 (T2) tries to update or insert new rows, it will be blocked until T1 completes:

INSERT INTO accounts (account_id, balance) VALUES (2, 2000);
-- T2 is blocked until T1 finishes

T1 will always see the same data, and no other transactions can modify or insert new data that could affect T1’s query results.

Summary of Isolation Levels and Anomalies

Choosing the Right Isolation Level

• READ UNCOMMITTED: Useful when performance is more important than consistency. You’re okay with reading uncommitted data (e.g., for logging or monitoring purposes).
• READ COMMITTED: The most commonly used isolation level. It prevents dirty reads, and is often the default in many databases like MySQL and PostgreSQL. It offers a good balance between consistency and performance.
• REPEATABLE READ: Ensures consistency for repeated reads of the same data, but allows for phantom reads. This is the default isolation level in MySQL.
• SERIALIZABLE: The safest but slowest isolation level. It guarantees that no anomalies will occur but can reduce concurrency significantly. Use this for critical transactions where consistency is paramount (e.g., in banking or financial systems).

Example in MySQL: Changing Isolation Levels

You can set the isolation level for a transaction in MySQL using:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Or, you can set it globally for the entire session:

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Then, you can begin your transaction:

START TRANSACTION;
-- Your SQL operations
COMMIT;

This is how you can control the consistency and performance trade-offs of your transactions.