Shulou(Shulou.com)06/01 Report--

This article is about what the four isolation levels are in MySQL. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

The isolation of transactions is more complex than expected, and four levels of isolation are defined in the SQL standard. In general, lower-level isolation usually performs higher concurrency and lower system overhead (recommended course: MySQL video tutorial)

READ UNCOMMITTED

This level is an uncommitted read. At this level, changes in a transaction are visible to other transactions even if they are not committed. Transactions can read uncommitted data, which is also known as dirty reading. This level can cause a lot of problems. In terms of performance, it is not much better than other levels, but lacks many of the benefits of other levels. Unless there is a really necessary reason, it is rarely used in practical applications.

READ COMMITTED

This level is submitted read. It is the default isolation level for most database systems, but MySQL is not. This level satisfies a simple definition of isolation: at the beginning of a transaction, only changes made by committed transactions can be "seen". In other words, any changes made by one transaction from the beginning to the commit are invisible to other transactions. This level is sometimes called unrepeatable, because executing the same query twice may get different results.

REPEATABLE READ

This level is repeatable and is the default transaction isolation level for MySQL. It solves the problem of dirty reading and ensures that the result of reading the same record multiple times in the same transaction is consistent.

But in theory, this level does not solve another problem: phantom reading. The so-called illusory reading means that when a transaction is reading a record in a certain range, another transaction inserts a new record in that range, and a magic row occurs when the previous transaction reads the record in that range again.

Phantom reading example: the first transaction modifies the data in a table, such as involving "all data rows" in the table. At the same time, the second transaction also modifies the data in the table by inserting a "row of new data" into the table. Then, the user who operates the first transaction will find that there are no modified data rows in the table, as if there were hallucinations.

The InnoDB storage engine solves the problem of phantom reading through multi-version concurrency control.

SERIALIZABLE

This level is serializable and is the highest isolation level. It avoids the phantom reading problem mentioned earlier by forcing the serial execution of transactions. In short, this level locks every row of data read, so it can lead to a large number of timeouts and lock contention problems.

This isolation level is also rarely used in practical applications and is considered only when there is a great need to ensure data consistency and it is acceptable that there is no concurrency.

Isolation level dirty reading possibility can not be repeated reading possibility phantom reading possibility plus lock reading READ UNCOMMITTEDYesYesYesNoREAD COMMITTEDNoYesYesNoREPEATABLE READNoNoYesNoSERIALIZABLENoNoNoYes thank you for your reading! So much for sharing what are the four isolation levels in MySQL. I hope the above content can be helpful to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.