吊打面试官系列之---吃透MySQL mvcc机制--锁模块

目录

事务四大特性（ACID）

事务并发访问引起的问题以及如何避免

1.更新丢失--mysql所有事务隔离级别都可以在数据库层面上均可避免

2.脏读——read-committed事务隔离级别以上可避免

3.不可重复读——repeatable-read事务隔离级别以上可避免

4.幻读——serializable事务级别可以避免

事务并发访问引起的问题以及如何避免-总结

InnoDB在可重复读（repeatable-read）隔离级别下如何避免幻读

RC、RR级别下的InnoDB的非阻塞读如何实现

InnoDB可重复读隔离级别下如何避免幻读

对主键索引或者唯一索引会用Gap锁吗?

Gap锁会用在非唯一索引或者不走索引的当前读中

1.当前读走到非唯一索引的情况

2.不走索引的情况

自我总结：（精简）

---

事务四大特性（ACID）

1.原子性（ Atomicity ）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

举例说明：假如将我要超市买东西作为一个事务，那么就必须要完成挑选，结账，付款的操作。这些步骤，缺一不可，不可再分。

2.一致性（ Consistency ）：事务完成时，必须使所有的数据都保持一致状态。

举例说明：假如事务A指的是A给B转账1k以元那么一致性就体现在，转账结束之后，A的账户必须减少1k，B的账户必须增加1k。

3.隔离性（ Isolation ）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环

境下运行。

4.持久性（Durability ）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

接下来我们来讨论

事务并发访问引起的问题以及如何避免

1.更新丢失--mysql所有事务隔离级别都可以在数据库层面上均可避免

大致模拟更新丢失的问题过程：

一个事务的更新覆盖另一个事务的更新，

由于现在主流数据库的自动加锁避免了这种问题发发生。

---

---

2.脏读——read-committed事务隔离级别以上可避免

一个事务读到另一个事务未更新的数据

模拟：（原本账户上有1k块钱）

先查看数据库的隔离级别，调低：

section1：

select @@tx_isolation;

set session transction isolation level read uncommitted；//将隔离级别设置成最低

start transaction；//开始事务

update account_innodb set balance = 1000 - 100 where id = 1;

select * from account_innodb;//此时查询余额变为900

注：此时并未提交事务

section2：

select @@tx_isolation;

set session transction isolation level read uncommitted；//将隔离级别设置成最低

start transaction；//开始事务

select * from account_innodb where id = 1;//此时查询余额变为900

---

section1：发生网络问题取款失败，发生回滚

select @@tx_isolation;

set session transction isolation level read uncommitted；//将隔离级别设置成最低

start transaction；//开始事务

update account_innodb set balance = 1000 - 100 where id = 1;

select * from account_innodb where id = 1;//此时查询余额变为900

rollback;//回滚数据变为1k

 section2：并不知道section1

select @@tx_isolation;

set session transction isolation level read uncommitted；//将隔离级别设置成最低

start transaction；//开始事务

select * from account_innodb where id =1;//此时查询余额变为900

update account_innodb set balance = 900 +200 where id = 1;

commit;//数据变为1100

 此时section2发现问题，而数据库将会如何避免呢：

---

将数据库隔开级别调回read-commited：

section1：

start transaction；//开始事务

update account_innodb set balance = 1000 - 100 where id = 1;

select * from account_innodb;//此时查询余额变为900

section2：

start transaction；//开始事务

select * from account_innodb where id =1;//此时查询余额为1000，未读取，避免脏读

update account_innodb set balance = 1000 +200 where id = 1;//1200

section1：遇到网络问题回滚

start transaction；//开始事务

update account_innodb set balance = 1000 - 100 where id = 1;

select * from account_innodb where id = 1;//此时查询余额变为900

rollback;//回滚数据变为1k

section2：

start transaction；//开始事务

select * from account_innodb where id =1;//此时查询余额为1000，未读取，避免脏读

update account_innodb set balance = 1000 +200 where id = 1;//1200

commit;//1200

这时候section2读取的数据是正确的。

因此read-committed，顾名思义，只能读取其他事务已经提交的数据

---

---

3.不可重复读——repeatable-read事务隔离级别以上可避免

重复读:事务A（section1）多次重复读取同一个数据，在读取过程中，事务B在事务A读取过程中，对数据做了更新和提交，事务A读取数据不同。

section2：（最开始account_innodb=1000）

select @@tx_isolation;//此时是read-commited隔离级别

rollback;

start transaction；//开始事务


update account_innodb set balance = balance +300 ;

select * from account_innodb where id = 1;//此时查询余额为1300

注：此时还未提交事务

section1：（注此时section1事务无法读到section2事务未更新的数据）

select @@tx_isolation;//此时是read-commited隔离级别

rollback;

start transaction；//开始事务

select * from account_innodb where id = 1;//此时查询余额为1000

接着 section2：

select @@tx_isolation;//此时是read-commited隔离级别

rollback;

start transaction；//开始事务


update account_innodb set balance = balance +300 ;

select * from account_innodb where id = 1;//此时查询余额为1300

commit;

section1:

select @@tx_isolation;//此时是read-commited隔离级别

rollback;

start transaction；//开始事务

select * from account_innodb where id = 1;//此时查询余额为1000

select * from account_innodb where id = 1;//此时查询余额为1300

此时section1并不知道section2提交事务，对数据做出了修改，读取了不同的数据，那么数据库将

会如何避免该问题呢：

---

section1/section2：(将事务隔离级别设置成repeatable read)

select @@tx_isolation;

set session transction isolation level repeatable read;//将隔离级别设置成repeatable read

section2：（最开始account_innodb=1000）

select @@tx_isolation;

set session transction isolation level repeatable read;//将隔离级别设置成repeatable read

select * from account_innodb where id = 1;//此时余额为1000

update account_innodb set balance = balance +400 ;

select * from account_innodb where id = 1;//此时查询余额为1400

注：此时未提交事务

section1：（无法读取section2未提交的数据）

select @@tx_isolation;

set session transction isolation level repeatable read;//将隔离级别设置成repeatable read

select * from account_innodb where id = 1;//此时余额为1000

接着section2提交事务，commit，我们会发现：

section1：

select @@tx_isolation;

set session transction isolation level repeatable read;//将隔离级别设置成repeatable read

select * from account_innodb where id = 1;//此时余额为1000

select * from account_innodb where id = 1;//此时读取余额任然为1000

update account_innodb set balance = balance - 100 where id = 1;//但此时是用1400-100

commite;

我们发现在更新数据时，账户余额是使用的section2未更新的数据1400来进行操作的。于是就是

1400-100=1300；

因此避免了更新数据时的紊乱，这就是repeatable-read

---

---

4.幻读——serializable事务级别可以避免

幻读：事务A读取与搜索条件相匹配的若干行，事务B以插入或删除行等方式来修改事务A的结果积，让事务A看起来像幻觉一样。

section1/section2：同时开始事务（此时事务隔离级别为repeatable read）

section1：

select @@tx_isolation;

start transction;

select * from account_innodb lock in share mode;//当前读，读取事务最新更新的数据，即添加了一个贡献读锁。

secton2：

select @@tx_isolation;

start transction;

insert into account_innodb values(4,'newman',500);

section1:

select @@tx_isolation;

start transction;

select * from account_innodb lock in share mode;

update accunt_innodb set balance = 1000;

section1在section2更新之后，紧接着将数据余额全部更新为1000，幻读就是本来应该更新三条记录，但是莫名更新了四条记录。（理论，并未认证）

然而当我们对第四条记录进行插入操作：

  发现插入操作被锁，需要section1提交才能插入。此时对于section1来说插入数据并未出现，可mysql的innodb居然在repeatable-read的情况下避免了幻读的情况，理论上是不可避免的，那么MySQl是怎么做到的呢？

首先我们先来模拟幻读的问题情况：

先将之前的数据rollback；将隔离级别调成read commit

section1:(以当前读来打开)

select @@tx_isolation;

set session transaction isolation level read committed;

start transction;

select * from account_innodb lock in share mode;

 section2:

select @@tx_isolation;

set session transaction isolation level read committed;

start transction;

insert into account_innodb values(4,'newman',500);

 与之前不同的是，此时插入是成功的，接着commit，提交事务。

section1：

select @@tx_isolation;

set session transaction isolation level read committed;

start transction;

select * from account_innodb lock in share mode;

update accunt_innodb set balance = 1000;

此时：

 这时候幻读情况出现了，section1不知道section2的操作，本来只是对三条数据做更新，结果更新

了四条数据。

如何彻底避免幻读呢？

---

把事务隔离级别调成最高的serializable。先将section1，2事务提交，重新进行一遍之前的操作

section1:

select @@tx_isolation;

set session transaction isolation level serializable;

start transaction;

set * from account_innodb;

updata account_innodb set balance = 1000;

注：只有在隔离级别为serializable下，所有的mysql执行都会上锁，即便我们不写lock in share mode。

此时显示的是四条数据：（这是上一次事务提交之后的）

 接着section2：

 我们运行发现，在隔离级别为serializable下，插入被锁住了，需要等待section1提交事务，或者rollack。

 就解决了幻读的情况。

事务并发访问引起的问题以及如何避免-总结

注   ：1. 不可重复读侧重对数据的修改，幻读侧重新增和删除。

        2.出于性能考虑，事务隔离级别越高，数据约安全，串行化越严重，但是性能越低，所以我们要选择适合的。

---

InnoDB在可重复读（repeatable-read）隔离级别下如何避免幻读

表象：快照读（非阻塞读）--伪MVCC

内在:next-key锁（行锁+gap锁）

当前读：(加了锁的增删改查)

select...lock inshare mode,select...for update//加共享锁

update,delect,insert//加排它锁

读取记录的最新版本，读取之后需要保证其他并发事务不能修改当前记录，对读取的记录加锁。

 快照读：不加锁的非阻塞读，select（以事务隔离级别不为serializable的条件下成立）

基于提升并发性能的考虑，快照读的实现是基于多版本并发性控制即mvcc，可以认为mvcc是行级

锁的一个变种，但在很多情况下避免了加锁操作，因此开销更低。所以它可

能读取的并不是数据的最新更新。

接下来看几个例子：（事务级别在rc和rr以下）

section1/section2：事务隔离级别为rc

section3/4：事务隔离级别为rr

section1、section2：同时开启事务，

section1：

select @@tx_isolation;

start transaction;

select * from account_innodb where id = 2;

 section2：

select @@tx_isolation;

start transaction;

select * from account_innodb where id = 2;

update account_innodb set balance = 600 where id = 2;

commit;

在更新完数据时候之后，我们进行提交。接着我们在section1中分别为用快照读和当前读来查看数据：

 我们发现在rc隔离级别下，当前读和快照读的更新版本是一样的。

 接着在rr隔离级别下演示：

section3:

select @@tx_isolation;

start transaction;

select * from account_innodb where id = 2;

此时账户余额为上次提交的600

section4：

select @@tx_isolation;

start transaction;

update account_innodb set balance = 300 where id = 2;

commit;

更新数据之后提交，接下来我们在section1中分别用当前读和快照读来查看数据：

当前读：（数据最新版本）

 快照读：（返回的数据与没修改之前一致）

 那么是否存在快照读返回的数据是最新的?

我们先对事务进行提交，接着

我们直接对section4：

select @@tx_isolation;

start transaction;

update account_innodb set balance = 30 where id = 2;

commit;

接着我们在section3中用当前读来查看：

 接下来我们用快照读来查看：（此时读取的是最新版本）

 那么这一次相对与上一次的区别是什么呢：

1.在上一次中我们先在section1中先使用了一次快照读来读取数据，接着section2更更新，最后section再使用快照读，读取的是旧版本的数据。

2.在这一次中，我们直接在section2中更新数据，在section1中用快照读查看数据，这时候得到的是数据的最新版

重点：创建快照的时机，决定读取数据的版本

---

RC、RR级别下的InnoDB的非阻塞读如何实现

 1.DB_TRX_ID：顾名思义，与事务相关，用来标识最近一次对本行记录的修改。

2.DB_ROLL_PTR：回滚指针，指写入回滚段，rollback segment的undo日志记录，如果一行被记录被更新，则undo log record，包含重该行记录被更新之前内容所必须包含的信息。

3.DB_ROW_ID即行号，包含一个随着新行插入而单调递增的行ID，当由innodb自动自动产生聚集

索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。

如果一个innodb的表即没有主键，也没有唯一键，innodb会为我们创建一个自身隐藏主键字段，即这里的DB_ROW_ID。

光有以上三个字段并不能实现我们的快照读，还需要依靠undo日志，当我们对数据进行变更操作时，就会产生undo记录。里面存储的是老版本的数据。为了能够读取老版本的数据，需要顺着undo链找到满足其可见性的记录。

undolog分为两种：

1.insert undo log 表示事务对insert 新记录的undo  log 只在事务回滚时需要，提交后删除

2.update undolog 在事务回滚时需要，在快照时也需要，不能随便删除，只有当数据库所使用的快照中，不涉及该日志记录对应的回滚日志，才会被purge线程删除。

日志的工作方式:(事务对行记录的更新过程) 

1.首先用排他锁，锁住该行，接着把该行修改前的值拷贝一份到undo log里面；

2.修改当前行的值，填写事务ID,DB_TRX_ID，使用回滚指针指向undo log中修改前的行

假如数据库中还有其他事务在用快照读读取该日志记录，那么对应的undo log还没被清除，此时某个事务又对同一行数据进行修改，这里将fields3改为了45，这时又多了一条undo日志

 现在有了行隐藏列，有了undo日志，只差read view.

read view主要用来做可见性判断，当我们执行快照读select的时候，会针对我们查询的数据创建一个read view来决定当前事务能看见的是哪个版本，有可能是当前最新版本的记录，也有可能只允许看undo kog中某一版本的数据，read view遵循一个可见性算法，主要是讲要修改数据的DB_TRX_ID取出与系统其他活跃事务ID做对比，如果>或者=，则通过DB_ROLL_PTR指针去取出undo log，就是取出上一层的DB_TRX_ID，直到undo log上一层的DB_TRX_ID<系统其他活跃事务ID,就保证了我们获取到的数据版本是当前可见的最稳定的版本。

我们来看一下mysql保存活跃事务的源码：

 （补充：每当我们的事务start transaction时，我们的事务ID就会去递增，越新开启的事务，它的ID越大。）

就用这两个值去和DB_TRX_ID做对比，就能觉得是不是让他回溯到undo log去取出适应该版本的数据版本来。

总结：正是因为生成时机的不同，造成rc，rr两种隔离级别的不同可加性。

1.在repeatable read级别下，之后的第一条快照读及read view 将当前系统中活跃的其他事务记录起来，此后在调用快照读时，还是用的同一个read view 。而在read committed级别下，事务中，每条select语句也就是每次调用快照读时，都会创建一个新的快照，这就是我们为什么在rc下能用快照读看到别的事务对已提交事务对表记录的增删改。

2.而在rr级别下，如果首次使用快照读，是在别的事务对数据做出增删改，并且提交之前，那么即便后面别的事务对表进行增删改，并提交，还是读不到数据变更。对与rr来讲，首次select的时机非常重要。

正是因为上面三个因子，才使得innodb在rr或rc级别支持非阻塞读，而读取数据时的非阻塞读机制实现了仿造版的MVCC。

MVCC:multi version concurrency control简称，代表多版本并发控制，读不加锁，读写不冲突，在都读多写少的应用中读写不冲突是非常重要的，极大增加了系统的并发性能。

那为什么这里只实现了伪MVCC？因为并没有实现核心的多版本共存。undo log只是串行化的结果，记录了多个事务的过程，不属于多版本过程。

---

---

InnoDB可重复读隔离级别下如何避免幻读

表象：快照读（非阻塞读）--伪MVCC

内在:next-key锁（行锁+gap锁）

行锁：对当个行记录上锁

Gap锁：gap指索引记录中插入的空隙，而间隙锁，即锁定一个范围，但不包括记录本身，目的，防止同一个事务的两次当前读出现幻读的情况。

gap锁在rc以及更低的事务隔离级别下是没有的，这就是在rc以及read uncomitted下无法避免幻读的原因，而在rr和serializable级别下，默认支持gap。

我们主要讨论在rr级别下，gap出现的场景，

对主键索引或者唯一索引会用Gap锁吗?

全部命中：精确查询的时候，所有记录都有，如果只有部分，则为部分命中。

表里有两个列，一个是name是该表的主线，另外一个ID是该表的唯一键，如果我们执行。

delete from table  where id=9该如何进行加锁呢？

此时，由于ID是unique索引，因此delete语句会选择走ID这一列的索引进行where条件的过滤，再找到ID=9的记录之后呢，首先会将这个unique索引上的ID为9的索引，加上行锁recollect，同时会根据读到的这个name类回主键索引及我们的密集索引，将这个密集索引上的这个name=d对应的主键索引项也加上一个recollect，也就是排它锁。

为什么密集索引上的记录也要加排它锁？

如果并发的一个SQL是通过主键索引来更新的，update tb 1 set id=90，他将这个ID改成90

where id = d；此时如果delete语句，没有将主键索引上的这个记录加锁，那么并发的这个update

就会感知不到delete语句的存在。这里，我们delete也是操作的是同一个记录，update也是这一

name=d的操作记录，这个记录这样就违背了同一记录上的更新或者删除，需要串行执行的约束。

所以呢，如果where条件全部命中，则不会用gap 锁。

例子：

 此时两个事务的隔开级别均为rr：

 section1：delete走的是唯一索引（前面添加explain可以查看）

 

 section 2：

 执行成功，说明当前读如果走的是唯一索引，并且命中数据，是不会添加gap锁。

那么说明时候会添加gap锁：

 section1：（先rollback）删除一个不存在的指

 section2：（先rollback）插入一个不存在的指

 我们会发现：虽然删除的是7，但是7周围的环境也被锁住了 ，即加了gap锁。

以上是全部命中的情况，接下来来看部分事务命中的情况：（先rollback） 

 section2：

 

 也就是说对5-9的间隙插入了gap锁，我们继续演示：

 但是10并未上锁：

 因此：如果是部分命中的话，那么也会是部分加上gap锁。我们再来对比全部命中的情况：

先将两个事务回滚

section1：执行当前读，精确命中所有数据，（将7改为6）

 section2：接着我们再去插入不存在的数据，执行是成功的

也就是说，全部命中的情况下是不会上gap锁的。因此，经过实验，我们就得出上述的两个结论如

果where条件全部并重，则不会用gap锁，只会加记录锁。如果where条件部分命中或者全都不命

中，则会加gap锁前面。

Gap锁会用在非唯一索引或者不走索引的当前读中

1.当前读走到非唯一索引的情况

大家可以试想一下，如果我们1事务A第一次用当前读选出ID为9的数据。

我们用这个delete from tb1 whereid=9，这个当前读来选出ID为九的数据，如果只锁住选出来的两

行，那么此后另外一个事物b。插入了ID，同样为9的数据并提交事务，再次用当前读选出ID为九的

数据时，会取出三条。这样就会发生幻读，因此这个时候我们需要引入gap锁。

gap所具体能在什么地方添加呢？

从图中我们可以了解到gap的地方，跟我们走的非唯一索引的值分布有很大的关系，都是一个左

开，右闭的区间。

我们查看官方文档：

除了最后一项到正无穷大之外呢，其他都是左开，右闭的区间。在这些区间内，一旦上了gap锁，

该区间就没办法插入数据了，因此gap锁是用来防止插入的。

对于普通非唯一索引来讲，并不是所有的gap都会去上锁，只会对要修改的地方的周边上gap锁。

2.不走索引的情况

当前读不走索引的时候，它会对所有的gap都上锁，这也就类似锁表了，这样也同样能达到防止换读的效果。

相比走非唯一索引的情况呢？不走索引的情况会对所有gap都上锁，大家可以试试别的gap，

发现同样都被锁住。不过相比表锁，这样上锁的代价更大。这种情况通常是需要避免的，

因为会降低数据库的效率。

总结：这里咱们总结一下innodb-rr级别，主要通过引入next key锁来避免幻读问题，而next key由record lock以及gap lock组成。gap lock，会用在非唯一索引或者不走索引的当前读，以及仅命中检索条件的部分结果积。并且呢，用到这个主键索引以及唯一索引的当前途中。

---

---

自我总结：（精简）

1.innodb使用行级锁，而不使用表锁的原因，也是为了提高操作效率，如果使用表锁，那么整个表里的数据都会被锁定，这时候就无法操作。

接着，关于当前读和快照读 我们用银行取钱来举例：

2.当前读：有一个ATM挤，当你取ATM机取钱时，其他人必须等待你取完钱，才能进行操作。

那么官方语言来说，我们的数据一直都是最新版本的数据，如果你的事务操作为执行完，那么其他

事务是无法提交事务，也就意味着操作失败，会进行回滚。

3.快照读：相当于这个银行有很多的柜台，那么是可以多个人同时进行操作的，但是我们只有在提交

数据的最后一刻，会去查看你的前面是否还有人在正在进行操作，如果有，那么你会操作失败，会

回滚。

而innodb使用这个mvcc机制，即快照读就是因为它提高了我们操作的效率。

4.吊打面试官的重点：但是这里我们为什么会说他是伪MVCC机制，是因为，我们这里的快照读，

相当于undo_log串行式链式结构由回滚指针项链，而为多版本数据结构，一个指一个，一个指一

个，必须要保证你前面的人提交事务，那么你才能行操作。而真正的MVCC机制，它是相当于我们

可以多个人同时取钱，但是我们看的是操作速度，谁操作更快，谁就能更新，而更新之后，也就是

DB_ROW_ID会进行+1，那么比你速度慢的人就会发现数据已经被修改。相比与伪MVCC的链式结

构，真正的MVCC是将相同的数据拷贝多份，大家都可以看到一个相同的结果，也就是多个人去看

各个副本（每个副本是一样的），当然MVCC机制不止应用于数据库，只是这里innodb为了提高效

率而应用的。

5、MVCC解决的问题

MVCC是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单项增长的时间戳，为每个修

改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。

因此，MVCC可以为数据库解决以下问题∶

1）、在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提

高了数据库并发读写的性能。

2）、解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题。

mvcc主要是解决读写冲突的，如果是写写的操作就要用锁来解决了，因为mvcc无法解决写的并发安全，所以一般都是 rr事务隔离+mvcc+锁 来保证所有情况安全。