Mysql加锁规则
2024-01-22 14:49:05 2 举报AI智能生成
MySQL 8.0的加锁规则
作者其他创作
大纲/内容
（21讲）加锁机制-最新版本的验证
基础材料
MySQL版本：8.1.0
查看当前表的锁上锁情况
select * from performance_schema.data_locks where object_name ='index_lock';
参数详解
https://www.jianshu.com/p/cfaea55f5a28
加锁规则
首要概念
锁是加在索引上的
基本规则 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
这其中的“访问到的”详解：
应该是是指在扫描树的过程中第一个匹配的数据或者已有数据后继续扫描至最后一个不匹配的数据，这个最后一个不匹配的也算是扫描到。
那如果是
应该是是指在扫描树的过程中第一个匹配的数据或者已有数据后继续扫描至最后一个不匹配的数据，这个最后一个不匹配的也算是扫描到。
那如果是
索引联动
当需要回表的时候，在当前索引加锁的同时，也会将主键索引的匹配记录（即需要回表的记录）进行记录锁。
如果当前语句是覆盖索引，那么lock in share mode 只锁覆盖索引，不会涉及对应的主键索引。
但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。
但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。
隔离等级的区别
RC级别的加锁规则可以简单理解为遵循RR级别的规则，只不过不存在间隙锁的操作步骤。
RC级别相对而言的优化：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交。
数据类型的区别
varchar 和 int 是一样的，排好序以后，相邻两个值之间就有间隙。
案例的总结规则
加锁的基本单位是 next-key lock。特殊场景下会退化为行锁和间隙锁。
普通字段的加锁会等同于锁全表。
1、间隙的等值查询（在间隙中进行等值查询，即未查询到匹配数据）：所有索引会加当前间隙的间隙锁。
2、记录的等值查询（等值查询有匹配数据）：（1）主键索引：只锁当前记录锁。（2）其他索引：会锁当前间隙的临界锁及右侧的间隙锁。
3、范围查询无命中（范围查询无匹配结果）：（1）主键索引：添加当前间隙间隙锁。（2）其他索引：添加当前间隙的临界锁。
4、范围查询边界命中（范围查询在边界处匹配结果）：（1）主键索引：左边界最多记录锁，右边界最多临界锁。（2）其他索引：所有涉及的间隙（包括右侧第一个不匹配的间隙）都加临界锁。
5、范围内命中（范围查询有匹配数据且不在范围边界）：（1）主键索引：涉及所有区间加临界锁、最右侧间隙加间隙锁。（2）其他索引：1、涉及所有区间加临界锁。
案例数据
CREATE TABLE `m_demo`.`Untitled` (
`a` int NOT NULL,
`b` int NULL DEFAULT NULL,
`c` int NULL DEFAULT NULL,
`d` int NULL DEFAULT NULL,
PRIMARY KEY (`a`) USING BTREE,
INDEX `UNIQUE_INDEX`(`b` ASC) USING BTREE,
INDEX `INDEX`(`c` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
`a` int NOT NULL,
`b` int NULL DEFAULT NULL,
`c` int NULL DEFAULT NULL,
`d` int NULL DEFAULT NULL,
PRIMARY KEY (`a`) USING BTREE,
INDEX `UNIQUE_INDEX`(`b` ASC) USING BTREE,
INDEX `INDEX`(`c` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
案例
数据类型简单按照int
索引类型
主键索引
唯一索引
普通索引
非索引
案例
等值查询1：未命中
间隙的等值查询，锁定某个不存在的行
间隙的等值查询，锁定某个不存在的行
主键索引
主键索引：当前间隙的间隙锁
子主题
唯一索引
唯一索引：当前间隙的间隙锁
子主题
因为无命中记录，不存在回表，所以不存在主键索引上锁
但此场景下lock_data显示了区间末尾记录行对应的主键值。
但此场景下lock_data显示了区间末尾记录行对应的主键值。
普通索引
普通索引：当前间隙的间隙锁
子主题
因为无命中记录，不存在回表，所以不存在主键索引上锁
但此场景下lock_data显示了区间末尾记录行对应的主键值。
但此场景下lock_data显示了区间末尾记录行对应的主键值。
非索引
主键索引：每个已有记录上临界锁即锁了所有区间，包括正无穷方向。
（全表上锁）
（全表上锁）
子主题
等值查询：命中
主键索引
主键索引：命中记录的记录锁
子主题
唯一索引
唯一索引：命中记录临界锁+下一个间隙的间隙锁
主键索引：命中记录的记录锁
主键索引：命中记录的记录锁
子主题
普通索引
普通索引：命中记录临界锁+下一个间隙的间隙锁
主键索引：命中记录的记录锁
主键索引：命中记录的记录锁
非索引
主键索引：每个已有记录上临界锁即锁了所有区间，包括正无穷方向。
（全表上锁）
（全表上锁）
子主题
范围查询1：范围无命中
主键索引
主键索引：当前间隙的间隙锁
子主题
唯一索引
唯一索引：当前间隙的临界锁
普通索引
唯一索引：当前间隙的临界锁
子主题
非索引
主键索引：每个已有记录上临界锁即锁了所有区间，包括正无穷方向。
（全表上锁）
（全表上锁）
子主题
范围查询2：范围边界命中
主键索引
主键索引：
1、所在间隙左侧边界如命中随即上记录锁。
2、所在间隙右侧边界如命中则临界锁，如
未命中则上间隙锁。
1、所在间隙左侧边界如命中随即上记录锁。
2、所在间隙右侧边界如命中则临界锁，如
未命中则上间隙锁。
子主题
子主题
子主题
唯一索引
唯一索引：
1、所在间隙左侧边界上如匹配上临界锁。
2、向右寻到第一个不匹配记录并和期间匹配的记录全部加临界锁。
主键索引：
回表需要的主键索引加记录锁。
1、所在间隙左侧边界上如匹配上临界锁。
2、向右寻到第一个不匹配记录并和期间匹配的记录全部加临界锁。
主键索引：
回表需要的主键索引加记录锁。
子主题
子主题
普通索引
普通索引：
1、所在间隙左侧边界上如匹配上临界锁。
2、向右寻到第一个不匹配记录并和期间匹配的记录全部加临界锁。
主键索引：
回表需要的主键索引加记录锁。
1、所在间隙左侧边界上如匹配上临界锁。
2、向右寻到第一个不匹配记录并和期间匹配的记录全部加临界锁。
主键索引：
回表需要的主键索引加记录锁。
子主题
子主题
子主题
非索引
主键索引：每个已有记录上临界锁即锁了所有区间，包括正无穷方向。
（全表上锁）
（全表上锁）
子主题
范围查询3：范围中命中
主键索引
主键索引：
1、涉及所有区间加临界锁、最右侧间隙加间隙锁。
1、涉及所有区间加临界锁、最右侧间隙加间隙锁。
子主题
子主题
唯一索引
唯一索引：
1、涉及所有区间加临界锁。
主键索引：
回表需要的主键索引加记录锁。
1、涉及所有区间加临界锁。
主键索引：
回表需要的主键索引加记录锁。
子主题
子主题
普通索引
唯一索引：
1、涉及所有区间加临界锁。
主键索引：
回表需要的主键索引加记录锁。
1、涉及所有区间加临界锁。
主键索引：
回表需要的主键索引加记录锁。
子主题
子主题
非索引
主键索引：每个已有记录上临界锁即锁了所有区间，包括正无穷方向。
（全表上锁）
（全表上锁）
子主题
子主题
0 条评论
下一页
