Four Isolation Levels

Check Isolation Level

  • MySQL 5.7 and below: SELECT @@SESSION.tx_isolation;
  • MySQL 8.0 and above: SELECT @@SESSION.transaction_isolation;

1. READ UNCOMMITTED

  • Allows dirty reads: Can read modifications not yet committed by other transactions
  • Highest concurrency, lowest consistency
  • Applicable scenarios: Statistical analysis, real-time monitoring

2. READ COMMITTED

  • Prevents dirty reads, but non-repeatable reads may occur
  • Transaction A reads data for the first time, transaction B modifies and commits, transaction A reads again with different results
  • Default level for most databases

3. REPEATABLE READ - MySQL Default

  • Prevents dirty reads and non-repeatable reads
  • Phantom reads may occur (but due to MySQL’s Next-Key Locking technology, phantom reads may not be reproducible)

4. SERIALIZABLE

  • Highest isolation level, fully locks read-write, prevents phantom reads
  • Worst performance, greatly reduces execution efficiency

Set Isolation Level

SET SESSION TRANSACTION ISOLATION LEVEL [transaction level];

Isolation Level Comparison

Isolation LevelDirty ReadNon-Repeatable ReadPhantom Read
READ UNCOMMITTED✓✓✓
READ COMMITTED×✓✓
REPEATABLE READ××✓
SERIALIZABLE×××