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

For the isolation level of transactions, the default is RR in MySQL and RC in Oracle. There is a big difference between the two transaction isolation levels. In other words, even if it is the transaction isolation level of RR, there will be some differences between the same relational database MySQL,SQLServer,postgreSQL. So the quarantine level part is worth taking some time to sum up.

I have seen an article by Master Ding Qi before, which is to analyze a "weird" phenomenon of InnoDB under the isolation level RR. Read to benefit a lot, Master Ding not only reasonable but also can change the code to solve problems, really admire, I also did some simple testing and analysis in my own environment.

The first step is to initialize the basic data. We open two windows, create a test table, and insert two records.

Create table t (id int not null, name varchar (10)) engine=innodb

Insert into t values (1), (3)

Although the whole process is two windows, but the operation is a serial process.

First of all, take a look at the phenomenon of RR itself. Session 1 starts a transaction, session 2 inserts a record, and querying session 1 should still be two pieces of data.

# session 1

> begin

Query OK, 0 rows affected (0.00 sec)

After opening the transaction, query the current data situation.

> select * from t

+-+ +

| | id | name |

+-+ +

| | 1 | name1 |

| | 3 | name3 |

+-+ +

2 rows in set (0.00 sec)

Session 2:

Session 2 inserts a record and submits it by default.

> insert into t values (4)

Query OK, 1 row affected (0.00 sec)

During this process, if you look at the data in session 1, it should still be two, which is what RR itself means.

Session 1:

> select * from t

+-+ +

| | id | name |

+-+ +

| | 1 | name1 |

| | 3 | name3 |

+-+ +

2 rows in set (0.00 sec)

Let's go on to do a update, where the record of the id=4 was just inserted in session 2, and the change here still results in a change in a row of data, which is a "weird" place.

> Update t set name= 'name_test' where id = 4

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

And the next place is the crux of the problem. When we query again, we output three lines of records. The original record of id=4,name='name4' was modified to id=4,name='name_test' in session 1.

> select * from t

+-+ +

| | id | name |

+-+ +

| | 1 | name1 |

| | 3 | name3 |

| | 4 | name_test |

+-+ +

3 rows in set (0.00 sec)

At this time, if you look at the data of session 2, the results are relatively reasonable.

Session 2:

Mysql > select * from t

+-+ +

| | id | name |

+-+ +

| | 1 | name1 |

| | 3 | name3 |

| | 4 | name4 |

+-+ +

3 rows in set (0.00 sec)

So this is the strategy for updating conflicts, as is the case with the current implementation of MySQL under the RR isolation level. According to our expected requirements, changes to session 2 should not be visible within the transaction of session 1.

At this point, the same is true of the results in 5.7. there will be some differences in the output of update in version 5.1.

For the code and modifications of this part, please see

Http://dinglin.iteye.com/blog/804655

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.