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

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

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

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

一、理解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加上排序时加锁规定会有肯定的变动，在前期文章中咔咔也会一直的提供很多案例供大家查看。

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

最新文章

一、理解next-key lock

二、next-key lock 加锁规定

三、惟一索引等值查问

四、一般索引等值查问

五、主键索引范畴锁

六、一般索引范畴锁

七、一般索引顺叙范畴锁

八、总结