理解mysql中的MVCC多版本并发控制，某些场景会出现幻读

目录

MVCC是为了实现数据库的并发控制而设计的一种协议。

几乎所有的RDBMS都支持MVCC。

针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。

mvcc并没有完全解决幻读的问题：以下做2个实验

SQL中定义的四种标准隔离级别：

InnoDB的MVCC实现机制

参考资料

---

MVCC是为了实现数据库的并发控制而设计的一种协议。

1.从我们的直观理解上来看，要实现数据库的并发访问控制，最简单的做法就是加锁访问，即读的时候不能写（允许多个西线程同时读，即共享锁，S锁），写的时候不能读（一次最多只能有一个线程对同一份数据进行写操作，即排它锁，X锁）。这样的加锁访问，其实并不算是真正的并发，或者说它只能实现并发的读，因为它最终实现的是读写串行化，这样就大大降低了数据库的读写性能。

2.加锁访问其实就是和MVCC相对的LBCC，即基于锁的并发控制（Lock-Based Concurrent Control），是四种隔离级别中级别最高的Serialize隔离级别。为了提出比LBCC更优越的并发性能方法，MVCC便应运而生。

 

几乎所有的RDBMS都支持MVCC。

它的最大好处便是，读不加锁，读写不冲突。在MVCC中，读操作可以分成两类，快照读（Snapshot read）和当前读（current read）。
1）快照读，读取的是记录的可见版本（可能是历史版本，即最新的数据可能正在被当前执行的事务并发修改），不会对返回的记录加锁；

2）当前读，读取的是记录的最新版本，并且会对返回的记录加锁，保证其他事务不会并发修改这条记录。

在MySQL InnoDB中，简单的select操作，如 select * from table where ? 都属于快照读；属于当前读的包含以下操作：

• select * from table where ? lock in share mode; （加S锁）

• select * from table where ? for update; （加X锁，下同）

• insert, update, delete操作

针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。

先对一条满足条件的记录加锁，返回给MySQL Server，做一些DML操作；然后再读取下一条加锁，直至读取完毕。需要注意的是，以上需要加X锁的都是当前读，而普通的select（除了for update）都是快照读，每次insert、update、delete之前都是会进行一次当前读的，这个时候会上锁，防止其他事务对某些行数据的修改，从而造成数据的不一致性。我们广义上说的幻读现象是通过MVCC解决的，意思是通过MVCC的快照读可以使得事务返回相同的数据集

mvcc并没有完全解决幻读的问题：以下做2个实验

背景：innodb引擎，事务隔离级别是：REPEATABLE-READ
mysql> select @@global.tx_isolation, @@tx_isolation;

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

| @@global.tx_isolation | @@tx_isolation |

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

| REPEATABLE-READ | REPEATABLE-READ |

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

 

实验1:

session A	session B
mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)
mysql> select * from test; +----+------+ | id | n | +----+------+ | 1 | 1 | | 2 | 2 | +----+------+ 2 rows in set (0.00 sec)	mysql> select * from test; +----+------+ | id | n | +----+------+ | 1 | 1 | | 2 | 2 | +----+------+ 2 rows in set (0.00 sec)
	mysql> insert into test(id,n) values(3,3); Query OK, 1 row affected (0.00 sec)
	mysql> commit; Query OK, 0 rows affected (0.00 sec)
mysql> select * from test; +----+------+ | id | n | +----+------+ | 1 | 1 | | 2 | 2 | +----+------+ 2 rows in set (0.00 sec)
惊讶的发现，影响了3条数据 mysql> update test set n = 4; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0
在本事务没有执行过插入时，突然多出了一条id=3的数据。这就是幻读 mysql> select * from test; +----+------+ | id | n | +----+------+ | 1 | 4 | | 2 | 4 | | 3 | 4 | +----+------+ 3 rows in set (0.00 sec)

 

实验2:

通过实验可以发现 mvcc没有完全解决幻读的问题，如果要解决幻读的问题，需要查询时上锁。

session A	session B
mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)
mysql> select * from test; +----+------+ | id | n | +----+------+ | 1 | 1 | | 2 | 2 | +----+------+ 2 rows in set (0.00 sec)	mysql> select * from test; +----+------+ | id | n | +----+------+ | 1 | 1 | | 2 | 2 | +----+------+ 2 rows in set (0.00 sec)
	mysql> insert into test(id,n) values(3,3); Query OK, 1 row affected (0.00 sec)
在未提交事务前，session B 插入数据后，对于 session A 不可见。 mysql> select * from test; +----+------+ | id | n | +----+------+ | 1 | 1 | | 2 | 2 | +----+------+ 2 rows in set (0.00 sec)	mysql> select * from test; +----+------+ | id | n | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ 3 rows in set (0.00 sec)
	mysql> commit; Query OK, 0 rows affected (0.00 sec)
session B 提交事务后，新的数据对于 session A 依旧不可见。 mysql> select * from test; +----+------+ | id | n | +----+------+ | 1 | 1 | | 2 | 2 | +----+------+ 2 rows in set (0.00 sec)
尝试插入id=3的数据，发现重复key，插入失败。实际上数据对于session A 是可知的。可以理解为一次幻读 mysql> insert into test(id,n) values(3,3); ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
此时执行加x锁的查询，发现查询超时，获取不了锁。 mysql> select * from test for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

SQL中定义的四种标准隔离级别

1.READ UNCOMMITTED (未提交读) ：可以读取未提交的记录。会出现脏读。

2.READ COMMITTED (提交读) ：事务中只能看到已提交的修改。不可重复读，会出现幻读。（在InnoDB中，会加行所，但是不会加间隙锁）该隔离级别是大多数数据库系统的默认隔离级别，但是MySQL的则是RR。

3.REPEATABLE READ (可重复读) ：在InnoDB中是这样的：RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，因此不存在幻读现象。但是标准的RR只能保证在同一事务中多次读取同样记录的结果是一致的，而无法解决幻读问题。InnoDB的幻读解决是依靠MVCC的实现机制做到的。

4.SERIALIZABLE （可串行化）：该隔离级别会在读取的每一行数据上都加上锁，退化为基于锁的并发控制，即LBCC。

需要注意的是，MVCC只在RC和RR两个隔离级别下工作，其他两个隔离级别都和MVCC不兼容。

 

InnoDB的MVCC实现机制

1.MVCC可以认为是行级锁的一个变种，它可以在很多情况下避免加锁操作，因此开销更低。MVCC的实现大都都实现了非阻塞的读操作，写操作也只锁定必要的行。InnoDB的MVCC实现，是通过保存数据在某个时间点的快照来实现的。一个事务，不管其执行多长时间，其内部看到的数据是一致的。也就是事务在执行的过程中不会相互影响。下面我们简述一下MVCC在InnoDB中的实现。

2.InnoDB的MVCC，通过在每行记录后面保存两个隐藏的列来实现：

• 行创建时的版本号。

• 行过期版本号（删除）。

• 每开始一个新的事务，系统版本号就会递增。在RR隔离级别下，MVCC的操作如下：

1）select操作。

a. InnoDB只查找版本早于（包含等于）当前事务版本的数据行。可以确保事务读取的行，要么是事务开始前就已存在，或者事务自身插入或修改的记录。
b. 行的删除版本要么未定义，要么大于当前事务版本号。可以确保事务读取的行，在事务开始之前未删除。

2）insert操作。
将新插入的行保存当前版本号为行版本号。

3）delete操作。
将删除的行保存当前版本号为删除标识。

4）update操作。
变为insert和delete操作的组合，insert的行保存当前版本号为行版本号，delete则保存当前版本号到原来的行作为删除标识。

3.purge：由于旧数据并不真正的删除，所以必须对这些数据进行清理，innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge。

 

参考资料

mvcc概念：http://blog.sina.com.cn/s/blog_499740cb0100ugs7.html

mvcc幻度问题：http://blog.sina.com.cn/s/blog_499740cb0100ugs7.html