MySQL-原理篇-④锁

学习核心

• MySQL并发事务访问相同记录

  • 读读、读写、写读、写写场景分析
  • 并发问题的解决方案（为何引入锁？）

• MySQL锁分类（*）

  • 全局锁
  • 表级锁
    • 表锁
    • 元数据锁（MDL）
      • MDL 核心概念
      • 线上加索引可能引发的事故和解决方案
        • 在线上增加索引，会发生什么？
        • 如何安全地在线上给表增加索引？
    • 意向锁
    • AUTO-INC锁
  • 行级锁（*）
    • MyISAM存储引擎有行级锁吗？
    • 行级锁有哪些？
    • InnoDB是如何加行级锁的？

• InnoDB中锁的内存结构

• 死锁（*）

  • 死锁是怎么发生的？
  • 发生死锁的条件？
  • 如何排查死锁问题？
  • 如何避免死锁问题？

• 悲观锁和乐观锁

  • MySQL的悲观锁、乐观锁概念解析
  • MySQL如何实现乐观锁？

学习资料

• MySQL是怎样运行的：从根儿上理解MySQL =》第25章 锁

• MDL锁相关

  • 线上版本加索引可能引发的事故（场景案例分析）
  • 在线修改表结构

• 死锁相关

  • 加了什么锁导致死锁？
  • 排查死锁
  • 解决死锁之路

• 悲观锁、乐观锁

  • 悲观锁与乐观锁的实现
  • 数据库中的悲观锁和乐观锁

MySQL并发事务访问相同记录

1.并发场景分析

​ MySQL的读写并发问题通常涉及到事务隔离级别、锁定机制和MVCC（多版本并发控制），结合场景理解学习

​ 锁是计算机协调多个进程或者线程并发访问某一资源的机制。在程序开发中会存在多线程同步的问题，通过加锁是为了确保数据的一致性，这个思想在数据库领域中同样重要。MySQL中锁机制的引入为实现MySQL的各个隔离级别提供了保证，与此同时锁冲突也是影响数据库并发访问性能的一个重要因素。

​ MySQL中并发事务访问相同记录的情况可以大致分为3种：

• 读-读：并发事务相继读取相同的记录（读操作本身不会对记录有任何影响，并不会引起什么问题，所以允许这种情况的发生）
• 写-写：并发事务相继对相同的记录做出改动（在多个未提交事务相继对一条记录做改动时，需要让这些事务排队执行 ，这个排队的过程其实是通过锁来实现的）
• 读-写 或 写-读：即一个事务进行读取操作，另一个事务进行改动操作。这种情况下可能会发生脏读、不可重复读、虚读/幻读等问题（各个数据库厂商对 SQL标准 的支持都可能不一样，例如MySQL在【可重复读】隔离级别上就已经解决了部分的幻读场景问题）

2.并发问题的解决方案

​ 针对不同并发场景，拆解数据库层面是如何处理并发问题的。（由于读-读是记录读取操作，不涉及数据变更，因此并发读并没有什么并发影响，是被允许的）

写-写（如何解决脏写/丢失更新问题？）

​ 写-写：指的是并发事务相继对相同的记录做出改动。基于这种场景可能会出现脏写（丢失更新）的情况。

• 所谓脏写/丢失更新：
  • 第一类丢失更新：撤销rollback一个事务时，把其他事务已经提交更新的数据回滚掉了
  • 第二类丢失更新：提交commit一个事务时，把其他事务已经提交更新的数据覆盖掉了

​ 实际上，不管数据库的哪个隔离级别都不会允许这种脏写的问题出现，而是通过加锁排队修改的方案来避免脏写。因此在多个未提交事务相继对一条记录做改动时，需要让这些事务排队执行 ，这个排队的过程其实是通过锁来实现的。

​ 以T1、T2事务为例，假设事务T1、T2同时对同一条记录进行修改操作，结合下述图示理解是如何通过加锁排队修改来避免脏写情况

• 【1】事务执行前：没有锁结构和记录关联
• 【2】T1事务请求改动记录，生成锁结构与记录关联。此处对应所结构可能还包含很多其他的信息，以核心信息进行拆解：
  • trx信息：关联事务（代表这个所结构是哪个事务生成的）
  • is_waiting：代表当前事务是否在等待
    • 若为true：等待中，此时事务并没有获取到锁，不可以继续执行操作
    • 若为false：此时事务成功获取到锁，无需等待，可以继续进行操作
• 【3】此时T2也请求改动记录，相应生成锁结构与记录关联，并进入等待状态（因为T1已经成功获取到锁，并且还没执行结束释放锁，因此其他事务需要进入排队等待状态）
• 【4】事务T1提交之后就会将事务生成的锁结构释放掉，然后继续检查是否还有其他事务在等待。此时其发现事务T2在等待获取锁，就会设置其is_waiting为false以唤醒对应线程并继续执行操作

如何解决脏读、不可重复读、虚读/幻读问题？

（1）方案1：读操作-MVCC、写操作-加锁

​ 所谓MVCC（多版本并发控制）：生成一个Read View，通过Read View找到符合条件的记录版本（(历史版本由undo日志构建)。查询语句只能读到在生成Read View之前已提交事务所做的更改，在生成Read View之前未提交的事务或者之后才开启的事务所做的更改是看不到的。而写操作肯定针对的是最新版本的记录，读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用MVCC时，读-写操作并不冲突

​ 普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录

• 避免脏读问题：在【READ COMMITTED】隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一个Read View，Read View的存在本身就保证了事务不可以读取到未提交的事务所做的更改 ，即避免了脏读现象
• 避免不可重复读和部分幻读问题：在【REPEATABLE READ】隔离级别下
  • Read View 的引入本身就保证了事务不可以读取到未提交的事务所做的更改 ，即避免了脏读现象
  • Read View + undo log：一个事务在执行过程中只有 第一次执行SELECT操作 才会生成一个Read View，之后的SELECT操作都 复用 这个Read View，则避免了不可重复读问题
  • 虚读/幻读问题：通过【MVCC + next-key lock】解决部分幻读问题
    • 针对快照读（普通 select 语句）， 通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题
    • 针对当前读（select ... for update 等语句），通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围

（2）方案2：读写操作都采用加锁方式

​ 从脏读、不可重复读、虚读/幻读的定义理解并发衍生问题产生的场景，然后找定加锁的时机来解决问题。

• 脏读：其产生是因为当前事务读取了另一个未提交事务写的一条记录
  • 如果另一个事务在写记录的时候就给这条记录加锁，那么当前事务就无法继续读取该记录了，也就不会有脏读问题的产生
• 不可重复读：其产生是因为同一个事务在不同时刻读取同一条数据发现前后数据不一致，是由于在这个期间有其他事务对该记录提交了修改操作导致
  • 如果在当前事务读取记录时就给该记录加锁，那么另一个事务就无法修改该记录，也就不会发生不可重复读
• 虚读/幻读：其产生是因为同一个事务在不同时刻访问同一个数据集（一个范围数据）发现前后数据集不一致，是由于在这个期间有其他事务提交了新增或删除操作导致
  • 采用加锁的方式解决幻读问题会有一点小麻烦，以新增操作来分析，事务在第一次读取记录的时候幻影记录并不存在，因此实际上并不明确要给读取的哪些记录加锁，因此设定了临键锁来解决场景问题

锁类型

核心概念

​ MySQL根据加锁范围可以分为全局锁、表级锁、行级锁三类

• 全局锁
  • FTWRL
• 表级锁
  • 表锁
  • 元数据锁
  • 意向锁
  • AUTO-INC锁
• 行级锁
  • Record Lock
  • Gap Lock
  • Next-Key Lock

1.全局锁

全局锁如何使用？

# 使用全局锁，执行命令，执行后整个数据库处于只读状态
flush tables with read lock;
# 释放全局锁
unlock tables；

​ 当执行加全局锁命令之后，整个数据库就处于只读状态，这时其他线程执行以下操作，都会被阻塞：

• 对数据的增删改操作，比如 insert、delete、update等语句；
• 对表结构的更改操作，比如 alter table、drop table 等语句；

​ 当执行释放全局锁命令或者会话断开的时候全局锁会被释放。

全局锁的应用场景？

​ 全局锁主要应用于做全库逻辑备份，基于此应用，在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样

​ ==场景分析：==在全库逻辑备份期间，假设不加全局锁的场景，看看会出现什么意外的情况。如果在全库逻辑备份期间，有用户购买了一件商品，一般购买商品的业务逻辑是会涉及到多张数据库表的更新，比如在用户表更新该用户的余额，然后在商品表更新被购买的商品的库存。那么，有可能出现这样的顺序：（在备份用户表和商品表之间，有用户购买了商品）

• 先备份了用户表的数据；
• 然后这期间有用户发起了购买商品的操作；
• 接着再备份商品表的数据；

​ 这种情况下，备份的结果是用户表中该用户的余额并没有扣除，反而商品表中该商品的库存被减少了，如果后面用这个备份文件恢复数据库数据的话，用户钱没少，而库存少了，等于用户白嫖了一件商品。所以，在全库逻辑备份期间，加上全局锁，用于避免全库数据备份期间的因为业务触发的多表更新导致备份数据前后不一致

​

加全局锁的缺点？

​ 加上全局锁，意味着整个数据库都是只读状态。那么如果数据库里有很多数据，备份就会花费很多的时间，关键是备份期间，业务只能读数据，而不能更新数据，这样会造成业务停滞

​ 由于全数据库备份需要一定的时间，加上全局锁意味着整个数据库都是只读状态，则在备份期间业务只能读取数据不能更新数据，就会造成业务停滞

备份数据库数据的时候使用全局锁会影响业务的话，有什么其他方式可以避免？

​ 最硬核的思路或许就是避免在业务使用高峰期做数据库备份操作，一定程度上减少对业务的影响。但是回归问题本质，还需要考虑在这一场景中如何让其他事务平滑写入而不受影响，如果不使用全局锁，此处可从事务的隔离级别切入“可重复读”隔离级别：在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。因为在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据

​ 备份数据库的工具是mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎（例如MySQL的InnoDB存储引擎默认的隔离级别是可重复读）

​ 但是，对于 MyISAM 这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法

2.表级锁

​ MySQL中表级锁有：表锁、元数据锁（MDL）、意向锁、AUTO-INC锁

表锁

​ 表锁包括共享锁（读锁）、独占锁（写锁），表锁除了会限制其他线程的读写操作之外，也会限制接本线程接下来的读写操作

# 对t_student表加锁
// 表级别的共享锁（读锁）；
lock tables t_student read;

// 表级别的独占锁（写锁）；
lock tables t_stuent write;

// 释放锁（通过命令释放当前会话的所有表锁）
unlock tables；

// 当前会话退出后也会释放所有表锁

​ 即如果本线程对学生表加了「共享表锁」，那么本线程接下来如果要对 t_student 执行写操作的语句，是会被阻塞的，当然其他线程对 t_student 进行写操作时也会被阻塞，直到锁被释放。

​ 一般建议尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁

元数据锁（MDL）

​ 元数据锁（MDL），不需要显式使用 MDL，因为当对数据库表进行操作时，会自动给这个表加上 MDL：

• 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
• 对一张表做结构变更操作的时候，加的是 MDL 写锁；

​ MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

​ 当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。反之，当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）

MDL不需要显式调用，那么它是什么时候释放的？

​ MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

​ 场景分析：如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没提交），那在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景：

• 首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁；
• 然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；
• 接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，
• 在线程 C 阻塞后，后续有对该表的 select 语句，都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了

基于上述场景，分析为什么线程 C 因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也会被阻塞？

​ 因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

​ 因此为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更

意向锁

​ 意向锁：分为意向共享锁、意向独占锁，两者都是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突

• 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」（S锁）之前，需要先在表级别加上一个「意向共享锁」（Intention Shared Lock，IS锁）；
• 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」（X锁）之前，需要先在表级别加上一个「意向独占锁」（Intention Exclusive Lock，IX锁）；

即：当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁；

对于InnoDB存储引擎而言，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的（即普通的 select 是不会加行级锁）

但 select 也是可以对记录加共享锁和独占锁的，具体方式如下：

//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

​ 表锁和行锁是满足读读共享、读写互斥、写写互斥的。

​ 如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

​ 那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

​ 所以，意向锁的目的是为了快速判断表里是否有记录被加锁，以避免用遍历的方式来查看表中有没有上锁的记录（其实IS锁与IX锁是兼容的、IX锁与IX锁是兼容的）

AUTO-INC锁

​ AUTO-INC 锁用于主键自增场景中的数据插入，在插入数据时会加表级别的AUTO-INC 锁，待插入语句执行完成后会将AUTO-INC 锁释放掉，当存在大量数据插入的场景时插入性能会受到影响（其他事务的插入操作会被阻塞）

​ 表里的主键通常都会设置成自增的（可通过对主键字段声明 AUTO_INCREMENT 属性实现），之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

​ AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。

​ 在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。

​ 那么，一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。但是， AUTO-INC 锁在对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。

如何解决大数据量插入场景使用AUTO-INC锁导致的性能阻塞问题

​ 为了解决大数据量插入的性能阻塞问题， 在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

​ InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。

• 当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；
• 当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。
• 当 innodb_autoinc_lock_mode = 1：
  • 普通 insert 语句，自增锁在申请之后就马上释放；
  • 类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

当 innodb_autoinc_lock_mode = 2 是性能最高的方式，但是当搭配 binlog 的日志格式是 statement 一起使用的时候，在「主从复制的场景」中会发生数据不一致的问题。

innodb_autoinc_lock_mode参数配置场景分析

​ 以下述场景进行分析：session A 往表 t 中插入了 4 行数据，session B创建了一个相同结构的表 t2，然后两个 session 同时执行向表 t2 中插入数据

步骤	session A	session B
1	创建表t并初始化数据 insert into t values(null,1,1); insert into t values(null,2,2); insert into t values(null,3,3); insert into t values(null,4,4);
2		创建表t2 create table t2 like t;
3	insert into t2 values(null,5,5);	insert into t2(c,d) select c,d from t;

如果 innodb_autoinc_lock_mode = 2，意味着「申请自增主键后就释放锁，不必等插入语句执行完」。那么就可能出现这样的情况：

• 步骤3中假设：session B 先插入了两个记录，(1,1,1)、(2,2,2)；（session B 插入的数据是从t表中读取的）
• 与此同时，session A 申请自增 id 得到 id=3，插入了（3,5,5)；（session B虽然先占据锁，但是其申请完主键id之后就立刻释放了，因此此时session A不需要等待步骤3中sessionB的insert语句全部执行完成，可以“见缝插针”申请下一个主键id，例如申请了id为3并执行insert操作）
• 随后，session B 继续执行，插入两条记录 (4,3,3)、 (5,4,4)

​ 基于上述步骤分析，可以看到session B 的insert语句生成的id并不连续。当「主库」发生了这种情况，binlog 面对 t2 表的更新只会记录这两个 session 的 insert 语句，如果 binlog_format=statement，记录的语句就是原始语句。记录的顺序要么先记 session A 的 insert 语句，要么先记 session B 的 insert 语句。

​ 但不论是哪一种，这个 binlog 拿去「从库」执行，这时从库是按「顺序」执行语句的，只有当执行完一条 SQL 语句后，才会执行下一条 SQL。因此，在从库上「不会」发生像主库那样两个 session 「同时」执行向表 t2 中插入数据的场景（也就不会复现上述假设的主库数据插入场景），假设备库中执行了sessionB的insert语句，会发现生成的结果中id都是连续的，也就说明出现了主从库数据不一致的场景

​ 如果备库中直接执行sessionA、sessionB中的insert语句，无论哪个先后，可能最终执行的结果都一样（按照原始语句插入5条数据），但实际上假设只执行sessionB的insert语句，就会出现上述主从表数据不一致的场景（例如原主表sessionB执行insert语句生成的记录ID应该是不连续的，但是从表执行后却生成了连续的记录ID，很明显是个隐藏的问题）

​ 为了解决上述场景中主从复制数据不一致的问题，可以调整 binlog 日志格式要设置为 row，以实现 binlog 里面记录的是主库分配的自增值，到备库执行的时候，主库的自增值是什么，从库的自增值就是什么

​ 当innodb_autoinc_lock_mode = 2 时，结合设定 binlog_format = row，既能提升并发性，又不会出现数据一致性问题

3.行级锁

​ InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁

​ 在前面的事务相关学习（MVCC实现原理）中，普通的 select 语句是不会对记录加锁的，因为它属于快照读。如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读

//对读取的记录加共享锁
select ... lock in share mode;

//对读取的记录加独占锁
select ... for update;

​ 上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句时，要加上 begin、start transaction 或者 set autocommit = 0

• 记录锁区分S、X锁
  • S: Shared Locks，共享锁 ；X: Exclusive Locks，排他锁(独占锁)
  • 共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥、读写互斥

行级锁的类型主要有三类：

• Record Lock（记录锁）：也就是仅仅把一条记录锁上；
• Gap Lock（间隙锁）：锁定一个范围，但是不包含记录本身；
• Next-Key Lock（Record Lock + Gap Lock 的组合）：锁定一个范围，并且锁定记录本身

Record Lock（记录锁）

​ Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：记录锁：读读兼容、读写不兼容、写读不兼容、写写不兼容

• 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
• 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。

# 执行语句
begin
select * from t_test where id = 2 for update; # 对t_test表中主键id为1的记录加上X型的记录锁，其他事务无法对这条记做修改操作

# 当事务执行commit后，事务过程中生成的锁都会被释放

Gap Lock（间隙锁）

​ Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象（在事务原理篇章学习中，MySQL的InnoDB存储引擎中引入可重复读隔离级别，通过Read View+Next-Key Lock来解决部分幻读问题（其中Next-Key Lock为Record Lock + Gap Lock 的组合））

​ 假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生

​ 间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的

Next-Key Lock（临键锁：Record Lock + Gap Lock 的组合）

​ Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

​ 假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录

​ next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

​ next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

​ 比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞。

​ 临键锁的S、X关系可从其定义出发去理解，它Record Lock + Gap Lock 的组合，自然也就遵守两边的规则，虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的

插入意向锁（一种特殊的间隙锁）

​ 一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。

​ 如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

​ 举个例子，假设事务 A 已经对表加了一个范围 id 为（3，5）间隙锁。

​ 当事务 A 还没提交的时候，事务 B 向该表插入一条 id = 4 的新记录，这时会判断到插入的位置已经被事务 A 加了间隙锁，于是事务 B 会生成一个插入意向锁，然后将锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），此时事务 B 就会发生阻塞，直到事务 A 提交了事务。

​ 插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。

​ 如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

​ 插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）

InnoDB中锁的内存结构

​ 基于前面对锁的简单理解：对一条记录加锁的本质就是在内存中创建一个锁结构和记录进行管理。思考一种场景：如果一个事务对多条记录加锁，是否就要创建多个锁结构呢？以下述语句为例

# 事务T1：执行下述语句需为hero表中所有记录进行加锁
SELECT * FROM hero LOCK IN SHARE MODE;

​ 从理论上分析对每条记录创建多个锁结构的思路是没有问题的，但切换到现实场景，如果记录量特别大的话，要生成大量类似的锁结构必然会占据一定的内存消耗。因此InnoDB的设计者据此进行了设计，在对不同记录加锁时，如果符合以下条件，则这些记录的锁就可以被放到一个锁结构中

• 在同一个事务中进行加锁操作
• 被加锁的记录在同一个页面中
• 加锁的类型是一样的
• 等待状态是一样的

​ 也就抽象出InnoDB存储引擎中的所结构设计：

结构属性	说明
锁所在的事务信息	不论是表锁还是行锁，都是在事务执行过程中生成的，哪个事务生成了这个锁结构，这里就记载着这个事务的信息
索引信息	对于行锁来说，需要记录一下加锁的记录是属于哪个索引的
表锁／行锁信息	表锁结构和行锁结构在这个位置的内容是不同的： 表锁：记录是对哪个表加的锁，以及一些其他信息 行锁：行锁特有还记录了三个重要信息： - Space ID：记录所在表空间 - Page Number：记录所在页号 - n_bits：对于行锁来说，一条记录就对应着一个比特位，一个页面中包含很多记录，用不同的比特位来区分到底是哪一条记录加了锁。为此在行锁结构的末尾放置了一堆比特位，这个n_bits属性代表使用了多少比特位
type_mode	一个32位的数，被分成了lock_mode、lock_type和rec_lock_type三个部分
其他信息	为了更好的管理系统运行过程中生成的各种锁结构而设计了各种哈希表和链表
一堆比特位	如果是行锁结构的话，在该结构末尾还放置了一堆比特位，比特位的数量是由上面提到的n_bits属性表示的

【type_mode】属性解析

​ type_mode是一个32位的数，被分成了lock_mode、lock_type和rec_lock_type三个部分

• 锁的模式（lock_mode），占用低4位（从右往左），可选的值如下：

  • LOCK_IS（十进制的0）：表示共享意向锁，也就是IS锁

  • LOCK_IX（十进制的1）：表示独占意向锁，也就是IX锁

  • LOCK_S（十进制的2）：表示共享锁，也就是S锁

  • LOCK_X（十进制的3）：表示独占锁，也就是X锁

  • LOCK_AUTO_INC（十进制的4）：表示AUTO-INC锁

• 锁的类型（lock_type），占用第5～8位，不过现阶段只有第5位和第6位被使用：

  • LOCK_TABLE（十进制的16），也就是当第5个比特位置为1时，表示表级锁
  • LOCK_REC（十进制的32），也就是当第6个比特位置为1时，表示行级锁

• 行锁的具体类型（rec_lock_type），使用其余的位来表示。只有在lock_type的值为LOCK_REC时，也就是只有在该锁为行级锁时，才会被细分为更多的类型：

  • LOCK_ORDINARY（十进制的0）：表示next-key锁。

  • LOCK_GAP（十进制的512）：也就是当第10个比特位置为1时，表示gap锁。

  • LOCK_REC_NOT_GAP（十进制的1024）：也就是当第11个比特位置为1时，表示正经记录锁。

  • LOCK_INSERT_INTENTION（十进制的2048）：也就是当第12个比特位置为1时，表示插入意向锁。

  • 其他的类型：还有一些不常用的类型我们就不多说了。

    怎么还没看见is_waiting属性呢？这主要还是设计InnoDB的大佬太抠门了，一个比特位也不想浪费，所以他们把is_waiting属性也放到了type_mode这个32位的数字中：

  • LOCK_WAIT（十进制的256） ：也就是当第9个比特位置为1时，表示is_waiting为true，也就是当前事务尚未获取到锁，处在等待状态；当这个比特位为0时，表示is_waiting为false，也就是当前事务获取锁成功

MySQL中select语句是如何加行级锁的？（8.0.21）

​ 行级锁加锁规则比较复杂，不同的场景，加锁的形式是不同的。加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。但是，next-key lock 在一些场景下会退化成记录锁或间隙锁（在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成记录锁或间隙锁）。

​ 换个角度思考，在拆解一些场景下next-key lock为什么会退化成记录锁或者间隙锁，就去分析这些场景下如果可以使用记录锁或者间隙锁就能避免幻读现象，则符合退化场景

案例准备

​ 以基础案例为范文拆解不同场景中MySQL是如何加行级锁的（MySQL 8.0.21、默认隔离级别【可重复读】）不同版本可能加锁规则不同，但万变不离其宗

CREATE TABLE `t_user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `age` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `t_user` (`id`, `name`, `age`) VALUES ('1', '路飞', '19');
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES ('5', '索隆', '21');
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES ('10', '山治', '22');
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES ('15', '乌索普', '20');
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES ('20', '香克斯', '39');

1.唯一索引

等值查询

当用唯一索引进行等值查询的时候，查询的记录存不存在，加锁的规则也会不同：

• 当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。
• 当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」

​ 此处「唯一索引」是用「主键索引」作为案例说明的，加锁只加在主键索引项上。

​ 如果是用二级索引（不管是否为唯一索引）进行锁定读查询的时候，除了会对二级索引项加行级锁（如果是唯一索引的二级索引，加锁规则和主键索引的案例相同），而且还会对查询到的记录的主键索引项上加「记录锁」

​ 即：唯一索引等值查询分为两种情况

• 如果是主键索引：只在主键索引上加锁
• 如果是普通的唯一索引：二级索引项加行级锁（规则与给主键索引加锁相同），如果记录存在还会对查询到的记录主键索引项加上记录锁

（1）记录存在的情况

# 【1】事务1：开启事务对指定行记录加锁
begin;
select * from user where id = 1 for update;

# 【2】事务2：此时事务1还没提交，事务2修改指定行记录时受到阻塞
update t_user set name = '小白' where id = 1;

# 【3】事务1：执行完成释放锁，此时事务2获取到锁便可继续进行操作（如果长时间没有获取到锁则该操作执行会失败，如果一定时间范围内获取到锁则继续执行修改操作）
commit; # 事务1提交，释放锁

​ 执行步骤【1】中语句，事务A会为id为1的记录加X型的记录锁。接下来如果有其他事务（例如上图中的事务2）对同一条记录进行更新或者删除操作的话，这些操作都会被阻塞（因为更新、删除操作也会对记录添加X型的记录锁，而X锁与X锁是互斥关系）

事务1	事务2	事务n
begin; # 开启事务
select * from user where id = 1 for update;（对行记录加锁）
	update t_user set name = '小白' where id = 1;（阻塞）
		delete from t_user where id = 1;（阻塞）

如何分析加了什么锁？（什么命令可以分析？）

​ 可通过select * from performance_schema.data_locks;语句查看事务执行SQL过程中加了什么锁

# 窗口1：开启事务
begin;
select * from user where id = 1 for update;

# 窗口2：分析事务执行SQL过程中加了什么锁（可通过跟踪LOCK_TYPE\LOCK_MODE属性）
select * from performance_schema.data_locks;
- LOCK_TYPE:  LOCK_MODE
- 表级锁：     X型的意向锁
- 行级锁：     X型的记录锁

​ 重点关注上述的LOCK_TYPE、LOCK_MODE属性

• LOCK_TYPE
  • TABLE：表级锁
  • RECORD：行级锁
• LOCK_MODE
  • X：next-key 锁
  • X,REC_NOT_GAP：记录锁
  • X,GAP：间隙锁

​ 由上述结果分析，此时事务1在id=1的主键索引上加的是记录锁，锁住的范围是id为1的这条记录，其他事务无法对id=1的记录做更新和删除操作

​ 据此可知加锁的对象是针对索引，因为查询语句扫描的 B+ 树是聚簇索引树，即主键索引树，所以是对主键索引加锁。将对应记录的主键索引加 记录锁后，就意味着其他事务无法对该记录进行更新和删除操作了

为什么唯一索引等值查询并且查询记录存在的场景下，该记录的索引中的 next-key lock 会退化成记录锁？

​ 在唯一索引等值查询并且查询记录存在的场景下，仅靠记录锁也能避免幻读的问题。

​ 幻读的定义就是，当一个事务前后两次查询的结果集，出现前后不相同时，就认为发生幻读。所以，要避免幻读就是避免结果集某一条记录被其他事务删除，或者有其他事务插入了一条新记录，这样前后两次查询的结果集就不会出现不相同的情况。

• 主键唯一性确保不会有id=1的另一条记录插入：由于主键具有唯一性，所以其他事务插入 id = 1 的时候，会因为主键冲突，导致无法插入 id = 1 的新记录。这样事务 A 在多次查询 id = 1 的记录的时候，不会出现前后两次查询的结果集不同，也就避免了幻读的问题。
• 记录锁的引入锁定了id=1的记录，其他事务无法对其进行删除操作：由于对 id = 1 加了记录锁，其他事务无法删除该记录，这样事务 A 在多次查询 id = 1 的记录的时候，不会出现前后两次查询的结果集不同，也就避免了幻读的问题

（2）记录不存在的情况

​ 类似地，参考上述案例分析记录不存在的场景（记得commit 操作释放锁，便于开启下一个副本学习挑战），拆解加锁情况

# 窗口1：开启事务
begin;
select * from t_user where id = 2 for update;

# 窗口2：查看加锁情况
select * from performance_schema.data_locks;

• 加锁说明
  • 表锁：X类型的意向锁
  • 行锁：X类型的间隙锁

​ 结合图示可以分析，此时事务1在id=5（LOCK_DATA=5）的主键索引上添加间隙锁（锁住的范围是（1，5）），因此接下来如果有其他事务插入id为2、3、4的记录时，这些相关的插入语句都会发生阻塞。如果其他事务是插入id=1或者id=5的记录并不会发生阻塞，而是会报主键冲突的错误（因为现有表中已经有id=1、id=5的记录了）

间隙锁的范围如何确定？（例如此处如何知道是（1，5））

​ 如果 LOCK_MODE 是 next-key 锁或者间隙锁，那么 LOCK_DATA 就表示锁的范围「右边界」（此处LOCK_DATA=5） ，然后锁范围的「左边界」是表中 id 为 5 的上一条记录的 id 值，即 1。

因此，间隙锁的范围(1, 5)。

为什么唯一索引等值查询并且查询记录「不存在」的场景下，在索引树找到第一条大于该查询记录的记录后，要将该记录的索引中的 next-key lock 会退化成「间隙锁」

原因就是在唯一索引等值查询并且查询记录不存在的场景下，仅靠间隙锁就能避免幻读的问题。

• 为什么 id = 5 记录上的主键索引的锁不可以是 next-key lock？

  • 如果是 next-key lock，就意味着其他事务无法删除 id = 5 这条记录，但是这次的案例是查询 id = 2 的记录，只要保证前后两次查询 id = 2 的结果集相同，就能避免幻读的问题了，所以即使 id =5 被删除，也不会有什么影响，那就没必须加 next-key lock，因此只需要在 id = 5 加间隙锁，避免其他事务插入 id = 2 的新记录即可

• 为什么不可以针对不存在的记录加记录锁？

  • 锁是加在索引上的，而这个场景下查询的记录是不存在的，自然就没办法锁住这条不存在的记录

范围查询

唯一索引的范围查询的加锁规则（与等值查询不同）

当唯一索引进行范围查询时，会对每一个扫描到的索引加 next-key 锁，如果遇到下面这些情况，会退化成记录锁或者间隙锁：

• 情况一：针对「大于等于」的范围查询，因为存在等值查询的条件，那么如果等值查询的记录是存在于表中，那么该记录的索引中的 next-key 锁会退化成记录锁
• 情况二：针对「小于或者小于等于」的范围查询，要看条件值的记录是否存在于表中：
  • 当条件值的记录不在表中，那么不管是「小于」还是「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁
  • 当条件值的记录在表中，如果是「小于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁；如果「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引 next-key 锁不会退化成间隙锁。其他扫描到的记录，都是在这些记录的索引上加 next-key 锁

（1）针对【大于或者大于等于】的范围查询

案例1：针对【大于】的范围查询情况

# 窗口1：开启事务
begin;
select * from t_user where id > 15 for update;

# 窗口2：查看加锁情况
select * from performance_schema.data_locks;

事务加锁的变化过程分析如下：

• 最开始要找的第一行是 id = 20，由于查询该记录不是一个等值查询（也不是大于等于条件查询），所以对该主键索引加的是范围为 (15, 20] 的 next-key 锁
• 由于是范围查找，就会继续往后找存在的记录，虽然看见表中最后一条记录是 id = 20 的记录，但是实际在 Innodb 存储引擎中，会用一个特殊的记录来标识最后一条记录，该特殊的记录的名字叫 supremum pseudo-record ，所以扫描第二行的时候，也就扫描到了这个特殊记录的时候，会对该主键索引加的是范围为 (20, +∞] 的 next-key 锁
• 停止扫描

​ 事务在主键索引上加了两个X型的next-key锁：

• 在 id = 20 这条记录的主键索引上，加了范围为 (15, 20] 的 next-key 锁，意味着其他事务即无法更新或者删除 id = 20 的记录，同时无法插入 id 值为 16、17、18、19 的这一些新记录
• 在特殊记录（ supremum pseudo-record）的主键索引上（此处特殊记录为id=20的记录），加了范围为 (20, +∞] 的 next-key 锁，意味着其他事务无法插入 id 值大于 20 的这一些新记录

• 加锁说明
  • 表锁：X类型的意向锁
  • 行锁：X类型的next-key 锁：范围分别为（15，20]、（20，+∞)

案例2：针对【大于等于】的范围查询情况

# 窗口1：开启事务
begin;
select * from t_user where id >= 15 for update;

# 窗口2：查看加锁情况
select * from performance_schema.data_locks;

事务加锁变化过程如下：

• 最开始要找的第一行是 id = 15，由于查询该记录是一个等值查询（等于 15），所以该主键索引的 next-key 锁会退化成记录锁，也就是仅锁住 id = 15 这一行记录；（参考主键ID等值查询的加锁机制）
• 由于是范围查找，就会继续往后找存在的记录，扫描到的第二行是 id = 20，于是对该主键索引加的是范围为 (15, 20] 的 next-key 锁；
• 接着扫描到第三行的时候，扫描到了特殊记录（ supremum pseudo-record）（MySQL会用一个特殊标识来标志最后一条记录），于是对该主键索引加的是范围为 (20, +∞] 的 next-key 锁；
• 停止扫描；

核心说明：等值查询加next-key锁（会退化成记录锁）

​ =》范围查找，检索到下一条满足条件的记录，进行加next-key锁 （例如此处满足条件的下一条记录id为20，则会对（15,20] 范围添加next-key锁）

​ =》继续扫描，直到扫描到特殊记录（对该特殊记录索引加next-key锁） （例如此处最后一条记录id为20（特殊记录），则会对(20, +∞] 范围添加next-key锁）

结合检索结果分析，事务A在主键索引上添加了3个X型的锁：

• 在 id = 15 这条记录的主键索引上，加了记录锁，范围是 id = 15 这一行记录；意味着其他事务无法更新或者删除 id = 15 的这一条记录；
• 在 id = 20 这条记录的主键索引上，加了 next-key 锁，范围是 (15, 20] 。意味着其他事务即无法更新或者删除 id = 20 的记录，同时无法插入 id 值为 16、17、18、19 的这一些新记录。
• 在特殊记录（ supremum pseudo-record）的主键索引上，加了 next-key 锁，范围是 (20, +∞] 。意味着其他事务无法插入 id 值大于 20 的这一些新记录

• 加锁说明
  • 表级锁：X类型的意向锁
  • 行级锁：X类型的记录锁（锁住id=15的记录）、X类型的next-key锁（加锁范围：(15，20] ）、X类型的next-key锁（加锁范围：(20, +∞] ）

​ 针对「大于等于」条件的唯一索引范围查询的情况下， 如果条件值的记录存在于表中，那么由于查询该条件值的记录是包含一个等值查询的操作（其加锁流程类似于唯一索引等值查询的加锁流程，且其为主键），所以该记录的索引中的 next-key 锁会退化成记录锁

（2）针对【小于或者小于等于】的范围查询

案例1：针对「小于」的范围查询时，查询条件值的记录「不存在」表中的情况

# 窗口1：开启事务
begin;
select * from t_user where id < 6 for update;  # id为6的记录并不存在于表中

# 窗口2：查看加锁情况
select * from performance_schema.data_locks;

事务 A 加锁变化过程如下：

• 最开始要找的第一行是 id = 1，于是对该主键索引加的是范围为 (-∞, 1] 的 next-key 锁；
• 由于是范围查找，就会继续往后找存在的记录，扫描到的第二行是 id = 5，所以对该主键索引加的是范围为 (1, 5] 的 next-key 锁；
• 由于扫描到的第二行记录（id = 5），满足 id < 6 条件，而且也没有达到终止扫描的条件，接着会继续扫描。
• 扫描到的第三行是 id = 10，该记录不满足 id < 6 条件的记录，所以 id = 10 这一行记录的锁会退化成间隙锁，于是对该主键索引加的是范围为 (5, 10) 的间隙锁。
• 由于扫描到的第三行记录（id = 10），不满足 id < 6 条件，达到了终止扫描的条件，于是停止扫描。

从上面的分析中，可以得知事务 A 在主键索引上加了三个 X 型的锁：

• 在 id = 1 这条记录的主键索引上，加了范围为 (-∞, 1] 的 next-key 锁，意味着其他事务即无法更新或者删除 id = 1 的这一条记录，同时也无法插入 id 小于 1 的这一些新记录
• 在 id = 5 这条记录的主键索引上，加了范围为 (1, 5] 的 next-key 锁，意味着其他事务即无法更新或者删除 id = 5 的这一条记录，同时也无法插入 id 值为 2、3、4 的这一些新记录
• 在 id = 10 这条记录的主键索引上，加了范围为 (5, 10) 的间隙锁，意味着其他事务无法插入 id 值为 6、7、8、9 的这一些新记录

​ 虽然这次范围查询的条件是「小于」，但是查询条件值的记录不存在于表中（ id 为 6 的记录不在表中），所以如果事务 A 的范围查询的条件改成 <= 6 的话，加的锁还是和范围查询条件为 < 6 是一样的。

• 加锁说明

  • 表级锁：X类型的意向锁

  • 行级锁：X类型的next-key锁（加锁范围： (-∞, 1] ）、X类型的next-key锁（加锁范围：(1，5] ）、X类型的next-key锁（加锁范围：(5，10）的间隙锁 ）

​ 因此，针对「小于或者小于等于」的唯一索引范围查询，如果条件值的记录不在表中，那么不管是「小于」还是「小于等于」的范围查询，扫描到终止范围查询的记录时，该记录中索引的 next-key 锁会退化成间隙锁，其他扫描的记录，则是在这些记录的索引上加 next-key 锁

案例2：针对「小于等于」的范围查询时，查询条件值的记录「存在」表中的情况

# 窗口1：开启事务
begin;
select * from t_user where id <= 5 for update;  # id为5的记录存在于表中

# 窗口2：查看加锁情况
select * from performance_schema.data_locks;

事务 A 加锁变化过程如下：

• 最开始要找的第一行是 id = 1，于是对该记录加的是范围为 (-∞, 1] 的 next-key 锁；
• 由于是范围查找，就会继续往后找存在的记录，扫描到的第二行是 id = 5，于是对该记录加的是范围为 (1, 5] 的 next-key 锁。
• 由于主键索引具有唯一性，不会存在两个 id = 5 的记录，所以不会再继续扫描，于是停止扫描

从上面的分析中，可以得到事务 A 在主键索引上加了 2 个 X 型的锁：

• 在 id = 1 这条记录的主键索引上，加了范围为 (-∞, 1] 的 next-key 锁。意味着其他事务即无法更新或者删除 id = 1 的这一条记录，同时也无法插入 id 小于 1 的这一些新记录
• 在 id = 5 这条记录的主键索引上，加了范围为 (1, 5] 的 next-key 锁。意味着其他事务即无法更新或者删除 id = 5 的这一条记录，同时也无法插入 id 值为 2、3、4 的这一些新记录

• 加锁说明

  • 表级锁：X类型的意向锁

  • 行级锁：X类型的next-key锁（加锁范围： (-∞, 1] ）、X类型的next-key锁（加锁范围：(1，5] 的间隙锁 ）

案例3：针对「小于」的范围查询时，查询条件值的记录「存在」表中的情况

# 窗口1：开启事务
begin;
select * from t_user where id < 5 for update;  # id为5的记录存在于表中

# 窗口2：查看加锁情况
select * from performance_schema.data_locks;

事务 A 加锁变化过程如下：

• 最开始要找的第一行是 id = 1，于是对该记录加的是范围为 (-∞, 1] 的 next-key 锁；
• 由于是范围查找，就会继续往后找存在的记录，扫描到的第二行是 id = 5，该记录是第一条不满足 id < 5 条件的记录，于是该记录的锁会退化为间隙锁，锁范围是 (1,5)。
• 由于找到了第一条不满足 id < 5 条件的记录，于是停止扫描。

事务 在主键索引上加了两种 X 型锁：

• 在 id = 1 这条记录的主键索引上，加了范围为 (-∞, 1] 的 next-key 锁，意味着其他事务即无法更新或者删除 id = 1 的这一条记录，同时也无法插入 id 小于 1 的这一些新记录
• 在 id = 5 这条记录的主键索引上，加了范围为 (1,5) 的间隙锁，意味着其他事务无法插入 id 值为 2、3、4 的这一些新记录

在针对「小于或者小于等于」的唯一索引（主键索引）范围查询时，存在这两种情况会将索引的 next-key 锁会退化成间隙锁的：

• 当条件值的记录「不在」表中时，那么不管是「小于」还是「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的主键索引中的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的主键索引上加 next-key 锁

• 当条件值的记录「在」表中时：

  • 如果是「小于」条件的范围查询，扫描到终止范围查询的记录时，该记录的主键索引中的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的主键索引上，加 next-key 锁
  • 如果是「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的主键索引中的 next-key 锁「不会」退化成间隙锁，其他扫描到的记录，都是在这些记录的主键索引上加 next-key 锁

• 加锁说明

  • 表级锁：X类型的意向锁

  • 行级锁：X类型的next-key锁（加锁范围： (-∞, 1] ）、X类型的间隙锁锁（加锁范围：(1，5）的间隙锁 ）

2.非唯一索引

等值查询

​ 当用非唯一索引进行等值查询的时候，因为存在两个索引，一个是主键索引，一个是非唯一索引（二级索引），所以在加锁时，同时会对这两个索引都加锁，但是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁。

针对非唯一索引等值查询时，查询的记录存不存在，加锁的规则也会不同：

• 当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁
• 当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁

（1）记录不存在的情况

案例1：针对非唯一索引等值查询时，查询的值不存在的情况

# 窗口1：开启事务
begin;
select * from t_user where age = 25 for update; 

# 窗口2：查看加锁情况
select * from performance_schema.data_locks;

事务加锁变化过程如下：

• 定位到第一条不符合查询条件的二级索引记录，即扫描到 age = 39，于是该二级索引的 next-key 锁会退化成间隙锁，范围是 (22, 39)
• 停止查询

​ 事务 A 在 age = 39 记录的二级索引上，加了 X 型的间隙锁，范围是 (22, 39)。意味着其他事务无法插入 age 值为 23、24、25、26、....、38 这些新记录。不过对于插入 age = 22 和 age = 39 记录的语句，在一些情况是可以成功插入的，而一些情况则无法成功插入

• 加锁说明

  • 表级锁：X类型的意向锁

  • 行级锁：X类型的间隙锁锁（加锁范围：(22，39）的间隙锁 ）

​ 当有一个事务持有二级索引的间隙锁 (22, 39) 时，什么情况下，可以让其他事务的插入 age = 22 或者 age = 39 记录的语句成功或阻塞？

​ 插入语句在插入一条记录之前，需要先定位到该记录在 B+树 的位置，如果插入的位置的下一条记录的索引上有间隙锁，才会发生阻塞。

​ 在分析二级索引的间隙锁是否可以成功插入记录时，要先要知道二级索引树是如何存放记录的？二级索引树是按照二级索引值（age列）按顺序存放的，在相同的二级索引值情况下， 再按主键 id 的顺序存放。知道了这个前提，才能知道执行插入语句的时候，插入的位置的下一条记录是谁。

基于前面的实验，事务 A 是在 age = 39 记录的二级索引上，加了 X 型的间隙锁，范围是 (22, 39)。

插入 age = 22 记录的成功和失败的情况分别如下：

• 当其他事务插入一条 age = 22，id = 3 的记录的时候，在二级索引树上定位到插入的位置，而该位置的下一条是 id = 10、age = 22 的记录，该记录的二级索引上没有间隙锁，所以这条插入语句可以执行成功
• 当其他事务插入一条 age = 22，id = 12 的记录的时候，在二级索引树上定位到插入的位置，而该位置的下一条是 id = 20、age = 39 的记录，正好该记录的二级索引上有间隙锁，所以这条插入语句会被阻塞，无法插入成功

插入 age = 39 记录的成功和失败的情况分别如下：

• 当其他事务插入一条 age = 39，id = 3 的记录的时候，在二级索引树上定位到插入的位置，而该位置的下一条是 id = 20、age = 39 的记录，正好该记录的二级索引上有间隙锁，所以这条插入语句会被阻塞，无法插入成功
• 当其他事务插入一条 age = 39，id = 21 的记录的时候，在二级索引树上定位到插入的位置，而该位置的下一条记录不存在，也就没有间隙锁了，所以这条插入语句可以插入成功

所以，当有一个事务持有二级索引的间隙锁 (22, 39) 时，插入 age = 22 或者 age = 39 记录的语句是否可以执行成功，关键还要考虑插入记录的主键值，因为「二级索引值（age列）+主键值（id列）」才可以确定插入的位置，确定了插入位置后，就要看插入的位置的下一条记录是否有间隙锁，如果有间隙锁，就会发生阻塞，如果没有间隙锁，则可以插入成功。

​ 「事务 A 在 age = 39 记录的二级索引上（INDEX_NAME: index_age ），加了范围为 (22, 39) 的 X 型间隙锁」。这个结论其实还不够准确，因为只考虑了 LOCK_DATA 第一个数值（39），没有考虑 LOCK_DATA 第二个数值（20）。那 LOCK_DATA：39，20 是什么意思？

• LOCK_DATA 第一个数值，也就是 39， 它代表的是 age 值。LOCK_DATA 第一个数值是 next-key 锁和间隙锁加锁范围的右边界值。
• LOCK_DATA 第二个数值，也就是 20， 它代表的是 id 值。

​ 之所以 LOCK_DATA 要多显示一个数值（ID值），是因为针对「当某个事务持有非唯一索引的 (22, 39) 间隙锁的时候，其他事务是否可以插入 age = 39 新记录」的问题，还需要考虑插入记录的 id 值。而 LOCK_DATA 的第二个数值，就是说明在插入 age = 39 新记录时，哪些范围的 id 值是不可以插入的

因此， LOCK_DATA：39，20 + LOCK_MODE : X, GAP 的意思是，事务 A 在 age = 39 记录的二级索引上（INDEX_NAME: index_age ），加了 age 值范围为 (22, 39) 的 X 型间隙锁，**同时针对其他事务插入 age 值为 39 的新记录时，不允许插入的新记录的 id 值小于 20 **。如果插入的新记录的 id 值大于 20，则可以插入成功

​ 但是无法从select * from performance_schema.data_locks; 输出的结果分析出「在插入 age =22 新记录时，哪些范围的 id 值是可以插入成功的」，这时候就得自己画出二级索引的 B+ 树的结构，然后确定插入位置后，看下该位置的下一条记录是否存在间隙锁，如果存在间隙锁，则无法插入成功，如果不存在间隙锁，则可以插入成功

（2）记录存在的情况

案例1：针对非唯一索引等值查询时，查询的值存在的情况

# 窗口1：开启事务
begin;
select * from t_user where age = 22 for update; 

# 窗口2：查看加锁情况
select * from performance_schema.data_locks;

事务 A 加锁变化过程如下：

• 由于不是唯一索引，所以肯定存在值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，最开始要找的第一行是 age = 22，于是对该二级索引记录加上范围为 (21, 22] 的 next-key 锁。同时，因为 age = 22 符合查询条件，于是对 age = 22 的记录的主键索引加上记录锁，即对 id = 10 这一行加记录锁。
• 接着继续扫描，扫描到的第二行是 age = 39，该记录是第一个不符合条件的二级索引记录，所以该二级索引的 next-key 锁会退化成间隙锁，范围是 (22, 39)。
• 停止查询。

可以看到，事务 A 对主键索引和二级索引都加了 X 型的锁：

• 主键索引：
  • 在 id = 10 这条记录的主键索引上，加了记录锁，意味着其他事务无法更新或者删除 id = 10 的这一行记录。
• 二级索引（非唯一索引）：
  • 在 age = 22 这条记录的二级索引上，加了范围为 (21, 22] 的 next-key 锁，意味着其他事务无法更新或者删除 age = 22 的这一些新记录，不过对于插入 age = 21 和 age = 22 新记录的语句，在一些情况是可以成功插入的，而一些情况则无法成功插入，具体哪些情况，会在后面说。
  • 在 age = 39 这条记录的二级索引上，加了范围 (22, 39) 的间隙锁。意味着其他事务无法插入 age 值为 23、24、..... 、38 的这一些新记录。不过对于插入 age = 22 和 age = 39 记录的语句，在一些情况是可以成功插入的，而一些情况则无法成功插入

• 加锁说明
  • 表级锁：X类型的意向锁
  • 行级锁：
    • 对主键索引：添加id=10的X类型的记录锁；
    • 对age二级索引：添加X类型的next-key锁（加锁范围：(21，22]）、添加X类型的间隙锁（加锁范围：(22，39））

从上图的分析，可以看到，事务 A 对二级索引（INDEX_NAME: index_age ）加了两个 X 型锁，分别是：

• 在 age = 22 这条记录的二级索引上，加了范围为 (21, 22] 的 next-key 锁，意味着其他事务无法更新或者删除 age = 22 的这一些新记录，针对是否可以插入 age = 21 和 age = 22 的新记录，分析如下：
  • 是否可以插入 age = 21 的新记录，还要看插入的新记录的 id 值，如果插入 age = 21 新记录的 id 值小于 5，那么就可以插入成功，因为此时插入的位置的下一条记录是 id = 5，age = 21 的记录，该记录的二级索引上没有间隙锁。如果插入 age = 21 新记录的 id 值大于 5，那么就无法插入成功，因为此时插入的位置的下一条记录是 id = 10，age = 22 的记录，该记录的二级索引上有间隙锁。
  • 是否可以插入 age = 22 的新记录，还要看插入的新记录的 id 值，从 LOCK_DATA : 22, 10 可以得知，其他事务插入 age 值为 22 的新记录时，如果插入的新记录的 id 值小于 10，那么插入语句会发生阻塞；如果插入的新记录的 id 大于 10，还要看该新记录插入的位置的下一条记录是否有间隙锁，如果没有间隙锁则可以插入成功，如果有间隙锁，则无法插入成功。
• 在 age = 39 这条记录的二级索引上，加了范围 (22, 39) 的间隙锁。意味着其他事务无法插入 age 值为 23、24、..... 、38 的这一些新记录，针对是否可以插入 age = 22 和 age = 39 的新记录，分析如下：
  • 是否可以插入 age = 22 的新记录，还要看插入的新记录的 id 值，如果插入 age = 22 新记录的 id 值小于 10，那么插入语句会被阻塞，无法插入，因为此时插入的位置的下一条记录是 id = 10，age = 22 的记录，该记录的二级索引上有间隙锁（ age = 22 这条记录的二级索引上有 next-key 锁）。如果插入 age = 22 新记录的 id 值大于 10，也无法插入，因为此时插入的位置的下一条记录是 id = 20，age = 39 的记录，该记录的二级索引上有间隙锁。
  • 是否可以插入 age = 39 的新记录，还要看插入的新记录的 id 值，从 LOCK_DATA : 39, 20 可以得知，其他事务插入 age 值为 39 的新记录时，如果插入的新记录的 id 值小于 20，那么插入语句会发生阻塞，如果插入的新记录的 id 大于 20，则可以插入成功。

同时，事务 A 还对主键索引（INDEX_NAME: PRIMARY ）加了 X 型的记录锁：

• 在 id = 10 这条记录的主键索引上，加了记录锁，意味着其他事务无法更新或者删除 id = 10 的这一行记录。

为什么这个案例中，需要在二级索引索引上加范围 (22, 39) 的间隙锁？

​ 要找到这个问题的答案，先理解MySQL 在可重复读的隔离级别场景下，为什么要引入间隙锁？其实是为了避免幻读现象的发生。

​ 如果这个实验案例中select * from user where age = 22 for update;

​ 如果事务 A 不在二级索引索引上加范围 (22, 39) 的间隙锁，只在二级索引索引上加范围为 (21, 22] 的 next-key 锁的话，那么就会有幻读的问题。

​ 在非唯一索引上加了范围为 (21, 22] 的 next-key 锁，是无法完全锁住 age = 22 新记录的插入，因为对于是否可以插入 age = 22 的新记录，还要看插入的新记录的 id 值，从 LOCK_DATA : 22, 10 可以得知，其他事务插入 age 值为 22 的新记录时，如果插入的新记录的 id 值小于 10，那么插入语句会发生阻塞，如果插入的新记录的 id 值大于 10，则可以插入成功。

​ 也就是说，只在二级索引索引（非唯一索引）上加范围为 (21, 22] 的 next-key 锁，其他事务是有可能插入 age 值为 22 的新记录的（比如插入一个 age = 22，id = 12 的新记录），那么如果事务 A 再一次查询 age = 22 的记录的时候，前后两次查询 age = 22 的结果集就不一样了，这时就发生了幻读的现象。

那么当在 age = 39 这条记录的二级索引索引上加了范围为 (22, 39) 的间隙锁后，其他事务是无法插入一个 age = 22，id = 12 的新记录，因为当其他事务插入一条 age = 22，id = 12 的新记录的时候，在二级索引树上定位到插入的位置，而该位置的下一条是 id = 20、age = 39 的记录，正好该记录的二级索引上有间隙锁，所以这条插入语句会被阻塞，无法插入成功，这样就避免幻读现象的发生。

​ 因此，为了避免幻读现象的发生，就需要在二级索引索引上加范围 (22, 39) 的间隙锁

范围查询

​ 非唯一索引和主键索引的范围查询的加锁也有所不同，不同之处在于非唯一索引范围查询，索引的 next-key lock 不会有退化为间隙锁和记录锁的情况，也就是非唯一索引进行范围查询时，对二级索引记录加锁都是加 next-key 锁。

# 窗口1：开启事务
begin;
select * from t_user where age >= 22  for update;

# 窗口2：查看加锁情况
select * from performance_schema.data_locks;

事务 A 的加锁变化：

• 最开始要找的第一行是 age = 22（虽然范围查询语句包含等值查询，但是这里不是唯一索引范围查询，所以是不会发生退化锁的现象）

  • 对该二级索引记录加 next-key 锁，范围是 (21, 22]
  • 对 age = 22 这条记录的主键索引加记录锁，即对 id = 10 这一行记录的主键索引加记录锁

• 由于是范围查询，接着继续扫描已经存在的二级索引记录，扫描的第二行是 age = 39 的二级索引记录

  • 对该二级索引记录加 next-key 锁，范围是 (22, 39]
  • 对 age = 39 这条记录的主键索引加记录锁，即对 id = 20 这一行记录的主键索引加记录锁

• 最后一条二级索引记录是 age = 39 的记录（在 Innodb 存储引擎中，会用一个特殊的记录 supremum pseudo-record来标识最后一条记录）

  • 扫描到特殊记录的时候，会对该二级索引记录加的是范围为 (39, +∞] 的 next-key 锁

• 停止查询

可以看到，事务 A 对主键索引和二级索引都加了 X 型的锁：

• 主键索引（id 列）：
  • 在 id = 10 这条记录的主键索引上，加了记录锁，意味着其他事务无法更新或者删除 id = 10 的这一行记录
  • 在 id = 20 这条记录的主键索引上，加了记录锁，意味着其他事务无法更新或者删除 id = 20 的这一行记录
• 二级索引（age 列）：
  • 在 age = 22 这条记录的二级索引上，加了范围为 (21, 22] 的 next-key 锁，意味着其他事务无法更新或者删除 age = 22 的这一些新记录，不过对于是否可以插入 age = 21 和 age = 22 的新记录，还需要看新记录的 id 值，有些情况是可以成功插入的，而一些情况则无法插入，具体哪些情况，我们前面也讲了。
  • 在 age = 39 这条记录的二级索引上，加了范围为 (22, 39] 的 next-key 锁，意味着其他事务无法更新或者删除 age = 39 的这一些记录，也无法插入 age 值为 23、24、25、...、38 的这一些新记录。不过对于是否可以插入 age = 22 和 age = 39 的新记录，还需要看新记录的 id 值，有些情况是可以成功插入的，而一些情况则无法插入，具体哪些情况，我们前面也讲了。
  • 在特殊的记录（supremum pseudo-record）的二级索引上，加了范围为 (39, +∞] 的 next-key 锁，意味着其他事务无法插入 age 值大于 39 的这些新记录。

​ 在 age >= 22 的范围查询中，明明查询 age = 22 的记录存在并且属于等值查询，为什么不会像唯一索引那样，将 age = 22 记录的二级索引上的 next-key 锁退化为记录锁？

​ 因为 age 字段是非唯一索引，不具有唯一性，所以如果只加记录锁（记录锁无法防止插入，只能防止删除或者修改），就会导致其他事务插入一条 age = 22 的记录，这样前后两次查询的结果集就不相同了，出现了幻读现象

• 加锁说明
  • 表级锁：X类型的意向锁
  • 行级锁：
    • 对主键索引：添加id=10的X类型的记录锁；
    • 对age二级索引：添加X类型的next-key锁（加锁范围：(21，22]）、添加X类型的next-key锁（加锁范围：(22，39]）、添加X类型的next-key锁（加锁范围：(22，+∞））

3.没有加索引的查询

​ 如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

​ 不只是锁定读查询语句不加索引才会导致这种情况，update 和 delete 语句如果查询条件不加索引，那么由于扫描的方式是全表扫描，于是就会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。

​ 因此，在线上在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，对于生产来说是一个挺严重的问题

4.MySQL行级锁的加锁规则总结

要理解 MySQL 为什么要这样加锁，主要要以避免幻读角度去分析，来加深对加锁规则的印象

唯一索引等值查询：

• 当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。
• 当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。

非唯一索引等值查询：

• 当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。
• 当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。

非唯一索引和主键索引的范围查询的加锁规则不同之处在于：

• 唯一索引在满足一些条件的时候，索引的 next-key lock 退化为间隙锁或者记录锁。
• 非唯一索引范围查询，索引的 next-key lock 不会退化为间隙锁和记录锁。

针对没有加索引的查询：

• 读操作：相当于走了全表扫描，会给每一条记录的索引都加上next-key锁
• 写操作：在线上在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了（因此线上千万不要执行没有带索引条件的 update 语句，不然会造成业务停滞）

MySQL中insert语句是如何加行级锁的？（8.0.21）

​ Insert 语句在正常执行时是不会生成锁结构的，它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的

隐式锁概念

​ 当事务需要加锁的时，如果这个锁不可能发生冲突，InnoDB会跳过加锁环节，这种机制称为隐式锁。隐式锁是 InnoDB 实现的一种延迟加锁机制，其特点是只有在可能发生冲突时才加锁，从而减少了锁的数量，提高了系统整体性能。

​ 隐式锁就是在 insert过程中不加锁，只有在特殊情况下，才会将隐式锁转换为显示锁，此处可列举两个场景：

• 如果记录之间加有间隙锁，为了避免幻读，此时是不能插入记录的；
• 如果 Insert 的记录和已有记录存在唯一键冲突，此时也不能插入记录

案例准备

CREATE TABLE `t_order` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `order_no` varchar(30) NOT NULL,
  `create_date` datetime ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_order_no` (`order_no`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `t_order` (`id`, `order_no`) VALUES ('1', '1001');
INSERT INTO `t_order` (`id`, `order_no`) VALUES ('2', '1002');
INSERT INTO `t_order` (`id`, `order_no`) VALUES ('3', '1003');
INSERT INTO `t_order` (`id`, `order_no`) VALUES ('4', '1004');
INSERT INTO `t_order` (`id`, `order_no`) VALUES ('5', '1005');

1.记录之间有加间隙锁

​ 每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，此时会生成一个插入意向锁，然后锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），现象就是 insert 语句会被阻塞

# 窗口1：开启事务
begin;
select * from t_order where order_no = '1006' for update; 

​ 本次SQL执行添加的是next-key锁（记录锁+间隙锁），锁的范围是（1005，+∞]。此时如果有另一个事务B在这个间隙锁中插入一个记录，这个事务B就会被阻塞

# 事务B模拟插入操作
begin;
INSERT INTO `t_order` (`order_no`,`create_date`) VALUES ('1010',now());
# 处于阻塞状态

# 查看事务B的加锁状态
select * from performance_schema.data_locks;

​ 事务 B 的状态为等待状态（LOCK_STATUS: WAITING），因为向事务 A 生成的 next-key 锁（记录锁+间隙锁）范围(1005, +∞] 中插入了一条记录，所以事务 B 的插入操作生成了一个插入意向锁（LOCK_MODE: X,INSERT_INTENTION），锁的状态是等待状态，意味着事务 B 并没有成功获取到插入意向锁，因此事务 B 发生阻塞

扩展：遇到索引失效的场景问题就转化为【不加索引的查询场景】了

​ 一开始没有注意到order_no的类型（varchar），在select语句中将varchar类型和int类型直接进行比较，在上述检索语句中直接通过select * from t_order where order_no = 1006 for update; 这种形式进行测试，结果就陷入了索引失效的小坑，于是并没有出现“预期的加锁机制”分析，甚至纠结是不是前面的学习内容理解错误，但实际上这个概念思路是正确的，正是因为索引失效导致这条语句变成一条不走索引的普通查询，于是它会对主键索引进行加锁（加锁机制分析自然回归到【没有加索引的查询】这一场景）

​ 调整过程，正常访问，会发现其执行的加锁机制如下，符合预期：

2.遇到唯一键冲突

​ 如果在插入新记录时，插入了一个与「已有的记录的主键或者唯一二级索引列值相同」的记录（不过可以有多条记录的唯一二级索引列的值同时为NULL，这里不考虑这种情况），此时插入就会失败，然后对于这条记录加上了 S 型的锁。

• 如果主键索引重复，插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加 S 型记录锁
• 如果唯一二级索引重复，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加 S 型 next-key 锁

案例1：主键索引冲突

# 事务A
begin;
INSERT INTO `t_order` (`id`,`order_no`,`create_date`) VALUES (5,'-1',now()); # 主键索引冲突错误

# 查看事务A的加锁状态
select * from performance_schema.data_locks;

​ 主键索引为 5 （LOCK_DATA）的这条记录中加了锁类型为 S 型的记录锁。注意，这里 LOCK_TYPE 中的 RECORD 表示行级锁，而不是记录锁的意思。如果是 S 型记录锁的话，LOCK_MODE 会显示 S, REC_NOT_GAP。

​ 所以，在隔离级别是「可重复读」的情况下，如果在插入数据的时候，发生了主键索引冲突，插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加 S 型记录锁

案例2：唯一 二级索引冲突

​ t_order 表中的 order_no 字段为唯一二级索引，且已存在 order_no 值为 1001 的记录，此时事务 A，插入了 order_no 为 1001 的记录，就出现了报错

# 事务A
begin;
INSERT INTO `t_order` (`order_no`,`create_date`) VALUES ('1001',now()); # 插入报错

# 查看事务A的加锁状态
select * from performance_schema.data_locks;

​ index_order 二级索引加了 S 型的 next-key 锁，范围是(-∞, 1001]

​ 切换窗口执行事务B，然后观察执行事务的加锁状态(此处注意order_no需传入字符串格式的数据进行比较，否则又会复现上面的问题)

# 事务B
begin;
select * from t_order where order_no = '1001' for update;# 事务B执行受到阻塞，这条语句想加X型的锁，与S型锁冲突，因此被阻塞

# 查看事务B的加锁状态
select * from performance_schema.data_locks;

案例3：两个事务执行过程中，执行了相同的insert场景

​ 目前t_order表中现有数据有5条，order_no为唯一二级索引，在隔离级别可重复读的情况下，开启两个事务，前后执行相同的 Insert 语句，此时事务 B 的 Insert 语句会发生阻塞

​ 场景1：order_no为唯一二级索引，且表中不存在order_no为1006的记录

事务A	事务B
begin;	begin;
INSERT INTO t_order (order_no,create_date) VALUES ('1006',now());
	INSERT INTO t_order (order_no,create_date) VALUES ('1006',now()); # 发生阻塞

两个事务的加锁过程分析如下：

• 事务 A 先插入 order_no 为 1006 的记录，可以插入成功，此时对应的唯一二级索引记录被「隐式锁」保护，此时还没有实际的锁结构（执行完这里的时候，可以看查 performance_schema.data_locks 信息，可以看到这条记录是没有加任何锁的）；
• 随后事务 B 也插入 order_no 为 1006 的记录，由于事务 A 已经插入 order_no 值为 1006 的记录，所以事务 B 在插入二级索引记录时会遇到重复的唯一二级索引列值，此时事务 B 想获取一个 S 型 next-key 锁，但是事务 A 并未提交，事务 A 插入的 order_no 值为 1006 的记录上的「隐式锁」会变「显示锁」且锁类型为 X 型的记录锁，所以事务 B 向获取 S 型 next-key 锁时会遇到锁冲突，因此进入阻塞状态

​ 场景2：order_no不是唯一二级索引，且表中不存在order_no为1007的记录

事务A	事务B
begin;	begin;
// 检查1007订单是否存在 select id from t_order where order_no=1007
	// 检查1007订单是否存在 select id from t_order where order_no=1007
INSERT INTO t_order (order_no,create_date) VALUES ('1007',now());
	INSERT INTO t_order (order_no,create_date) VALUES ('1007',now()); # 发生阻塞
commit	commit

​ 最终呈现存在两条订单为1007的记录（幻读现象）

死锁

1.什么是死锁？

​ 死锁场景分析：双方持有对方所需的锁资源，都在等对方释放锁资源，而造成的阻塞等待问题

案例准备

CREATE TABLE `t_order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_no` int DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_order` (`order_no`) USING BTREE
) ENGINE=InnoDB ;

# 数据填充
insert into t_order(id,order_no,create_date) values(1,'1001',now());
insert into t_order(id,order_no,create_date) values(2,'1002',now());
insert into t_order(id,order_no,create_date) values(3,'1003',now());
insert into t_order(id,order_no,create_date) values(4,'1004',now());
insert into t_order(id,order_no,create_date) values(5,'1005',now());
insert into t_order(id,order_no,create_date) values(6,'1006',now());

​ 假设有两事务，一个事务要插入订单 1007 ，另外一个事务要插入订单 1008，因为需要对订单做幂等性校验，所以两个事务先要查询该订单是否存在，不存在才插入记录

事务A	事务B
begin;	begin;
// 检查1007订单是否存在 select id from t_order where order_no = 1007 for update;
	// 检查1008订单是否存在 select id from t_order where order_no = 1008 for update;
// 如果没有则插入订单记录 insert into t_order(order_no,create_date) values('1007',now()); # 阻塞等待
	// 如果没有则插入订单记录 insert into t_order(order_no,create_date) values('1008',now()); # 阻塞等待

​ 根据上述步骤分析，会发现在没有打开死锁检测的前提下两个事务都陷入了等待状态，都在等待对方释放锁。此处在查询记录是否存在的时候，使用了 select ... for update 语句，目的为了防止事务执行的过程中，有其他事务插入了记录，而出现幻读的问题（参考上述案例【两个事务执行过程中，执行了相同的insert场景】的场景2示例，如果此处使用普通查询操作，则如果有插入同样订单号的记录请求同时进来，就会出现两个重复的订单，就可能出现幻读问题）

2.为什么会产生死锁？

​ 首先得理解MySQL的InnoDB引擎的【可重复读】隔离机制，为了解决幻读问题，需结合不同场景进行分析：

• 基于MVCC机制实现快照读
• 通过Next-Key锁实现当前读：其中next-key锁由记录锁和间隙锁组合而成，在一定场景下next-key锁会退化为间隙锁或者记录锁

​ 行锁的释放时机是在事务提交（commit）后，锁就会被释放，并不是一条语句执行完就释放行锁

​ 结合上述死锁案例，拆解语句执行过程中加了什么锁：

• 事务A执行查询语句，查看其锁状态
• 紧跟着事务B执行插入语句，当事务 B 往事务 A next-key 锁的范围 (1006, +∞] 里插入 id = 1008 的记录就会被锁住

# 事务A
begin;
select id from t_order where order_no = 1007 for update;

# 查看加锁状态
select * from performance_schema.data_locks;

# 事务B
begin;
Insert into t_order (order_no, create_date) values (1008, now());

​ 执行插入语句时会在插入间隙上获取插入意向锁，而插入意向锁与间隙锁是冲突的，所以当其它事务持有该间隙的间隙锁时，需要等待其它事务释放间隙锁之后，才能获取到插入意向锁。而间隙锁与间隙锁之间是兼容的，所以所以两个事务中 select ... for update 语句并不会相互影响

​ 案例中的事务 A 和事务 B 在执行完后 select ... for update 语句后都持有范围为(1006,+∞]的next-key 锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，导致死锁

如何理解间隙锁与间隙锁之间是兼容的？

​ 间隙锁的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁，共享和排他的间隙锁是没有区别的，他们相互不冲突，且功能相同，即两个事务可以同时持有包含共同间隙的间隙锁

共同间隙包括两种场景：

• 其一是两个间隙锁的间隙区间完全一样；
• 其二是一个间隙锁包含的间隙区间是另一个间隙锁包含间隙区间的子集

对于next-key lock的分析：next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的

• 对于持有类似范围为（1,10]的 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞。

  虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系。X 型的记录锁与 X 型的记录锁是冲突的，比如一个事务执行了 select ... where id = 1 for update，后一个事务在执行这条语句的时候，就会被阻塞的

• 对于持有类似范围为（1006, +∞] 的 next-key lock，两个事务是可以同时持有的，不会冲突。因为 +∞ 并不是一个真实的记录，自然就不需要考虑 X 型与 S 型关系

3.如何避免死锁？

​ 死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

• 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒

  当发生超时后，提示错误：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

• 开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启

  当检测到死锁后，提示错误：ERROR 1213 (48001): Deadlock found when trying to get lock; try restarting transaction

​ 可通过上述策略来避免死锁，也可以回归业务的角度来预防死锁，对订单做幂等性校验的目的是为了保证不会出现重复的订单，可以直接将 order_no 字段设置为唯一索引列，利用它的唯一性来保证订单表不会出现重复的订单，不过有一点不好的地方就是在插入一个已经存在的订单记录时就会抛出异常。

4.如何排查死锁

​ 通过指令查看日志SHOW ENGINE INNODB STATUS; 通过分析死锁日志来排查死锁

悲观锁与乐观锁

​ 悲观锁和乐观锁概念的引入是一种定义，是一种思想。它的实现需要依托于相应的设计，例如悲观锁的实现需要借助数据库锁机制，而乐观锁的实现则是通过SQL调整进行相应控制（具体结合实际场景应用进行分析）

1.核心概念

悲观锁和乐观锁定义

悲观锁

​ 悲观锁对数据的修改抱有悲观态度的并发控制方式，认为数据被并发修改的概率比较大，需要修改之前先加锁

​ 悲观并发控制：采用==先取锁后访问：==的保守策略，为数据处理的安全提供了保证。

• 处理效率：处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会
• 并行性：降低并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数据

乐观锁

​ 乐观锁是相对于悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做

​ 相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本

​ 乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁

实现方式

（1）悲观锁的实现方式

​ 悲观锁的实现一般是依赖数据库提供的锁机制。在数据库中，悲观锁的流程如下：

• 在对记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）
• 如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定
• 如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了
• 其间如果有其他事务对该记录做加锁的操作，都要等待当前事务解锁或直接抛出异常

​ MySQL默认使用autocommit模式，即当执行一个更新操作后，MySQL会立刻将结果进行提交。因此要使用悲观锁，必须关闭mysql数据库中自动提交的属性，命令set autocommit=0; 即可关闭

场景案例（【淘宝下单过程中扣减库存的需求】中使用悲观锁）

​ 在一个事务中，先对要修改的记录进行加锁，随后执行更新操作，最后提交事务释放锁

//0.开始事务
begin; 
//1.查询出商品库存信息（悲观锁加锁sql语句）
select quantity from items where id=1 for update;
//2.修改商品库存为2
update items set quantity=2 where id = 1;
//3.提交事务
commit;

（2）乐观锁的实现方式

​ 使用乐观锁则不需要借助数据库的锁机制，结合其定义可知乐观锁的核心实现步骤有两个步骤：冲突检测、数据更新（其实现方式最为典型的为：CAS（Compare And Swap）技术）

​ CAS：是一项乐观锁技术，当多个线程尝试使用CAS同时更新同一个变量时，只有其中一个线程能更新变量的值，而其它线程都失败，失败的线程并不会被挂起，而是被告知这次竞争中失败，并可以再次尝试

场景案例（【淘宝下单过程中扣减库存的需求】中使用乐观锁）

​ 在一个事务中，先检索出要修改的记录值，在更新执行的时候将数据表对应记录的当前值和前面检索出来的记录值进行比对，两者一致才予以更新，否则认定为过期数据

//查询出商品库存信息，quantity = 3
select quantity from items where id=1
//修改商品库存为2
update items set quantity=2 where id=1 and quantity = 3;

​ 但是基于这种场景就会衍生ABA问题，因为比较的是记录值而忽略了记录值中间的变化过程，就会出现ABA问题（例如基于这种场景中如果出现quantity被其他线程进行了更改，从3改到2（甚至改了好几道），最终又改回了3，那么此时原线程进行CAS操作会发现当前数据库中的值还是3（和前面查询的是一致的），就会继续执行更新操作。虽然执行了更新操作，但也并不代表这个过程中是没有问题的）

如何避免ABA问题

• 方式1：为记录引入version版本号属性

​ 可以通过一个单独的可以顺序递增的version字段（可以理解为为记录额外指定一个版本号），只要数据记录被修改，则其version也会联动变更。因此在进行CAS操作的时候匹配当前记录的版本号和前面检索出来的版本号是否一致就知道这个过程中记录有没有被其他线程进行修改（因为这个版本号属性脱离业务概念，是用作定义记录版本的，一旦记录被修改，其相应版本也要调整，以此来定位记录的修改操作）

//查询出商品信息，version = 1
select version from items where id=1
//修改商品库存为2
update items set quantity=2,version = 3 where id=1 and version = 2;

• 方式2：使用时间戳（时间戳天然具有顺序性）

• 方式3：高并发场景中可以通过减小乐观锁粒度，最大程度提升吞吐率

//修改商品库存
update item 
set quantity=quantity - 1 
where id = 1 and quantity - 1 > 0
# 通过quantity - 1 > 0的方式进行乐观锁控制,该update语句在执行过程中，会在一次原子操作中自己查询一遍quantity的值，并将其扣减掉1

乐观锁 VS 悲观锁

在乐观锁与悲观锁的选择上面，主要结合两者的区别以及适用场景：

• 乐观锁并未真正加锁，效率高。一旦锁的粒度掌握不好，更新失败的概率就会比较高，容易发生业务失败

• 悲观锁依赖数据库锁，效率低。更新失败的概率比较低

随着互联网三高架构（高并发、高性能、高可用）的提出，悲观锁已经越来越少的被使用到生产环境中了，尤其是并发量比较大的业务场景

​ 悲观锁阻塞事务、乐观锁回滚重试，两者各有优缺点，结合实际场景进行选择：乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行重试，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适

todo：--- 慢查询跟踪

慢查询跟踪：

MySQL查看当前事务锁并kill

select
  t.trx_mysql_thread_id,
  t.trx_started,
  t.trx_state,
  t.trx_tables_in_use,
  t.trx_tables_locked,
  t.trx_rows_locked,
  timestampdiff(second, t.trx_started, now()) as trx_run_seconds,
  concat(e.timer_wait/1000000000000, 's') as timer_wait_seconds
from information_schema.innodb_trx t
join performance_schema.threads c on c.processlist_id=t.trx_mysql_thread_id
join performance_schema.events_statements_current e on e.thread_id=c.thread_id