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

Today, I would like to talk to you about what is the repeatable reading of mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something from this article.

Introduce a problem

This question comes from an after-class thinking question of an online course, which goes like this:

I use the following table structure and initialization statements as the experimental environment, and the transaction isolation level is repeatable. Now, I'm going to zero the c value of all the rows with the same field c and id value, but I found a

It's a "weird" situation that can't be changed. Please construct this situation and explain its principle.

Mysql > CREATE TABLE `test2` (

`id`int (11) NOT NULL

`c`int (11) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB

Insert into test2 (id, c) values (1), (2), (3), (4)

Add to explain this problem, mysql environment, innodb engine, transaction isolation level is repeatable, a table has only two fields, and then insert 4 pieces of data, I hope you construct a situation in the above figure, that is, clearly update, but the result is not updated, select does not seem to take effect.

Give the answer to the question

Let's just give the answer first.

Open two mysql interaction windows to simulate the operation of two things, such as one thing called An and one thing called B.

Here's the process.

/ / thing A

Start transaction with consistent snapshot

/ / thing B

Update test2 set c = c + 4

/ / thing A

Update test2 set c = 0 where id = c

/ / thing A

Select * from test2

The screenshot of the specific operation is as follows

After things commit (things are over), the real data can be seen through select.

Explain and explain

To understand the answer to this question, you first need to understand what is a repeatable isolation level.

The isolation level can be read repeatedly, and a view read-view is created when transaction A starts, and then during transaction An execution, even if other transactions modify the data, transaction A still sees what it sees at startup.

We first execute start transaction with consistent snapshot on thing A, which starts the life cycle of thing An and is a manual thing. Because start transaction disables autocommit by default.

Then transaction B begins to execute the update directly. Automatic things are used here for ease of operation. That is, thing B is submitted after update.

At this point, the data looks like this:

Idc15263748

Then thing A continues to execute update test2 set c = 0 where id = c * .It is obvious that since thing B has been submitted and its update is currently read, the judgment condition is not met, so thing A will not update any data.

Then, thing An executes the select statement, why is the result still the previous data? It is because the select of thing A uses consistent reading, also known as snapshot reading, which reads the previous snapshot data.

A consistent read reads data that has been committed at a certain point in time, which in this case is the point in time that start transaction with consistent snapshot executes.

Index, now most Internet companies will set the isolation level to RC (Read Commited), that is, read submitted mode. Of course, in addition to the above problem, there are other reasons, this is not the focus of this article.

After reading the above, do you have any further understanding of what is the repeatability of mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.