Shulou(Shulou.com)05/31 Report--

What is the transaction analysis in MySQL? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

As we all know, the processing speed of the computer is very fast, but no matter how fast the computer is, it is also faced with such a problem. There are many requests to operate on unified resources at the same time. Therefore, in the database, transactions are introduced to solve such problems.

Let's take a simple example. I bought two rows of Yili duo on the street and asked Alipay to transfer 25 yuan to the merchant. This will be done at this time. Alipay first checks whether I still have 25 yuan in my balance, and then withholds 25 yuan from my balance. Then the merchant's Alipay increases by 25 yuan. If my Alipay happens to have only 25 yuan, at the moment of transferring it to the merchant, I used another mobile phone to buy a 20-yuan watermelon on pinduoduo, and also paid with Alipay, because with database transactions, these two operations will not be successful at the same time.

Mysql's database has four features, which we call ACID. Atomicity, consistency, isolation, and persistence.

Atomicity) A transaction must be regarded as an indivisible minimum unit of work, and all operations in the whole transaction either commit successfully or fail to roll back. For a transaction, it is impossible to perform only part of the operations. This is the atomicity of the transaction. In the above example, either I deduct 25 yuan, the merchant of Yilido has 25 yuan more, or I don't deduct the money, and the merchant doesn't have much money. There will be no situation in which my money is deducted and the merchant does not receive the money. It is estimated that there are all kinds of complaints and disputes every day.

Consistency (consistency) databases always transition from one consistent state to another. (in the previous example, consistency ensures that the 25 yuan is either not given to the merchant or has already arrived in the merchant's account, and it will not disappear out of thin air.)

Isolation generally speaking, changes made by one transaction are not visible to other transactions until they are finally committed. In the previous example, when I was still paying 25 yuan to the commissary seller, I felt that I still had 25 yuan for another transaction I paid on pinduoduo, only after my whole transaction was submitted. The other transaction knew that I had deducted the corresponding amount. Therefore, when we perform the deduction, we also have to judge whether the balance is sufficient.)

Durability) once a transaction commits, its changes are permanently saved to the database. (even if the system crashes, the modified data will not be lost.)

In real time, if the database wants to strictly follow this property, it is bound to degrade the performance of the database. So, in InnoDB, there are many different transaction levels. They are read unsubmitted, read submitted, repeatable, and serialization.

Read uncommitted: transactions that have been changed by others have not yet been committed, and I can read them in my transactions. In the above example, if the deduction occurs after my amount has been reduced by 25%, but the transaction has not been committed, if I read the database at this time, I will already read that the data is 0. Obviously, if this time, the previous time rollback, then the result of this read is called dirty read.

Read committed: I can only read it in my transaction if someone else has changed the data. In the above example, if the transaction deducting 25 is not completed, all you read is result 25. If the balance is read multiple times in a later transaction, it is possible to read 25 and 0, which we call unrepeatable.

Repeatable: a transaction in which someone else changed the data has been committed, and I do not read it in my transaction. When the data is read for the first time, the corresponding view is actually formed, and only the corresponding data can be read later.

Serial: no one else can change the data before my transaction is committed. This is strict serialization. In the above example, only after the previous deduction of 25 yuan is completed can the later transaction be started.

For these four isolation levels, the parallel performance decreases and the security increases in turn.

This is the answer to the question about the transaction analysis in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about 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.