mysql 设置mvcc_MySQL MVCC

回顾

在MySQL的众多存储引擎中，只有InnoDB支持事务，所有这里说的事务隔离级别指的是InnoDB下的事务隔离级别。

读未提交：一个事务可以读取到另一个事务未提交的修改。这会带来脏读、幻读、不可重复读问题。(基本没用)

读已提交：一个事务只能读取另一个事务已经提交的修改。其避免了脏读，但仍然存在不可重复读和幻读问题。

可重复读：同一个事务中多次读取相同的数据返回的结果是一样的。其避免了脏读和不可重复读问题，但幻读依然存在。

串行化：事务串行执行。避免了以上所有问题。

以上是SQL-92标准中定义的四种隔离级别。在MySQL中，默认的隔离级别是REPEATABLE-READ(可重复读)，并且解决了幻读问题。简单的来说，mysql的默认隔离级别解决了脏读、幻读、不可重复读问题。

不可重复读重点在于update和delete，而幻读的重点在于insert。

在这里，我们只讨论可重复读。

知识储备

MVCC

译注：

MVCC的全称是“多版本并发控制”。这项技术使得InnoDB的事务隔离级别下执行一致性读操作有了保证，换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值。这是一个可以用来增强并发性的强大的技术，因为这样的一来的话查询就不用等待另一个事务释放锁。这项技术在数据库领域并不是普遍使用的。一些其它的数据库产品，以及mysql其它的存储引擎并不支持它。

说明

网上看到大量的文章讲到MVCC都是说给没一行增加两个隐藏的字段分别表示行的创建时间以及过期时间，它们存储的并不是时间，而是事务版本号。

事实上，这种说法并不准确，严格的来讲，InnoDB会给数据库中的每一行增加三个字段，它们分别是DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID(如果表设置了主键或者唯一索引，row_id则不会分配)。

但是，为了理解的方便，我们可以这样去理解，索引接下来的讲解中也还是用这两个字段的方式去理解。

增删查改

在InnoDB中，给每行增加两个隐藏字段来实现MVCC，一个用来记录数据行的创建时间，另一个用来记录行的过期时间(删除时间)。在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。

于是乎，默认的隔离级别(REPEATABLE READ)下，增删查改变成了这样：

SELECT

读取创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。

INSERT

将当前事务的版本号保存至行的创建版本号

UPDATE

新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号

DELETE

将当前事务的版本号保存至行的删除版本号

快照读和当前读

快照读：读取的是快照版本，也就是历史版本

当前读：读取的是最新版本

普通的SELECT就是快照读，而UPDATE、DELETE、INSERT、SELECT ...  LOCK IN SHARE MODE、SELECT ... FOR UPDATE是当前读。

一致性非锁定读和锁定读

锁定读

在一个事务中，标准的SELECT语句是不会加锁，但是有两种情况例外。SELECT ... LOCK IN SHARE MODE 和 SELECT ... FOR UPDATE。

SELECT ... LOCK IN SHARE MODE

给记录假设共享锁，这样一来的话，其它事务只能读不能修改，直到当前事务提交

SELECT ... FOR UPDATE

给索引记录加锁，这种情况下跟UPDATE的加锁情况是一样的

一致性非锁定读

consistent read (一致性读)，InnoDB用多版本来提供查询数据库在某个时间点的快照。如果隔离级别是REPEATABLE READ，那么在同一个事务中的所有一致性读都读的是事务中第一个这样的读读到的快照；如果是READ COMMITTED，那么一个事务中的每一个一致性读都会读到它自己刷新的快照版本。Consistent read(一致性读)是READ COMMITTED和REPEATABLE READ隔离级别下普通SELECT语句默认的模式。一致性读不会给它所访问的表加任何形式的锁，因此其它事务可以同时并发的修改它们。

悲观锁和乐观锁

悲观锁，正如它的名字那样，数据库总是认为别人会去修改它所要操作的数据，因此在数据库处理过程中将数据加锁。其实现依靠数据库底层。

乐观锁，如它的名字那样，总是认为别人不会去修改，只有在提交更新的时候去检查数据的状态。通常是给数据增加一个字段来标识数据的版本。

锁

有这样三种锁我们需要了解

Record Locks(记录锁)：在索引记录上加锁。

Gap Locks(间隙锁)：在索引记录之间加锁，或者在第一个索引记录之前加锁，或者在最后一个索引记录之后加锁。

Next-Key Locks：在索引记录上加锁，并且在索引记录之前的间隙加锁。它相当于是Record Locks与Gap Locks的一个结合。

假设一个索引包含以下几个值：10,11,13,20。那么这个索引的next-key锁将会覆盖以下区间：

(negative infinity, 10]

(10, 11]

(11, 13]

(13, 20]

(20, positive infinity)

了解了以上概念之后，接下来具体就简单分析下REPEATABLE READ隔离级别是如何实现的

理论分析

之所以说是理论分析，是因为要是实际操作证明的话我也不知道怎么去证明，毕竟作者水平实在有限。

但是，这并不意味着我在此胡说八道，有官方文档为证。

这段话的大致意思是，在默认的隔离级别中，普通的SELECT用的是一致性读不加锁。而对于锁定读、UPDATE和DELETE，则需要加锁，至于加什么锁视情况而定。如果你对一个唯一索引使用了唯一的检索条件，那么只需锁定索引记录即可；如果你没有使用唯一索引作为检索条件，或者用到了索引范围扫描，那么将会使用间隙锁或者next-key锁以此来阻塞其它会话向这个范围内的间隙插入数据。

作者曾经有一个误区，认为按照前面说MVCC下的增删查改的行为就不会出现任何问题，也不会出现不可重复读和幻读。但其实是大错特错。

举个很简单的例子，假设事务A更新表中id=1的记录，而事务B也更新这条记录，并且B先提交，如果按照前面MVVC说的，事务A读取id=1的快照版本，那么它看不到B所提交的修改，此时如果直接更新的话就会覆盖B之前的修改，这就不对了，可能B和A修改的不是一个字段，但是这样一来，B的修改就丢失了，这是不允许的。

所以，在修改的时候一定不是快照读，而是当前读。

而且，前面也讲过只有普通的SELECT才是快照读，其它诸如UPDATE、删除都是当前读。修改的时候加锁这是必然的，同时为了防止幻读的出现还需要加间隙锁。

一致性读保证了可用重复读

间隙锁防止了幻读

回想一下

1、利用MVCC实现一致性非锁定读，这就有保证在同一个事务中多次读取相同的数据返回的结果是一样的，解决了不可重复读的问题

2、利用Gap Locks和Next-Key可以阻止其它事务在锁定区间内插入数据，因此解决了幻读问题

综上所述，默认隔离级别的实现依赖于MVCC和锁，再具体一点是一致性读和锁。

演示

上面四幅截图对比，可以看到由于id是主键，用id作为检索条件时只锁定那一个索引记录。接下来，看索引范围的例子

这两幅截图，可以看出，由于没有使用唯一索引作为检索条件，导致不光锁定了索引记录，还锁定了索引之间的间隙，应该是是使用了next-key锁。

参考 https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html