What are isolation levels? Simply put, they define the extent to which a transaction (which can be understood as a series of indivisible database operations, such as a money transfer) is "isolated" from the effects of other concurrent transactions in a database system. Higher isolation levels mean stronger data consistency guarantees, but often come with performance overhead; lower levels offer better performance, but may expose data in an "intermediate" state. The SQL standard defines four levels, and MySQL's InnoDB storage engine, in most versions, uses repeatable read as the default starting point.
This default choice is not accidental, but a well-considered trade-off. Let's first look at the problems that might arise without the protection of "repeatable read."
The first problem is called "dirty read." Imagine that transaction A modifies a piece of data but has not yet committed it (for example, changing the account balance from 100 to 80). Transaction B then reads this uncommitted "dirty" data of 80. Subsequently, transaction A rolls back for some reason, restoring the balance to 100, while transaction B continues with erroneous operations based on the non-existent "80." This is clearly unacceptable. The second problem is called "non-repeatable read." Within the same transaction, reading the same data twice yields different results. For example, transaction B first reads the account balance as 100. Then transaction A commits a deduction, changing the balance to 80. Transaction B reads it again, and the balance is now 80. This can cause confusion in many business logics.
MySQL's default "repeatable read" level effectively solves these two problems. It ensures that once a transaction starts, regardless of how long the transaction runs, the data view it reads is consistent each time. It's like taking a snapshot of the data at a moment's time; subsequent transaction operations are based on this unchanging snapshot, unaffected by updates from other transactions. This perfectly meets the core business needs of financial accounting and reconciliation, which require a stable data view.
However, the story doesn't end there. In the SQL standard, above "repeatable read," there is a more stringent "serialization" level, which eliminates all interference through complete transaction ordering. Why didn't MySQL choose it as the default? The answer lies in balance. The "serialization" level of locking is very strict. While it ensures absolute data security, it severely limits concurrency performance, causing a large number of transactions to queue and wait. For most web applications and online services that require high concurrency, this performance cost is too high.
So, is MySQL's "repeatable read" perfect? Not quite. It faces a theoretical challenge—"phantom reads." A phantom read occurs when a transaction sees rows newly inserted by another transaction while querying the same range twice. For example, transaction B wants to count the total number of accounts with a balance greater than 50. The first query finds 10. Then transaction A inserts an account with a balance of 60 and commits. Transaction B counts again and finds 11 accounts, like a "phantom." The InnoDB engine, through a mechanism called "gap locking," largely suppresses phantom reads, making its "repeatable read" actually provide stronger isolation guarantees than defined by the SQL standard. This is a pragmatic design choice made by MySQL after weighing the pros and cons: providing the highest possible data consistency without excessively sacrificing performance.
Setting "Repeatable Read" as the default reflects a core design philosophy of MySQL: providing a safe, reliable, and reasonably performant starting point for the broadest range of general scenarios. It safeguards data consistency by default, preventing most concurrency issues. For novice developers, this default setting lowers the barrier to understanding and managing concurrency complexity; for experienced architects, they fully understand that they can proactively lower the isolation level to "Read Committed" in non-core business scenarios requiring extreme performance, based on the sensitivity and performance requirements of specific business needs.
Therefore, the next time you start a transaction in MySQL, you can understand that this default setting silently provides you with a stable data world. It ensures that the data landscape you see is solidified and reliable throughout the lifecycle of a transaction. It may not be the optimal solution in all situations, but it is a time-tested and excellent default solution that balances correctness, complexity, and efficiency—the essence of database system design.
