MySQL内核学习

迷路剑客

已于 2024-04-17 16:52:31 修改

阅读量2.3k

点赞数 1

分类专栏： mysql 文章标签： mysql

于 2018-10-29 13:28:01 首次发布

原文链接：https://mp.weixin.qq.com/s/tmkRAmc1M_Y23ynduBeP3Q

版权

mysql 专栏收录该内容

5 篇文章 1 订阅

订阅专栏

MySQL内核学习

转载声明：

本文系转载自

关于MySQL内核，一定要知道的！
出处：架构师之路
作者： 58沈剑
mysql 事务的实现原理
出处： csdn
作者： Running-Waiting
MySQL事务
出处：简书
作者：王王王王王景

转载仅为方便学习查看，一切权利属于原作者，本人只是做了整理和排版，如果带来不便请联系我删除。

摘要

本文主要讲了innodb对比myisam、锁、并发等内容。

1 InnoDB 对比 MyISAM

原文：InnoDB，5项最佳实践，知其所以然？

1.1 count(*)

1.1.1 MyISAM和InnoDB的区别

知识点：MyISAM会直接存储总行数，InnoDB则不会，需要按行扫描。
潜台词是：对于select count(*) from t不加where条件; 如果数据量大，MyISAM会瞬间返回，而InnoDB则会一行行扫描。
实践：数据量大的表，InnoDB不要轻易select count(*)，性能消耗极大。
常见坑：只有查询全表的总行数，MyISAM才会直接返回结果，当加了where条件后，两种存储引擎的处理方式类似。

1.1.2 示例

例如有用户信息表如下：

t_user(uid, uname, age, sex);
uid PK
age index

现在我们要查询未成年少女个数，sql如下：

select count(*) where age<18 and sex='F';

两种存储引擎的处理方式类似，都需要进行索引扫描。

1.1.3 小结

不管哪种存储引擎，都要建立好索引。

1.2 全文索引

知识点：MyISAM支持全文索引，InnoDB5.6之前不支持全文索引。
实践：不管哪种存储引擎，在数据量大并发量大的情况下，都不应该使用数据库自带的全文索引，会导致小量请求占用大量数据库资源，而要使用索引外置的架构设计方法。
启示：大数据量+高并发量的业务场景，全文索引，MyISAM也不是最优之选。

1.3 事务

知识点：MyISAM不支持事务，InnoDB支持事务。
实践：事务是选择InnoDB非常诱人的原因之一，它提供了commit，rollback，崩溃修复等能力。在系统异常崩溃时，MyISAM有一定几率造成文件损坏，这是非常烦的。但是，事务也非常耗性能，会影响吞吐量，建议只对一致性要求较高的业务使用复杂事务。
画外音：Can’t open file ‘XXX.MYI’. 碰到过么？
小技巧：MyISAM可以通过lock table表锁，来实现类似于事务的东西，但对数据库性能影响较大，强烈不推荐使用。

1.4 外键

知识点：MyISAM不支持外键，InnoDB支持外键。
实践：不管哪种存储引擎，在数据量大并发量大的情况下，都不应该使用外键，而建议由应用程序保证完整性。

1.5 行锁与表锁

1.5.1 MyISAM和InnoDB的区别

知识点：MyISAM只支持表锁，InnoDB可以支持行锁。
分析：
- MyISAM：执行读写SQL语句时，会对表加锁，所以数据量大，并发量高时，性能会急剧下降。
- InnoDB：细粒度行锁，在数据量大，并发量高时，性能比较优异。
实践：网上常常说，select+insert的业务用MyISAM，因为MyISAM在文件尾部顺序增加记录速度极快。楼主的建议是，绝大部分业务是混合读写，只要数据量和并发量较大，一律使用InnoDB。
常见坑：
InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。
画外音：Oracle的行锁实现机制不同。

1.5.2 示例

例如有用户信息表如下，且无其他索引：

t_user(uid, uname, age, sex) innodb;
uid PK

那么有如下结论：

update t_user set age=10 where uid=1;
命中索引，行锁。

update t_user set age=10 where uid != 1;
未命中索引，表锁。

update t_user set age=10 where name='shenjian';
无索引，表锁。

启示：InnoDB务必建好索引，否则锁粒度较大，会影响并发。

1.6 索引存储方式不同

innoDB是聚簇索引
Myisam是非聚簇索引

1.7 总结

在大数据量，高并发量的互联网业务场景下，对于MyISAM和InnoDB:

有where条件，count(*)两个存储引擎性能差不多；无时，MyISAM直接返回，InnoDB扫描全表
不要使用全文索引，应当使用索引外置的设计方案
事务(InnoDB特性)影响性能，强一致性要求才使用事务
不用外键(InnoDB特性)，由应用程序来保证完整性
不命中索引，InnoDB也不能用行锁而是和MyISAM同样使用表锁
在大数据量，高并发量的互联网业务场景下，请使用InnoDB，他最吸引人的:
- 行锁，对提高并发帮助很大
- 事务，对数据一致性帮助很大

2 InnoDB并发如此高的原因

原文：InnoDB并发如此高，原因竟然在这？

2.1 并发控制

原因
并发的任务对同一个临界资源进行操作，如果不采取措施，可能导致不一致，故必须进行并发控制（Concurrency Control）。
实现方式
通过并发控制保证数据一致性的常见手段有：
- 锁（Locking）
- 数据多版本（Multi Versioning）

2.2 锁

2.2.1 如何使用普通锁保证一致性？

普通锁，被使用最多：
1. 操作数据前，锁住，实施互斥，不允许其他的并发任务操作；
2. 操作完成后，释放锁，让其他任务执行；

2.2.2 普通锁存在什么问题？

太过粗暴，连“读任务”也无法并行，即任务执行过程本质上是串行的。于是出现了以下锁：

读-共享锁（Share Locks，记为S锁），读取数据时加S锁。共享锁之间不互斥，简记为：读读可以并行
写-排他锁（eXclusive Locks，记为X锁），修改数据时加X锁。排他锁与任何锁互斥，简记为：写读，写写不可以并行

可以看到，一旦写数据的任务没有完成，数据是不能被其他任务读取的，这对并发度有较大的影响。

画外音：对应到数据库，可以理解为，写事务没有提交，读相关数据的select也会被阻塞。

有没有可能，进一步提高并发呢？

也就是期望即使写任务没有完成，其他读任务也可能并发，这就引出了数据多版本MVCC。

2.3 数据多版本

2.3.1 概念

参考通俗易懂数据库MVCC讲解，后悔没早点学
在这里插入图片描述

数据多版本是一种能够进一步提高并发的方法，它的核心原理是：

写任务发生时，将数据克隆一份，以版本号区分
写任务操作新克隆的数据，直至提交
并发读任务可以继续读取旧版本的数据，不至于阻塞

如上图：
最开始数据的版本是V0；
T1时刻发起了一个写任务，这是把数据clone了一份，进行修改，版本变为V1，但任务还未完成；
T2时刻并发了一个读任务，依然可以读V0版本的数据；
T3时刻又并发了一个读任务，依然不会阻塞；
直到提交后才会去读V1版本新数据

在这里插入图片描述

2.3.2 小结

可以看到，数据多版本，通过“读取旧版本数据”能够极大提高任务的并发度。

提高并发的演进思路，就在如此：

普通锁，本质是串行执行，所有操作不可并发
读写锁，可以实现读读并发，不可并发读写
数据多版本，可以实现读写并发

画外音：这个思路，比整篇文章的其他技术细节更重要，希望大家牢记。

2.4 redo, undo, 回滚段

对应到InnoDB上，具体是怎么玩的呢？
在进一步介绍InnoDB如何使用“读取旧版本数据”极大提高任务的并发度之前，有必要先介绍下redo日志，undo日志，回滚段（rollback segment）。

2.4.1 redo log

数据库事务提交后，必须将更新后的数据刷到磁盘上，以保证ACID特性。磁盘随机写性能较低，如果每次都刷盘，会极大影响数据库的吞吐量。

优化方式是，将修改行为先写到redo日志里（此时变成了顺序写），再定期将数据刷到磁盘上，极大提高了性能。

具体来说，redolog有两部分：

内存中的redo log buffer
磁盘上的redo log file

画外音：这里的架构设计方法是，随机写优化为顺序写，思路更重要。

假如某一时刻，数据库崩溃，还没来得及刷盘的数据，在数据库重启后，会replay redo日志里的内容，以保证已提交事务对数据产生的影响都刷到磁盘上。

例子如下：
在这里插入图片描述
通过事务执行以下语句，但尚未提交：

start transaction;

select balance from bank where name="zhangsan";

// 生成 redo log balance=600
update bank set balance = balance - 400; 

// 生成 redo log amount=400
update finance set amount = amount + 400;

事务和redo log流程如下
在这里插入图片描述

Buffer Pool
MySQL的表数据是存放在磁盘上的，因此想要存取的时候都要经历磁盘IO,然而即使是使用SSD磁盘IO也是非常消耗性能的。

为此，为了提升性能InnoDB提供了缓冲池(Buffer Pool)，Buffer Pool中包含了Data Page磁盘数据页的映射，可以当做缓存来使用：
- 读数据：会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘Data Page读取在放入缓冲池；
- 写数据：会首先写入Buffer Pool，Buffer Pool中的数据使用后台线程定期去做同步到磁盘中；
如果还没来得及同步（上图红色操作）的时候宕机或断电了怎么办？这样会导致丢失部分已提交事务的修改信息？
所以引入了redo log来记录已成功提交事务的修改信息，并且会在提交事务时把redo log持久化到磁盘。

这样，如果有部分数据没有从Buffer Pool同步到data page就发生了故障宕机，则系统重启之后可读取redo log来恢复最新数据放到data page，保证已提交事务持久性。

一句话，redo日志用于保障，已提交事务的持久性。

2.4.2 事务提交前-写入undo log

数据库事务未提交时，会将事务修改数据的镜像（即修改前的旧版本）存放到undo日志里，当事务回滚时，或者数据库奔溃时，可以利用undo日志，即旧版本数据，撤销未提交事务对数据库产生的影响。

事务提交后，可删除undo日志。

画外音：更细节的：

对于insert操作，undo日志记录新数据的PK(ROW_ID)，回滚时直接删除；
对于delete/update操作，undo日志记录旧数据row，回滚时直接恢复；
他们分别存放在不同的buffer里。

一句话，undo日志用于保障，未提交事务不会对数据库的ACID特性产生影响。

2.4.3 回滚段

存储undo日志的地方，是回滚段。

undo日志和回滚段和InnoDB的MVCC密切相关，这里举个例子展开说明一下：

2.4.3.1 例1

在这里插入图片描述
事务提交前会生成undo log，记录修改前的信息。

如果事务回滚，则可从undo log中恢复旧数据。

2.4.3.2 例2

表结构：
```
t(id PK, name);
```
数据：
```
1, shenjian
2, zhangsan
3, lisi
```
事务提交前
此时没有事务未提交，故回滚段是空的：

事务启动但未提交
接着启动了一个事务并且事务处于未提交的状态：

start trx;
delete (1, shenjian);
update set(3, lisi) to (3, xxx);
insert (4, wangwu);

这时回滚段状态如下：

从上图中可以看到：
1. 被删除前的(1, shenjian)作为旧版本数据，进入了回滚段；
2. 被修改前的(3, lisi)作为旧版本数据，进入了回滚段；
3. 被插入的数据，PK(4)进入了回滚段；

画外音：如果事务已提交，那么回滚段里的undo日志可以删除。

事务回滚
接下来，假如事务rollback，此时可以通过回滚段里的undo日志回滚：

从上图可以看到：
1. 被删除的将回滚段中旧数据放回
2. 被修改的恢复为回滚段中的旧数据
3. 新插入的数据，直接按回滚段中的该新数据的主键删除
事务回滚成功后如图：

画外音：undo用来保证事务原子性，要么事务提交都提交，要么回滚时都撤销

2.5 InnoDB是基于多版本并发控制的存储引擎

2.5.1 InnoDB与MVCC

概述
InnoDB是高并发互联网场景最为推荐的存储引擎的根本原因就是其多版本并发控制（Multi Version Concurrency Control, MVCC）。MVCC就是通过“读取旧版本数据”来降低并发事务的锁冲突，提高任务的并发度。主要实现思想是通过数据多版本来做到读写分离，从而实现不加锁读，进而做到读写并行。

行锁，并发，事务回滚等多种特性都和MVCC相关。
核心问题：
- 旧版本数据存储在哪里？
  旧版本数据存储在回滚段（undo）里
- 存储旧版本数据，对MySQL和InnoDB原有架构是否有巨大冲击？
  对MySQL和InnoDB原有架构体系冲击不大；
InnoDB的内核，会对所有row数据增加三个内部属性：
1. DB_TRX_ID，6字节，记录每一行最近一次修改它的事务ID；
2. DB_ROLL_PTR，7字节，记录指向回滚段undo日志的指针；
3. DB_ROW_ID，6字节，单调递增的行ID（当没指定主键时才有值，否则null）；

MVCC实现细节可以见MySQL事务

2.5.2 InnoDB为何能够做到这么高的并发-快照读

回滚段里的数据，其实是历史数据的快照（snapshot），这些数据是不会被修改的，select可以肆无忌惮的并发读取他们。

快照读（Snapshot Read），这种一致性（这里的一致性是指，事务读取到的数据，要么是事务开始前就已经由其他已提交事务产生的数据；要么是本事务自身插入或者修改的数据）不加锁的读（Consistent Nonlocking Read），就是InnoDB并发如此之高的核心原因之一。

MVCC处理高并发能力最强，但系统开销比最大（较表锁、行级锁），这是最求高并发付出的代价。

除非显示加锁，普通的select语句都是快照读，例如：

select * from t where id>2;

这里的显示加锁，非快照读是指：

select * from t where id>2 lock in share mode;
select * from t where id>2 for update;

问题来了，这些显示加锁的读，是什么读？会加什么锁？和事务的隔离级别又有什么关系？

请看3.3.5章节快照读在RR和RC下有何差异？

2.6 总结

常见并发控制保证数据一致性的方法有锁，数据多版本 MVCC；
普通锁串行，读写锁读读并行，数据多版本读写并行；
redo log保证已提交事务的ACID特性，设计思路是，通过顺序写替代随机写，提高并发；
undo log用来回滚未提交的事务，它存储在回滚段里，保证事务回滚时原子性；
InnoDB是基于MVCC的存储引擎，它利用了存储在回滚段里的undo日志，即数据的旧版本，提高并发；
InnoDB之所以并发高，快照读不加锁；
InnoDB所有普通select都是快照读；

画外音：本文的知识点均基于MySQL5.6。

3 事务

原文：4种事务的隔离级别，InnoDB如何巧妙实现？

3.1 概念

事务ACID特性实现如下(ACID详细可参考分布式-事务ACID章节)：
在这里插入图片描述

A 原子性是通过 redo log 和 undo log 来实现的
整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。
D 持久性性是通过 redo log 来实现的
I 隔离性是通过 (读写锁+MVCC)来实现的
隔离性是指，多个用户的并发事务访问同一个数据库时，一个用户的事务不应该被其他用户的事务干扰，多个并发事务之间要相互隔离。至于隔离到什么程度得看业务系统的场景了，MySQL 有多个不同隔离级别。
C 一致性是通过原子性，持久性，隔离性共同来实现的

3.2 事务原子性实现

3.2.1 概念

整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。

如果事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

例如：银行转账，从 A 账户转 100 元至 B 账户，分为两个步骤：

从 A 账户取 100 元。
存入 100 元至 B 账户。
这两步要么都完成，要么都不完成。因为如果只完成第一步，第二步失败，钱会莫名其妙少了 100 元。

3.2.2 事务提交成功时的原子性

事务提交时的原子性是通过 redo log来实现的。如果事务提交后发生宕机，则机器重启后通过redo log将这一事务重新一起提交即可。

3.2.3 事务回滚时的原子性

事务回滚时的原子性是通过 undo log 来实现的。如果事务未提交时，发生了某种异常行为，需要回滚该事务，则将undo log中的旧数据做逆操作恢复即可。

比如之前这个操作：
在这里插入图片描述
事务流程如下：

上图要点：

每条数据变更(insert/update/delete)操作都伴随一条undo log的生成,
回滚日志必须先于数据持久化到磁盘上，否则可能导致已提交数据丢失
所谓的回滚就是根据回滚日志做逆向操作，比如delete的逆向操作为insert，insert的逆向操作为delete等

事务回滚时操作如下：
在这里插入图片描述
undo log记录了数据被修改前的信息以及新增和被删除的数据信息，根据undo log生成回滚语句，并执行即可。

3.3 事务一致性实现

3.3.1 概念

指在事务开始之前和事务结束以后，数据库数据的一致性约束没有被破坏，数据库总是从一个一致性的状态转移到另一个一致性的状态。

以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

3.3.2 实现

由前面提到的AID特性共同保证。

下面举个例子:zhangsan 从银行卡转400到理财账户

start transaction;
select balance from bank where name=“zhangsan”;

//生成 重做日志 balance=600
update bank set balance = balance - 400;

// 生成重做日志 amount=400
update finance set amount = amount + 400;

commit;

如果执行完 update bank set balance = balance - 400;之后发生异常了，银行卡的钱也不能平白无辜的减少，而是回滚到最初状态。

这个是原子性，由undo log保证。
如果事务提交之后，缓冲池还没同步到磁盘的时候宕机了，这也是不能接受的，应该在重启的时候恢复并持久化。否则用户看到界面提示转入理财金成功成功，结果理财账户却永远查不到买入的钱。

这个是持久性，由redo log保证。
如果有并发事务请求的时候也应该做好事务之间的可见性问题，避免造成脏读，不可重复读，幻读等。

在涉及并发的情况下往往在性能和一致性之间做平衡，做一定的取舍，所以隔离性也是对一致性的一种破坏。

隔离性使用各种锁和MVCC保证。运用的优化思想有读写分离和并行、读读并行。

3.4 事务隔离性实现

3.4.1 概念

数据库允许多个并发事务同时对数据进行读写和修改的能力，如果一个事务要访问的数据正在被另外一个事务修改，只要另外一个事务未提交，它所访问的数据就不受未提交事务的影响。

隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

例如：现有有个交易是从 A 账户转 100 元至 B 账户，在这个交易事务还未完成的情况下，如果此时 B 查询自己的账户，是看不到新增加的 100 元的。

3.4.2 事务干扰其他事务的例子

3.4.2.1 数据

假设有InnoDB表：

t(id PK, name);

表中有三条记录：

1, shenjian
2, zhangsan
3, lisi

3.4.2.2 脏读

事务A，先执行，处于未提交的状态：

insert into t values(4, wangwu);

事务B，后执行，也未提交：

select * from t;

如果事务B能够读取到事务A操作的(4, wangwu)这条记录，事务A就对事务B产生了影响，这个影响叫做脏读：读到了尚未提交事务操作的记录。

3.4.2.3 不可重复读

事务A，先执行：

select * from t where id=1;

结果集为：

1, shenjian

事务B，后执行，并且提交：

update t set name=xxoo where id=1;
commit;

事务A，再次执行相同的查询：

select * from t where id=1;

结果集为：

1, xxoo

这次是已提交事务B对事务A产生的影响，这个影响叫做不可重复读，一个事务内相同的查询，得到了不同的结果。

3.4.2.4 幻读

事务A先执行：

select * from t where id>3;

结果集为：

NULL

事务B后执行，并且提交：

insert into t values(4, wangwu);
commit;

事务A首次查询了id>3的结果为NULL，于是想插入一条为4的记录：

insert into t values(4, xxoo);

结果集为：

Error : duplicate key!

事务A的内心OS是：你TM在逗我，查了id>3为空集，insert id=4告诉我PK冲突？这次是已提交事务B对事务A产生的影响，这个影响叫做“幻读”。

在这里插入图片描述

不可重复读和幻读到底有什么区别呢？

不可重复读是读取了其他事务更改的数据，针对update操作
解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。
幻读是读取了其他事务新增的数据，针对insert与delete操作
解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

幻读和不可重复读都是指的一个事务范围内的操作受到其他事务的影响了。只不过幻读是重点在插入和删除，不可重复读重点在修改。

幻读重现步骤如下：

A事务开始，然后使用select查询主键pk对应数据，此时数据不存在
B事务开始，插入一条指定主键pk的数据并提交事务
A事务再次执行刚才的查询，此时不会读取到B事务插入的数据
A事务修改或以B事务那条新增记录的主键为主键插入数据
此时会发现数据成功修改或成功新增，这跟我们前两次查询不符，出现幻觉了？这就是幻读

3.4.2.5 小结

可以看到，并发的事务可能导致其他事务：

脏读
不可重复读
幻读

3.4.3 InnoDB实现了哪几种事务的隔离级别？

按照SQL92标准，InnoDB实现了四种不同事务的隔离级别：

RU-读未提交(Read Uncommitted)
RC-读提交(Read Committed)
RR-可重复读(Repeated Read)
串行化(Serializable)

他们与上述几种不正常读的关系如下，打钩代表会发生，打叉代表不会发生：
在这里插入图片描述
不同事务的隔离级别，实际上是一致性与并发性的一个权衡与折衷。

InnoDB使用不同的锁策略(Locking Strategy)来实现不同的隔离级别。

3.4.3.1 RU-读未提交(Read Uncommitted)

这种事务隔离级别下，select语句不加锁。

画外音：官方的说法是
SELECT statements are performed in a nonlocking fashion.
此时，可能读取到不一致的数据，即脏读。
事务A未提交的数据，可以被事务B读取到。那么这里读到的数据就被视为脏数据。
这是并发最高，一致性最差的隔离级别。
读写可并行，性能高；但可能产生脏读、不可重复度、幻读。
脏读例子
公司发工资了，领导把5000元打到singo的账号上，但是该事务并未提交，而singo正好去查看账户，发现工资已经到账，是5000元整，非常高兴。可是不幸的是，领导发现发给singo的工资金额不对，是2000元，于是迅速回滚了事务，修改金额后，将事务提交，最后singo实际的工资只有 2000元，singo空欢喜一场。也就是说singo事务产生了对未提交的打钱事务的脏读。

这个例子时间轴如下：

3.4.3.2 RC-读提交(Read Committed)

事务A已提交的数据才能让其他事务读到
select读取时：
1. 普通的select和RR中相同也是MVCC快照读，所以可避免读到未提交事务即脏读；
2. 加锁的select, update, delete等语句，除了在外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会封锁区间，其他时刻都只使用记录锁；
RC可避免脏读
RC下其他事务的插入依然可以执行，就可能导致不可重复读和幻读。
具体来说，RC级别下的MVCC每次select的时候新生成一个版本号，所以每次select的时候读的不是同一个快照副本，此时就出现不可重复读。
这是互联网最常用的隔离级别
不可重复读例子
singo拿着工资卡去消费，系统读取到卡里确实有2000元，而此时她的老婆也正好在网上转账，把singo工资卡的2000元转到另一账户，并在 singo之前提交了事务。

当singo扣款时，系统再次读取singo的工资卡时已经没有钱，扣款失败，singo十分纳闷，明明卡里有钱，为何…

出现上述情况，即我们所说的不可重复读 ，两个并发的事务，“事务A：singo消费”、“事务B：singo的老婆网上转账”，事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。

3.4.3.3 RR-可重复读(Repeated Read)

在一个事务内的多次读取的结果是一样的。这种级别下可以避免查询时脏读、不可重复读问题，但还是有可能遇到幻读的情况
这是InnoDB默认的隔离级别
select读取时：
1. 普通的select无使用锁，而是使用读写分离的快照读(snapshot read)，避免脏读
  这是一种不加锁的一致性读(Consistent Nonlocking Read)，底层使用MVCC来实现，具体的原理请参见2.5.2章节中内容；注意这里跟RC不同的是，一个事务内多次读取的是同一个版本号的快照，所以可以避免不可重复读的情况：
  
  这种方式可支持读写并行，但实现的复杂度较高，且还是会发生幻读。
2. 加锁的select(select ... in share mode / select ... for update), update, delete等语句，它们的锁，依赖于它们是否在唯一索引(unique index)上使用了唯一的查询条件(unique search condition)，或者范围查询条件(range-type search condition)：
  关于RR与幻读，可参考：
- 既然MySQL中InnoDB使用MVCC，为什么REPEATABLE-READ不能消除幻读？
- mysql在RR的隔离级别下,究竟是通过MVCC解决幻读的还是通过行锁的next key算法解决的?
- 到底什么是mysql的幻读和不可重复读
  - 在唯一索引上使用唯一的查询条件，会使用记录锁(record lock)，而不会封锁记录之间的间隔，即不会使用间隙锁(gap lock)与临键锁(next-key lock)，所以可避免不可重复读的情况。
  - 范围查询条件，会使用间隙锁与临键锁，锁住索引记录之间的范围，避免范围间插入记录，以避免产生幻影行记录，以及避免不可重复的读
画外音：这一段有点绕，多读几遍。
幻读例子
singo的老婆工作在银行部门，她时常通过银行内部系统查看singo的信用卡消费记录。有一天，她查询到singo当月信用卡的总消费金额（select sum(amount) from transaction where month = 本月）为80元，而singo此时正好在外面胡吃海塞后在收银台买单，消费1000元，即新增了一条1000元的消费记录（insert transaction … ），并提交了事务，随后singo的老婆将singo当月信用卡消费的明细打印到A4纸上，却发现消费总额为1080元，singo的老婆很诧异，以为出现了幻觉，幻读就这样产生了。
关于记录锁，间隙锁，临键锁的更多说明，详见InnoDB，select为啥会阻塞insert？

3.4.3.4 串行化(Serializable)

这种事务的隔离级别下，所有select语句都会被隐式的转化为select ... in share mode，采用排他锁，所有事务全部串行执行。
这可能导致，如果有未提交的事务正在修改某些行，所有读取这些行的select都会被阻塞住。

画外音：官方的说法是
To force a plain SELECT to block if other transactions have modified the selected rows.
这是一致性最好的，但并发性最差的隔离级别。
只要有一个事务在执行，则其他事务全部排队。

不会发生任何误读情况。
注意：在互联网大数据量，高并发量的场景下，几乎不会使用RU和串行化两种隔离级别。
吞吐量太低，用户体验极差

3.4.3.5 快照读在RR和RC下有何差异？

原文：InnoDB，快照读，在RR和RC下有何差异？

3.4.3.5.1 知识回顾

快照读(Snapshot Read)
MySQL数据库，InnoDB存储引擎，为了提高并发，使用MVCC机制，在并发事务时，通过读取数据行的历史数据版本，不加锁，来提高并发的一种不加锁一致性读(Consistent Nonlocking Read)。
读提交(Read Committed)
- 数据库领域，事务隔离级别的一种，简称RC
- 它解决“脏读”问题，保证读取到的数据行都是已提交事务写入的
- 它可能存在“读幻影行”问题，同一个事务里，连续相同的read可能读到不同的结果集
可重复读(Repeated Read)
- 数据库领域，事务隔离级别的一种，简称RR
- 它不但解决“读脏”问题，还能部分解决了“读幻影行”问题，同一个事务里，连续相同的read读到相同的结果集

在读提交(RC)，可重复读(RR)两个不同的事务的隔离级别下，快照读有什么不同呢？

先说结论：

事务总能够读取到，自己写入(update /insert /delete)的行记录
RC，快照读总是能读到最新的行数据快照。当然，必须是已提交事务写入的
RR，某个事务首次read记录的时间为T，未来不会读取到T时间之后已提交事务写入的记录，以保证连续相同的read读到相同的结果集

画外音：可以看到

读取到的数据和并发事务的开始时间没关系，和事务首次read的时间有关；
由于不加锁，和互斥关系也不大；

3.4.3.5.2 示例问题背景

这些就能解答InnoDB的快照读，到底和什么相关？中的问题了。

InnoDB表：

t(id PK, name);

表中有三条记录：

1, shenjian
2, zhangsan
3, lisi

3.4.3.5.2 示例问题case 1

两个并发事务A，B执行的时间序列如下（A先于B开始，B先于A结束）：

A1: start transaction;
         B1: start transaction;
A2: select * from t;
         B2: insert into t values (4, wangwu);
A3: select * from t;
         B3: commit;
A4: select * from t;

提问1：假设事务的隔离级别是可重复读RR，事务A中的三次查询，A2, A3, A4分别读到什么结果集？
回答：RR中
1. A2读到的结果集肯定是{1, 2, 3}，这是事务A的第一个read，假设为时间T；
2. A3读到的结果集也是{1, 2, 3}，因为B还没有提交；
3. A4读到的结果集还是{1, 2, 3}，因为事务B是在时间T之后提交的，A4得读到和A2一样的记录；
提问2：假设事务的隔离级别是读提交RC，A2, A3, A4又分别读到什么结果集呢？
回答：RC中
1. A2读到的结果集是{1, 2, 3}；
2. A3读到的结果集也是{1, 2, 3}，因为B还没有提交；
3. A4读到的结果集是{1, 2, 3, 4}，因为事务B已经提交；

3.4.3.5.3 示例问题case 2

仍然是上面的两个事务，只是A和B开始时间稍有不同（B先于A开始，B先于A结束）：

B1: start transaction;
A1: start transaction;
A2: select * from t;
         B2: insert into t values (4, wangwu);
A3: select * from t;
         B3: commit;
A4: select * from t;

提问3：假设事务的隔离级别是可重复读RR，事务A中的三次查询，A2, A3, A4分别读到什么结果集？
回答：RR中
1. A2读到的结果集肯定是{1, 2, 3}，这是事务A的第一个read，假设为时间T；
2. A3读到的结果集也是{1, 2, 3}，因为B还没有提交；
3. A4读到的结果集还是{1, 2, 3}，因为事务B是在时间T之后提交的，A4得读到和A2一样的记录；
提问4：假设事务的隔离级别是读提交RC，A2, A3, A4的结果集又是什么呢？
回答：RC中
1. A2读到的结果集是{1, 2, 3}；
2. A3读到的结果集也是{1, 2, 3}，因为B还没有提交；
3. A4读到的结果集是{1, 2, 3, 4}，因为事务B已经提交

结论：事务的开始时间不一样，不会影响“快照读”的结果，所以结果集和case 1一样。

3.4.3.5.4 示例问题case 3

仍然是并发的事务A与B（A先于B开始，B先于A结束）：

A1: start transaction;
         B1: start transaction;
         B2: insert into t values (4, wangwu);
         B3: commit;
A2: select * from t;

提问5：假设事务的隔离级别是可重复读RR，事务A中的A2查询，结果集是什么？
回答：RR中
A2读到的是{1, 2, 3, 4}。
因为A2是事务A的第一个read，假设为时间T，它能读取到T之前提交事务写入的数据行，故结果集为{1, 2, 3, 4}。
提问6：假设事务的隔离级别是读提交RC，A2的结果集又是什么呢？
回答：RC中
A2读到的是{1, 2, 3, 4}

3.4.3.5.4 快照读小结

RR下，事务在第一个Read操作时，会建立Read View。后面的读结果无论如何都不会改变。
RC下，事务在每次Read操作时，都会建立Read View。只要有新的事务提交，就会读到新数据。

3.4.3.6 小结

并发事务之间相互干扰，可能导致事务出现脏读，不可重复度，幻读等问题
InnoDB实现了SQL92标准中的四种隔离级别：

RU-读未提交：select不加锁，可能出现脏读；并发最高，但基本不用
RC-读提交：用得最多。普通select快照读，锁select /update /delete 会使用记录锁，可能出现不可重复读、幻读；
RR-可重复读：InnoDB默认。普通select快照读，锁select /update /delete 根据查询条件情况，会选择记录锁，或者间隙锁/临键锁，以防止幻读；
串行化：select隐式转化为select … in share mode，会被update与delete互斥；并发最低，基本不用

或许有朋友问，为啥没提到insert？可以查阅InnoDB并发插入，居然使用意向锁？

3.5 事务持久性实现

3.5.1 概念

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

3.5.2 实现

事务持久性由redo log实现。

详细参考 redo log

4 InnoDB的七种锁

总的来说，InnoDB共有七种类型的锁：

自增锁(Auto-inc Locks)
共享/排它锁(Shared and Exclusive Locks)
意向锁(Intention Locks)
插入意向锁(Insert Intention Locks)
记录锁(Record Locks)
间隙锁(Gap Locks)
临键锁(Next-key Locks)

4.1 自增锁

原文：插入InnoDB自增列，居然是表锁？

4.1.1 引子-案例说明

MySQL，InnoDB，默认的隔离级别(RR)，假设有数据表：

t(id AUTO_INCREMENT, name);

数据表中有数据：

1, shenjian
2, zhangsan
3, lisi

事务A先执行，还未提交：

insert into t(name) values(xxx);

事务B后执行：

insert into t(name) values(ooo);

问：事务B会不会被阻塞？

4.1.2 案例分析

InnoDB在RR隔离级别下，能解决幻读问题，上面这个案例中：

事务A先执行insert，会得到一条(4, xxx)的记录，由于是自增列，故不用显示指定id为4，InnoDB会自动增长，注意此时事务并未提交；
事务B后执行insert，假设不会被阻塞，那会得到一条(5, ooo)的记录；

此时，并未有什么不妥，但如果，

事务A继续insert：

insert into t(name) values(xxoo);

会得到一条(6, xxoo)的记录。

事务A再select：

select * from t where id>3;

得到的结果是：

4, xxx
6, xxoo

画外音：不可能查询到5的记录，再RR的隔离级别下，不可能读取到还未提交的其他事务生成的数据。

咦，这对于事务A来说，就很奇怪了，对于AUTO_INCREMENT的列，连续插入了两条记录，一条是4，接下来一条变成了6，就像莫名其妙的幻影。

4.1.3 自增锁（Auto-inc Locks）

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。
最简单的场景，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

画外音：官网是这么说的
An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

与此同时，InnoDB提供了innodb_autoinc_lock_mode配置，可以调节与改变该锁的模式与行为。

4.1.4 假如不是自增列

上面的案例，假设不是自增列，又会是什么样的情形呢？
表：

t(id unique PK, name);

数据表中有数据：

10, shenjian

20, zhangsan

30, lisi

事务A先执行，在10与20两条记录中插入了一行，还未提交：

insert into t values(11, xxx);

事务B后执行，也在10与20两条记录中插入了一行：

insert into t values(12, ooo);

这里，便不再使用自增锁，那：

会使用什么锁？
事务B会不会被阻塞呢？

4.2 共享/排他锁

原文：InnoDB并发插入，居然使用意向锁？

读-共享锁（Share Locks，记为S锁），读取数据时加S锁。共享锁之间不互斥，简记为：读读可以并行
写-排他锁（eXclusive Locks，记为X锁），修改数据时加X锁。排他锁与任何锁互斥，简记为：写读，写写不可以并行

可以看到，一旦写数据的任务没有完成，数据是不能被其他任务读取的，这对并发度有较大的影响。

在InnoDB里当然也实现了标准的行级锁(row-level locking)，共享/排它锁：

事务拿到某一行记录的共享S锁，才可以读取这一行；
事务拿到某一行记录的排它X锁，才可以修改或者删除这一行；

其兼容互斥表如下：

	S	X
S	兼容	互斥
X	互斥	互斥

即：

多个事务可以拿到一把S锁，读读可以并行；
而只有一个事务可以拿到X锁，写写/读写必须互斥；

共享/排它锁的潜在问题是，不能充分的并行，解决思路是数据多版本，这里不再深入展开。

4.3 意向锁

InnoDB支持多粒度锁(multiple granularity locking)，它允许行级锁与表级锁共存，实际应用中，InnoDB使用的是意向锁。

意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。

意向锁有这样一些特点：

首先，意向锁，是一个表级别的锁(table-level locking)；
意向锁分为：
- 意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁
- 意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁
  举个例子：

select ... lock in share mode，要设置IS锁；
select ... for update，要设置IX锁；

意向锁协议(intention locking protocol)：
- 事务要获得某些行的S锁，必须先获得表的IS锁
- 事务要获得某些行的X锁，必须先获得表的IX锁
由于意向锁仅仅表明意向，它其实是比较弱的锁，意向锁之间并不相互互斥，而是可以并行，其兼容互斥表如下：

	IS	IX
IS	兼容	兼容
IX	兼容	兼容

既然意向锁之间都相互兼容，那其意义在哪里呢？它会与共享锁/排它锁互斥，其兼容互斥表如下：

	S	X
IS	兼容	互斥
IX	互斥	互斥

画外音：排它锁是很强的锁，不与其他类型的锁兼容。这也很好理解，修改和删除某一行的时候，必须获得强锁，禁止这一行上的其他并发，以保障数据的一致性。

4.4 插入意向锁

4.4.1 概念

对已有数据行的修改与删除，必须加强互斥锁X锁，那对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？插入意向锁，孕育而生。

插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。

画外音：有点尴尬，间隙锁下一篇文章才会介绍，暂且理解为，它是一种实施在索引上，锁定索引某个区间范围的锁。

插入意向锁的玩法是：
多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

画外音：官网的说法是
Insert Intention Lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

4.4.2 示例

这样，之前挖坑的例子，就能够解答了。

在MySQL，InnoDB，RR下：
表:

t(id unique PK, name);

数据表中有数据：

10, shenjian
20, zhangsan
30, lisi

事务A先执行，在10与20两条记录中插入了一行，还未提交：

insert into t values(11, xxx);

事务B后执行，也在10与20两条记录中插入了一行：

insert into t values(12, ooo);

会使用什么锁？
事务B会不会被阻塞呢？

回答：虽然事务隔离级别是RR，虽然是同一个索引，虽然是同一个区间，但插入的记录并不冲突，且这里id不是自增字段，故这里：

使用的是插入意向锁
并不会阻塞事务B

4.4.3 思路总结

InnoDB使用共享锁，可以提高读读并发；
为了保证数据强一致，InnoDB使用强互斥锁，保证同一行记录修改与删除的串行性；
InnoDB使用插入意向锁，可以提高插入并发；

4.4.4 遗留问题

假设不是插入并发，而是读写并发，又会是什么样的结果呢？

MySQL，InnoDB，默认的隔离级别(RR)。

表结构:

t(id unique PK, name);

数据表中有数据：

10, shenjian
20, zhangsan
30, lisi

事务A先执行，查询了一些记录，还未提交：

select * from t where id>10;

事务B后执行，在10与20两条记录中插入了一行：

insert into t values(11, xxx);

这里：

会使用什么锁？
事务B会不会被阻塞呢？

4.5 记录锁

原文：InnoDB，select为啥会阻塞insert？
记录锁，它封锁索引记录，例如：

select * from t where id=1 for update;

它会在id=1的索引记录上加锁，以阻止其他事务插入，更新，删除id=1的这一行。

需要说明的是：

select * from t where id=1;

则是快照读(SnapShot Read)，它并不加锁。

4.6 间隙锁

间隙锁，它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

依然是上面的例子，InnoDB，RR：

表结构：

t(id PK, name KEY, sex, flag);

表中有四条记录：

1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B

SQL语句

select * from t 
    where id between 8 and 15 
    for update;

上面这个SQL会封锁区间，以阻止其他事务id=10的记录插入。

画外音：
为什么要阻止id=10的记录插入？

如果能够插入成功，头一个事务执行相同的SQL语句，会发现结果集多出了一条记录，即幻影数据。

间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”。

如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。

4.7 临键锁

临键锁，是记录锁与间隙锁的组合，它的封锁范围既包含索引记录，又包含索引区间。

更具体的，临键锁会封锁索引记录本身，以及索引记录之前的区间。

如果一个会话占有了索引记录R的共享/排他锁，其他会话不能立刻在R之前的区间插入新的索引记录。

画外音：原文是说
If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

依然是上面的例子，InnoDB，RR：

表结构:

t(id PK, name KEY, sex, flag);

表中有四条记录：

1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B

PK上潜在的临键锁为：

(-infinity, 1]
(1, 3]
(3, 5]
(5, 9]
(9, +infinity]

临键锁的主要目的，也是为了避免幻读(Phantom Read)。

如果把事务的隔离级别降级为RC，临键锁则也会失效。

4.8 总结

InnoDB的锁，与索引类型，事务的隔离级别相关
记录锁锁定索引记录
间隙锁锁定间隔，防止间隔中被其他事务插入
临键锁锁定索引记录+间隔，防止幻读

各类语句使用到的锁如下(原文：别废话，各种SQL到底加了什么锁？)：

4.8.1 普通select

在读未提交(Read Uncommitted)，读提交(Read Committed, RC)，可重复读(Repeated Read, RR)这三种事务隔离级别下，普通select使用快照读(snpashot read)，不加锁，并发非常高；
在串行化(Serializable)这种事务的隔离级别下，普通select会升级为select … in share mode，使用互斥锁，会被update与delete互斥;

4.8.2 加锁select

加锁select主要是指：

select ... for update
select ... in share mode

如果，在唯一索引(unique index)上使用唯一的查询条件(unique search condition)，会使用记录锁(record lock)，而不会封锁记录之间的间隔，即不会使用间隙锁(gap lock)与临键锁(next-key lock)；

举个栗子，假设有InnoDB表：

t(id PK, name);

表中有三条记录：

1, shenjian
2, zhangsan
3, lisi

SQL语句：

select * from t where id=1 for update;

以上SQL只会封锁记录，而不会封锁区间。

其他的查询条件和索引条件，InnoDB会封锁被扫描的索引范围，并使用间隙锁与临键锁，避免索引范围区间插入记录；

4.8.3 update与delete

和加锁select类似，如果在唯一索引上使用唯一的查询条件来update/delete，例如：

update t set name=xxx where id=1;

以上SQL也只加记录锁；

否则，符合查询条件的索引记录之前，都会加排他临键锁(exclusive next-key lock)，来封锁索引记录与之前的区间；
尤其需要特殊说明的是，如果update的是聚集索引(clustered index)记录，则对应的普通索引(secondary index)记录也会被隐式加锁，这是由InnoDB索引的实现机制决定的：普通索引存储PK的值，检索普通索引本质上要二次扫描聚集索引。

4.8.4 insert

同样是写操作，insert和update与delete不同，它会用排它锁封锁被插入的索引记录，而不会封锁记录之前的范围。

同时，会在插入区间加插入意向锁(insert intention lock)，但这个并不会真正封锁区间，也不会阻止相同区间的不同KEY插入。

5 InnoDB调试死锁的方法

原文：超赞，InnoDB调试死锁的方法！
近期写了不少InnoDB锁相关的文章，不少小伙伴问，如何在MySQL终端模拟并发事务，如何复现之前文章中的案例。今天，咱们一起动起手来，模拟并发事务的互斥与死锁。

5.1 事前准备

安装MySQL服务端、客户端

安装能够模拟多个并发事务的终端

画外音：楼主使用的是MySQL5.6，官方客户端mysql，模拟并发终端用的SecureCRT。

5.2 配置的确认与修改

要测试InnoDB的锁互斥，以及死锁，有几个会影响实验结果的配置务必要提前确认：

5.2.1 区间锁是否关闭

区间锁(间隙锁，临键锁)是InnoDB特有施加在索引记录区间的锁，MySQL5.6可以手动关闭区间锁，它由innodb_locks_unsafe_for_binlog参数控制：
- 设置为ON，表示关闭区间锁，此时一致性会被破坏（所以是unsafe）
- 设置为OFF，表示开启区间锁
MySQL5.6的默认值为OFF，表示使用区间锁

可以这么查询该参数：

show global variables like "innodb_locks%";

5.2.2 事务自动提交(auto commit)是否关闭

MySQL默认把每一个单独的SQL语句作为一个事务，自动提交。

MySQL5.6的默认值为On，表示事务自动提交。

可以这么查询事务自动提交的参数：

show global variables like "autocommit";

要模拟并发事务，需要修改事务自动提交这个选项，每个session要改为手动提交。

任何连上MySQL的session，都要手动执行以下命令以手动控制事务的提交：

set session autocommit=0;

事务自动提交

如上图，需要把session的autocommit设置为OFF。

可以看到，修改session变量，并不影响global变量，全局其他的session仍然是ON。

画外音：session变量默认继承global变量，也可以单独修改。

5.2.3 事务的隔离级别(isolation level)

不同事务的隔离级别，InnoDB的锁实现是不一样。

MySQL5.6的默认值为RR，事务隔离级别为可重复读

可以这么查询事务的隔离级别：

show global variables like "tx_isolation";

可以这么设置事务的隔离级别：

set session transaction isolation level X;

X可取：

read uncommitted
read committed
repeatable read
serializable

事务的隔离级别

5.3 数据准备

InnoDB的行锁都是实现在索引上的，实验可以使用主键，建表时设定为innodb引擎：

create table t (
id int(10) primary key
)engine=innodb;

插入一些实验数据：

start transaction;
insert into t values(1);
insert into t values(3);
insert into t values(10);
commit;

注意：这是实验的初始状态，不同实验开始之初，都默认回到初始状态。

5.4 实验一，间隙锁互斥

开启区间锁，RR的隔离级别下，上例会有：

(-infinity, 1)
(1, 3)
(3, 10)
(10, infinity)

这四个区间。

事务A删除某个区间内的一条不存在记录，获取到共享间隙锁，会阻止其他事务B在相应的区间插入数据，因为插入需要获取排他间隙锁。

session A：
set session autocommit=0;
start transaction;
delete from t where id=5;

session B：
set session autocommit=0;
start transaction;
insert into t values(0);
insert into t values(2);
insert into t values(12);
insert into t values(7);

事务B插入的值：0, 2, 12都不在(3, 10)区间内，能够成功插入，而7在(3, 10)这个区间内，会阻塞。

可以使用：

show engine innodb status;

来查看锁的情况。

间隙锁互斥

如上图，可以看到（请把图放大）：

insert into t values(7);

正在等待共享间隙锁的释放。

如果事务A提交或者回滚，事务B就能够获得相应的锁，以继续执行。

如果事务A一直不提交，事务B会一直等待，直到超时，超时后会显示：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

5.5 实验二，共享排他锁死锁

回到数据的初始状态，这次需要三个并发的session。

画外音：SecureCRT得开三个窗口了。

session A先执行：

set session autocommit=0;
start transaction;
insert into t values(7);

session B后执行：

set session autocommit=0;
start transaction;
insert into t values(7);

session C最后执行：

set session autocommit=0;
start transaction;
insert into t values(7);

三个事务都试图往表中插入一条为7的记录：

A先执行，插入成功，并获取id=7的排他锁；
B后执行，需要进行PK校验，故需要先获取id=7的共享锁，阻塞；
C后执行，也需要进行PK校验，也要先获取id=7的共享锁，也阻塞；

如果此时，session A执行：

rollback;

id=7排他锁释放。

则B，C会继续进行主键校验：

B会获取到id=7共享锁，主键未互斥；
C也会获取到id=7共享锁，主键未互斥；

B和C要想插入成功，必须获得id=7的排他锁，但由于双方都已经获取到id=7的共享锁，它们都无法获取到彼此的排他锁，死锁就出现了。

当然，InnoDB有死锁检测机制，B和C中的一个事务会插入成功，另一个事务会自动放弃：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

5.6 实验三，并发间隙锁的死锁

共享排他锁，在并发量插入相同记录的情况下会出现，相应的案例比较容易分析。而并发的间隙锁死锁，是比较难定位的。

回到数据的初始状态，这次需要两个并发的session，其SQL执行序列如下：

A：set session autocommit=0;
A：start transaction;
A：delete from t where id=6;
        B：set session autocommit=0;
        B：start transaction;
        B：delete from t where id=7;
A：insert into t values(5);
        B：insert into t values(8);

A执行delete后，会获得(3, 10)的共享间隙锁。

B执行delete后，也会获得(3, 10)的共享间隙锁。

A执行insert后，希望获得(3, 10)的排他间隙锁，于是会阻塞。

B执行insert后，也希望获得(3, 10)的排他间隙锁，于是死锁出现。

仍然使用：

show engine innodb status;

来查看死锁的情况。

并发间隙锁的死锁

事务1占有什么锁，请求什么锁；事务2占有什么锁，请求什么锁，一清二楚（请把图放大）。

另外，检测到死锁后，事务2自动回滚了：

WE ROLL BACK TRANSACTION (2)

事务1将会执行成功。

5.7 总结

说了很多，希望大家能起手来，这样对InnoDB锁的机制，以及锁的调试印象会更加深刻：

并发事务，间隙锁可能互斥

A删除不存在的记录，获取共享间隙锁；
B插入，必须获得排他间隙锁，故互斥；

并发插入相同记录，可能死锁(某一个回滚)

并发插入，可能出现间隙锁死锁(难排查)

show engine innodb status; 可以查看InnoDB的锁情况，也可以调试死锁

6 Log

6.1 概述

马士兵讲mysql
https://www.bilibili.com/video/BV1yA411x7zC?p=3&vd_source=8b887a61a1af8d964ecaea547d51b762

MYSQL调优实战之事务原理
https://www.bilibili.com/video/BV1jD4y137YQ/?spm_id_from=333.337.search-card.all.click&vd_source=8b887a61a1af8d964ecaea547d51b762

全面解析MySQL中binlog二进制日志
https://www.bilibili.com/video/BV1VR4y1N7Rw/?spm_id_from=333.337.search-card.all.click&vd_source=8b887a61a1af8d964ecaea547d51b762

Mysql MTS并行复制，解决并发太多复制延迟问题。
在这里插入图片描述

6.2 slowlog

慢查询日志，记录比较慢的SQL

6.3 binlog

在这里插入图片描述
单线程IO Thread读取binlog同步到slave

在这里插入图片描述
事务提交时：

将binlog cache内容写入内存中的Page Cache
第一步写完后，Binlog数据同步到磁盘中

6.4 relaylog

在这里插入图片描述
DML并发很多，写入relayLog缓存。

单线程SQL Thread 读取realyLog重放来使得修改对slave生效，和master同步

6.5 undo log

实现事务原子性

6.6 redo log

实现事务持久性

两阶段提交保证master slave一致性

在这里插入图片描述

迷路剑客

关注

1
点赞
踩
8

收藏

觉得还不错? 一键收藏
1
评论
MySQL内核学习

MySQL内核学习转载声明：本文系转载自关于MySQL内核，一定要知道的！公众号：架构师之路作者： 58沈剑转载仅为方便学习查看，一切权利属于原作者，本人只是做了整理和排版，如果带来不便请联系我删除。0x01 InnoDB，5项最佳实践原文：InnoDB，5项最佳实践，知其所以然？1.1 count(*)1.1.1 MyISAM和InnoDB的区别知识点：MyISAM...
复制链接

扫一扫

专栏目录