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

What is the transaction isolation level of mysql database and what are dirty, unrepeatable and phantom reads? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

I. four basic elements for the correct execution of database transactions

1.1ACID principle.

The    ACID principle is the four basic elements for the normal execution of database transactions, which refers to atomicity, consistency, independence and persistence.

The Atomicity of a    transaction means that a transaction is either fully executed or not executed, that is to say, a transaction cannot be stopped after only half execution. For example, if you withdraw money from an ATM, the transaction can be divided into two steps: one card and two payments. It is impossible to draw a card and the money does not come out. These two steps must be completed at the same time. Or don't finish it.

   transaction consistency (Consistency) means that the operation of the transaction does not change the consistency of the data in the database. For example, integrity constrains a+b=10, and if a transaction changes a, then b should change with it. In other words, if A transfers 300 yuan to B, then A's account must be reduced by 300 yuan, and B's account must be increased by 300 yuan. It cannot be said that it has increased or decreased, such as 200 yuan, and so on. This accords with the atomicity of the transaction, but does not conform to the consistency of the transaction. In the actual business, it is not so simple. In the past, it is similar to the logic of buying things and deducting inventory. There is inventory in the main table, inventory in the inventory table, and in the SKU table, and then because of design defects, even if the transaction is added, there is still the problem of oversold and SKU inventory falling short of the total inventory. This is the consistency is not satisfied.

   independence (Isolation): transaction independence, also known as isolation, means that more than two transactions do not interleave execution, because this may lead to data inconsistency.

   persistence (Durability): once a transaction is committed or rolled back, this state is persisted to the database, regardless of read problems caused by isolation.

1.2 dirty reading, unrepeatable reading, phantom reading.

   dirty read (Dirty read): reads data in one transaction that is not committed by another transaction. For example, when a transaction is accessing the data and making changes to the data that have not yet been committed to the database, another transaction accesses the data and then uses the data.

   non-repeatable read (NonRepeatable Read): can not read the same data content. It means that the same data is read many times within a transaction, and before the end of the transaction, another transaction also accesses the same data and modifies it, then, between the two reads of the data in the first transaction, due to the modification of the second transaction, the data read by the first transaction may not be the same.

   phantom reading (Phantom Read): in a transaction, the results of two queries are inconsistent (for the insert operation). Refers to a phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies the data in a table, which involves all rows of data in the table. At the same time, the second transaction also modifies the data in the table, which inserts a row of new data into the table. Then, the user who operates the first transaction later finds that there are no modified data rows in the table, as if there were hallucinations.

   for example, an editor changes a document submitted by the author, but when the production department merges its changes into the main copy of the document, it finds that the author has added unedited new material to the document. This problem can be avoided if no one can add new material to the document until the editors and production departments have finished working on the original document.

II. Isolation level of database transactions

There are four isolation levels for    database transactions, from low to high are Read uncommitted (read uncommitted), Read committed (read commit), Repeatable read (repeatable read) and Serializable (serialization). These four levels can solve the problems of dirty reading, non-repeatable reading and phantom reading one by one.

2.1 Read uncommitted (read unsubmitted)

The    company paid the salary, and the leader transferred 5000 yuan to the singo account, but the business was not submitted, and singo happened to check the account and found that the salary reached 5000 yuan. He was very happy. Unfortunately, the leader found that the working capital given to singo should be 2000 yuan, so he quickly rolled back the transaction (rollback 5000 yuan), modified the amount (revised to 2000 yuan), and submitted the transaction. In the end, the actual salary of singo was only 2000 yuan, and singo was happy.

The above situation occurs in   , that is, what we call dirty reading, two concurrent transactions, "transaction A: the leader pays singo", "transaction B:singo queries the payroll account", and transaction B reads the data that transaction A has not yet committed.

   dirty reads may occur when the isolation level is set to Read uncommitted (read uncommitted), and dirty reads can be avoided if we raise the isolation level to Read committed (read committed) at this time.

2.2 Read committed (read submitted)

   singo takes the wage card to consume, and the system reads that there is indeed 2000 yuan in the card, and at this time his wife also happens to be transferring 2000 yuan from the singo wage card to another account, and submitted the transaction before singo. When singo deducts the money, the system checks that the singo wage card has no money, and the deduction fails. Singo is very puzzled that there is money in the Ming card.

   occurs the above situation, that is, what we call non-repeatable reading, two concurrent transactions, "transaction A:singo consumption" and "transaction B:singo 's wife online transfer". Transaction A reads the data in advance, transaction B immediately updates the data and commits the transaction, and when transaction A reads the data again, the data has changed.

   avoids dirty reads when the isolation level is set to Read committed (read committed), but may result in unrepeatable reads (neither the same data content can be read).

The default level of most    databases is Read committed (read committed), such as Sql Server and Oracle. If the isolation level is raised to Repeatable read (repeatable read), dirty and unrepeatable reads can be avoided.

2.3 Repeatable read (repeatable)

   when the isolation level is set to Repeatable read (repeatable readable), unrepeatable reads can be avoided. When singo takes the wage card to spend, once the system starts to read the payroll information (that is, the transaction begins), it is impossible for singo's wife to modify the record, that is, singo's wife cannot transfer money at this time.

   (here two blogs give different examples, please indicate the reason) or, there are two sessions An and B, which respectively open two transactions, and then A transfers 500 yuan to B, A commits the transaction, and B goes to check, and finds that it is still the original amount of money. B can only end the current transaction and start a new transaction in order to query the changes in the data, thus avoiding non-repeatable reading. If we set Seriizable (serialization), it is equivalent to locking the table, and only one transaction is allowed to access the table at a time.

Although    Repeatable read avoids unrepeatable readings, it is still possible to have phantom readings.

  , such as singo's wife, who works in the banking department, often checks singo's credit card records through the bank's internal system. One day, she was inquiring that the total consumption amount of singo's credit card in that month (select sum (amount) from transaction where month = this month) was 80 yuan, while singo was just out eating Haisai and paid at the cashier, spending 1000 yuan, that is, a new spending record of 1000 yuan (insert transaction). ), and submitted the transaction, and then singo's wife printed the details of singo's monthly credit card consumption on A4 paper, only to find that the total consumption was 1080 yuan. Singo's wife was surprised and thought there was an illusion, and the illusion came into being.

   Note: the default isolation level for Mysql is Repeatable read.

2.4 Serializable (serialization)

   Serializable (serialization) is the highest transaction isolation level with the highest cost, low performance and little use. at this level, transaction sequential execution can avoid not only dirty and unrepeatable reads, but also phantom reads.

III. Summary

3.1 isolation level and corresponding table of possible problems

Isolation level dirty read (Dirty read) non-repeatable read (NonRepeatable Read) phantom read (Phantom Read) read uncommitted (Read uncommitted) possible read (Read committed) impossible repeatable (Repeatable read) is not possible serialization (Serializable) impossible thank you for reading! After reading the above, do you have a general understanding of the transaction isolation level of mysql database and what dirty reading, unrepeatable reading and phantom reading are? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

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.