Analysis of MYSQL REPEATABLE-READ isolation level, repeatableread

Analysis of MYSQL REPEATABLE-READ isolation level, repeatableread

The REPEATABLE-READ can be read repeatedly, and the contents of the select table in the set autocommit = 0 or start transaction state will not change. This isolation level may cause the read to have been modified.

For example:

Session 1 reads a field with a line a = 1

In Session 2, this field is modified to a = 0 and submitted.

When the field a = 0 is updated in session 1, it will find that the number of affected rows is 0, so that you can determine whether the modification is successful based on whether the number of affected rows is 0 or 1!

This is useful in some programs!

Session 1:

Mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

Mysql> select * from test. dd where id = 1;
+ ---- + ------ +
| Id | aa |
+ ---- + ------ +
| 1 | 2 |
+ ---- + ------ +
1 row in set (0.00 sec)

Session 2:

Mysql> update test. dd set aa = 1 where id = 1;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Session 3:

Mysql> select * from test. dd where id = 1;
+ ---- + ------ +
| Id | aa |
+ ---- + ------ +
| 1 | 2 |
+ ---- + ------ +
1 row in set (0.00 sec)

Mysql> update test. dd set aa = 1 where id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

The number of affected rows here is 0. We can determine whether the update is successful based on this value. This is useful when you need to change the status bit of some rows!

How to isolate MySql transactions

MySQL InnoDB tables have
Transaction-isolation =
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE
Four
None for MyIsam,
You can see what type of Enginee is in your table.

Detailed description of database and mysql transaction isolation level repeatable-read

First question:
The new version of mysql solves the phantom read problem through mvcc, so you do not see the phantom read Phenomenon

Second question:
Transaction B is unable to see the changes made by transaction A, unless your transaction B level is lower than repeatable-read