MySQL InnoDB Default Transaction Isolation Level

Understanding the default transaction isolation level in MySQL’s InnoDB storage engine is crucial for managing database concurrency, integrity, and performance. This article explains what the default isolation level is, how it functions under the hood to prevent data anomalies, and how you can check or modify this setting for your specific application needs.

The Default Isolation Level: REPEATABLE READ

The default transaction isolation level for the MySQL InnoDB storage engine is REPEATABLE READ.

Under this isolation level, all consistent reads within the same transaction read the snapshot established by the first read. This means that if you query the same data multiple times within a single transaction, you will receive the exact same results, even if other concurrent transactions have committed changes to those rows in the meantime.

How InnoDB Implements REPEATABLE READ

InnoDB achieves the REPEATABLE READ isolation level using two primary mechanisms:

1. Multi-Version Concurrency Control (MVCC): InnoDB uses MVCC to present a consistent snapshot of the database at a specific point in time. When a transaction reads data, it reads a historical version of the row if another transaction has modified it. This prevents “dirty reads” and “non-repeatable reads” without requiring heavy read locks, allowing high read concurrency.
2. Next-Key Locking: According to the standard SQL specification, the REPEATABLE READ isolation level is susceptible to “phantom reads” (where new rows inserted by another transaction become visible in subsequent queries). However, InnoDB prevents phantom reads by using next-key locking. This algorithm locks not only the index records found by a query but also the “gap” between them, preventing other transactions from inserting new rows into those gaps.

The Four Standard Isolation Levels

For context, MySQL supports all four standard SQL isolation levels. Ranked from the least restrictive (highest concurrency, lowest consistency) to the most restrictive (lowest concurrency, highest consistency):

• READ UNCOMMITTED: Allows dirty reads; transactions can see uncommitted changes from other transactions.
• READ COMMITTED: Prevents dirty reads, but allows non-repeatable reads. Each consistent read within a transaction establishes its own new snapshot.
• REPEATABLE READ (MySQL Default): Prevents dirty reads, non-repeatable reads, and phantom reads.
• SERIALIZABLE: The strictest level. It implicitly converts all plain SELECT statements into SELECT ... FOR SHARE, locking every row read and drastically reducing concurrency.

How to Check Your Current Isolation Level

To verify the current transaction isolation level in your MySQL instance, run the following SQL command:

SELECT @@transaction_isolation;

(Note: In MySQL versions older than 5.7.20, use SELECT @@tx_isolation;)

How to Change the Isolation Level

Depending on your application’s requirements, you may want to change the isolation level. You can do this at different scopes:

For the Current Session

To change the isolation level only for your current database connection:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Globally for All New Connections

To change the isolation level globally for all connections established after the change:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Permanently via Configuration

To make the change persistent across database restarts, add the following line to your MySQL configuration file (my.cnf or my.ini) under the [mysqld] section:

transaction-isolation = READ-COMMITTED