mysql实战-RR下的锁机制详解

昨天我的一位同学问我能不能写一篇rr下的锁机制，感觉非常灵异，首先我要肯定这个想法，因为直到本人所使用的的mysql8.0.18版本为止，RR下的加锁机制都不是那么容易理解。

首先你要知道，在RR下，锁的基本单位是next-key lock,并且被查询到的数据都会加锁，也就是说除非特殊情况，否则一律是next-key lock。如果你不明白什么是next-key lock,那么你可能需要百度。简单理解就是行锁加间隙锁，行锁锁记录，间隙锁锁索引间隙（这个很重要）。

现在我们新建一张表，语句如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(10)  NOT NULL,
  `password` varchar(20)  NOT NULL,
  `age` int(4) NOT NULL,
  `hobbys` varchar(40)  NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `age` (`age`) USING BTREE
) ENGINE=InnoDB CHARSET=utf8;

我们往其中插入几条记录

INSERT INTO `demo03`.`user`(`id`, `username`, `password`, `age`, `hobbys`) VALUES (0, '东方青天0', '123', 0, '唱跳rap');
INSERT INTO `demo03`.`user`(`id`, `username`, `password`, `age`, `hobbys`) VALUES (5, '东方青天5', '123', 5, '唱跳rap');
INSERT INTO `demo03`.`user`(`id`, `username`, `password`, `age`, `hobbys`) VALUES (7, '东方青天7', '123', 7, '唱跳rap');
INSERT INTO `demo03`.`user`(`id`, `username`, `password`, `age`, `hobbys`) VALUES (10, '东方青天10', '123',10, '唱跳rap');
INSERT INTO `demo03`.`user`(`id`, `username`, `password`, `age`, `hobbys`) VALUES (11, '东方青天11', '123', 11, '唱跳rap');
INSERT INTO `demo03`.`user`(`id`, `username`, `password`, `age`, `hobbys`) VALUES (12, '东方青天12', '123', 12, '唱跳rap');

唯一索引等值查询

注意此时表中有一个唯一索引id和非唯一索引age。现在我们在唯一索引上执行等值查询。

 看到这个结果你肯定觉得奇怪，前面不是说了锁的基本单位是next-key lock吗？如果是next-key lock 的话id=6的记录是插不进去的，难道是隔离级别的问题？

这里我要跟你说一个特殊情况，唯一索引查询命中了一行，那么next-key lock会退化成行锁，只锁命中的那一行，我们可以用非唯一索引继续尝试这个操作。

非唯一索引等值查询

 

在这个操作中，age=7命中了行，但是锁的基本单位是next-key lock，这里锁的范围就是（5,7],但是由于这里的age不是唯一索引，查询并不能终止，必须继续向右边查询，直到查询到age=10这一行，显然10与7是不相等的，这里mysql将会做另一个优化，非唯一索引等值查询查询到最右边不符合的第一个值，会退化成间隙锁。所以这里锁的范围是age=(5,10),当前行是行锁，也就是说age=7这一行是行锁，这个后面不再赘述。

唯一索引范围查询

 

根据上面我们说到的，唯一索引命中行会退化成行锁，所以id>=7由（5,7]变成锁id=7这一行，id<10仍然是next-key lock，锁（7,10],所以一起就是[7,10]。

 非唯一索引范围查询

由于没有退化成行锁，所以 (5,10]都被锁住了。

LIMIT关键字对锁的影响

现在我们要说一个比较特殊的例子，大家都知道，非唯一索引是有可能重复的，我们现在修改表数据操作如下

 这个结果我们很容易推断出来，age（7,15)被锁了。我们做如下操作

 可以看到，当加了limit时，(10,15)并没有加锁，这就是我要强调的另外一点，只有被查询到的数据才会加锁，上面虽然我们知道只有两行，但是查询引擎并不清楚，所以会一直向右查询，但是这里由于使用了limit，我们限制了只查询两条记录，所以在查询到第二条记录时，查询就已经结束了，后面的数据并不在查询范围内，所以不会加锁。

文章编写不易，转载请声明出处，谢谢。