What are Transaction Isolation Levels?
When multiple transactions are executed concurrently, problems may arise if they are not isolated. These include dirty reads, non-repeatable reads, and phantom reads. Transaction isolation levels are designed to address these issues.
MySQL defines four transaction isolation levels, each resulting in different outcomes for concurrent execution. In practical applications, the appropriate isolation level should be chosen based on business characteristics. The four transaction isolation levels in MySQL are, in order: Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
Read Uncommitted
Read Uncommitted is the lowest isolation level, allowing one transaction to read and use modifications from another transaction that have not yet been committed. As a result, dirty reads can occur at this level.
A dirty read happens when, in two concurrently executing transactions, one transaction reads data that the other has not yet committed. In the Read Uncommitted level, if one transaction modifies data but does not commit it, another transaction reading that data may get incorrect results, leading to a dirty read.
Example 1:
Transaction A updates data in table t1 but does not commit:
begin;update t1 set name='aaa' where id=1;Transaction B reads data from table t1:
select * from t1 where id=1;At this point, Transaction B may read the row with name=’aaa’, even though the name column has not actually been updated until Transaction A commits.
Therefore, the Read Uncommitted level is unsafe and not recommended.
Read Committed
In the Read Committed level, a transaction can only read data modified by other transactions that have already been committed. Thus, this level resolves the dirty read problem.
However, non-repeatable reads and phantom reads can still occur at this level.
A non-repeatable read refers to a situation where, within the same transaction, the same query returns different results due to interference from other transactions.
Example 2:
Transaction A reads data from table t1:
begin;select * from t1 where id=1;Before Transaction A commits, Transaction B modifies data in table t1:
begin;update t1 set name='bbb' where id=1;commit;When Transaction A executes the same query again, the result will be different.
A phantom read occurs when, within the same transaction, the set of rows returned by the same query condition changes due to interference from other transactions.
Example 3:
Transaction A reads data from table t1:
begin;select * from t1 where name like '%a%';Before Transaction A commits, Transaction B inserts data into table t1:
begin;insert into t1 (name) values ('abc'), ('def');commit;When Transaction A executes the same query again, the result will be different.
To address non-repeatable reads and phantom reads, a higher isolation level is required.
Repeatable Read
In the Repeatable Read level, multiple reads of the same row within a transaction will return consistent results. Thus, this level resolves the non-repeatable read problem.
However, phantom reads can still occur.
To solve phantom reads, row locks are introduced. MySQL uses next-key lock for this purpose.
next-key lock locks a range of indexes to prevent phantom reads.
Serializable
Serializable is the highest isolation level. It forces transactions to execute serially, eliminating dirty reads, non-repeatable reads, and phantom reads. At this level, MySQL places shared or exclusive locks on all read rows until the transaction ends.
Due to its significant impact on performance, Serializable is generally not recommended. It should only be used in business scenarios where full isolation is required and concurrency is not a priority.
Summary
MySQL provides four transaction isolation levels:
- Read Uncommitted is the lowest level and suffers from dirty reads.
- Read Committed resolves dirty reads but still allows non-repeatable reads and phantom reads.
- Repeatable Read resolves non-repeatable reads but may still have phantom reads.
- Serializable eliminates all concurrency issues but severely impacts performance.
In practice, the appropriate isolation level should be selected based on business needs to ensure data correctness and concurrency.