关于mysql:聊聊MySQL的加锁规则死磕MySQL系列-十五

大家好，我是咔咔 不期速成，日拱一卒

本期来聊聊 MySQL 的加锁规定，晓得这些规定后能够判断 SQL 语句的加锁范畴，同时也能够写出更好的 SQL 语句，避免幻读问题的产生，在能力范畴内最大水平的晋升 MySQL 并发处理事务能力。

当初你应该晓得了 MVCC 解决了快照读下的幻读问题，但以后读的幻读问题还是基于锁解决的，也就是 next-key lock。

最新文章

死磕 MySQL 系列总目录

为什么 MySQL 字符串不加引号索引生效？《死磕 MySQL 系列 十一》

关上 order by 的大门，一探到底《死磕 MySQL 系列 十二》

重重封闭，让你一条数据都拿不到《死磕 MySQL 系列 十三》

闯祸了，生成环境执行了 DDL 操作《死磕 MySQL 系列 十四》

一、理解 next-key lock

在文章幻读：据说有人认为我是被 MVCC 干掉的这期文章中，具体阐明了幻读在以后读、快照读下的解决形式。

快照读简略来说就是简略的 select 操作，没有加任何锁，在 Innodb 存储引擎下执行简略的 select 操作时，会记录下以后的快照读数据，之后的 select 会沿用第一次快照读的数据，即便有其它事务提交也不会影响以后的 select 后果，因而通过快照读查问的数据尽管是统一的，但有可能不是最新的数据，而是历史数据。

这个是从官网文档中获取的材料，解释在以后读下 Innodb 应用 next-key lock 锁来解决幻读问题。

To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the“gap”before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

大抵意思，为了避免幻读，Innodb 应用 next-key lock 算法，将行锁 (record lock) 和间隙锁 (gap lock) 联合在一起。Innodb 行锁在搜寻或者扫描表索引时，会在遇到的索引记录上设置共享锁或者排它锁，因而行锁理论是索引记录锁。另外，在索引记录上设置的锁同样会影响索引记录之前的“间隙(gap)”。即 next-key lock 是索引记录行加上索引记录之前的“gap”上的间隙锁定。

二、next-key lock 加锁规定

加锁规定总结为以下几点，不同 MySQL 版本会有渺小的差别

• 查问过程中只有拜访的数据都会加锁，加锁的根本单位是 next-key lock，左开右闭
• 惟一索引等值查问，next-key lock 进化为行锁
• 索引等值查问，须要拜访到第一个不满足条件的值，此时的 next-key lock 会进化为间隙锁
• 索引范畴查问须要拜访到不满足条件的第一个值为止

之前看过丁老师的文章说是在惟一索引下，范畴查问会拜访到不满足条件的第一个值为止，这个问题在 MySQL8.0.18 曾经修复了

目前咔咔应用的 MySQL 版本是 8.0.26，接下来依据这几条规定设计几条 SQL，一起来看看都锁了那些数据。

创立 next_key_lock 表，建表的初始化语句如下。

CREATE TABLE `next_key_lock` (`id` int(11) NOT NULL AUTO_INCREMENT,
  `class` tinyint(4) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_class` (`class`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO next_key_lock (`class`,`name`) VALUES (1,'咔咔'),(3,'小刘'),(8,'小张'),
(15,'小李'),(20,'张但'),(25,'王五'),(25,'李四');

三、惟一索引等值查问

下图是 SQL 的执行流程，分为了三个终端，依照终端程序来执行 SQL

剖析这条 SQL 满足那些规定

规定一：查问过程中只有拜访到的数据都会加锁，加锁的根本单位是 next-key lock，左开右闭状态。

规定二：惟一索引等值查问，next-key lock 进化为行锁。

规定三：索引等值查问，须要拜访到第一个不满足条件的值，此时的 next-key lock 会进化为间隙锁

依据规定一，加锁范畴为(7,∞]

依据规定二，进化为行锁，但显著此条 SQL 不满足条件，因为表里边就不存在 id= 9 的这条记录，所以此条规定不失效

依据规定三，next-key lock 进化为间隙锁，加锁范畴为(7,∞)

论断

得悉惟一索引等值查问时，行数据存在的时候是行锁，行数据不存在，那就是间隙锁。

因而终端 2 的语句会始终处于期待状态，直到终端 1 执行实现。

四、一般索引等值查问

剖析这条 SQL 满足那些规定

规定一：查问过程中只有拜访到的数据都会加锁，加锁的根本单位是 next-key lock，左开右闭状态。

规定二：索引等值查问，须要拜访到第一个不满足条件的值，此时的 next-key lock 会进化为间隙锁

依据规定一，加锁范畴是(3,8]

依据规定二，须要拜访到第一个不满足的值，加锁范畴(8,15]，又因为会进化为间隙锁，加锁范畴变为(8,15)

论断

三条 SQL 执行后，你看到的景象是 MySQL2 执行胜利，MySQL3SQL 期待

MySQL3 要退出的值是 9，在锁范畴内所以须要等 MySQL1 提交事务后才可执行胜利。

为什么 MySQL2 为什么会执行胜利

总结的加锁规定中，查问过程中拜访到的数据都会加锁，但 MySQL2 应用的笼罩索引，所以并不需要回表查问主键索引，所以主键索引上是没有加任何锁的。

你要了解这块就须要晓得主键索引、一般索引的索引构造，在 B +tree 中主键索引叶子节点存储的是整行数据，而一般索引叶子节点存储的是主键的值。

扩大

当初你晓得了在这个例子中，lock in share mode 值锁笼罩索引，然而如果是 for update 就会给主键索引上满足条件的行加上行锁。所以你也晓得了应用了笼罩索引是防止不了数据被更新的，若想实现数据防止更新就须要绕过笼罩索引的优化。

当初你应该晓得应用 for update 会给主键索引加锁，如果查问条件为一般索引但值是存在多个雷同数据的，此时的加锁就会依据主键索引加锁。

五、主键索引范畴锁

从上图得悉 MySQL2 和 MySQL3 都处于期待 MySQL1 中

剖析这条 SQL 满足那些规定

规定一：拜访到的数据都会加锁

规定二：惟一索引等值查问，next_key_lock 进化为行锁

规定三：索引范畴查问须要拜访到不满足条件的第一个值为止

依据规定一，加锁范畴(7,8]

依据规定二，进化为行锁，加锁范畴只是 id= 8 这一行（后边解释）

依据规定三，范畴查问就往后持续找，加锁范畴(8,∞]

论断

此条 SQL 加锁范畴，行锁 id=8，next_key lock(8,∞]

问题：为什么从 next-key lock 进化为行锁

首先你须要明确所谓的等值判断和范畴判断，指的是这一行数据被查问选中的时候走的判断条件是通过 a=b 还是 a>b 或 a <b 来确定的，直白点就是这行数据是通过等值来的还是范畴查问来的。

从 SQL 返回后果可得悉数据是依据 id= 8 来的，因而 next-key lock 会进化为行锁。

六、一般索引范畴锁

执行 SQL 为

select * from next_key_lock where class >= 8 and class<10 for update;

能够看到这个 SQL 跟第五案例的 MySQL1 的惟一区别是一般索引没有进化行锁的规定。

剖析这条 SQL 满足那些规定

规定一：索引等值查问须要拜访到第一个不满足的值，next_key lock 进化为间隙锁

规定二：索引范畴查问须要拜访到不满足条件的第一个值为止

依据规定一，加锁范畴(7,8]

依据规定二，加锁范畴(8,15]

论断

加锁范畴为(7,8]、(8,15]

问题：为什么没有进化为间隙锁

认真看规定，索引等值查问须要拜访到不满足的值才会进化为间隙锁，此时是能够拜访到 8 这个数据的，因而不会进化为间隙锁。

七、一般索引顺叙范畴锁

在以上的所有案例中都是默认正序规定，接下来看下顺叙时的加锁规定是怎么样的

执行 SQL 为

select * from next_key_lock where class >= 15 and class<=20  order by desc lock in share mode;

因为 SQL 加上了 order by，因而第一个要定位 class 索引最左边的值，也就是 class=20，因为 class 是一般索引等值查问，因而会加上 next-key lock 左开右闭(15,20]，一般索引等值查问会拜访到不满足条件的值为止，所以还会持续扫描，直到遇到 25，又会加上一个 next-key lock (20,25]，又因为 25 不满足查问条件，因而会进化为间隙锁(20,25)

还有一个条件是 class >= 15，向左扫描到 class = 8 才会停下来晓得了是小于 15 了，加锁单位是 next-key loc，左开右闭范畴是(3,8]

又因为查问是 *，绕过了笼罩索引，须要回表查问，因而给主键 ID 也会加锁，加锁为 id=4，id= 5 两个行锁。

论断

因而这条 SQL 加锁范畴在索引 class 是(3,25)，主键索引上 id=4,5 两个行锁。

八、总结

本期文章带大家理解 next_key lock 的加锁范畴，并且给大家总结了四条加锁规定，通过五个实战案例给再给大家说几个留神点。

惟一索引等值查问时 next-key lock 进化为行锁，这里指查问到数据，若没有查到数据则仍然是间隙锁

一般索引等值查问 next-key lock 进化为间隙锁

最初一点当 SQL 加上排序时加锁规定会有肯定的变动，在前期文章中咔咔也会一直的提供很多案例供大家查看。

保持学习、保持写作、保持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮忙，我是咔咔，下期见。