mysql - 事务隔离可重复读深入理解

我们都知道在mysql中默认的事务隔离级别是 “ repeatable read”，从字面意思上我们来理解就是可重复读，就是session事件开始时其他session对数据作的变更无法感应到，到事实上是这样吗？

我们来做一个例子。

比如我们有这一条记录

mysql> select * from user where id=1;

+----+---------+------+--------------------+---------+------------+---------+------+--------+-----------+

| id | name | age | email | deleted | createtime | version | FAB | specid | work_info |

+----+---------+------+--------------------+---------+------------+---------+------+--------+-----------+

| 1 | fuckyou | 99 | test1@baomidou.com | 1 | NULL | 0 | FAB1 | NULL | NULL |

+----+---------+------+--------------------+---------+------------+---------+------+--------+-----------+

1 row in set (0.00 sec)

1.我们开始三个客户端都开启begin事务

2.我们在客户端1上查询结果如下

mysql> select * from user where id=1;

+----+---------+------+--------------------+---------+------------+---------+------+--------+-----------+

| id | name | age | email | deleted | createtime | version | FAB | specid | work_info |

+----+---------+------+--------------------+---------+------------+---------+------+--------+-----------+

| 1 | fuckyou | 99 | test1@baomidou.com | 1 | NULL | 0 | FAB1 | NULL | NULL |

+----+---------+------+--------------------+---------+------------+---------+------+--------+-----------+

1 row in set (0.00 sec)

3.我们在客户端2上update age为98，并且commit结束事务

mysql> update user set age=98 where id=1;

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.08 sec)

4.我们在客户1在查询一次，发现age值未做变更还是99

mysql> select * from user where id=1;

+----+---------+------+--------------------+---------+------------+---------+------+--------+-----------+

| id | name | age | email | deleted | createtime | version | FAB | specid | work_info |

+----+---------+------+--------------------+---------+------------+---------+------+--------+-----------+

| 1 | fuckyou | 99 | test1@baomidou.com | 1 | NULL | 0 | FAB1 | NULL | NULL |

+----+---------+------+--------------------+---------+------------+---------+------+--------+-----------+

1 row in set (0.00 sec)

5.我们客户端3上进行查询，发现age已变为98

mysql> select * from user where id=1;

+----+---------+------+--------------------+---------+------------+---------+------+--------+-----------+

| id | name | age | email | deleted | createtime | version | FAB | specid | work_info |

+----+---------+------+--------------------+---------+------------+---------+------+--------+-----------+

| 1 | fuckyou | 98 | test1@baomidou.com | 1 | NULL | 0 | FAB1 | NULL | NULL |

+----+---------+------+--------------------+---------+------------+---------+------+--------+-----------+

1 row in set (0.00 sec)

6.我们在客户1进行commit结束事务，在查询一 次，发现age变为98

mysql> commit

-> ;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id=1;

+----+---------+------+--------------------+---------+------------+---------+------+--------+-----------+

| id | name | age | email | deleted | createtime | version | FAB | specid | work_info |

+----+---------+------+--------------------+---------+------------+---------+------+--------+-----------+

| 1 | fuckyou | 98 | test1@baomidou.com | 1 | NULL | 0 | FAB1 | NULL | NULL |

+----+---------+------+--------------------+---------+------------+---------+------+--------+-----------+

1 row in set (0.00 sec)

从以上的事例中我们得到这样结论，mysql可重复是在事务开始之后执行第一个sql才生成可重复读的数据镜像，而不是begin一始就生成重复读的数据，且这种重复读数据一直维持到当前事务结束。