MySQL锁 - MySQL行锁规则 - 《Java开发》

唯一索引列等值查询
- 当唯一索引列的等值查询命中时：
- 当唯一索引列的等值查询未命中时：
唯一索引列范围查询
- 当边界值存在于表中，同时命中的情况：
- 当边界值不存在于表中时，不可能命中，故只有未命中一种情况：
普通索引列等值查询
普通索引列等值查询+limit
普通索引列范围查询
参考文档

记录锁、间隙锁和 Next-key Lock 的加锁规则是十分复杂的，关于加锁规则的叙述将分为三个方面：唯一索引列、普通索引列和普通列，每一方面又将细分为等值查询和范围查询两方面。
需要注意的是，这里加的锁都是指排它锁。在开始之前，先来回顾一下示例表以及表中可能存在的行级锁。


mysql> select * from user;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  5 | 重塑   |    5 |
| 10 | 达达   |   10 |
| 15 | 刺猬   |   15 |
+----+--------+------+
3 rows in set (0.00 sec)

表中可能包含的行级锁首先是每一行的记录锁——(5,重塑,5)，(10,达达,5)，(15,刺猬,15)。
假设 user 表的索引值有最大值 maxIndex 和最小值 minIndex，user 表还可能存在间隙锁(minIndex,5)，(5,10)，(10,15)，(15,maxIndex)。
共三个记录锁和四个间隙锁。

唯一索引列等值查询

首先来说唯一索引列的等值查询，这里的等值查询可以分为两种情况：命中与未命中。

当唯一索引列的等值查询命中时：

sessionA	sessionB
begin;
select * from user where id=5 for update;

| insert into user values(1,’斯斯与帆’,1),(6,’夏日阳光’,6),(11,’告五人’,11),(16,’面孔’,16); | |
| update user set age=18 where id=5;（Blocked） | |
| update user set age=18 where id=10; | |
| update user set age=18 where id=15; |

上表中 sessionB 的执行结果是除了 id=5 行的更新语句被阻塞，其他语句都正常执行。
sessionB 中的 insert 语句是为了检查间隙锁，update 语句是为了检查记录锁（行锁）。执行结果表明 user 表的所有间隙都没有被上锁，记录锁中只有 id=5 这一行被上锁了。
MySQL行锁规则 - 图1
所以，当唯一索引列的等值查询命中时，只会给命中的记录加锁。

当唯一索引列的等值查询未命中时：

sessionA	sessionB
begin;
select * from user where id=6 for update;
	insert into user values (2,’反光镜’,2);
	update user set age=18 where id=5;
	insert into user values (6,’夏日阳光’,6);（Blocked）
	update user set age=18 where id=10;
	insert into user values (11,’告五人’,11);
	update user set age=18 where id=15;
	insert into user values (16,’面孔’,16);

上表的执行结果是 sessionB 中 id=6 的记录插入被阻塞，其他语句正常执行。
根据执行结果可以知道 sessionA 给 user 表加的锁是间隙锁(5,10)。 MySQL行锁规则 - 图2
所以，当唯一索引列的等值查询未命中时，会给id值所在的间隙加上间隙锁。

唯一索引列范围查询

范围查询比等值查询要更复杂一些，它需要考虑到边界值存在于表中，以及是否命中边界值。
首先来看边界值存在于表中，但未命中的情况：

sessionA	sessionB
begin;
select * from user where id<10 for update;
	insert into user values (1,’斯斯与帆’,1);（Blocked）
	update user set age=18 where id=5;（Blocked）
	insert into user values (6,’夏日阳光’,6);（Blocked）
	update user set age=18 where id=10;（Blocked）
	insert into user values (11,’告五人’,11);
	update user set age=18 where id=15;
	insert into user values (16,’面孔’,16) ;

此时 sessionA 给 user 表加上的锁是记录锁 id=5，id=10 以及间隙锁(minIndex,5)，(5,10)。
我们知道间隙锁+记录锁就是 Next-key Lock，所以上述的加锁情况可以看作是两条 Next-key Lock：(minIndex, 5]，(5,10]，即 Next-key Lock —— (minIndex,10]。
MySQL行锁规则 - 图3

当边界值存在于表中，同时命中的情况：

sessionA	sessionB
begin;
select * from user where id<=10 for update;
	insert into user values (1,’斯斯与帆’,1);（Blocked）
	update user set age=18 where id=5;（Blocked）
	insert into user values (6,’夏日阳光’,6);（Blocked）
	update user set age=18 where id=10;（Blocked）
	insert into user values (11,’告五人’,11);（Blocked）
	update user set age=18 where id=15;（Blocked）
	insert into user values (16,’面孔’,16) ;

此时 sessionA 给 user 表加上的锁是Next-key Lock —— (minIndex,15]。
MySQL行锁规则 - 图4

当边界值不存在于表中时，不可能命中，故只有未命中一种情况：

sessionA	sessionB
begin;
select * from user where id<=9 for update;
	insert into user values (1,’斯斯与帆’,1);（Blocked）
	update user set age=18 where id=5;（Blocked）
	insert into user values (6,’夏日阳光’,6);（Blocked）
	update user set age=18 where id=10;（Blocked）
	insert into user values (11,’告五人’,11);
	update user set age=18 where id=15;
	insert into user values (16,’面孔’,16) ;

此时 sessionA 给 user 表加上的锁是 Next-key Lock —— (minIndex,10]，与第一种情况一样。
MySQL行锁规则 - 图5

综上所述，在对唯一索引进行范围查询时：

会给范围中的记录加上记录锁，间隙加上间隙锁；
对于范围查询(大于/大于等于/小于/小于等于)是比较特殊的，它会将记录锁加到第一个边界之外的记录上，若其中有额外的间隙也会加上间隙锁（即会将Next-key Lock加到第一个边界之外的记录上）。

需要注意的是，第一条中所说的间隙指的是，边界值所在的间隙，如间隙为(5,10)，查询条件为 id>7 时，这个间隙锁就是(5,10)，而不是(7,10)。
第二条举例1：查询条件为 id<10，第一个边界之外的记录是 id=10，所以 Next-key Lock锁会加到 id=10 的记录上，被锁住的范围是(minIndex,10]。
第二条举例2：查询条件为 id<=10，第一个边界之外的记录是 id=15，所以 Next-key Lock锁会加到 id=15 的记录上，被锁住的范围是(minIndex,15]。
第二条举例3：查询条件为 id>10，第一个边界之外的记录是 id=10，Next-key Lock 锁会加到 id=10 的记录上，由于 Next-key Lock 锁指的是记录以左的部分，所以被锁住的范围是(5,maxIndex]。

普通索引列等值查询

普通索引与唯一索引的区别就在于唯一索引可以根据索引列确定唯一性，所以等值查询的加锁规则也有不同之处。
给 user 表再加一条记录：INSERT INTO user VALUES (16, '达达2.0', 10);
这时 user 表的索引 age 结构如下图所示：
MySQL行锁规则 - 图6
在索引 age 中可能存在的行锁是4个记录锁以及5个间隙锁。
先来看索引 age 上的加锁情况：

sessionA	sessionB
begin;
select * from user where age=10 for update;
	insert into user values (2,’达达’,2);
	update user set name=’痛仰’ where age=5;
	insert into user values (6,’达达’,6);（Blocked）

| update user set name=’痛仰’ where age=10 and id=10;（Blocked） | |

| update user set name=’痛仰’ where age=10 and id=16;）（Blocked） | |
| insert into user values (17,’达达’,10);（Blocked） | |
| insert into user values (11,’达达’,11);（Blocked） | |
| update user set name=’痛仰’ where age=15; | |
| insert into user values (16,’面孔’,16) ; |

MySQL行锁规则 - 图7
即索引 age 上的加锁区域为(5, 15)。
由于普通索引无法确定记录的唯一性，所以普通索引列等值查询中，为索引 age 加锁时，会找到第一个age小于10的值（即5）和第一个age大于10的值（即15），在这个范围内的间隙加上间隙锁，记录加上记录锁。
这是索引 age 上的加锁情况，由于查询语句是查询记录的所有列，根据查询规则，会通过索引 age 上对应的 id 值到主键索引树上进行回表操作，得到所有列，所以主键索引上也会加锁。在这里，满足 age=10 的记录的主键id分别是10和16，所以在主键索引上这两行也会被加上排它锁。
即，普通索引列等值查询如果需要回表，满足条件的记录对应的主键也会被加上记录锁。
这里如果把 sessionA 中的查询改为 select id from user where age=10 lock in share mode;，则会因为覆盖索引优化而不进行回表操作，所以主键索引上也不会加锁。

普通索引列等值查询+limit

这里需要额外提一提 limit 这个语法，它的加锁范围（只讨论普通索引）要更小一些，请看示例：

sessionA	sessionB
begin;
select * from user where age=10 limit 1 for update;
	insert into user values (2,’达达’,2);
	update user set name=’痛仰’ where age=5;
	insert into user values (6,’达达’,6);（Blocked）

| update user set name=’痛仰’ where age=10 and id=10;（Blocked） | |

可以看到，与没有加 limit 相比，多了两条 insert 语句顺利执行了。
由上表的语句及执行结果来看，索引 age 上的加锁情况是：
MySQL行锁规则 - 图8
由此可见：limit 语法只会将锁加到满足条件的记录，能够减小加锁范围。

普通索引列范围查询

接下来看普通索引列上的范围查询（这里只讨论索引 age 的加锁范围，主键索引的加锁如果存在回表会锁住对应的id值）：

sessionA	sessionB
begin;
select * from user where age>8 and age<=12 for update;

| update user set name=’痛仰’ where age=10 and id=10;（Blocked） | |

| update user set name=’痛仰’ where age=10 and id=16;（Blocked） | |
| insert into user values (17,’达达’,10);（Blocked） | |
| insert into user values (11,’达达’,11);（Blocked） | |

| update user set name=’痛仰’ where age=15;（Blocked） | |
| insert into user values (16,’面孔’,16) ; |

与普通索引列等值查询不同的是，范围查询比等值查询多了一个 age=15 的记录锁。
MySQL行锁规则 - 图9
这个边界值与唯一索引列范围查询的原理是一样的，可以参照上文所述来理解，这里不多加赘述了。

参考文档

公众号文章：MySQL 锁机制及行锁规则