[大数据]MySQL 锁的类型、加锁规则及详细案例

索引锁类型

默认情况下，InnoDB在REPEATABLE READ事务隔离级别下运行。在这种情况下，InnoDB使用next-key锁进行搜索和索引扫描，这可以防止幻行

索引锁的类型：

• Record Locks：行锁，对一行记录进行加锁
• Gap Locks：间隙锁，对范围记录进行加锁
• Next-Key Locks：Record Locks + Gap Locks

InnoDB所有锁的类型详见innodb-locking，如共享(S)锁和排它(X)锁

锁定读取

如果在同一事务中先查询数据，然后插入或更新相关数据，则常规SELECT语句无法提供足够的保护。因为其他事务可以更新或删除刚刚查询的相同行。

InnoDB支持两种类型的锁定读取，可提供额外的安全性

当事务提交或回滚时，所有由FOR SHARE和FOR UPDATE查询设置的锁都会被释放。

只有在禁用自动提交时才能锁定读取（通过使用START TRANSACTION或设置autocommit为0开始事务）。

FOR SHARE

SELECT ... FOR SHARE会在扫描到的行里设置共享锁。其他会话可以读取这些行，但在您的事务提交之前不能修改它们。如果其中任何行被另一个尚未提交的事务更改，您的查询将等待该事务结束，然后使用最新值。

1. FOR SHARE只会锁定扫描过程中使用的索引里的记录行，即如果你的查询正好使用了覆盖索引，那么只有这个索引里的记录行会被锁定，主键索引的记录行是不会被锁定的。
2. FOR SHARE是LOCK IN SHARE MODE的代替版，支持额外的功能，详见 Locking Read Concurrency with NOWAIT and SKIP LOCKED

FOR UPDATE

对于搜索遇到的索引记录，锁定行和任何关联的索引条目，与使用UPDATE语句一致

InnoDB 锁和锁等待信息

当事务更新表中的一行或使用SELECT FOR UPDATE锁定时，InnoDB会在该行上建立一个列表或锁定队列。相应已锁定的行或间隙能在innoDB对应的表中查询

MySQL 5.7 使用innodb_lock_waits
MySQL 8.0 使用data_lock_waits

加锁规则

两个原则：

1. 加锁的基本单位是next-key lock，next-key lock是前开后闭区间。
2. 查找过程中访问到的对象才会加锁。

两个优化：

1. 索引上的等值查询，给唯一索引加锁的时候，如果满足条件，next-key lock退化为行锁。
2. 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。

注意，非等值查询是不会优化的

bug：

1. 唯一索引上的范围查询会访问到不满足条件的第一个值为止。（MySQL 8.0.26修复）
2. 唯一索引上临界<=查询时，会锁住下一个next-key的前开后闭区间（MySQL 8.0.17修复，修改为前开后开区间）

加锁案例

示例的建表语句及初始化语句如下

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;
 
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

如上一共插入6条数据，对应next-key lock：(-∞,0]、(0，5]、(5，10]、(10，15]、(15，20]、(20，25]、(25，+∞)，由于next-key lock包含行锁，因此会形成前开后闭区间范围

等值查询行锁

最终加锁范围id=5行锁

1. 根据原则 1，加锁单位是 next-key lock，因此会给 (0,5] 加上 next-key lock
2. 同时根据优化 2，这是一个等值查询满足条件 (id=5)，next-key lock 退化成行锁，因此最终加锁的范围是id=5。

等值查询间隙锁

最终加锁范围(5,10)

1. 根据原则 1，加锁next-key lock (5,10]
2. 同时根据优化 2，next-key lock 退化成间隙锁，因此最终加锁的范围是(5,10)

非唯一索引等值锁

最终加锁范围(0,10)

1. 根据原则 1，加锁next-key lock (0,5]
2. 注意 c 是普通索引，需要向右遍历，直到c=5才停止。根据原则 2，访问到的都要加锁，加锁next-key lock(5,10]
3. 同时符合优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件。因此退化成间隙锁 (5,10)
4. 根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁

需要注意，在这个例子中，for share只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

唯一索引范围锁

最终加锁范围(10,20]

1. 根据原则 1，加锁next-key lock (10,15]
2. 由于bug 2，唯一索引上临界<=查询时，会锁住下一个next-key的前开后闭区间(15,20]

非唯一索引范围锁

最终加锁范围(5,15]

1. 根据原则 1，加锁next-key lock(5,10]
2. 由于索引c是非唯一索引，会继续往下查找，找到15这条记录发现不满足。根据原则2，加锁(10,15]

非唯一索引间隙范围

接下来的例子，是为了更好地说明“间隙”这个概念。这里，我给表 t 插入一条新记录。

mysql> insert into t values(30,10,30);

最终加锁范围(c=5,id=5)~(c=15,id=15)开区间。(c=5,id=5)和(c=15,id=15)这两行上都没有锁

1. 根据原则 1，加锁next-key lock (c=5,id=5)~(c=10,id=10)
2. 由于索引c是非唯一索引，会继续往下查找，找到15这条记录发现不满足。根据原则2，加锁(c=10,id=10)~(c=15,id=15) 前开后闭区间
3. 根据优化 2，等值查询退化为间隙锁(c=10,id=10)~(c=15,id=15) 前开后开区间

辅助索引的叶子节点中得数据是顺序存放的

limit 语句加锁

与非唯一索引间隙范围中的例子为对照案例，场景如下所示：

最终加锁范围(c=5,id=5)~(c=10,id=30)前开后闭区间

1. 根据原则 1，加锁next-key lock (c=5,id=5)~(c=10,id=10)
2. 由于索引c是非唯一索引，会继续往下查找，在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了，加锁(c=10,id=10)~(c=10,id=30) 前开后闭区间

这个例子对我们实践的指导意义就是，在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。

非唯一索引 desc语句加锁

如果加了关键字desc，优化规则依然有效，依旧是前开后闭，向右扫描变成了向左扫描

索引向左扫描，最终索引c加锁范围(0,15)，主键索引上id=10/15两个行锁

1. 根据原则 1，加锁next-key lock(10,15]
2. 由于索引c是非唯一索引，会继续往下查找，直至找到5这条记录发现不满足。根据原则2，加锁(5,10]及(0,5]

死锁

前面的例子中，我们在分析的时候，是按照 next-key lock 的逻辑来分析的，因为这样分析比较方便。最后我们再看一个案例，目的是说明：next-key lock 实际上是间隙锁和行锁加起来的结果。

现在，我们按时间顺序来分析一下为什么是这样的结果。

1. session A 启动事务后执行查询语句加for share，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)；
2. session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待；
3. 然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚。

你可能会问，session B 的 next-key lock 不是还没申请成功吗？

其实是这样的，session B 的“加 next-key lock(5,10] ”操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，这时候才被锁住的。

我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。

参考资料：

1. innodb-locking
2. MySQL 实战 45 讲
3. MySQL InnoDB gap lock
4. MySQL next-key lock 加锁范围总结
5. 为什么我只改一行的语句，锁这么多？