一、前言

最近在读《MySQL 是怎样运行的》、《MySQL技术内幕 InnoDB存储引擎 》，后续会随机将书中部分内容记录下来作为学习笔记，部分内容经过个人删改，因此可能存在错误，如想详细了解相关内容强烈推荐阅读相关书籍

---

系列文章内容目录：

1. 【MySQL00】【 杂七杂八】
2. 【MySQL01】【 Explain 命令详解】
3. 【MySQL02】【 InnoDB 记录存储结构】
4. 【MySQL03】【 Buffer Pool】
5. 【MySQL04】【 redo 日志】
6. 【MySQL05】【 undo 日志】
7. 【MySQL06】【MVCC】
8. 【MySQL07】【锁】
9. 【MySQL08】【死锁】

---

二、事务的读写情况

事务并发时可能会出现 读-读、写-写、读-写的情况，其中 读-读 情况本身并不会对记录有任何影响，所以不会引起什么问题。下面主要来看后两种情况。

1. 写-写情况

在 写-写 情况下没如果不做任何作出会发生脏写现象（如果一个事务修改了另一个未提交事务修改过的数据，就意味着发生了脏写。），任何一种数据库隔离级别都不允许出现这种情况，因此在多个未提交事务相继对一条记录进行改动时，需要让他们排队执行。这个排队的过程就是通过锁来实现的。这个锁本质上是一个内存中的结构，在事务执行之前是没有锁的，也就是说一开始是没有锁结构与记录进行关联的。

当一个事务想对某条记录做改动时，首先会判断内存中是否有与这条记录关联的锁结构，如果没有就会在内存中生成一个锁结构并与之关联。

锁结构中有很多信息，这里只说明两个重要属性：

• trx 信息 ：表示这个索结构是与哪个事务关联的。
• is_waiting ：表示当前事务是否在等待。

我们以下面的场景为例来说明下：

1. 假如 事务 T1 要对记录进行修改，就会生成一个锁结构与该记录关联。因为之前没有别的事务为这条记录加锁，所以 is_waiting 的属性就是false。我们将这个场景称为加锁成功，或者获取锁成功，如下：
   

2. 在事务T1 提交之前，事务T2 也想对该记录进行改动，T2 会首先去判断是否有锁结构与这条记录关联，如果有则 T2 也生成一个索结构与这条记录关联，锁结构的 is_waiting 属性为 true，表示需要等待，我们将这个场景称为获取锁失败或者加锁失败。如下：
   

3. 事务T1 提交后就会将其生成的锁结构释放掉，然后检测一下是否还有与该记录关联的锁结构。如果有则将其锁结构的 is_waiting 设置为 fasle，然后把该事物对应的线程唤醒，让T2 继续执行，此时T2就算获取到锁了。

2. 读-写情况

在 读-写 情况会出现 脏读、不可重复读、幻读现象。不同的隔离级别下解决程度也不同，如下：

隔离级别	解释
读未提交(READ UNCOMMITTED)	这种隔离级别会产生脏读，不可重复读和幻读。
读已提交 (READ COMITTED)	这种事务隔离级别可以避免脏读出现，但是可能会出现不可重复读和幻读。
可重复读(REPEATABLE READ)	这种事务隔离级别可以防止脏读，不可重复读。但是可能出现幻读
可串行化 (SERIALIZABLE)	事务被处理为顺序执行。除了防止脏读，不可重复读外，还避免了幻像读。但是不建议使用，他将事务完全按照串行处理。

在 MySQL中 REPEATABLE READ 隔离级别可以很大程度地避免幻读现象。

这里需要注意 REPEATABLE READ 并不能完全避免幻读，在本文 第六部分 普通 SELECT 语句 部分 进行了具体介绍。

避免 脏读、不可重复读、幻读有两种可选的解决方案：

1. 读操作使用多版本并发控制（MVCC），写操作进行加锁。
   MVCC 在 【MySQL06】【MVCC】 有过详细介绍，简单来说就是通过 ReadView在 Undo 日志版本链中找到符合条件的记录版本。写操作肯定针对的是最新版本的记录，读记录的历史版本和改动记录的最新版本二者并不冲突，也就是说采用 MVCC 时 读-写操作并不冲突。

   普通的 select 语句在 READ COMITTED 和 REPEATABLE READ 隔离级别下会使用到 MVCC 读取记录。
   在 READ COMITTED 隔离级别下，一个事务在执行过程中每次执行 select 操作时都会生成一个 ReadView。ReadView 的存在本身就保证了事务不可以读取到未提交事务所做的更改。也就避免了脏读。
   在 REPEATABLE READ 隔离级别下，一个事务在执行过第一次 select 操作才会生成一个 ReadView，之后的 select 都复用这个 ReadView，也就避免了不可重复读和幻读。

2. 读写操作都采用加锁的方式。
   在某些场景下不允许读取记录的旧版本，而是本次都必须去读取记录的最新版本，这种情况下就需要对读操作进行加速操作，同样的，写操作也要加锁。这就意味着 读-写操作 也要像 写-写操作 意义排队执行。

   脏读：如果一个事务在写记录的时候给记录加了锁，其余事务就无法获取到锁，也就无法读取到记录，从而避免了脏读。
   不可重复读：不可重复读产生的原因是当前事务先读取了一条记录，另外一个事务对该记录进行了改动。如果当前事务在读取记录的时候就给该记录加了锁，那么另一个事务就无法修改该记录，也就不会出现不可重复读现象。
   幻读：幻读产生的原因是因为某个事务读取了符合某些搜索条件的记录，之后别的事务又插入了符合相同搜索条件的新记录，导致该事务再次读取相同搜索条件的记录时，可以读到别的事务插入的新纪录，这些新纪录称为幻影记录。而即使采用加锁的方式，也因为当前事务在第一次读取记录时那些幻影记录不存在，所以在读取的时候加锁也无从加起。

一般情况下采用 MVCC 的方式性能更高，但是在某些特殊业务场景中要求必须采用加锁的方式执行也是没有办法的事。

3. 一致性读

事务利用 MVCC 进行的读取操作称为一致性读（Consistent Read）或者一致性无锁读（也称为快照读），所有普通的 select 语句在 READ COMITTED 和 REPEATABLE READ 隔离级别下都算是一致性读。
一致性读并不会对表中的任何记录进行加锁操作，其他事务可以自由地对表中的记录进行改动。

4. 锁定读

2.1 共享锁和独占锁

MySQL中对锁类型做了分类

• 共享锁（Shared Lock）：S 锁。在事务要读取一条记录时，需要首先获取该记录的 S 锁。
• 独占锁（Exclusive Lock）：X锁。也称为排他锁，在事务要改动一条记录时需要先获取该记录的 X 锁。

事务T1 获取一条记录的 S 锁，之后事务T2 也要访问该记录：

• 如果T2想要获取记录的 S 锁，则T2可以获取到该锁，此时 T1 和 T2 同时持久该记录的S锁
• 如果T2想要获取记录的 X锁，此时T2操作会被阻塞，直至T1提交之后释放 S 锁。

事务T1 获取一条记录的 X 锁，之后事务T2 无论是获取记录的 S 锁还是 X 锁，都会被阻塞，直至事务T1提交之后将 X 锁释放掉为止。

综上S 锁与 X 锁的兼容性如下：

兼容性	X锁	S 锁
X锁	不兼容	不兼容
S锁	不兼容	兼容

2.2 锁定读的语句

大部分情况下，我们在读取一条记录的时候都会获取该记录的 S 锁，但是有时候我们想在读取记录的时候就获取记录的 X 锁从而禁止别的事务读写该记录，这种情况我们称为锁定读（Locking Read），如下两种方式支持锁定读。

1. 对读取的记录加 S 锁。

   select ... lock in share mode;
   

   通过上述方式，如果当前事务执行了该语句，那么他会为读取到的记录加S锁，这样可以允许别的事务继续获取这些记录的S锁（别的事务也可以通过上述语句获取记录的 S 锁），但是不允许获取这些记录的 X 锁。如果别的事务想要获取这些记录的 X 锁则会被阻塞，知道当前事务提交后将这些记录上的 S 锁都释放掉。

2. 对读取的记录加 X 锁。

   select ... from update
   

   通过上述方式，如果当前事务执行乱改语句，那么他会为读取到的记录加 X锁。这样其他事务无法在这些记录上加上 S 锁或 X锁（会被阻塞），直至当前事务提交后将记录的 X 锁释放掉。

5. 写操作

即 DELETE、UPDATE、INSERT 三种情况，如下：

1. DELETE ：对一条记录执行 DELETE 操作的过程其实是先在 B+Tree 中定位到这条记录的位置，然后获取这条记录的X锁，最后再执行 delete mark 操作。这个“先定位待删除记录在B+Tree 中的位置再获取这条记录的 X 锁的过程” 看出是一个获取 X 锁的锁定读。

2. UPDATE ：在对一条记录进行 UPDATE 时分为下面3种情况

   • 如果未修改该记录的键值并且被更新列所占用的存储空间在修改前后未发生变化，则现在 B+Tree 中定位到这条记录的位置，然后在获取记录的X锁，最后在原纪录的位置进行修改。
   • 如果未修改记录的键值并且至少一个被更新的列所占用的存储空间在修改前后发生变化，则先在 B+Tree 中定位到这条记录的位置，然后获取记录的X锁，之后将该记录彻底删除（移到垃圾链表），最后再插入一条新记录。
   • 如果修改了该记录的键值，则相当于在原记录上执行 DELETE 操作之后再来一次 INSERT 操作，加锁操作就需要按照 DELETE 和 INSERT 规则进行。

3. INSERT ：一般情况下，新插入的一条记录受隐式锁保护，不需要在内存中为其生成对应的锁结构。

三、多粒度锁

InnoDB 是支持行锁和表锁的。对一个表加锁会影响表中所有的记录，表锁也可以分为共享锁（S 锁）和独占锁（X 锁）

• 如果一个事务给表加了 S 锁，则别的事务可以继续获取到该表或该表中某些记录的 S 锁，但不能再获取到该表或该表中某些记录的 X 锁
• 如果一个事务给表加了 X 锁，则别的事务不能在获取到该表或该表中某些记录的 S 锁和 X 锁。

除此之前 InnoDB 还存在意向锁（Intention Lock）机制：

• 意向共享锁（Intention Shared Lock）：IS 锁，当某个事务准备在某条记录上加 S 锁时，需要先在表级别加一个 IS 锁。
• 意向排他锁（Intention Exclusive Lock）：IX锁，当事务准备在某条记录上加 X 锁时，需要先在表级别加一个 IX 锁。

IS 锁和 IX 锁是表级锁，作用是为了在加表级锁（S锁或X锁）时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁记录。IS 和 IX 是相互兼容的。

当某个事务需要对表加上表级锁时，首先需要判断是否有其他事务是否已经对表或表中某些记录进行加锁，对于表级锁可以直接判断，但是对于表中那些记录被加了锁总不可能去遍历表中每条记录是否被加锁，因此就通过 意向锁来判断。

假设事务 T1 对表中某些记录加了 X 锁（行级锁），那么T1 首先会在表级别加一个 IX 锁，然后在对行记录加上 X 锁。当事务T2 需要对表加上表级 X 锁时，那么事务T2 就需要判断这个表没有被其他事务加上 X 锁，同时表中的记录没有被其他事务加上 X 锁，通过 表上的 IX 锁，事务T2 可以得知当前表中某些记录被加了 X 锁，因此 T2 等待 T1 将提交后将锁释放后才能加上 X 锁。

综上，表级别的锁的兼容性如下：

兼容性	X	IX	S	IS
X	不兼容	不兼容	不兼容	不兼容
IX	不兼容	兼容	不兼容	兼容
S	不兼容	不兼容	兼容	兼容
IS	不兼容	兼容	兼容	兼容

四、表锁和行锁

1. 表级锁

1.1 表级别的 S锁 和 X锁

在对某个表执行 SELECT、INSERT、DELETE、UPDATE 语句时，InnoDB是不会为这个表添加表级别的 S 锁或 X 锁。但在对表执行DDL语句时，则其他表执行 DML 语句则会发生阻塞，反之，如果在执行DML 语句时其他事务执行 DDL 语句也会被阻塞。这个过程是通过 server 层使用一种称为元数据锁（Metadata Lock， MDL）来实现的，一般情况下也不会使用 InnoDB的表级别的 S 锁和 X 锁。

可以通过如下语句手动获取表级别的 S 锁或 X 锁：

InnoDB会对 t1 表加表级别的 S 锁 ：LOCK TABLE t1 READ
InnoDB会对 t1 表加表级别的 X 锁 ：LOCK TABLE t1 WRITE

不过尽量避免这种语句因为其并没有提供额外保护，仅仅会降低并发能力而已。

1.2 表级别的 IS 锁和 IX锁

IS 锁和 IX 锁是表级锁，作用是为了在加表级锁（S锁或X锁）时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁记录。上面已经有过介绍，这里不在赘述。

1.3 表级别的 AUTO-INC 锁

如果我们对表的某个列添加 AUTO_INCREMENT 属性，在插入记录时就可以不指定该列的值，系统会自动给他赋予递增的值。而实现 AUTO_INCREMENT 递增效果的实现方式主要有下面两个：

1. 采用 AUTO-INC 锁 ：在执行插入语句前就加一个表级别的 AUTO-INC 锁，然后为每条待插入记录的 AUTO_INCREMENT 修饰的列分配递增的，在语句执行结束后再将 AUTO-INC锁释放掉。

   如果我们插入的语句在执行前并不知道要插入多少记录（如 insert…select、replace…select 或 load data这种无法语句插入多少条记录的语句）一般是使用 AUTO-INC 来实现 AUTO_INCREMENT 功能。

   AUTO-INC 锁的作用范围是单个插入语句，当插入语句执行完成就释放了。

2. 采用一个轻量级的锁：在为插入语句生成 AUTO_INCREMENT 修饰的列的值时获取这个轻量级锁，然后在本次插入语句需要用到的 AUTO_INCREMENT 修饰的列的值之后，就把该轻量级锁释放掉，就不需要等待整个插入语句执行完才释放锁。

   与 AUTO-INC 相比，轻量级的锁是获取到列的自增值后就释放，而 AUTO-INT 锁则是等待整个插入语句结束后才释放。

InnoDB 提供一个 innodb_autoinc_lock_mode 系统变量来控制使用上述哪种方式，为 0 时采用 AUTO-INC 锁，为 1 时采用 轻量级锁，为 2 时表示混着来（这种情况可能会造成不同事务中插入语句的 AUTO_INCREMENT 修饰的列的值是交叉的，在主从场景下有安全隐患。）

2. 行级锁

以下面表为例

CREATE TABLE `hero` (`number` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(100) DEFAULT NULL,`country` varchar(100) DEFAULT NULL,PRIMARY KEY (`number`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;# 插入如下数据
INSERT INTO `demo`.`hero` (`number`, `name`, `country`) VALUES (1, 'l刘备', '蜀');
INSERT INTO `demo`.`hero` (`number`, `name`, `country`) VALUES (3, 'z诸葛亮', '蜀');
INSERT INTO `demo`.`hero` (`number`, `name`, `country`) VALUES (8, 'c曹操', '魏');
INSERT INTO `demo`.`hero` (`number`, `name`, `country`) VALUES (15, 'x荀彧', '魏');
INSERT INTO `demo`.`hero` (`number`, `name`, `country`) VALUES (20, 's孙权', '吴');

2.1 行级锁的分类

1. Record Lock ：仅仅将一条记录锁上（官方名为 LOCK_REC_NOT_GAP，书上给起名字叫 正经记录锁，下文也用此名称描述）。正经记录锁有 S 锁和 X 锁之分，称为 S型正经记录锁 和 X型正经记录锁。当一个事务获取一条记录的 S型正经记录锁，其他事务也可以继续获取该记录的 S 型正经记录锁，但是不可以获取 X 型正经记录锁；当一个事务获取一条记录的 X型正经记录锁，其他事务无法获取到该记录的 S 型正经记录锁 和 X 型正经记录锁。

2. Gap Lock ：MySQL 在 REPEATABLE READ 隔离级别下是可以很大程度上解决幻读现象，解决方案有两种： MVCC 和 加锁。使用加锁方案时存在一个问题，就是事务在第一次执行读取操作时，幻影记录并不存在，因此无法为幻影记录加上正经记录锁。因此 InnoDB 提出来一种 Gap Lock 锁，官方称为 LOCK_GAP，简称 gap 锁。

   gap 锁的提出仅仅是为了防止插入幻影记录的。虽然 gap 锁有共享 gap 锁和 独占 gap 锁的说法，但是起到的作用都是相同的。如果一条记录加了 gap 锁并不会限制其他事务对这条记录加正经记录锁或者继续加 gap 锁。

   在下图中，为 number = 8 的记录加一个 gap 锁 意味着不允许别的事务在 number 值为8的记录的前面的间隙插入新纪录，即 number列的值在区间 (3，8) 的新纪录是不允许立即插入的，直到施加这个 gap 锁的事务提交了将 gap 锁释放后才可以插入。
   

这里存在一个问题，如果想阻止其他事务插入 number 区间在 （20， +∞）的新纪录，我们可以使用 Infimum 记录和 Supremum 两条伪记录。我们可以给索引中最后一条记录（number = 20 的记录）所在页面的 Supremum 记录加上一个 gap 锁，这样就可以阻止其他事务在 （20， +∞）的区间插入值。如下图：

3. Next-Key Lock ：官方称为 LOCK _ORDINARY，可以简称为 next-key 锁 。next-key 锁的本质就是一个正经记录锁和一个 gap 锁的合体，他不仅会锁住区间，还会锁住当前记录。如下图：
   

4. Insert Intention Lock ：一个事务在插入一条记录时需要判断插入位置是否已经被别的事务加了 gap 锁（next-key锁也包含 gap锁），如果有的话插入则需要等待，直到拥有 gap 锁的那个事务提交为止。但InnoDB规定 事务在等待时也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但现在处于等待状态。InnoDB将这种锁命名为 Insert Intention Lock，官方称为 LOCL_INSERT_INTENTION，也被称为插入意向锁。

   以下面为例：事务T1 为 number = 8 的记录加了一个 gap 锁，然后事务T2 和 T3 分别想向 hero 表中插入 number 值分别为 4、5的两条记录，现在为 number = 8 的记录加的锁示意图如下：

   

   从上图可以看到：T1 持有 gap 锁，所以 T2、T3 需要生成一个插入意向锁的锁结构并处于等待状态。当T1提交后会将他获取到的锁释放掉，此时T2、T3就能获取到对应的插入意向锁了（本质上就是将 is_waiting 字段改为 false）。T2 和 T3 之间也不会相互阻塞，可以同时获取到 number = 8 的插入意向锁，然后执行插入操作。

5. 隐式锁

   InnoDB还有一个隐式锁的概念，隐式锁起到了延迟生成锁结构的用处，如果别的事务在执行过程中不需要获取与该隐式锁相冲突的锁，就可以避免在内存中生成锁结构。

   一般情况下执行 INSERT 语句是不需要再内存中生成锁结构的（如果即将插入的间隙被施加了 gap 锁，那么本次插入会阻塞，并生成一个插入意向锁），但如果完全不加锁则可能会导致一些问题的出现。

   以下面为例：

   1. 事务T1首先插入了一条记录，事物T2立即使用 select … lock in shart mode 语句读取这条记录（也就是要获取这条记录的 S 锁），或者使用 select … for update 语句读取这套记录（也就是要获取这条记录的 X 锁），如果允许这种情况发生，则可能出现脏读现象。
   2. 事务T1首先插入了一条记录，事务T2立即修改这条记录（也就是要获取这条记录的 X 锁），如果允许这种情况发送，则可能会出现脏写现象。

   上述的情况可以通过事务id 来解决，我们将聚簇索引和二级索引分开来看：

   情景1 ：对于聚簇索引记录来说，有一个 trx_id 隐藏列，该隐藏列记录着最后改动该记录的事务的事务id。在当前事务中新插入一条聚簇索引记录后，该记录的 trx_id隐藏列代表的就是当前事务的事务id。如果其他事务此时想对该记录条件 S 锁或 X锁，首先会看一下该记录的 trx_id 隐藏列代表的事务是否是当前的活跃事务，如果不是的话就可以正常读取；如果是的话，就帮助当前事务创建一个 X 锁的索结构，该锁结构的 is_waiting 属性是 false；然后为自己也创建一个锁结构，该锁结构的 is_waiting 属性为 true，之后自己进入等待状态。

   情景2 ：对于二级索引记录来说，本身并没有 trx_id 隐藏列，但是在二级索引页面的 Page Header 部分有一个 PAGE_MAX_TRX_ID 属性，该属性代表对该页面做改动的最大的事务id。如果 PAGE_MAX_TRX_ID 小于当前最小活跃事务id，那就说明对该页面做修改的事务已经提交了，否则就需要再页面中定位到对应的二级索引记录，然后通过回表操作找到它对应的聚簇索引记录，然后再重复情景1 的做法。

   综上，一个事务对新插入的记录可以不显示地加锁（生成锁结构），但是由于事务id的存在，相当于加了一个隐式锁。别的事务在对这条记录加 S 锁或 X锁时，由于隐式锁的存在会先帮助当前事务生成一个锁结构，然后自己再生成一个锁结构，最后进入等待状态。

五、锁的内存结构

一条记录加锁的本质就是在内存中创建一个索结构与之关联（隐式锁除外）。那么一个事物对多条记录加锁时，是否就需要创建多个锁结构呢？如果要执行一条 SELECT * FROM hero LOCK IN SHARE MODE; 语句难不成要将表里所有记录都添加一个锁结构？想想也是不可能的，实际上，如果符合下面的条件，则多条记录可以放到一个锁结构中：

1. 在同一个事务中进行加锁操作；
2. 被加锁的记录在同一个页面；
3. 加锁的类型是一样的；
4. 等待状态是一样的；

如下图，为 InnoDB 中的锁结构

我们来看看具体字段：

1. 锁所在的事务信息：记载着该锁对应的事务信息。这个信息在内存结构中只是一个指针，所以并不会占用多大空间

2. 索引信息：对于行级锁来说需要记录下加锁的记录属于哪个索引的。

3. 表锁/行锁信息：表级锁结构和行级锁结构存储的结构是不同的，如表级锁记录这是对应哪个表加的锁以及一些其他信息，而对于行级锁则记载了 SpaceID（记录所在的表空间）、Page Number（记录所在的页号）、n_bits（对行级锁来说一条记录对应一个比特，一个页面中包含多条记录，用不同的比特来进行区分是哪条记录加锁，为此行级锁结构的尾部放置了一堆比特位，这个 n_bits 表示用了多少比特）

   并不是记录了多少记录，n_bits 属性值就是多少，为了之后再页面中插入记录时不至于重新分配表结构， n_bits 的值一般比页面中的记录条数多一些。

4. typ_mode：32 比特的数，被分为 lock_mode（表示锁模式，S 锁、X锁、IS 锁、IX 锁）、lock_type （锁类型，表级锁或行级锁）和 rec_lock_type （行锁的具体类型，正经记录锁、插入意向锁、gap 锁、next-key 锁）三个部分。

5. 其他信息：InnoDB 设计了各种哈希表和链表以方便管理各种锁结构。

6. 一堆比特位：对行级锁来说一条记录对应一个比特，一个页面中包含多条记录，用不同的比特来进行区分是哪条记录加锁，为此行级锁结构的尾部放置了一堆比特位。

六、语句加锁分析

首先对 hero 添加一个辅助索引 idx_name，如下SQL：

ALTER TABLE hero ADD INDEX idx_name (NAME)

此时 hero 的索引示意图如下：

下面以普通 SELECT 语句、锁定读语句、半一致性读、INSERT 语句四种情况进行分析。

1. 普通 SELECT 语句

在不同的隔离级别下，普通 SELECT 语句具有不同的表现，如下：

隔离级别	解释
读未提交(READ UNCOMMITTED)	这是事务最低的隔离级别，它允许事务可以看到这个事务未提交的数据。这种隔离级别会产生脏读，不可重复读和幻读。
读已提交 (READ COMITTED)	保证一个事务修改的数据提交后才能被另外一个事务读取。 另外一个事务不能读取该事务未提交的数据。这种事务隔离级别可以避免脏读出现，但是可能会出现不可重复读和幻读。
可重复读(REPEATABLE READ)	这种事务隔离级别可以防止脏读，不可重复读。但是可能出现幻读。它除了保证-一个事务不能读取另一个事务未提交的数据外，还保证了避免下面的情况产生(不可重复读) （MySql 默认就是这个级别）
可串行化(SERIALIZABLE)	这是花费最高代价但是最可靠的事务隔离级别。事务被处理为顺序执行。除了防止脏读，不可重复读外，还避免了幻像读。但是不建议使用，他将事务完全按照串行处理。

MVCC 并不能完全禁止幻读，以如下场景为例：

执行顺序	事务 T1	事务 T2
1	begin;
2	select * from hero where number = 30;
3		begin;
4		insert into hero values(30, ‘g关羽’, ‘魏’);
5		commit;
6	update hero set country = ‘蜀’ where number = 30;
7	select * from hero where number = 30;
8	commit;

在 REPEATABLE READ 隔离级别下，T1 第一次执行普通 select 语句时生成了一个 ReadView，之后 T2 向 hero 表新插入一条记录并提交。ReadView 并不能阻止 T1 执行 UPDATE 或者 DELETE 语句来改动这条新插入的记录（由于 T2 已经提交，因此改动该记录并不会造成阻塞），但这样一来，新纪录的 trx_id 列的值就变成了 T1 的事务 id。之后T1再通过普通 select 查询语句查询该记录时就可以看到该记录。因此这个特殊现象的存在，我们可以认为 InnoDB 的 MVCC 并不能完全禁止幻读。

在 SERIALIZABLE 隔离级别下，需要分为两种情况：

1. 在禁止自动提交时，普通 select 语句会被转为 select … lock in share mode 语句，也就是读取记录前需要先获取记录的 S 锁。
2. 在启用自动提交时，普通 select 语句并不会加锁，只是利用 MVCC 生成一个 ReadView 来读取记录。因为启用自动提交则代表着一个事务只有一条语句，一条语句也就不会出现脏读、幻读、不可重复读的现象了。

2. 锁定读语句

我们将下面四种语句一起讨论

• 语句1 ：select … lock in share mode;
• 语句2 ：select … for update;
• 语句3 ：update …
• 语句4 ：delete …

其中语句1 和语句2 时 MySQL中规定的两种锁定读的语法格式，而语句3 和 语句4 由于在执行过程中需要首先定位到被改动的记录并给记录加锁，因此也可以认为是一种锁定读。

2.1 匹配模式 和 唯一性搜索

下面先介绍一下 匹配模式 和 唯一性搜索 两个概念：

1. 匹配模式（match mode）：在使用索引执行查询时，查询优化器首先会生成若干个扫描区间。针对每一个扫描区间，我们都可以在该扫描区间内快速定位到第一条记录，然后沿着这条记录所在的单向链表就可以访问到该扫描区间内的其他记录，直到某条记录不在该扫描区间中为止，如果被扫描的区间是一个单点扫描区间，就可以说此时的匹配模式为精确匹配。

2. 唯一性搜索（unique search）：如果在扫描某个区间的记录前，就能事先确定该扫描区间内最多只包含一条记录，就把这种情况称为唯一性搜索。

   确定扫描区间最多只包含一条记录，需要满足以下条件：

   1. 匹配模式使用精确匹配
   2. 使用的索引是主键或唯一二级索引
   3. 如果使用的索引是唯一二级索引，那么搜索条件不能为 “索引 is null”，因为唯一二级索引允许存在多个值为 NULL 的记录
   4. 如果索引中包含多个列，那么在生成扫描区间时，每一列都得被用到。

2.2 加锁过程分析

由于在语句执行过程中，对记录加锁影响的因素太多（如事务的隔离级别、语句执行使用的索引类型、是否是精确匹配、是否是唯一性搜索等），所以这里先分析一般情况下语句执行过程中的加锁过程：

事务在执行过程中获取的锁一般只有在事务提交或回滚时才会释放，但在隔离级别不大于 READ COMMITTED 时，某些情况下也会提前将一些不符合搜索条件的记录上的锁释放掉。

我们把锁定读的执行看成时依次读取若干个扫描区间中的记录（如果是全表扫描，就把他看成是扫描区间是（-∞，+∞）），在一般情况下读取某个扫描区间的过程如下：

步骤1 ：定位 B+Tree 上该扫描区间中第一条记录，把该记录作为当前记录。

步骤2 ：为当前记录加锁。

一般情况下，在 READ UNCOMMITTED、READ COMITTED 的隔离级别下，会为当前记录加正经记录锁。在隔离级别为 REPEATABLE READ、SERIALIZABLE 时，会为当前记录加 next-key 锁。

步骤3 ：判断索引下推条件是否成立。在存在索引条件下推的条件时，如果当前记录符合索引条件下推的条件，则跳到第四步继续执行；否则直接获取当前记录所在的单向链表的下一条记录，将该记录作为新的当前记录，并跳回步骤2。另外当前步骤还会判断当前记录是否符合形成扫描区间的边界条件，如果不符合则跳过步骤 4 和 步骤5， 直接向 server 层返回查询完毕信息。这一步并不会释放锁。

索引下推（Index Condition Pushdown， ICP）：用来将查询过程中与被使用索引有关的搜索条件下推到存储引擎中判断，而不是返回到 server 层在判断。需要注意：ICP 只是为了减少回表次数，也就是减少读取完整的聚簇索引记录的次数从而减少 IO 操作。所以他只适用于二级索引，不适用于聚簇索引，并且 ICP 只适用于 SELECT 语句。

步骤4 ：执行回表操作。如果读取的是二级索引记录，则需要进行回表操作，获取到对应的聚簇索引记录并给该聚簇索引记录加正经记录锁。

步骤5 ：判断边界条件是否成立。如果该记录符合边界条件，则跳到步骤6继续执行，否则在 READ UNCOMMITTED、READ COMITTED 隔离级别就要释放掉加在记录上的锁（在REPEATABLE READ、SERIALIZABLE隔离级别时不释放加在该记录上的锁）。然后向 server 层返回查询完毕信息

步骤6 ：server 层判断其余搜索条件是否成立。除了索引下推条件外，server 层还需要判断其他搜索条件是否成立，如果成立则将该记录发送到客户端，否则在 READ UNCOMMITTED、READ COMITTED 隔离级别就要释放掉加在该记录上的锁（在REPEATABLE READ、SERIALIZABLE 隔离级别时不释放加在该记录上的锁）

步骤7 ：获取当前记录所在单向链表的下一条记录，并将其作为新的当前记录，并跳回步骤2。

我们结合实例来分析上面的内容：

2.2.1 实例1

以 下面语句为例：

SELECT * FROM `hero` WHERE number > 1 AND number <= 15 AND country = '魏' LOCK IN SHARE MODE;

EXPLAIN 语句执行后如下：

mysql> EXPLAIN SELECT * FROM `hero` WHERE number > 1 AND number <= 15 AND country = '魏' LOCK IN SHARE MODE;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | hero  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |    20.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.03 sec)

从执行计划可以看出，查询优化器将通过 range 访问方法来读取聚簇索引记录中的一些记录，通过搜索条件 number > 1 AND number <=15 来生成扫描区间 (1，15]，也就是需要扫描 number 值在 (1， 15] 区间中的所有聚簇索引记录：

在 隔离级别为 READ UNCOMMITTED、READ COMITTED 场景下：

• 对 number = 3 的聚簇索引记录加锁过程如下：

  步骤1 ：读取在(1，15] 区间的第一条聚簇索引记录，也就是number = 3 的聚簇索引记录
  步骤2 ：为 number = 3 的聚簇索引记录加上 S 型正经记录锁。
  步骤3 ：由于读取本身是聚簇索引记录，所以没有索引下推的条件
  步骤4 ：由于读取本身是聚簇索引记录，所以不需要回表操作
  步骤5 ：形成扫描区间 (1，15] 的边界条件是 number > 1 AND number <= 15，则 numbner = 3 的聚簇索引记录符合该边界条件。
  步骤6 ：server 层继续判断 number = 3 的索引记录是否符合条件 number > 1 AND number <= 15 AND country = ‘魏’。很显然不符合，所以释放掉加在该记录的锁。
  步骤7 ：获取 number = 3 的聚簇索引记录所在单向链表的下一条记录，也就是 number = 8 的聚簇索引记录

• 对 number = 8 的聚簇索引记录加锁过程如下：

  步骤2 ：为 number = 8 的聚簇索引记录加上 S 型正经记录锁。
  步骤3 ：由于读取本身是聚簇索引记录，所以没有索引下推的条件
  步骤4 ：由于读取本身是聚簇索引记录，所以不需要回表操作
  步骤5 ：形成扫描区间 (1，15] 的边界条件是 number > 1 AND number <= 15，则 numbner = 8 的聚簇索引记录符合该边界条件。
  步骤6 ：server 层继续判断 number = 8 的索引记录是否符合条件 number > 1 AND number <= 15 AND country = ‘魏’。很显然符合，所以将其发送到客户端，并且不释放掉加在该记录的锁。
  步骤7 ：获取 number = 8 的聚簇索引记录所在单向链表的下一条记录，也就是 number = 15 的聚簇索引记录

• 对 number = 15 的聚簇索引记录加锁过程如下：

  步骤2 ：为 number = 15 的聚簇索引记录加上 S 型正经记录锁。
  步骤3 ：由于读取本身是聚簇索引记录，所以没有索引下推的条件
  步骤4 ：由于读取本身是聚簇索引记录，所以不需要回表操作
  步骤5 ：形成扫描区间 (1，15] 的边界条件是 number > 1 AND number <= 15，则 numbner = 15 的聚簇索引记录符合该边界条件。
  步骤6 ：server 层继续判断 number = 15 的索引记录是否符合条件 number > 1 AND number <= 15 AND country = ‘魏’。很显然符合，所以将其发送到客户端，并且不释放掉加在该记录的锁。
  步骤7 ：获取 number = 15 的聚簇索引记录所在单向链表的下一条记录，也就是 number = 20 的聚簇索引记录

• 对 number = 20 的聚簇索引记录加锁过程如下：

  步骤2 ：为 number = 20的聚簇索引记录加上 S 型正经记录锁。
  步骤3 ：由于读取本身是聚簇索引记录，所以没有索引下推的条件
  步骤4 ：由于读取本身是聚簇索引记录，所以不需要回表操作
  步骤5 ：形成扫描区间 (1，15] 的边界条件是 number > 1 AND number <= 15，则 numbner = 20的聚簇索引记录不符合该边界条件。释放掉加在该记录上的锁，并且给 server 层返回一个 查询完毕 的信息。
  步骤6 ：server 层收到存储引擎返回的 查询完毕 信息，结束查询。

综上，该语句在执行过程中的加过效果如下图（数字标记为加锁顺序）：

在 隔离级别为 REPEATABLE READ、SERIALIZABLE 场景下：

• 对 number = 3 的聚簇索引记录加锁过程如下：

  步骤1 ：读取在(1，15] 区间的第一条聚簇索引记录，也就是number = 3 的聚簇索引记录
  步骤2 ：为 number = 3 的聚簇索引记录加上 S 型 next-key 锁。
  步骤3 ：由于读取本身是聚簇索引记录，所以没有索引下推的条件
  步骤4 ：由于读取本身是聚簇索引记录，所以不需要回表操作
  步骤5 ：形成扫描区间 (1，15] 的边界条件是 number > 1 AND number <= 15，则 numbner = 3 的聚簇索引记录符合该边界条件。
  步骤6 ：server 层继续判断 number = 3 的索引记录是否符合条件 number > 1 AND number <= 15 AND country = ‘魏’。很显然不符合，但是现在隔离级别为 EPEATABLE READ 或 SERIALIZABLE，所以不会释放掉加在该记录上的锁。
  步骤7 ：获取 number = 3 的聚簇索引记录所在单向链表的下一条记录，也就是 number = 8 的聚簇索引记录

• 对 number = 8 的聚簇索引记录加锁过程如下：

  步骤2 ：为 number = 8 的聚簇索引记录加上 S 型next-key 锁。
  步骤3 ：由于读取本身是聚簇索引记录，所以没有索引下推的条件
  步骤4 ：由于读取本身是聚簇索引记录，所以不需要回表操作
  步骤5 ：形成扫描区间 (1，15] 的边界条件是 number > 1 AND number <= 15，则 numbner = 8 的聚簇索引记录符合该边界条件。
  步骤6 ：server 层继续判断 number = 8 的索引记录是否符合条件 number > 1 AND number <= 15 AND country = ‘魏’。很显然符合，所以将其发送到客户端，并且不释放掉加在该记录的锁。
  步骤7 ：获取 number = 8 的聚簇索引记录所在单向链表的下一条记录，也就是 number = 15 的聚簇索引记录

• 对 number = 15 的聚簇索引记录加锁过程如下：

  步骤2 ：为 number = 15 的聚簇索引记录加上 S 型next-key 锁。
  步骤3 ：由于读取本身是聚簇索引记录，所以没有索引下推的条件
  步骤4 ：由于读取本身是聚簇索引记录，所以不需要回表操作
  步骤5 ：形成扫描区间 (1，15] 的边界条件是 number > 1 AND number <= 15，则 numbner = 15 的聚簇索引记录符合该边界条件。
  步骤6 ：server 层继续判断 number = 15 的索引记录是否符合条件 number > 1 AND number <= 15 AND country = ‘魏’。很显然符合，所以将其发送到客户端，并且不释放掉加在该记录的锁。
  步骤7 ：获取 number = 15 的聚簇索引记录所在单向链表的下一条记录，也就是 number = 20 的聚簇索引记录

• 对 number = 20 的聚簇索引记录加锁过程如下：

  步骤2 ：为 number = 20的聚簇索引记录加上 S 型 next-key 锁。
  步骤3 ：由于读取本身是聚簇索引记录，所以没有索引下推的条件
  步骤4 ：由于读取本身是聚簇索引记录，所以不需要回表操作
  步骤5 ：形成扫描区间 (1，15] 的边界条件是 number > 1 AND number <= 15，则 numbner = 20的聚簇索引记录不符合该边界条件,但是现在隔离级别为 EPEATABLE READ 或 SERIALIZABLE，所以不会释放掉加在该记录上的锁。之后给 server 层返回一个 查询完毕 的信息。
  步骤6 ：server 层收到存储引擎返回的 查询完毕 信息，结束查询。

综上，该语句在执行过程中的加过效果如下图（数字标记为加锁顺序）：

2.2.2 实例2

以 下面语句为例：

SELECT * FROM `hero` FORCE INDEX(idx_name) WHERE name > 'c曹操' AND  name <= 'x荀彧' AND country != '吴' LOCK IN SHARE MODE;

EXPLAIN 语句执行后如下：

mysql> EXPLAIN SELECT * FROM `hero` FORCE INDEX(idx_name) WHERE name > 'c曹操' AND  name <= 'x荀彧' AND country != '吴' LOCK IN SHARE MODE;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | hero  | NULL       | range | idx_name      | idx_name | 303     | NULL |    4 |    80.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
1 row in set (0.02 sec)

从执行计划可以看出，查询优化器将通过 range 访问方法来读取二级索引 idx_name 中的一些记录。根据搜索条件 name > ‘c曹操’ AND name <= ‘x荀彧’ 来生成扫描区间 (‘c曹操’, ‘x荀彧’], 也就是需要扫描 name 值在 (‘c曹操’, ‘x荀彧’] 区间中的所有二级索引记录。另外 执行计划中 Extra 列显示了 Using index condition，说明该语句查询时将使用到索引下推的条件。

因为在实际查询中，查询优化器会判断是否使用索引,在成本较大时可能会使用全表扫描，所以这里使用 FORCE INDEX(idx_name) 是为了强制使用 idx_name 索引执行查询。

在 隔离级别为 READ UNCOMMITTED、READ COMITTED 场景下：

• 对 name = ‘l刘备’ 的二级索引记录的加锁过程如下：

  步骤1 ：读取在 (‘c曹操’, ‘x荀彧’] 扫描区间的第一条二级索引记录，也就是name = 'l刘备’的二级索引记录。
  步骤2 ：为 name = ‘l刘备’ 的二级索引记录加 S 型正经记录锁。
  步骤3 ：本语句的索引下推条件是 name > ‘c曹操’ AND name <= ‘x荀彧’，因此 name = 'l刘备’的二级索引记录符合索引条件下推的条件。
  步骤4 ：因为读取的是二级索引，所以需要对记录进行回表操作，找到对应的聚簇索引，即 number = 1 的聚簇索引记录，然后为该聚簇索引记录加一个 S 型正经记录锁。
  步骤5 ：形成扫描区间 (‘c曹操’, ‘x荀彧’] 的边界条件是 name > ‘c曹操’ AND name <= ‘x荀彧’， name = ‘l刘备’ 的记录满足该边界条件。
  步骤6 ：server 层继续判断 name = ‘l刘备’ 的二级索引记录对应的聚簇索引记录是否符合条件 country != ‘吴’, 很显然符合，所以将其发送到客户端，并且不释放客户端上的锁。
  步骤7 ：获取 name = ‘l刘备’ 的二级索引记录所在单向链表的下一条记录，也就是 name = ‘s孙权’ 的二级索引记录。

• 对 name = ‘s孙权’ 的二级索引记录的加锁过程如下：

  步骤2 ：为 name = ‘s孙权’ 的二级索引记录加 S 型正经记录锁。
  步骤3 ：本语句的索引下推条件是 name > ‘c曹操’ AND name <= ‘x荀彧’，因此 name = 's孙权’的二级索引记录符合索引条件下推的条件。
  步骤4 ：因为读取的是二级索引，所以需要对记录进行回表操作，找到对应的聚簇索引，即 number = 20 的聚簇索引记录，然后为该聚簇索引记录加一个 S 型正经记录锁。
  步骤5 ：形成扫描区间 (‘c曹操’, ‘x荀彧’] 的边界条件是 name > ‘c曹操’ AND name <= ‘x荀彧’， name = ‘s孙权’ 的记录满足该边界条件。
  步骤6 ：server 层继续判断 name = ‘s孙权’ 的二级索引记录对应的聚簇索引记录是否符合条件 country != ‘吴’, 很显然不符合，所以释放掉加在该二级索引记录以及对应的聚餐索引记录上的锁。
  步骤7 ：获取 name = ‘s孙权’ 的二级索引记录所在单向链表的下一条记录，也就是 name = ‘x荀彧’ 的二级索引记录。

• 对 name = ‘x荀彧’ 的二级索引记录的加锁过程如下：

  步骤2 ：为 name = ‘x荀彧’ 的二级索引记录加 S 型正经记录锁。
  步骤3 ：本语句的索引下推条件是 name > ‘c曹操’ AND name <= ‘x荀彧’，因此 name = 'x荀彧’的二级索引记录符合索引条件下推的条件。
  步骤4 ：因为读取的是二级索引，所以需要对记录进行回表操作，找到对应的聚簇索引，即 number = 15 的聚簇索引记录，然后为该聚簇索引记录加一个 S 型正经记录锁。
  步骤5 ：形成扫描区间 (‘c曹操’, ‘x荀彧’] 的边界条件是 name > ‘c曹操’ AND name <= ‘x荀彧’， name = ‘x荀彧’ 的记录满足该边界条件。
  步骤6 ：server 层继续判断 name = ‘x荀彧’ 的二级索引记录对应的聚簇索引记录是否符合条件 country != ‘吴’, 很显然符合，所以将其发送到客户端，并且不释放客户端上的锁。
  步骤7 ：获取 name = ‘x荀彧’ 的二级索引记录所在单向链表的下一条记录，也就是 name = ‘z诸葛亮’ 的二级索引记录。

• 对 name = ‘z诸葛亮’ 的二级索引记录的加锁过程如下：

  步骤2 ：为 name = ‘z诸葛亮’ 的二级索引记录加 S 型正经记录锁。
  步骤3 ：本语句的索引下推条件是 name > ‘c曹操’ AND name <= ‘x荀彧’，因此 name = 'z诸葛亮’的二级索引记录不符合索引下推条件。由于它还不符合边界条件，所以就不再去找当前记录的下一条记录了，因此跳过步骤4 和 步骤5，直接向 server 层报告查询完毕
  步骤4 ：因为读取的是二级索引，所以需要对记录进行回表操作，找到对应的聚簇索引，即 number = 15 的聚簇索引记录，然后为该聚簇索引记录加一个 S 型正经记录锁。
  步骤5 ：步骤被跳过.
  步骤6 ：步骤被跳过。
  步骤7 ：server 层收到存储引擎层报关的 查询完毕 信息，结束查询。

综上，在 READ UNCOMMITTED、READ COMITTED 隔离级别下，该语句在执行过程中的加锁效果如图：

需要注意的是：对于 name = ‘s孙权’ 的二级索引记录，以及 number = 20 的聚簇索引记录来说，都是先加锁，后释放锁。另外，name = ‘z诸葛亮’ 的二级索引记录在步骤3 中被判断为不符合边界条件，但该步骤并不会释放加在该记录上的锁，而是直接向 server 层报告 查询完毕 信息，因此导致整个语句在执行结束后也不会释放加在 name = ‘z诸葛亮’ 的二级索引记录上的锁。

---

在 隔离级别为 REPEATABLE READ、SERIALIZABLE 场景下：

• 对 name = ‘l刘备’ 的二级索引记录的加锁过程如下：

  步骤1 ：读取在 (‘c曹操’, ‘x荀彧’] 扫描区间的第一条二级索引记录，也就是name = 'l刘备’的二级索引记录。
  步骤2 ：为 name = ‘l刘备’ 的二级索引记录加 S 型next-key锁。
  步骤3 ：本语句的索引下推条件是 name > ‘c曹操’ AND name <= ‘x荀彧’，因此 name = 'l刘备’的二级索引记录符合索引条件下推的条件。
  步骤4 ：因为读取的是二级索引，所以需要对记录进行回表操作，找到对应的聚簇索引，即 number = 1 的聚簇索引记录，然后为该聚簇索引记录加一个 S 型正经记录锁。
  步骤5 ：形成扫描区间 (‘c曹操’, ‘x荀彧’] 的边界条件是 name > ‘c曹操’ AND name <= ‘x荀彧’， name = ‘l刘备’ 的记录满足该边界条件。
  步骤6 ：server 层继续判断 name = ‘l刘备’ 的二级索引记录对应的聚簇索引记录是否符合条件 country != ‘吴’, 很显然符合，所以将其发送到客户端，并且不释放客户端上的锁。
  步骤7 ：获取 name = ‘l刘备’ 的二级索引记录所在单向链表的下一条记录，也就是 name = ‘s孙权’ 的二级索引记录。

• 对 name = ‘s孙权’ 的二级索引记录的加锁过程如下：

  步骤2 ：为 name = ‘s孙权’ 的二级索引记录加 S 型next-key锁。
  步骤3 ：本语句的索引下推条件是 name > ‘c曹操’ AND name <= ‘x荀彧’，因此 name = 's孙权’的二级索引记录符合索引条件下推的条件。
  步骤4 ：因为读取的是二级索引，所以需要对记录进行回表操作，找到对应的聚簇索引，即 number = 20 的聚簇索引记录，然后为该聚簇索引记录加一个 S 型正经记录锁。
  步骤5 ：形成扫描区间 (‘c曹操’, ‘x荀彧’] 的边界条件是 name > ‘c曹操’ AND name <= ‘x荀彧’， name = ‘s孙权’ 的记录满足该边界条件。
  步骤6 ：server 层继续判断 name = ‘s孙权’ 的二级索引记录对应的聚簇索引记录是否符合条件 country != ‘吴’, 很显然不符合，但是现在隔离级别是 REPEATABLE READ、SERIALIZABLE，所以不会释放掉加在该记录上的锁。
  步骤7 ：获取 name = ‘s孙权’ 的二级索引记录所在单向链表的下一条记录，也就是 name = ‘x荀彧’ 的二级索引记录。

• 对 name = ‘x荀彧’ 的二级索引记录的加锁过程如下：

  步骤2 ：为 name = ‘x荀彧’ 的二级索引记录加 S 型next-key锁。
  步骤3 ：本语句的索引下推条件是 name > ‘c曹操’ AND name <= ‘x荀彧’，因此 name = 'x荀彧’的二级索引记录符合索引条件下推的条件。
  步骤4 ：因为读取的是二级索引，所以需要对记录进行回表操作，找到对应的聚簇索引，即 number = 15 的聚簇索引记录，然后为该聚簇索引记录加一个 S 型正经记录锁。
  步骤5 ：形成扫描区间 (‘c曹操’, ‘x荀彧’] 的边界条件是 name > ‘c曹操’ AND name <= ‘x荀彧’， name = ‘x荀彧’ 的记录满足该边界条件。
  步骤6 ：server 层继续判断 name = ‘x荀彧’ 的二级索引记录对应的聚簇索引记录是否符合条件 country != ‘吴’, 很显然符合，所以将其发送到客户端，并且不释放客户端上的锁。
  步骤7 ：获取 name = ‘x荀彧’ 的二级索引记录所在单向链表的下一条记录，也就是 name = ‘z诸葛亮’ 的二级索引记录。

• 对 name = ‘z诸葛亮’ 的二级索引记录的加锁过程如下：

  步骤2 ：为 name = ‘z诸葛亮’ 的二级索引记录加 S 型正经记录锁。
  步骤3 ：本语句的索引下推条件是 name > ‘c曹操’ AND name <= ‘x荀彧’，因此 name = 'z诸葛亮’的二级索引记录不符合索引下推条件。由于它还不符合边界条件，所以就不再去找当前记录的下一条记录了，因此跳过步骤4 和 步骤5，直接向 server 层报告查询完毕
  步骤4 ：因为读取的是二级索引，所以需要对记录进行回表操作，找到对应的聚簇索引，即 number = 15 的聚簇索引记录，然后为该聚簇索引记录加一个 S 型正经记录锁。
  步骤5 ：步骤被跳过.
  步骤6 ：步骤被跳过。
  步骤7 ：server 层收到存储引擎层报关的 查询完毕 信息，结束查询。

综上，在 REPEATABLE READ、SERIALIZABLE 隔离级别下，该语句在执行过程中的加锁效果如图：

在上图中，执行语句对 name = ‘l刘备’、‘s孙权’、‘x荀彧’、‘z诸葛亮’ 的二级索引记录都加了 S 型next-key 锁, 对 number = 1、15、20 的聚簇索引记录加了 S 型正经记录锁。

对于锁定读语句来说，如果一条二级索引记录不符合索引条件下推中的条件，即使当前事务隔离级别为 READ UNCOMMITTED、READ COMITTED 也不会释放掉加在该记录上的锁。书中作者推测是 InnoDB 认为存储引擎不配拥有释放锁的权利。

2.3 UPDATE 语句

对应 UPATE 语句来说，加锁方式与 SELECT … FOR UPDATE 语句类似。不过如果更新了二级索引列，那么所有被更新的二级索引在更新之前都需要加 X 型正经记录锁。

以 下面语句为例：

UPDATE hero SET name = 'cao曹操' WHERE number > 1 AND number <= 15 AND country = '魏';

EXPLAIN 语句执行后如下：

mysql> EXPLAIN UPDATE hero SET name = 'cao曹操' WHERE number > 1 AND number <= 15 AND country = '魏';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | hero  | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    3 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.03 sec)

执行计划显示，这个查询语句在执行时，会扫描聚簇索引中 (1，15] 扫描区间中的记录，但是由于更新了 name 列，并且 name 列又是一个索引列，所以在更新前也需要为 idx_name 二级索引中对应的记录加锁。

在隔离级别为 READ UNCOMMITTED、READ COMITTED 时，该语句在执行过程中的加锁情况如下图：

在上图中， 对 number = 3 的聚簇索引记录来说，由于其不符合 country = ‘魏’ 这个条件，所以对该记录先加锁后释放锁。对于 number = 20 的聚簇索引来说，由于它不符合边界条件，所以对该记录先加锁后释放锁。另外 由于 name = ‘c曹操’、‘x荀彧’ 的二级索引也会被更新，所以也需要对他们加锁。

在隔离级别为 REPEATABLE READ、SERIALIZABLE 时，该语句在执行过程中的加锁情况如下图：

2.3 DELETE语句

对于 DELETE 语句来说，加锁方式与 SELECT … FOR UPDATE 类似，不过如果表中包含二级索引，那么二级索引记录在被删除之前都需要加 X 型正经记录锁。

对于 UPDATE 、DELETE 语句来说，在对被更新或者删除的二级索引记录加锁时，实际上加的是隐式锁，效果与 X 型正经记录锁一样。另外，对于隔离级别为 为 READ UNCOMMITTED、READ COMITTED 的情况，采用的是一种称为半一致性读的方式来执行 UPDATE 语句。

2.4 一些特殊情况

1. 当隔离级别为 READ UNCOMMITTED、READ COMITTED 时，如果匹配模式为精确匹配，则不会为扫描区间后面的下一条记录加锁。
   以下面SQL为例

   SELECT * FROM hero WHERE name = 'c曹操' FOR UPDATE;
   

   执行计划如下：

   mysql> EXPLAIN SELECT * FROM hero WHERE name = 'c曹操' FOR UPDATE;
   +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
   | id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
   +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
   |  1 | SIMPLE      | hero  | NULL       | ref  | idx_name      | idx_name | 303     | const |    1 |   100.00 | NULL  |
   +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
   1 row in set (0.02 sec)
   

   执行计划显示，查询优化器决定使用二级索引 idx_name，需要单点扫描区间 [‘c曹操’，‘c曹操’]中的二级索引记录。在读取完 name = ‘c曹操’ 的二级索引记录后，获取到下一条二级索引记录，也就是 name = 'l刘备’的二级索引记录。由于这里的匹配模式为精确匹配，因此存储引擎内部判断出该记录不符合精确匹配的条件，所以直接向 server 层报告 查询完毕 信息，而不是先给该记录加锁，然后再交给 server 层判断是否要释放锁。

   所以该语句在 隔离级别为 READ UNCOMMITTED、READ COMITTED 时的加锁情况如下：

   

2. 当隔离级别为 REPEATABLE READ、SERIALIZABLE 时，如果匹配模式为精确匹配，则会为扫描区间后面的下一条记录加 gap 锁。
   还是以下面SQL 为例

   SELECT * FROM hero WHERE name = 'c曹操' FOR UPDATE;
   

   执行计划显示，查询优化器决定使用二级索引 idx_name，需要单点扫描区间 [‘c曹操’，‘c曹操’]中的二级索引记录。在读取完 name = ‘c曹操’ 的二级索引记录后，获取到下一条二级索引记录，也就是 name = 'l刘备’的二级索引记录。由于这里的匹配模式为精确匹配，因此存储引擎内部判断出该记录不符合精确匹配的条件，所以所以向该记录加一个 gap 锁，然后再交给 server 层判断是否要释放锁。
   所以该语句在 隔离级别为 REPEATABLE READ、SERIALIZABLE 时的加锁情况如下：

   

   有时，扫描区间中没有记录，那么也要为扫描区间后面的下一条记录加一个 gap 锁，如下：

   SELECT * FROM hero WHERE name = 'g关羽' FOR UPDATE;
   

   执行计划显示，查询优化器决定使用二级索引 idx_name，需要单点扫描区间 [‘g关羽’，‘g关羽’] 中的二级索引记录，但并不存在 name = ‘g关羽’ 的二级索引记录，所以需要为 [‘g关羽’，‘g关羽’]扫描区间后面的下一条记录（name = 'l刘备’的记录）加 gap 锁，目的是为了防止别的记录插入值在（‘c曹操’，‘l刘备’）之间的二级索引记录。
   所以该语句在 隔离级别为 REPEATABLE READ、SERIALIZABLE 时的加锁情况如下：

3. 当隔离级别为 REPEATABLE READ、SERIALIZABLE 时，如果匹配模式不为景区匹配，并且没有找到匹配的记录，则会为该扫描区间后面的下一条记录加 next-key 锁。
   以下面SQL为例

   SELECT * FROM hero WHERE name > 'd' and name < 'l' FOR UPDATE;
   

   执行计划如下：

   mysql> EXPLAIN SELECT * FROM hero WHERE name > 'd' and name < 'l' FOR UPDATE;
   +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
   | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
   +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
   |  1 | SIMPLE      | hero  | NULL       | range | idx_name      | idx_name | 303     | NULL |    1 |   100.00 | Using index condition |
   +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
   1 row in set (0.02 sec)
   

   执行计划显示，查询优化器决定使用二级索引 idx_name，需要单点扫描区间 (‘d’，‘l’) 中的二级索引记录，但并不存在满足条件的记录，所以需要为 (‘d’，‘l’) 扫描区间后面的下一条记录（name = ‘l刘备’ 的记录）加上 next-key 锁。

   所以该语句在 隔离级别为 REPEATABLE READ、SERIALIZABLE 时的加锁情况如下：
   

4. 当隔离级别为 REPEATABLE READ、SERIALIZABLE 时，如果使用的是聚簇索引，并且扫描的扫描区间是左闭区间，而且定位到的第一条聚簇索引记录的 number 值正好与扫描区间中最小的值相同，则会为该聚簇索引记录加正经记录锁。
   以下面SQL为例

   SELECT * FROM hero WHERE number > 8 FOR UPDATE;
   

   执行计划如下：

   mysql> EXPLAIN SELECT * FROM hero WHERE number > 8 FOR UPDATE;
   +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
   | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
   +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
   |  1 | SIMPLE      | hero  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
   +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
   1 row in set (0.03 sec)
   

   执行计划显示，查询优化器决定使用聚簇索引，需要扫描扫描区间 [8, +∞) 中的聚簇索引记录，由于 [8, +∞) 是左闭区间，而表中正好存在一个 number = 8 的记录，所以会对这条 number = 8 的聚簇索引记录只添加正经记录锁。
   所以该语句在 隔离级别为 REPEATABLE READ、SERIALIZABLE 时的加锁情况如下：
   

   在上图中可以看到，为 number = 8 的聚簇索引记录加了正经记录锁，为扫描到的其他记录加了 next-key锁。需要注意的是，该语句还为 Supremum 记录加了 next-key 锁，这样就可以组织其他语句插入 number 在 (20，+∞) 件的记录了。
   之所以这样是为了避免误伤，因为主键不可重复的特性，所以表中不会再出现 number = 8 的记录，所以我们只需要在 number = 8 的聚簇索引记录上加一个正经记录锁，而不必要为其加一个 next-key 锁。

5. 无论哪种隔离级别要是唯一性搜索，并且读取的记录没有被标记为 已删除（记录头信息中的 deleted_flag 为 1），就位读取到的记录加正经记录锁。
   以下面SQL为例

   SELECT * FROM hero WHERE number = 8 FOR UPDATE;
   

   执行计划如下：

   mysql> EXPLAIN SELECT * FROM hero WHERE number = 8 FOR UPDATE;
   +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
   | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
   +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
   |  1 | SIMPLE      | hero  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
   +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
   1 row in set (0.02 sec)
   

执行计划显示，查询优化器决定使用聚簇索引，需要扫描扫描区间 [8,8] 中的聚簇索引记录。由于是唯一性搜索，所以只需要为 number = 8 的聚簇索引记录添加正经记录锁。在隔离级别为 REPEATABLE READ、SERIALIZABLE 时，该语句执行时的加锁情况如下：

6. 当隔离级别为 REPEATABLE READ、SERIALIZABLE 时，如果是按照从右往左扫描扫描区间中的记录时，就会给匹配到的第一条记录的下一条记录加 gap 锁。
   以下面SQL为例

   SELECT * FROM hero FORCE INDEX(idx_name) WHERE name > 'c曹操' AND name <= 'x荀彧' AND country != '吴' ORDER BY name DESC FOR UPDATE;
   

   执行计划如下：

   mysql> EXPLAIN SELECT * FROM hero FORCE INDEX(idx_name) WHERE name > 'c曹操' AND name <= 'x荀彧' AND country != '吴' ORDER BY name DESC FOR UPDATE;
   +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
   | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                              |
   +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
   |  1 | SIMPLE      | hero  | NULL       | range | idx_name      | idx_name | 303     | NULL |    4 |    80.00 | Using index condition; Using where |
   +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
   1 row in set (0.03 sec)
   

执行计划显示，查询优化器决定使用二级索引 idx_name, 需要扫描的扫描区间 （‘c曹操’, ‘x荀彧’] 中的二级索引记录。由于语句中包含 ORDER BY name DESC ，也就是需要从大到小排序，因此可以在扫描扫描区间 （‘c曹操’, ‘x荀彧’] 中的二级索引记录时，直接定位到该扫描区间的最后一条记录，也就是 name = ‘x荀彧’ 的二级索引记录，然后按照从右到左的顺序进行扫描即可。不过在定位到 name = ‘x荀彧’ 的二级索引记录后，需要对该记录所在单向链表的下一条二级索引记录（name = ‘z诸葛亮’）加一个gap锁（目的是防止其他事务插入 name = ‘x荀彧’ 的新记录）。在隔离级别为 REPEATABLE READ、SERIALIZABLE 时，该语句执行时的加锁情况如下：

3. 半一致性读

半一致性读（ Semi-Consistent Read）是一种夹在一致性读和锁定读之间的读取方式。当隔离级别 为READ UNCOMMITTED、READ COMITTED 且执行 UPDATE 语句时将使用半一致性读。所谓半一致性读，就是当 UPDATE 语句读取到已经被其他事务加 X 锁的记录时， InnoDB 会将该记录的最新提交版本读取出来，然后判断该版本是否与 UPDATE 语句中的搜索条件相匹配，如果不匹配则不对该记录加锁，从而跳到下一条记录，如果匹配，则再次读取该记录并对其进行加锁。这样处理的目的是为了让 UPDATE 语句尽量少被别的语句阻塞。

以下面为例：
假如 事务 T1 隔离级别为 READ COMITTED ，T1 执行如下语句：

SELECT * FROM hero WHERE number = 8 FOR UPDATE;

该语句在执行时对 number = 8 的聚簇索引记录加了 X 型正经记录锁，如下图：

此时事务 T2 也为 READ COMITTED 隔离级别，T2 执行如下语句

UPDATE hero SET name = 'cao曹操' WHERE number >= 8 AND number < 20 AND  country != '魏';

该语句在执行时需要依次会哦去 number = 8, 15, 20 的聚簇索引记录的 X 型正经记录锁（number = 20 的记录的锁会稍后释放）。由于 T1 已经获取了 number = 8 的聚簇索引记录的 X 型正经记录锁，按理说此时 T2 应该由于获取不到 number = 8 的聚簇索引记录的 X 型正经记录锁而阻塞。但由于进行的事半一致性读，所以存储引擎会先获得 number = 8 的聚簇索引记录最新提交的版本并返回给 server 层。该版本的 country 值为 ‘魏’，不符合 country != ‘魏’ 的条件，所以 server 层决定放弃获取 number = 8 的聚簇索引记录上的 X型正经记录锁，转而让存储引擎读取下一条记录。

4. INSERT 语句

INSERT 语句一般情况下不需要在内存中生成锁结构，单纯依靠隐式锁保护插入的记录。不过当前事务在插入一条记录前，需要先定位到该记录在 B+Tree 中的位置。如果该位置的下一条记录已经被加了 gap 锁 或 next-key 锁，那么当前事务就会为该记录集上一种类型为 插入意向锁的锁，并且事务进入等待状态。

下面看在执行 INSERT 语句时，会在内存中生成锁结构的两种特殊情况。

4.1 遇到重复键

在插入一条新纪录时，首先要做的就是确认新纪录应该插入到 B+Tree 的哪个位置。如果在确定位置发现重复的主键或唯一二级索引，此时会报错。在生成报错信息前，还需要对聚簇索引或唯一二级索引中重复唯一键的记录加 S 锁（主键重复则在聚簇索引上加锁，唯一二级索引重复则在唯一二级索引上加锁），加锁的具体类型如下：

• 在隔离级别为 READ UNCOMMITTED、READ COMITTED 时，加的是 S 型正经记录锁
• 在隔离级别为 REPEATABLE READ、SERIALIZABLE 时，加的是 next-key 锁

另外，如果通过 INSERT…ON DUPLICATE KEY… 的语法插入记录时，如果遇到主键或唯一二级索引重复，会在B+Tree中对已经存在的相同的键值记录加 X 锁，而不是S 锁。

4.2 外键检测

InnoDB 是支持外键存储的，在向子表插入时，存在两种情况

• 插入的外键在父表中有对应记录 ： 在插入成功之前，无论什么隔离级别都只需要直接给 父表 中关联的记录加一个 S 型正经记录锁即可。
• 插入的外键在父表中没有对应记录 ：此时会插入失败，但过程中也有可能加锁。如果隔离级别是 READ UNCOMMITTED、READ COMITTED 时，并不对记录加锁。如果隔离级别是 REPEATABLE READ、SERIALIZABLE 时，加的锁 gap锁。

七、参考内容

书籍：《MySQL是怎样运行的——从根儿上理解MySQL》、《MySQL技术内幕 InnoDB存储引擎 》
https://blog.csdn.net/filling_l/article/details/112854716

如有侵扰，联系删除。 内容仅用于自我记录学习使用。如有错误，欢迎指正