MySQL锁篇

如何避免呢：如果数据库支持可重复读的隔离级别，那么在备份数据库之前先开启事务，创建 Read View ，然后整个事务都使用这个 Read View ，而且由于 MVCC 的支持，备份期间业务仍然可以对数据进行更新操作。备份MySQL数据库的工具是 mysqldump ，在使用 mysqldump 时加上 -single-transaction 参数的时候，就会在备份数据库之前开启事务（MyISAM 这种不支持事务的引擎，在备份数据库的时候就要使用全局锁的方法哟~）

1.2、表级锁

1.2.1、表锁

表锁除了会限制别的线程的读写之外，也会限制本线程接下来的读写操作。

解释一下上句话：也就是说，如果本线程对表加了【共享表锁】，那么本线程接下来要对学生表执行写操作的语句是会被阻塞的，其他线程进行写操作也会被阻塞，直到锁被释放。

1.2.2、元数据锁（MDL）

我们不需要显示的使用 MDL，因为当我们对数据库进行操作时，会自动给这个表加上 MDL：

对一张表进行 CURD（增删改查）操作时，加的是 MDL 读锁
对一张表做结构变更操作时，加的是 MDL 写锁

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

当有线程在执行 select 语句（加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（释放 MDL 读锁）。

相反，如果有线程对表结构进行变更（加 MDL 写锁）的期间，如果有其他线程执行了 CURD 操作（申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（释放 MDL 写锁）。

突然，有一个问题偷袭我！MDL 不显示调用，那它啥时候释放呢？

答：MDL 是在事务提交之后才会释放这就是说事务执行期间，MDL 是一直持有的。下面举个🌰：

首先，事务 A 先启动了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁。
然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为【读读】并不冲突
紧接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时 C 线程就无法申请到 MDL 写锁，就会被阻塞

在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这是数据库的线程很快就会爆满啦~

为什么 C 线程申请不到 MDL 的写锁会导致后续申请的读锁的查询操作也会被阻塞？

答：这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CURD 操作。所以为了更安全的对表结构进行变更，在变更之前先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果考虑 kill 这个长事务，然后再做表结构变更。

1.2.3、意向锁

在使用 InnoDB 引擎里面的表对某些记录加上【共享锁】之前，需要先在表级别加上一个【意向共享锁】
在使用 InnoDB 引擎的表里对某些记录加上【独占锁】之前，需要现在表级别加上一个【意向独占锁】

也就是说，在进行【增删改】操作时，需要先对表加上【意向独占锁】，然后对该记录加独占锁。而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。不过 select 也是可以对记录加共享锁和独占锁的

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

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

注意：意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁和独占表锁发生冲突。

表锁和行锁是读读共享、读写互斥、写写互斥的

如果没有【意向锁】，那么在加【独占行锁】时，就需要遍历表里所有记录，查看是否有记录存在独占锁，效率会很慢。有了【意向锁】，由于在对记录加独占锁前，会加上表级别的意向独占锁，那么在加【独占行锁】时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

得出：意向锁得目的是为了快速判断表里是否有记录被加锁。

1.2.4、AUTO-INC 锁

表中的主键通常都会设置为自增的，这是通过对主键字段声明【AUTO-INCREMENT】属性实现的，之后可以再插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过【AUTO-INC】锁实现的。

【AUTO-INC】锁是特殊的表锁机制，锁不是在一个事务提交之后才释放的，而是在执行完插入语句之后立即释放。在插入数据时，会加上一个表级别的【AUTO-INC】锁，然后为被【AUTO-INCREMENT】修饰的字段的值是连续递增的值，等插入语句执行完成后，才会把【AUTO-INC】锁释放掉。

当一个事务在持有【AUTO-INC】锁的过程中，其他事物如果要向该表插入语句都会被阻塞，从而保证插入数据时，被【AUTO-INCREMENT】修饰的字段的值是连续递增的。

问题来啦 ~ 【AUTO-INC】锁在对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞，如何解决呢？

答：在MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。一样的是在插入数据的时候，会被【AUTO-INCREMENT】修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

InnoDB 引擎中提供了一个【innnodb_autoinc_lock_mode】的系统变量，用来控制选择用【AUTO-INC】锁，还是轻量级的锁。

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

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

举个🌰：

session A 往表 t 中插入了 4 行数据，然后创建了一个相同结构的表 t2 ，然后两个 session 同时执行向表 t2 中插入数据。如果 innodb_autoinc_lock_mode = 2，意味着【申请自增主键后就释放锁，不必等插入语句执行完】。就可能出现如下情况：

session B 先插入两个记录，（1，1，1）、（2，2，2）
然后，session A 来申请自增 id 得到 id = 3，插入了（3，5，5）
之后，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 中插入数据的场景。所以，在备库上执行了 session B 的 insert 语句，生成的结果里面，id 都是连续的。此时主从库就发生了数据不一致。

想解决这个问题，binlog 日志格式要设置为 row，这样在 binlog 里面记录的是主库分配的自增值，到备库执行的时候，主库的自增值是什么，从库的子增值就是什么。所以当 innodb_autoinc_lock_mode = 2 时，并且 binlog_format = row，既能提升并发性能，又不会出现数据不一致性问题。

1.3、行级锁

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

普通的 select 语句是不会对记录加锁的，因为它属于快照读。如果要在查询时对记录加行锁，可以使用下面这两个方式，这两种查询会加锁的语句称为锁定读。

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

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

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

共享锁（S锁）是 读读共享、读写互斥

独占锁（X锁）是 读写互斥、写写互斥

行级锁主要有三类：

Record Lock：记录锁，也就是仅仅把一条记录锁上

Gap Lock：间隙锁，锁定一个范围，但是不包含记录本身

Next-Key Lock：Record Lock 和 Gap Lock 的组合，锁定一个范围，并且锁定记录本身

1.3.1、Record Lock

Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁（共享锁）和 X 锁（独占锁）之分的：

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

1.3.2、Gap Lock

Gap Lock 称为记录锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

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

1.3.3、Next-Key Lock

Next-Key-Lock 称为临建锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身，举个🌰：

此时把【汤面】也包含进来了啦~

所以，next-key lock 既能保护该记录，又能阻止其他事物将新记录插入到被保护记录前面的间隙中。

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

1.3.4、插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事物加了间隙锁（next-key lock 也包含间隙锁）。如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

举个🌰：

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

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

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

如果说间隙锁锁住的是一个区间，那么【插入意向锁】锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。插入意向锁与间隙锁的另一个非常重要的差别是：尽管【插入意向锁】也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内是可以的）。

二、MySQL 是怎么加锁的？

2.1、为什么 SQL 语句会加行级锁？

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁，所以后面的内容都是基于 InnoDB 引擎的。

所以，在说 MySQL 是怎么加行级锁的时候，其实就是再说 InnoDB 引擎是怎么加行级锁的。

普通的 select 语句是不会对记录加锁的（除了串行化隔离级别），因为它属于快照读，是通过 MVCC（多版本并发控制）实现的。

如果要在查询时对记录加行级锁，可以使用下面两个方式，这两种查询会加锁的语句称为锁定读。

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

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

上面这两条记录必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin 或者 start transaction 开启事务的语句。

除了上面的两条锁定读语句会加行级锁之外，update 和 delete 操作都会加行级锁，且锁的类型都是独占锁（X型锁）。

//对操作的记录加独占锁（X型锁）
update table ... where id = 1;

//对操作的记录加独占锁（X型锁）
delete from table where id = 1;

共享锁（S锁）读读共享，读写互斥
独占锁（X锁）写写互斥，读写互斥

2.2、MySQL是怎么加行级锁的？

2.2.1、唯一索引等值查询

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

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

PS：本篇文章中的【唯一索引】是用【主键索引】作为案例说明的，加锁只加在主键索引项上。许多人误认为如果是二级索引的【唯一索引】，加锁也是只加在二级索引项上。这个是不对的，这里特别说明一下，如果是用二级索引（不管是不是非唯一索引，还是唯一索引）进行锁定读查询的时候，除了会对二级索引项加行级锁（如果是唯一索引的二级索引，加锁规则和主键索引的案例相同），而且还会对查询到的记录的主键索引项加【记录锁】。

①、记录存在的情况

假设事务 A 执行了一条等值查询语句，查询的记录是【存在】于表中的。那么，事务 A 会为 id = 1 的这条记录就会加上 X 型的记录锁。

接下来如果有其他事物，对 id = 1 的记录进行更新或者删除操作的话，这些操作都会被阻塞，因为更新或者删除操作也会对记录加 X 型的记录锁，而 X 锁和 X 锁之间是互斥关系。

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

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

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

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

②、记录不存在的情况

假设事务 A 执行了这条等值查询语句，查询的记录是【不存在】于表中的。

select * from user where id = 2 for update;

此时，事务 A 在 id = 5 记录的主键索引上加的是间隙锁，锁住的范围是（1，5）

接下来如果有事务插入 id 值为 2，3，4 这一些记录的话，这些插入语句都会发生阻塞。如果其他事务插入的 id = 1 或者 id = 5 的记录话，并不会报主键冲突的错误，因为已经存在 id = 1 和 id = 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 的新记录就行

为什么不可以针对不存在的记录加记录锁？锁是加在索引上的，而这个场景下查询的记录是不存在的，自然就没办法锁住这条不存在的记录

2.2.2、唯一索引范围查询

范围查询和等值查询的加锁规则是不同的。当唯一索引进行范围查询时，会对每一个扫描到的索引加 next-key lock，然后如果遇到以下情况，会退化为记录锁或者间隙锁（PS：由于可能会对一行加多种不同的锁，这里的 “退化” 是指加的多种的锁中等级最低的，锁等级顺序如下：next-key lock > gap lock > record lock）：

情况1：针对【大于等于】的范围查询，因为存在等值查询的条件，那么如果等值查询的记录是存在于表中，那么该纪录的索引中的 next-key 锁会退化成记录锁。

情况2：针对【小于或者小于等于】的范围查询，要看条件值得记录是否存在于表中：

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

当条件值的记录在表中，如果是【小于】条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key lock 会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加next-key lock ；如果【小于等于】条件的范围查询，扫描到终止范围查询的记录时，该记录的索引 next-key lock 不会退化成间隙锁。其他扫描到的记录，都是在这些记录的索引上加 next-key lock。

①、情况1中针对【大于】的范围查询情况：

假设事务 A 执行了以下语句：

select * from user where id > 15 for update;

事务 A 加锁变化如下：

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

由上图可得：

在 id = 20 这条记录的主键索引上，加了范围为（15，20] 的 next-key lock ，意味着其他事物即无法更新或者删除 id = 20 的记录，同时无法插入 id 值为 16，17，18，19 的这一些新记录。

在特殊记录（supremum pseudo-record）的主键索引上，加了范围为（20，+无穷] 的 next-key lock 意味着其他事物无法插入 id 值大于 20 的这一些新记录。

②、情况1中针对【大于等于】的范围查询情况：

假设事务 A 执行了以下语句：

select * from user where id >= 15 for update;

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

最开始要找的第一行是 id = 15 ，由于查询该记录是一个等值查询（等于 15），所以该主键索引的next-key lock 会退化为记录锁，也就是仅锁住 id = 15 这一行记录。
由于是范围查找，就会继续往后找存在的记录，扫描到的第二行是 id = 20 ，于是对该主键索引加的是范围为 (15，20] 的 next-key lock。
接着扫描到第三行的时候，扫描到了特殊记录（supremum pseudo-record），于是对该主键索引加的范围为 (20，+无穷] 的 next-key lock 。
停止扫描。

由上图可知：

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

情况2中针对【小于或者小于等于】的范围查询情况：

①、针对【小于】的范围查询时，查询条件值得记录【不存在】表中的情况。

假设事务 A 执行了以下语句：

select * from user where id < 6 for update;

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

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

由上图可知：

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

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

②、针对【小于等于】的范围查询时，查询条件值得记录【存在】表中得情况

假设事务 A 执行了这条范围查询语句：

select * from user where id <= 5 for update;

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

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

由上图可知：

在 id = 1 这条记录的主键索引上，加了范围为（-无穷，1] 的 next-key lock 意味着其他事物既无法更新或者删除 id = 1 的这一条记录，同时也无法插入 id 小于 1 的这些新记录
在 id = 5 这条记录的主键索引上，加了范围为（1，5] 的 next-key lock ，意味着其他事物既无法更新或者删除 id = 5 的这一条记录，同时也无法插入 id 值为 2，3，4 的这一些新记录

③、针对【小于】的范围查询，且查询条件值得记录（id = 5）存在于表中的情况

假设事务 A 执行了以下语句：

select * from user where id < 5 for update;

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

最开始要找的第一行是 id = 1，于是对该记录加的是范围为（-无穷，1] 的 next-key lock

由于是范围查找，就会继续往后找存在的记录，扫描到的第二行是 id = 5 ，该记录是第一条不满足 id < 5 条件的记录，于是该记录的锁会退化成间隙锁，锁的范围是（1，5）

由于找到了第一条不满足 id < 5 条件的记录，于是停止扫描

由上图可知：

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

2.2.3、非唯一索引等值查询

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

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

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

当查询到的记录【不存在】时，扫描到的第一条不符合条件的二级索引记录，该二级索引的 next-key lock 会退化成间隙锁，因为不存在满足查询条件的记录，所以不会对主键索引加锁。

①、针对非唯一索引等值查询时，查询的值不存在的情况

假设事务 A 对非唯一索引（age）进行了等值查询，且表中不存在 age = 25 的记录

select * from user where age = 25 for update;

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

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

由上图可知：

事务 A 在 age = 39 记录的二级索引上，加了范围为（22，39）的 X 型间隙锁。此时如果有其他事物插入了 age 值为 23，24，25，26，.... ，38 这些新记录，那么这些插入语句都会发生阻塞。不过对于插入 age = 39 记录的语句，在一些情况是可以成功插入的，而一些情况就无法成功插入。

汤圆有个疑惑，什么情况下插入语句会发生阻塞呢？

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

PS：二级索引是如何存放记录的呢？答：二级索引树是按照二级索引值（age 列）按顺序存放的，在相同的二级索引值情况下，再按主键 id 的顺序存放。

问题又来啦~ ！当有一个事务持有二级索引的间隙锁（22，39）时，什么情况下，可以让其他事物的插入 age = 22 或者 age = 39 这条记录的语句成功？什么情况下，插入 age = 22 或者 age = 39 记录时的语句会被阻塞？

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

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

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

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

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

②、针对非唯一索引等值查询时，查询的值存在的情况

假设事务 A 对非唯一索引（age）进行了等值查询，且表中存在 age = 22 的记录。执行以下语句

select * from user where age = 22 for update;

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

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

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

二级索引：

在 age = 22 这条记录的二级索引上，加了范围为（21，22] 的 next-key lock ，意味着其他事物无法更新或者删除 age = 22 的这一些新记录，不过对于插入 age = 20 和 age = 21 新记录的语句，在一些情况下是可以成功插入的，而一些情况则无法插入成功。
在 age = 39 这条记录的二级索引上，加了范围（22，39）的间隙锁。意味着其他事物无法插入 age 值为 23，24，...，38 的这一些新记录。不过对于插入 age = 22 和 age = 39 记录的语句，在一些情况可以插入成功，而一些情况无法插入成功。

在 age = 22 这条记录的二级索引上，加了范围为（21，22] 的 next-key lock ，意味着其他事务无法删除 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 值，其他事物插入 age 值为 22 的新记录时，如果插入的新记录的 id 值小于 10 ，那么插入语句会发生阻塞，如果插入的新记录的 id 值大于 10，还要看该新记录插入的位置的下一条记录是否有间隙锁，如果没有间隙锁则可以插入成功，如果有间隙锁，则无法插入成功。

一个问题呼之欲出！！！：为什么在这个案例中，需要给二级索引加范围（22，39）的间隙锁？

宝子们，先想一想，加锁最初是为了什么呢？是为了避免幻读现象的发生呀~，在非唯一索引上加了范围为（21，22] 的 next-key lock ，是无法完全锁住 age = 22 新记录的插入，因为对于是否可以插入 age = 22 的新记录值还需要看插入的新记录的 id 值，其他事物插入 age = 22 的新记录时，如果插入的新纪录的 id 值小于 10，那么插入语句会发生阻塞，如果插入的新记录的 id 值大于 10，则可以插入成功

也就是说，只有在二级索引上加发范围为（21，22] 的 next-key lock ，其他事物是有可能插入 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 的记录，正好该记录的二级索引上有间隙锁，所以这条插入语句会被阻塞，无法插入成功，这样就可以避免幻读现象的发生。

2.2.4、非唯一索引范围查询

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

假设事务 A 执行下面这条查询语句：

select * from user where age >= 22  for update;

事务 A 的加锁变化：

最开始要找的第一行是 age = 22，虽然范围查询语句包含等值查询，但是这里不是唯一索引范围查询，所以是不会发生退化锁的现象，因此对该二级索引记录加 next-key lock ，范围是（21，22] 。同时，对 age = 22 这条记录的主键索引加记录锁，即对 id = 10 这一行记录的主键索引加记录锁。
由于是范围查询，接着继续扫描已经存在的二级索引记录，扫描的第二行是 age = 39 的二级索引记录，于是对该二级索引记录加 next-key lock ，范围是（22，39] ，同时，对 age = 39 这条记录的主键索引加记录锁，即对 id = 20 这一行记录的主键索引加记录锁。
虽然我们看到表中最后一条二级索引记录是 age = 39 的记录，但是实际在 InnoDB 存储引擎中，会用一个特殊的记录来标识最后一条记录，该特殊记录【supremum pseudo-record】，所以扫描第二行的时候，也就扫描到了这个特殊记录的时候，会对该二级索引记录加的是范围为（39，+无穷] 的 next-key lock。
停止查询

主键索引（id 列）：

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

二级索引（age 列）：

在 age = 22 这条记录的二级索引上，加了范围为（21，22] 的 next-key lock，意味着其他事务无法更新或者删除 age = 22 的这一些新记录，不过对于是否可以插入 age = 21 和 age = 22 的新记录，还需要看新记录的 id 值，有些情况是可以成功插入的，而一些情况则无法插入（如上文所述）
在 age = 39 这条记录的二级索引上，加了范围为（22，39] 的 next-key lock ，意味着其他事务无法更新或者删除 age = 39 的这一些记录，也无法插入 age 值为 23，24，25，...，38 的这一些新记录。不过对于是否可以插入 age = 22 和 age = 39 的新记录，还需要看新记录的 id 值，有些情况是可以成功插入的，而一些情况无法插入。
在特殊记录【supremum pseudo-record】的二级索引上，加了范围为（39，+无穷] 的 next-key lock，意味着其他事务无法插入 age 值大于 39 的这些新记录

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

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

2.2.5、没有加索引的查询

前面的案例，我们的查询语句都有使用索引查询，也就是查询记录的时候，是通过索引扫描的方式查询的，然后对扫描出来的记录进行加锁。

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

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

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

三、 update 没加索引会锁全表？

当我们执行 update 语句时，实际是会对记录加独占锁的，如果其他事务对持有独占锁的记录进行修改时会被阻塞的。另外，这个锁并不是执行完 update 语句就会释放的，而是会等事务结束时才会释放。

在 update 语句的 where 条件没有使用索引，就会全表扫描，于是就会对所有记录加上 next-key lock ，相当于把整个表都锁住了而不是使用了表锁。那 update 语句的 where 带上索引就能避免全表记录扫描了吗？并不是，还是得看这条语句在执行过程中，优化器最终选择的是索引扫描还是全表扫描，如果走了全表扫描，就会对全表的记录加锁了。

如何解决呢？

可以将 MySQL 里的 sql_safe_updates 参数设置为 1 ，开启安全更新模式。当设置为 1 时， update 语句必须满足以下条件才能执行成功：

使用 where ，并且 where 条件中必须有索引列
使用 limit
同时使用 where 和 limit ，此时 where 条件中可以没有索引列

delete 语句必须满足以下条件才能执行成功：

同时使用 where 和 limit ，此时 where 条件中可以没有索引列

如果 where 条件带上了索引列，但是优化器最终扫描选择的是全表，而不是索引的话，我们可以用 force index( [ index_name ] ) 可以告诉优化器使用哪个索引，以此避免有几率锁全表带来的隐患。

四、MySQL 记录锁 + 间隙锁可以防止删除操作而导致的幻读吗？

T1 时间执行的结果有 5 条记录，而 T2 时间执行结果有 6 条记录，那就产生了幻读的问题
T1 时间执行的结果有 5 条记录，而 T2 时间执行结果有 4 条记录，也是产生了幻读的问题

当我们使用 update，delete，select ... for update 等具有加锁性质的语句时，如果没走索引，就会导致全表扫描，会将整个表都锁住。此时的更新删除等操作都不能进行，所以可防止删除操作导致的幻读。

四、MySQL 死锁了，怎么办？

4.1、死锁🌰

先举一个死锁的🌰：

此时表中已经有 6 条数据：

主键索引为 id ，二级索引为 order_no

id      order_no     create_date
1         1001          2021
2         1002          2021
3         1003          2021
4         1004          2021
5         1005          2021
6         1006          2021

事务 A 在执行第一条语句的时候，在二级索引上加的是 X 型的 next-key lock，锁范围是（1006，+无穷] 此时如果此时事务 B 直接执行第二条语句，就会被阻塞。因为插入意向锁和间隙锁是冲突的（insert 语句会先加一个插入意向锁判断插入的位置有没有间隙锁），所以当其他事务持有该间隙的间隙锁时，需要等待其他事物释放之后，才能获取意向锁。但是间隙锁与间隙锁之间是兼容的，所以两个事务中的 select ... for update 语句不会影响，而是各自加了一个间隙锁。在事务 A 和 B 都执行完 select ... for update 语句之后，接下来的插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是造成了循环等待，导致死锁。

为什么间隙锁之间是兼容的呢？

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

共同间隙：

两个间隙锁的间隙区间完全一样
一个间隙锁包含的间隙区间是另一个间隙锁包含间隙区间的子区

注意：next-key lock 是包含间隙锁 + 记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。这就奇怪了，上文不是说锁住的区域不都是（1006，+无穷] 的 next-key lock 吗？为什么都能获取呢？听我来说：虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们还是要考虑 X 型和 S 型的关系。X 型和 X 型的记录锁是冲突的，但是对于范围为（1006，+无穷] 的 next-key lock ，两个事务是可以同时持有的，不会冲突。因为 +无穷不是一个真实的记录，不需要考虑 X 型与 S 型的关系。

4.2、Insert 语句是怎么加行级锁的？

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

什么是隐式锁呢？

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

隐式锁就是在 insert 过程中不加锁，只有在特殊情况下，才会将隐式锁转换为显示锁下面用两个场景分别举🌰：

如果记录之间加有间隙锁，为了避免幻读，此时是不能插入记录的（这个就不举🌰了，前面讲过了，很明显会上锁然后阻塞）
如果 insert 语句和已有的记录存在唯一键冲突，此时也不能插入记录

4.2.1、遇到唯一键冲突

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

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

接下来，分析两个事务执行过程中，执行了相同的 insert 语句的场景。

表中有如下数据，order_no 为唯一二级索引
id     order_no     create_date
1       1001          2001
2       1002          2001
3       1003          2001
4       1004          2001
5       1005          2001

两个事务加锁的过程：

事务 A 先插入 order_no 为 1006 的记录，可以插入成功，此时对应的唯一二级索引记录被【隐式锁】保护，此时还没有实际的锁结构

接着，事务 B 也插入 order_no 为 1006 的记录，由于事务 A 已经插入 order_no 值为 1006 的记录，所以事务 B 在插入二级索引记录时会遇到重复的唯一二级索引列值，此时事务 B 想获取一个 S 型 next-key lock ，但是事务 A 并未提交，事务 A 插入的 order_no 值为 1006 的记录上的【隐式锁】会变【显示锁】且锁的类型为 X 型的记录锁，所以事务 B 向获取 S 型 next-key lock 时会遇到锁冲突，事务 B 进入阻塞状态。

4.3、如何避免死锁呢？

死锁产生的条件：循环等待，占有并等待，不可剥夺，互斥四大条件缺一不可

在数据库层面中，有两种策略通过【打破循环等待条件】来解除死锁状态：

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

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

芒果甜汤圆

关注

23
点赞
踩
28

收藏

觉得还不错? 一键收藏
打赏
1
评论
MySQL锁篇

这就奇怪了，上文不是说锁住的区域不都是（1006，+无穷] 的 next-key lock 吗？那么当在 age = 39 这条记录的二级索引上加了范围为（22，39）的间隙锁后，其他事务是无法插入一个 age = 22，id = 12 的新记录，因为当其他事务插入一条 age = 22，id = 12 的新记录时，在二级索引定位到插入的位置，而该位置的下一条是 id = 20，age = 39 的记录，正好该记录的二级索引上有间隙锁，所以这条插入语句会被阻塞，无法插入成功，这样就可以避免幻读现象的发生。
复制链接

扫一扫