乐趣区

关于mysql:MySQL普通索引的加锁

jiezi

前言

后面曾经介绍了主键索引的加锁范畴和非主键惟一索引的加锁范畴。

主键索引：

  1. 加锁时，会先给表增加意向锁，IX 或 IS；
  2. 加锁是如果是多个范畴，是离开加了多个锁，每个范畴都有锁；（这个能够实际下 id < 20 的状况）
  3. 主键等值查问，数据存在时，会对该主键索引的值加行锁 X,REC_NOT_GAP；
  4. 主键等值查问，数据不存在时，会对查问条件主键值所在的间隙增加间隙锁 X,GAP；

  5. 主键等值查问，范畴查问时状况则比较复杂：

    1. 8.0.17 版本是前开后闭，而 8.0.18 版本及当前，批改为了 前开后开 区间；
    2. 临界 <= 查问时，8.0.17 会锁住下一个 next-key 的前开后闭区间，而 8.0.18 及当前版本，修复了这个 bug。

非主键惟一索引:

  1. 非主键惟一索引等值查问，数据存在，for update 是会在主键加锁的，而 for share 只有在走笼罩索引的状况下，会仅在本人索引上加锁；
  2. 非主键索引等值查问，数据不存在，无论是否索引笼罩，相当于一个范畴查问，仅仅会在非主键索引上加锁，加的还是间隙锁，前开后开区间；
  3. 在非主键惟一索引范畴查问时，不是笼罩索引的时候，会对相应的范畴加前开后闭区间，并且如果存在数据，会对对应的主键加行锁；
  4. 在非主键惟一索引范畴查问时，如果是笼罩索引时，会对所有的后闭区间对应的主键，加行锁；
  5. 在非主键惟一索引加锁时，还是存在 next-key 锁住下一个区间的 bug。

这篇文章来一起看一下一般索引和一般字段的加锁范畴是什么?

数据库表数据

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

数据库数据如下：

思路和非主键惟一索引雷同，只不过惟一的区别是这里看的是 c 和 d 字段。

因为后面小伙伴对 data_locks 应该有了肯定的理解，这里就间接剖析 data_locks 的数据信息。

一般索引

一般索引等值查问 —— 数据存在

mysql> begin; select * from t where c = 210 for update;

间接剖析 data_locks

  1. 表意向锁；
  2. 索引 idx_c 上增加了 210 区间的前开后闭锁；
  3. 索引 idx_c 上增加了 215 区间的间隙锁，LOCK_MODE 为 X,GAP；
  4. 主键上增加了 15 的行锁，LOCK_MODE 为 X,REC_NOT_GAP。

次要是因为一般索引不能惟一锁定一条记录，所以要锁定该字段的前后范畴。

一般索引等值查问 —— 数据不存在

mysql> begin; select * from t where c = 211 for update;

间接剖析 data_locks

  1. 表意向锁；
  2. 索引 idx_c 上增加了 215 区间的间隙锁。

剖析是因为数据不存在，只须要锁住 215 间隙就能够了，因为 215 和 210 必定不属于这个范畴。

一般索引范畴查问

mysql> begin; select * from t where c > 210 and c <= 215 for update;

对于锁住 idx_c 索引的 215 的前开后闭区间是能够了解的，然而锁住了 220 就不太了解了，应该也是那个 bug 没有齐全修复。

一般字段

一般字段就更好了解了。

对一般字段而言，无论是哪个查问，都须要扫描全副记录，所以这个锁间接加在了主键上，并且是锁住全副的区间。

总结

本文在基于第一篇和第二篇的根底上，间接通过剖析 data_locks 的信息，进行判断加锁范畴。

select * from performance_schema.data_locks;
LOCK_MODE LOCK_DATA 锁范畴
X,REC_NOT_GAP 15 15 那条数据的行锁
X,GAP 15 15 那条数据之前的间隙，不蕴含 15
X 15 15 那条数据的间隙，蕴含 15
  1. LOCK_MODE = X 是前开后闭区间；
  2. X,GAP 是前开后开区间（间隙锁）；
  3. X,REC_NOT_GAP 行锁。

从而得出一般索引和一般字段的论断。

一般索引

  1. 一般索引等值查问，因为不能确定唯一性，所以即便定位到记录，也是会向后查问，直到查问到不为该值的记录，从而锁定该值的区间；
  2. 一般索引的锁也是加载该索引上的，如果波及到存在的记录，会对该主键加行锁；
  3. 一般索引的范畴查问，同样呈现 next-key 查问下一个区间的 bug。

一般字段

一般字段查问，会查问全表，这里锁的话就会锁住主键的所有区间。

退出移动版