MVCC

MVCC

Multi-Version Concurrency Control

MVCC并没有简单的使用数据库的行锁,而是使用了行级锁,row_level_lock,而非InnoDB中的innodb_row_lock

总结

1. 什么是MVCC ?

2. MVCC适用隔离级别?

3. RR 隔离级别下,insert update delete 和select 的工作原理?

4. mysql 两个视图概念是什么?

5. begin / start transaction 下的事务read-view 和snapshot 创建时间

6. update 是”当前读”吗?

7. 什么是一致性非锁定读?

8. 一致性锁定读的情况列举一下? 各是什么锁级别?

9. 怎么创建开启事务就创建一致性读视图?

10. MVCC 和undo log 是怎么配合的?

11. undo log中的insert 什么时候删除,update 和 delete 什么时候删除,为什么?

问题总结

1. MVCC是多版本并发控制一致性读

2. MVCC 适用隔离级别是read-committed read-repeatable

3. RR级别下

   ① 创建版本号为DB_TRX_ID 删除版本号DB_ROLL_PTR

   ② insert 的时候会更新行记录的创建版本号为本事务的事务版本号

   ③ delete 的时候会更新行记录的删除版本号为本事务的事务版本号

   ④ update的时候会copy一个新行,创建版本号为当前事务版本号,就行数据的删除版本号为此事务的版本号

   ⑤ select 的时候

   1. 创建版本号<=当前事务版本号,保证事务开启前已经存在或者本事务的数据操作

   2. 删除版本号为空或者删除版本号>当前事务版本号,保证事务开始前未被删除

4. 视图有两个,一个是事务视图,是事务执行的第一条语句创建的read-view,一个是sleect 查询view构成的查询链表

5. 事务中的update、 delete、 insert、 select in lock share mod 、select …for update是当前读，读取最新的数据

6. 一致性非锁定读指的就是MVCC和隔离级别实现的多版本一致性读

7. 一致性锁定读是select … in lock share mode（S共享锁）、select…for update（X拍它锁）

8. begin/start transaction 是第一个select 的时候创建一致性读视图，执行第一个sql语句的时候创建事务视图，start transaction with consistent snapshot会在事务开启的时候创建一致性读视图

9. update的时候会copy 一份新的数据，新数据的创建版本是次事务版本号(DB_TRX_ID)，旧数据的删除版本是当前事务版本（DB_ROLL_PTR)

10. 事务开启执行的第一条select sql语句会生成一致性读视图或者start transaction with snapshot生成事务一致性视图在undo log 中,事务视图是在执行第一条sql语句时候生成的

11. insert 事务提交就可以删除了,update 和delete 回滚和事务都需要,要等到purge thread 线程去删除

CREATE TABLE testmvcc (
id int(11) DEFAULT NULL,
name varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=uf8;

基本原理

MVCC的实现,通过保存数据在某个时间点的快照来实现的,这意味着一个事务无论运行多长时间,在同一个事务里能够看到数据的一致的视图,根据事务开始的时间不同,同时也就意味着在同一个时刻不同事务看到相同表里的数据可能是不同的

基本特征

• 每行数据都存在一个版本,每次数据更新的时候都更新版本

• 修改时copy出当前版本随意修改,各个事务之间无干扰

• 保存时候比较版本号,如果成功(commit),则覆盖原纪录;失败则放弃copy(rollback)

InnoDB存储引擎的MVCC的实现策略

在每一行数据中额外保存连个隐藏的列: 当前创建时的版本号和删除时的版本号可能为空，其实还有一列称为回滚指针，用于事务回滚，不在本文范畴）。这里的版本号并不是实际的时间值，而是系统版本号。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。

每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。

MVCC下InnoDB的增删改查时怎么工作的

事务版本号DB_TRX_ID

删除版本号 DB_ROLL_PTR

insert

记录的版本号是当前事务的版本号

执行一条数据语句

insert into testmvcc values(1,“test”);

假设事务id是1,那么插入后的数据行如下:

update

​ 在更新操作的时候,采用的是先标记旧的那行记录为已删除,并且删除版本号是事务版本号,然后在插入一条新的记录的方式

​ 比如,针对上面的那行记录,事务id为2要把name字段更新

​ update table set name= ‘new_value’ where id=1;

*delete*

删除操作的时候,就是把事务的版本号作为删除版本号

delete from table where id=1;

select

查询的时候要满足以下两个条件才会被查询出来

n InnoDB 只查找创建版本早于当前事务id或者等于当前事务版本号****(<=当前事务版本号),****这样可以确保事务读取的行,要么是在事务开始之前已经存在,要么是当前事务插入的

n 删除版本号未指定或者大于当前事务版本号,即查询事物开启后确保读取的行未被删除–不是在此事务版本号之前的事务删除的(即上述事务id2的事务查询的时候,依然能查询到事务id3锁删除的行)

MVCC和undo log 的配合

下面右侧数据:一行数据记录,主键ID是10,name=’jack’,age=10,被update更新set为name= ‘Tom’，age=23。

事务会先使用“排他锁”锁定改行，将该行当前的值复制到undo log中，然后再真正地修改当前行的值，最后填写事务的DB_TRX_ID，使用回滚指针DB_ROLL_PTR指向undo log中修改前的行DB_ROW_ID。

• DB_TRX_ID: 6字节DB_TRX_ID字段，表示最后更新的事务id(update,delete,insert)。此外，删除在内部被视为更新，其中行中的特殊位被设置为将其标记为已软删除。

• DB_ROLL_PTR: 7字节回滚指针，指向前一个版本的undolog记录，组成undo链表。如果更新了行，则撤消日志记录包含在更新行之前重建行内容所需的信息。

• DB_ROW_ID: 6字节的DB_ROW_ID字段，包含一个随着新行插入而单调递增的行ID, 当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。如果表中没有主键或合适的唯一索引, 也就是无法生成聚簇索引的时候, InnoDB会帮我们自动生成聚集索引, 聚簇索引会使用DB_ROW_ID的值来作为主键; 如果表中有主键或者合适的唯一索引, 那么聚簇索引中也就不会包含 DB_ROW_ID了 。

• 其它：insert undo log只在事务回滚时需要, 事务提交就可以删掉了。update undo log包括update 和 delete , 回滚和快照读 都需要。

适用事务级别

读已提交 Read committed

可重复读 Repeatable Read

Read uncimmitted由于存在脏读，即能读到未提交事务的数据行，所以不适用MVCC.

原因是MVCC的创建版本和删除版本只要在事务提交后才会产生。

串行化由于是会对所涉及到的表加锁，并非行锁，自然也就不存在行的版本控制问题。

快照读和当前读

快照读:

快照读存在 RC 和RR事务级别下

RC 是每次select 生成一个快照

RR 在begin/start transaction 下是第一次select的时候生成快照

在 start transaction with consistent snapshot 时候是事务开始时候创建的

update 和 delete insert 是当前读,读取最新的数据版本

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

当前读:

读取的是最新版本

普通的select就是快照读,而update、delete、insert、。。。lock in share mode、select 。。。for update是当前读

当前读的实现方式：next-key锁(行记录锁+Gap间隙锁)

1. 对主键或唯一索引，如果当前读时，where条件全部精确命中(=或者in)，这种场景本身就不会出现幻读，所以只会加行记录锁。

2. 没有索引的列，当前读操作时，会加全表gap锁，生产环境要注意。

3. 非唯一索引列，如果where条件部分命中(>、<、like等)或者全未命中，则会加附近Gap间隙锁。例如，某表数据如下，非唯一索引2,6,9,9,11,15。如下语句要操作非唯一索引列9的数据，gap锁将会锁定的列是(6,11]，该区间内无法插入数据。
   

一致性非锁定读和锁定读

锁定读

在一个事务中，标准的select 语句是不会加锁的，但是有两种情况例外

SELECT … LOCK IN SHARE MODE加共享锁

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

**SELECT … FOR UPDATE **加排他锁

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

一致性非锁定读

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

begin / start transaction

RC隔离和RR隔离中一致性读区别

一致性读，又称为快照读。使用的是MVCC机制读取undo中的已经提交的数据。所以它的读取是非阻塞的。

相关文档：http://dev.mysql.com/doc/refman/5.6/en/innodb-consistent-read.html

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction.

一致的读取意味着InnoDB使用多版本控制在某个时间点向查询显示数据库的快照。查询将查看在此时间点之前提交的事务所做的更改，而之后提交的事务或未提交的事务则不做任何更改。这个规则的例外是，查询可以看到相同事务中前面语句所做的更改。

一致性读肯定是读取在某个时间点已经提交了的数据，有个特例：本事务中修改的数据，即使未提交的数据也可以在本事务的后面部分读取到。

• RR 是第一次select时间点判断提交的时间点建立的snapshot一致性读快照

• RC 是每次select都会以当前的时间点判断提交的时间点

RC隔离和RR隔离中一致性读的区别

根据隔离级别不同,一致性读也是不一样的,不同点在于判断是否提交的”某个人时间点”

RR隔离

If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction.

如果事务隔离级别是可重复读取(默认级别)，则同一事务中的所有一致读取将读取该事务中第一次读取所建立的快照。

文档中说的是：the first such read in that transaction。实际上实验的结果表明，这里的 the first such read指的是：对同一个表或者不同表进行的*第一次select语句建立了该事务中一致性读的snapshot*. 其它update, delete, insert 语句和一致性读snapshot的建立没有关系。在snapshot建立之后提交的数据，一致性读就读不到，之前提交的数据就可以读到。

事务的起始点其实是以执行的第一条语句为起始点的，而不是以begin作为事务的起始点的。

总结

• **begin /**start transaction ,不是在事务开始的时候建立一致性读的snapshot

• RR级别下的一致性读,是以第一条select建立的snapshot,即使是不同的表

• tart transaction with consistent snapshot 会开启事务的时候马上开启一致性视图

试验1

上面的实验说明:RR级别下的一致性读,不是以begin开始的时间点作为snapshot建立时间点,而是以*第一条select语句的时间点作为snapshot建立的时间点

试验2

该使用说明:

RR隔离级别下的一致性读,是以第一条select语句的执行点作为snapshot建立的时间点,即使是不同表的select语句,这是是因为session A在insert之前对t表执行了select,所以建立了snapshot,所以后面的select * from t1不能读取到insert的值

试验3

session A的第一条语句,发生在sessionB的insert语句提交之前,所以sessionA的第二条select语句不能读取到数据,因为RR中的一致性读是以事务中第一个select语句执行的时间点作为snapshot建立的时间点,而此时,sessionB的insert语句还没有执行,所以读不到数据

试验4

该试验说明:本事务中进行修改的数据,即使没有提交,在事务外的后面也可以读取到,update语句因为进行的是当前读” ,锁以他可以修改成功

RC隔离级别

With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.

使用读提交隔离级别，事务中的每个一致读将设置并读取自己的新快照。

事务中每一次读取都是以当前的时间点作为判断是否提交的实际点，也即是 reads its own fresh snapshot.

RC是语句级多版本(事务的多条只读语句，创建不同的ReadView，代价更高)，RR是事务级多版本(一个ReadView)；

事务开始时间点

一般我们会认为 begin/start transaction 是事务开始的时间点，也就是一旦我们执行了 start transaction，就认为事务已经开始了，其实这是错误的。上面的实验也说明了这一点。事务开始的真正的时间点(LSN)，是 start transaction 之后执行的第一条语句，不管是什么语句，不管成功与否。

但是如果你想要达到将 start transaction 作为事务开始的时间点，那么我们必须使用：

START TRANSACTION WITH consistent snapshot

它的含义是：执行 start transaction 同时建立本事务一致性读的 snapshot . 而不是等到执行第一条语句时，才开始事务，并且建立一致性读的 snapshot .

The WITH CONSISTENT SNAPSHOT modifier starts a consistent read for storage engines that are capable of it. This applies only to InnoDB. The effect is the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB table. See Section 14.2.2.2, “Consistent Nonlocking Reads”. The WITH CONSISTENT SNAPSHOT modifier does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that permits a consistent read. The only isolation level that permits a consistent read is REPEATABLE READ. For all other isolation levels, the WITH CONSISTENT SNAPSHOT clause is ignored. As of MySQL 5.7.2, a warning is generated when the WITH CONSISTENT SNAPSHOT clause is ignored.

具有一致快照修饰符的存储引擎可以启动一致的读取。这只适用于InnoDB。其效果与发出一个START事务，后面跟着一个SELECT from any InnoDB表是一样的。参见14.2.2.2节，“一致的非锁定读取”。使用一致快照修饰符不会更改当前事务隔离级别，因此仅当当前隔离级别允许一致读取时，它才提供一致的快照。唯一允许一致读的隔离级别是可重复读。对于所有其他隔离级别，将忽略WITH compatible SNAPSHOT子句。从MySQL 5.7.2开始，当WITH consistency快照子句被忽略时，将生成一个警告。

http://dev.mysql.com/doc/refman/5.6/en/commit.html
效果等价于： start transaction 之后，马上执行一条 select 语句(此时会建立一致性读的snapshot)。

If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries. (RR隔离级别中的一致性读的snapshot是第一条select语句执行时建立的，其实应该是第一条任何语句执行时建立的)事务的read-view 和一致性读的read-view是两个视图,不是一个概念

http://dev.mysql.com/doc/refman/5.6/en/innodb-consistent-read.html

我们在 mysqldump --single-transaction 中使用的就是该语句：

SET session TRANSACTION isolation LEVEL REPEATABLE read

START TRANSACTION /*!40100 WITH consistent snapshot */

1）START TRANSACTION 时，是第一条语句的执行时间点，就是事务开始的时间点，第一条select语句建立一致性读的snapshot；

2）START TRANSACTION WITH consistent snapshot 时，则是立即建立本事务的一致性读snapshot，当然也开始事务了

实验1：

实验2：

上面两个实验很好的说明了 start transaction 和 start tansaction with consistent snapshot的区别。第一个实验说明，start transaction执行之后，事务并没有开始，所以insert发生在session A的事务开始之前，所以可以读到session B插入的值。第二个实验说明，start transaction with consistent snapshot已经开始了事务，所以insert语句发生在事务开始之后，所以读不到insert的数据。

start transaction with consistent snapshot一致性读

mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`k` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

MYSQL 两个视图概念

• 一个view,他是一个查询语句定义的虚拟表,在调用的时候执行查询语句并生成结果.创建视图的语法是create view…,而他的查询方法与表一样

• 另一个InnoDB在实现MVCC时用到的一致性视图,即consistent read view,用于支持RC(read committed,读提交) 和RR(repeatable read,可重复读)隔离级别的实现

它没有物理结构,作用是执行期间用来定义”我能看见什么”.