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

This article mainly introduces "what are the four isolation levels of MySQL transactions". In daily operations, I believe many people have doubts about what the four isolation levels of MySQL transactions are. The editor consulted all kinds of materials and sorted out simple and useful operation methods, hoping to help you answer the questions of "what are the four isolation levels of MySQL transactions?" Next, please follow the editor to study!

Four isolation levels for Mysql transactions

There are four isolation levels of database transactions, from low to high are Read uncommitted, Read committed, Repeatable read, Serializable. Moreover, dirty reading, unrepeatable reading and phantom reading may occur in the concurrent operation of the transaction. The following examples illustrate their concepts and relationships one by one.

Read uncommitted

Read uncommitted, as the name implies, means that one transaction can read the data of another uncommitted transaction.

Example: the boss wants to pay the programmer, and the programmer's salary is 36000 / month. But when paying the salary, the boss accidentally pressed the wrong number, press 39000 / month, the money has been transferred to the programmer's account, but the transaction has not yet been submitted, at this time, the programmer went to check his salary this month and found that it was 3, 000 yuan more than usual. I thought I was very happy with the raise. But the boss found out in time that something was wrong, immediately rolled back the transaction that was almost committed, and changed the number to 36000 before submitting.

Analysis: the actual programmer's salary is still 36000 this month, but what the programmer sees is 39000. What he saw was the data when the boss had not yet submitted the transaction. This is dirty reading.

Then how to solve the dirty reading? Read committed! Reading submission can solve the problem of dirty reading.

Read committed

Read commit, as the name implies, means that one transaction cannot read data until another transaction commits.

Example: the programmer enjoys life with a credit card (of course, there is only 36000 in the card). When he pays the bill (the programmer's transaction is turned on), the charging system detects that there is 36000 in his card in advance, at this time! The programmer's wife wants to transfer all the money for household use and submit it. When the charging system is ready to deduct the money, check the amount in the card and find that there is no money (the second time, of course, you have to wait for the wife to transfer the amount to be submitted). Programmers will be very depressed, clearly there is money in the card.

Analysis: this is read commit. If a transaction updates the data (UPDATE), the read transaction has to wait for the update transaction to read the data, which can solve the dirty read problem. But in this case, there is a transaction scope where two identical queries return different data, which is non-repeatable.

So how to solve the possible unrepeatable reading problem? Repeatable read!

Repeatable read

Repeat read, that is, when you start reading data (transaction is open), you are no longer allowed to modify the operation

Example: the programmer enjoys life with a credit card (of course, there is only 36000 in the card). When he pays the bill (transaction is opened, UPDATE modification of other transactions is not allowed), the charging system detects that there is 36000 in his card in advance. His wife can't transfer the amount at this time. Then the charging system can deduct money.

Analysis: repeated reading can solve the problem of unrepeatable reading. At this point, it should be clear that unrepeatable reads correspond to modifications, that is, UPDATE operations. But there may also be phantom reading problems. Because the phantom reading problem corresponds to the insert INSERT operation, not the UPDATE operation.

When will there be phantom reading?

Example: programmer one day to spend, spent 2, 000 yuan, and then his wife to check his consumption record today (full table scan FTS, wife affairs open), see is indeed spent 2, 000 yuan, at this time, programmers spent 10, 000 to buy a computer, that is, a new INSERT consumption record, and submitted. When the wife prints the programmer's consumption record list (the wife's affairs are submitted), she finds that she spends 12000 yuan and seems to have hallucinations. This is illusion.

How to solve the problem of phantom reading? Serializable!

Serializable serialization

Serializable is the highest transaction isolation level at which transactions are serialized sequentially to avoid dirty reads, unrepeatable reads, and phantom reads. However, this transaction isolation level is inefficient, consumes database performance, and is generally not used.

It is worth mentioning that the default transaction isolation level for most databases is Read committed, such as Sql Server, Oracle. The default isolation level for Mysql is Repeatable read.

At this point, the study on "what are the four isolation levels of MySQL transactions" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.