前言

在上一篇文章中已经介绍了主键索引的加锁范围，现在来回顾一下：

这篇文章会对非主键唯一索引进行操作实践。

数据库表数据

CREATE TABLE `t` (
  `id` int NOT NULL COMMENT '主键',
  `a` int DEFAULT NULL COMMENT '唯一索引',
  `c` int DEFAULT NULL COMMENT '普通索引',
  `d` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_a` (`a`),
   KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

数据库数据如下：

数据库的字段 a 是唯一索引。

非主键唯一索引

非主键唯一索引等值查询 —— 数据存在

mysql> begin; select * from t where a = 110 for update;

分析一下这条 SQL：

查看 data_locks

一切和分析的一样。

如果把 for update 换成 for share，其实也是相同，在主键和唯一索引上都加了锁。

这里执行的 SQL 都是 ，如果替换为 呢？

mysql> begin; select id from t where a = 110 for update;

分析一下这条 SQL：

所以看出并无什么区别。

把 for update 换成 for share，这时候区别来了：

只有两条锁记录：表意向锁和 uniq_a 索引的 锁。

很明显，for share 覆盖索引时，只是对自己的索引加锁。

update t set c = 2101 where id = 10;

这时候使用主键更新 c 是否能更新？ 那下面两个 SQL 呢？

update t set a = 1101 where id = 10;

update t set c = 2101 where a = 110;

执行结果很显然，第一个可以执行，而后两个是会阻塞的。

所以，非主键唯一索引等值查询，数据存在，for update 是会在主键加锁的，而 for share 只有在走覆盖索引的情况下，会仅在自己索引上加锁。

非主键唯一索引等值查询 —— 数据不存在

mysql> begin; select * from t where a = 111 for update;

分析这一条 SQL：

事实证明，分析结果不正确。

并且我执行 也过了。

所以是不是可以理解为，非主键索引等值查询，数据不存在，相当于一个范围查询，仅仅会在非主键索引上加锁，加的还是间隙锁，前开后开区间；

如果此时走索引覆盖呢？ 其实结果也是相同的。

非主键唯一索引范围查询

mysql> begin; select * from t where a >= 110 and a < 115 for update;

分析 SQL

事实证明，又一次是错误的！

分析 data_locks:

很明显 110 和 115 之前的间隙以及它们自身的记录都被锁住了。

经过一番分析，难道是因为。

脑袋炸裂呀，完全和主键索引的 next-key lock 加锁范围不同，人家 sql 是什么就锁什么。

有小伙伴知道原因可以告诉我。

如果我把 sql 改成下面的这种呢？

mysql> begin; select *  from t where a > 110 and a < 114 for update;

诶？？？

奇了怪了！

我唯一能想到的原因就是前开后闭了。 因为 中的等于是属于上一个区间的，所以需要锁住上一个区间。

我只能说懵逼三连了！！！

其实还是有结论的：

在非主键唯一索引范围查询时，会对相应的范围加前开后闭区间，并且如果存在数据，会对对应的主键加行锁。

这时候如果走覆盖索引呢？？

mysql> begin; select id from t where a >= 110 and a < 115 for update;

按照刚才的思路，前开后闭：

事实又错了！

还锁住了主键 15 的行锁。

把等号去掉 15 是锁住的。

感觉脑袋完全不够用啊。重点是我没有理解怎么主键还是前开后开，这里就前开后闭了？

难道我在这里试试那个 bug？

啪啪打脸啊！

之前还说这个 bug 在 8.0.18 被修复了，并优化成了前开后开区间，这直接打脸，明摆着没有修复。

我只是操作 竟然把 120 给我锁住了，不就是 next-key 的 bug。

尝试一下 sql

很明显~ 这个 bug 在非主键唯一索引上，并没有修复！！！

总结

在非主键唯一索引情况下：

实践完本文的所有操作，个人处于有些懵逼的状态。我使用的版本是

仔细一想，似乎又可以理解。

因为主键上的 next-key 的 bug 被修复了，同时优化了前开后闭区间为前开后开区间，而非主键唯一索引上这个 bug 没有被修复，所以没有优化。

嗯~ 大概就是这样吧！

相关推荐