MySQL Transaction Isolation Levels: Read Uncommitted to S...

Isolation Level Types

The concurrent transaction problems mentioned earlier, such as Lost Update, Dirty Read, Non-repeatable Read, and Phantom Read, are essentially database consistency problems. These problems arise from improper access sequencing to shared data when multiple transactions execute concurrently. To solve these problems, MySQL database ensures data consistency through the Transaction Isolation Levels mechanism.

Database systems based on ANSI/ISO SQL standard provide the following 4 transaction isolation levels for selection, each corresponding to different concurrency control strengths:

1. Read Uncommitted

• The lowest isolation level
• Allows transactions to read data changes not committed by other transactions (dirty read)
• Best performance but worst data consistency
• Typical application scenario: Statistical analysis scenarios with low requirements for data accuracy

2. Read Committed

• Default isolation level for most databases (such as Oracle, PostgreSQL)
• Only allows reading committed data
• Solves dirty read problem but still has non-repeatable read problem
• Implementation: Usually uses row-level locking mechanism

3. Repeatable Read

• MySQL InnoDB engine’s default isolation level
• Ensures consistent results when reading the same data multiple times within a transaction
• Solves dirty read and non-repeatable read problems
• Phantom read may still occur
• InnoDB implements through Multi-version Concurrency Control (MVCC) and Gap Lock

4. Serializable

• The highest isolation level
• Completely serializes transaction execution
• Solves all concurrency problems but has the worst performance
• Implementation: Usually uses table-level locks or range locks
• Application scenario: Scenarios with extremely high requirements for data consistency like bank transfers

---

Practical Applications

In practical applications, the higher the isolation level, the better the data consistency, but the lower the concurrent performance. Database administrators need to select the appropriate isolation level based on business requirements (data accuracy requirements, concurrency, etc.). For example:

• E-commerce systems may choose “Read Committed” to balance performance and consistency
• Financial systems may choose “Repeatable Read” or “Serializable” to ensure absolute data accuracy
• Data warehouses may choose “Read Uncommitted” to improve report generation speed

---

Relationship Between Isolation Levels and Locks

Transaction isolation level is an important concept defined in the SQL92 standard. It provides a complete set of concurrency control solutions. The standard defines four isolation levels (Read Uncommitted, Read Committed, Repeatable Read, and Serializable). Essentially, it encapsulates the underlying complex concurrency control details through lock mechanisms and Multi-version Concurrency Control (MVCC) technology, providing developers with a simple and easy-to-use interface.

Lock mechanism is the core foundation for databases to implement concurrency control. Database systems implement transaction isolation through different types of locks (such as shared locks, exclusive locks, intention locks, etc.). Specifically:

• When executing read operations, the system adds shared locks (S locks), allowing other transactions to read simultaneously but not allowing modifications
• When executing write operations, the system adds exclusive locks (X locks), preventing other transactions from any read or write operations
• Through the lock compatibility matrix and lock escalation mechanism, the system can flexibly control the granularity of concurrent access

There are differences in default isolation levels between MySQL and mainstream databases:

• MySQL defaults to Repeatable Read (REPEATABLE READ), which is closely related to its InnoDB storage engine’s MVCC implementation
• Oracle and SQL Server default to Read Committed (READ COMMITTED), a choice that more focuses on balancing performance and concurrency

---

Isolation Level Control

MySQL’s default transaction isolation level is Repeatable Read, which is InnoDB storage engine’s default isolation level.

Viewing Transaction Isolation Level

In MySQL 5.7 and earlier versions:

SHOW VARIABLES LIKE 'tx_isolation';

Or

SELECT @@tx_isolation;

In MySQL 8.0 and later versions:

SHOW VARIABLES LIKE 'transaction_isolation';

Or

SELECT @@transaction_isolation;

Setting Transaction Isolation Level

-- Set read uncommitted isolation level
SET tx_isolation='READ-UNCOMMITTED';

-- Set read committed isolation level
SET tx_isolation='READ-COMMITTED';

-- Set repeatable read isolation level (MySQL default)
SET tx_isolation='REPEATABLE-READ';

-- Set serializable isolation level
SET tx_isolation='SERIALIZABLE';

---

Summary

Isolation Level	Dirty Read	Non-repeatable Read	Phantom Read
Read Uncommitted	✓ Possible	✓ Possible	✓ Possible
Read Committed	✗ Impossible	✓ Possible	✓ Possible
Repeatable Read	✗ Impossible	✗ Impossible	✓ Possible
Serializable	✗ Impossible	✗ Impossible	✗ Impossible

Best Practice Suggestions:

1. Prefer using the database’s default isolation level
2. When encountering specific concurrency problems, consider using pessimistic or optimistic locks
3. Adjust isolation level only when necessary, because higher isolation levels usually mean greater performance overhead