Four isolation levels for mysql Databases and four isolation levels for mysql Databases

Four isolation levels for mysql Databases and four isolation levels for mysql Databases

I recently read the high-performance MYSQL book, so I have been impressed by its examples.

The transaction operation of the database is actually a group of atomic operations. Either all operations are successful or all operations fail.

　　

For example, if I want to sell a movie ticket externally and register the sales information to another table, the following three steps are required:

1. query whether the number of movie tickets meets the requirement of selling one movie ticket SELECT remain_count FROM cinema WHERE film_id = 123456789;

2. UPDATE the number of movie tickets UPDATE remain_count = remain_count-1 FROM cinema WHERE film_id = 123456789;

3. INSERT sales information insert into sell_mes (id, mes) values (id, mes );

Imagine if one of our steps is wrong or is disrupted by other operations, it is very easy to have problems. For example, there are two sales systems, A and B, which are selling the same ticket. At this time, there is only one ticket left. A receives the order and wants to sell one ticket. Then, he can check that the number of movie tickets is greater than 1, so when the product is to be sold, that is, when the first step is completed and the second step is executed, B also receives the order and sees that the remaining ticket is greater than 1, and B also needs to sell one ticket. In this case, there is a ridiculous situation where only one ticket is available but two tickets are sold.

Therefore, a good system needs to pass ACID Testing. As for what ACID is, Baidu will do it on its own. This blog post describes isolation levels and examples.

There are four isolation levels for transactions. databases with high isolation levels have high reliability, but the concurrency is low. databases with low isolation levels have low reliability, but the concurrency is high and the system overhead is small.

　　

1. read uncimmitted (uncommitted READ)

Modifications in the transaction can be seen in other transactions even if they are not committed. For example, the two steps above are called dirty reads. This isolation level will cause many problems, if not necessary, do not use it as needed

Example: In the ticket sales system, Tom and Xiaohua are ticket sellers. They are employees in two different windows. Now the ticket sales system only has three tickets. At this time, A will buy three tickets for Xiaohua, B To James to buy a ticket, Xiao Hua found there is still to receive the order, it is necessary to perform the third step, Xiao Ming received B's request to query whether there is any ticket. Once I saw that Xiaohua sold three tickets, I refused to sell the tickets. However, the Xiaohua system encountered a problem and the execution failed in step 3. The database rolled back the data to ensure atomicity. That is to say, none of the tickets were sold.

Conclusion: this means that the transaction has not been committed, and other transactions can see the consequences of the modified data, that is, dirty reads.

　　

2. read committed (submit READ)

The default isolation level of most database systems is read committed. This isolation level is the beginning of a transaction and can only see the results of completed transactions, which are being executed, it cannot be seen by other transactions. At this level, the old data will be read.

Example: James Xiaohua salesman, with three remaining tickets. A came to Xiaohua and requested three tickets. Xiaohua accepted the order and sold three tickets. When the above sales steps were in progress, B Also came to James to buy tickets. Because Xiaohua's sales transaction was half done, James did not see Xiaohua's transaction execution. The number of votes read was 3. When he was preparing to accept the order, xiaohua's sales transaction was completed. At this time, James's system turned to show 0 tickets, and Xiao Minggang tried to press the mouse to accept the order and quickly contract back.

Summary: this means that Xiaohua's transaction is half executed, while James does not see the operations he performs, so he sees old data, that is, invalid data.

 

3. repeatable read (repeatable read)

Repeatable read solves the dirty READ problem. This level ensures that the results of records in each row are consistent, that is, the old data READ problem mentioned above, but it cannot solve another problem. Phantom rows, as the name suggests, are the rows of data suddenly popped out. This means that a transaction reads data within a certain range, but another transaction inserts data into this range, resulting in inconsistent data rows during multiple reads.

Example: The sales department stipulates that if the sales record is lower than the specified value, the salary is deducted. At this time, the manager checks James's sales record on the backend console, I was surprised to find that the sales records could not meet the specified number of times. I was prepared to print the sales list. I was so confident that I did not expect the number of sales records in the sales list to increase by a few times, just to meet the requirements, the angry manager tore the list paper. It turned out that James sold a few tickets at the moment he was about to print, so he avoided the treasure of wage reduction.

Conclusion: Although reading the same piece of data ensures consistency, it cannot ensure that no new data is inserted.

 

4. SERIALIZABLE (SERIALIZABLE)

SERIALIZABLE is the highest isolation level. It forces transaction serial execution (note that it is serial) to avoid the previous phantom read situation. Because of its large number of locks, it causes a large number of requests to time out, therefore, the isolation level can be considered only when data consistency is required and the concurrency is not so large.