Introduction to the four isolation levels in MySQL

This article brings you an introduction to the four isolation levels in MySQL. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Transaction isolation is more complicated than imagined. There are four levels of isolation levels defined in the SQL standard. Generally speaking, lower levels of isolation can usually perform higher concurrency and have lower system overhead (recommended course: MySQL Video Tutorial)

READ UNCOMMITTED

This level is Uncommitted Read. At this level, modifications in a transaction are visible to other transactions even if they are not committed. Transactions can read uncommitted data, which is also called dirty reading. This level causes a lot of problems, performance-wise it's not much better than the other levels, but lacks many of the benefits of the other levels. Unless there is a very necessary reason, it is rarely used in practical applications.

READ COMMITTED

This level is Committed Read. This is the default isolation level for most database systems, but not MySQL. This level meets the simple definition of isolation: when a transaction starts, only modifications made by committed transactions can be "seen". In other words, any modifications made by a transaction from the beginning until it is committed are not visible to other transactions. This level is sometimes called Non-repeatable read, because executing the same query twice may result in different results.

REPEATABLE READ

This level is Repeatable Read, which is the default transaction isolation level of MySQL. It solves the problem of dirty reading and ensures that the results of reading the same record multiple times in the same transaction are consistent.

But theoretically, this level cannot solve another problem: phantom reading. The so-called phantom read means that when a transaction reads records in a certain range, another transaction inserts a new record in the range. When the previous transaction reads the records in the range again, it will Produce phantom lines.

Phantom read example: The first transaction modifies the data in a table. For example, this modification involves "all data rows" in the table. At the same time, the second transaction also modifies the data in this table. This modification inserts "a row of new data" into the table. Then, in the future, the user who operates the first transaction will find that there are still unmodified data rows in the table, as if a hallucination has occurred.

The InnoDB storage engine solves phantom reads through multi-version concurrency control The problem.

SERIALIZABLE

This level is serializable and is the highest isolation level. It avoids the phantom read problem mentioned earlier by forcing transactions to be executed serially. In short, this level locks every row of data read, so it can cause a lot of timeouts and lock contention problems.

This isolation level is rarely used in actual applications. This level should only be considered when it is very necessary to ensure data consistency and no concurrency is acceptable.

Isolation level	Dirty read possibility	Non-repeatable read possibility	Phantom read possibility	LOCK READ
READ UNCOMMITTED	Yes	Yes	Yes	No
READ COMMITTED	No	Yes	Yes	No
REPEATABLE READ	No	No	Yes	No
SERIALIZABLE	No	No	No	Yes

The above is the detailed content of Introduction to the four isolation levels in MySQL. For more information, please follow other related articles on the PHP Chinese website!