mysql insert update 同时执行_MySQL是如何解决不可重复读隔离级别中的幻读问题的...

大部分数据库系统(如Oracle)都将都将读提交(Read-Commited)作为默认隔离级别，而MySQL却选择可重复读(Repeatable-Read)作为其默认隔离级别。这篇文章我们就分析下MySQL为何会选取不可重复读隔离级别作为默认隔离机制以及是如何解决不可重复读隔离级别的幻读问题。

隔离级别

展开分析之前，我们先来认识下隔离级别的概念。

隔离级别总共有四种：

读未提交(Read-Uncommited)

读提交(Read-Commited)

可重复读(Repeatable-Read)

串行化(Serializable)

其中，读未提交和串行化隔离级别一般不会使用到，因为读未提交会导致脏读，不可重复读，幻读等一系列问题。而串行化是将所有的事务强制串行执行，严重影响并发性能。

这里我们简单介绍下脏读，不可重复读和幻读的概念：

脏读：事务A读取到了事务B修改但未提交且最后要回滚的数据。

如上图所示，t3时刻，事务A读取到了事务B还累加但是还未提交的a值，且在t3时刻，事务B回滚了，那么事务A基于t3时刻的查询所做的操作就会出现问题。

不可重复读：事务A前后读取到的数据不一致。

如上图所示，事务A在t2时刻读取到a的值，和t4时刻读取到的a的值不一致，因为事务B在t3时刻对a值进行了更新并提交。

幻读：事务A前后读取的结果条数不一致。

如上图所示，事务A在t2时刻和t4时刻获取到的数据条数不一致，因为事务B在t3时刻新增了一条符合事务A查询条件的数据并提交了。

其中，读提交(RC)隔离级别可以避免脏读的产生，但是会有不可重复读和幻读的问题；可重复读(RR)隔离级别可以避免脏读和不可重复读的问题，但是会有幻读的问题。

从binlog说起

关于MySQL为何会采用可重复读作为其默认隔离级别，得从MySQL的binlog说起。

binlog是MySQL的二进制日志，其记录数据表结构变更(alter，create)以及表数据更改(update，delete，insert)。

binlog日志有三种记录模式并各有优缺点：

MySQL默认的binlog记录模式为row。

在早期版本的MySQL中，binlog只有statement这一种记录模式，而此种模式导致的一个致命问题就是，在读提交(RC)隔离级别下会导致主从数据不一致。

在binlog中，记录日志的规则为：事务commit之后，记录日志。

我们看下在RC隔离机制下的一个案例：

假设此时binlog记录模式为statement。

那么记录binlog的顺序为：

t4时刻，记录t1表的delete语句；

t6时刻，记录对t表的update语句。

之后主从同步，master将自身的binlog同步给slave，slave执行同步时就会遇到的一个问题：slave会先执行删除t1表的内容，再执行更新t表的记录，此时会导致主从不一致。

接下来，我们在看下在RR隔离机制下的相同案例：

在RR隔离机制下，事务B的操作被阻塞，所以不会使得binlog在statement模式下记录顺序出现颠倒而导致主从数据不一致问题。

所以，由于早期MySQL版本中binlog只有statement模式，而在读提交(RC)隔离级别下记录的binlog使用statement模式会导致主从数据不一致的问题，所以，MySQL选择使用可重复读(RR)作为默认隔离级别以保证主从复制数据一致性。

MVCC

避免幻读

在高性能MySQL第三版中可重复读隔离级别的描述中写到：可重复读不能避免幻读的产生。幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新行，当之前的事务再次读取该范围的记录时，会产生幻行。InnoDB存储引擎通过多版本并发控制(MVCC)解决了幻读问题。

我们先来看一个可重复读隔离级别(RR)下的实例：

分析以上实例：

理论上，在t3时刻，事务B插入了一条符合事务A查询条件的记录并提交了事务，那么事务A在t2和t4时刻的查询应该是不一样的，但是实际结果确是：事务A前后查询结果一致。

实际上，这是MVCC的功劳。MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，在某个时间点事务开启时，其看到的数据是该时间点之前已经提交的数据的快照内容，这就保证了事务执行期间看到的数据时一致的。

分析“RR级别避免幻读”图示中的事务：

事务A在t2时刻获取到快照a，此快照将持续到t4时刻事务A提交事务。

事务B在t3时刻插入一条数据，但是事务A的快照a是基于t2时刻的快照，所以事务A并不能获取到事务B插入的数据。

快照

当然，MySQL的MVCC快照并不是每一个事务进来就copy一份数据库信息，而是基于数据表每行信息后面保存的系统版本号去实现的。如下图所示，一行信息会有多个版本并存，每个事务可能读取到的版本不一样。

每开启一个新的事务，系统版本都会自动递增，事务开始时刻的系统版本号会作为事务版本号，用来和查询到的每行记录的版本号进行比较。

针对select，insert，delete，update操作，InnoDB的MVCC具体操作为：

select：

InnoDB会根据两个条件检查每行记录值：

1、InnoDB只查找行的系统版本号小于或等于事务的系统版本号的记录，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。

2、行的删除版本要么未定义，要么大于当前事务版本号，确保可以读取到未删除之前的数据。

insert：

InnoDB为新插入的行保存当前系统版本号作为行版本号。

delete：

InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

update：

InnoDB为插入的一行新记录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

文字太抽象，我们通过图来了解下：

start transaction with consistent snapshot;表示立即开启事务。

1、select

假设表T(id,a,b) 有数据[1,1,1],[2,2,2],[3,3,3]

假设当前事务ID为10：

如上，事务B之所以获取不到事务A的insert，是因为事务B的事务ID比事务A提交的插入数据的行标志ID小。

2、update

update的常规commit和select类似，我们看下未提交的情况。

假设表T(id,a,b) 有数据[1,1,1],[2,2,2],[3,3,3]

假设当前事务ID为10，当前id=1行版本号为10：

我们根据上图分析下流程。

首先为了方便理解，我们将行版本ID以及当前行记录内容记为x{z,z,z}

那么初始版本为：10{1,1,1}

事务A：可读版本为10,11

事务B：可读版本为10,11,12

事务C：可读版本为10,11,12,13

执行流程：

a、事务A，B，C依次开启事务；

b、事务C首先update并commit，那么此时版本为13{1,2,1}；

c、接下来事务B执行update，此时查询到当前最新行版本为13{1,2,1}，update需要当前读的数据，以防数据不一致，所以拿到13{1,2,1}版本数据进行update，此时版本变更为12{1,3,1}；

d、然后事务A执行了select，查询时，因为事务A的事务版本号为11，所以只能读取行版本号小于等于11的版本，所以还是原始数据。

整个版本变更过程为：

10{1,1,1} -> 13{1,2,1} -> 12{1,3,1}

以上就是update的一种情况。

delete就和select，update类似，就不再详细说明了。

总结

MySQL之所以选择可重读事务隔离机制是因为早期binlog只支持statement格式，而此种格式在读提交隔离机制下回导致主从不一致。

MySQL的可重读隔离机制解决幻读的问题关键是靠MVCC的实现，事务ID和行版本ID保证了读取的一致性和隔离性。

在MySQL中，通过多版本并发控制(MVCC)去避免幻读的问题，但是只是在select的时候可以避免幻读，update之后再select还是可能会出现幻读现象。