杭州网站设计公司联系亿企邦,wordpress帮助手册,唐山网站建设电话,建设区服务网站1. MySQL 加锁全局视角
MySQL 分成了 Server 层和存储引擎两部分#xff0c;每当执行一个查询时#xff0c;Server 层负责生成执行计划#xff0c;然后交给存储引擎去执行。其整个过程可以这样描述#xff1a;
Server 层向 Innodb 获取到扫描区间的第 1 条记录Innodb 通过…
1. MySQL 加锁全局视角
MySQL 分成了 Server 层和存储引擎两部分每当执行一个查询时Server 层负责生成执行计划然后交给存储引擎去执行。其整个过程可以这样描述
Server 层向 Innodb 获取到扫描区间的第 1 条记录Innodb 通过 B 树定位到扫描区间的第 1 条记录然后返回给 Server 层Server 层判断是否符合搜索条件如果符合则发送给客户端不符合则跳过。接着继续向 Innodb 要下一条记录Innodb 继续根据 B 树的双向链表找到下一条记录会执行具体的 row_search_mvcc 函数做加锁等操作返回给 Server 层Server 层继续处理该条记录并向 Innodb 要下一条记录继续不停执行上述过程直到 Innodb 读到一条不符合边界条件的记录为止
通过上面这个过程明白两个重要的认识
Innodb 并不是一次性把所有数据找到然后返回给 Server 层的而是会循环很多次row_search_mvcc 这个函数是做具体的加锁、加什么锁的重要逻辑并且由于 Server 层与 Innodb 会循环多次因此该函数也是会执行多次的
弄懂了上面两个认识会对后续大家理解有很大帮助。例如对于 select * from user where id 5 进行分析的时候为什么会出现说第一次加锁是精确查询它明明是范围查询呀这是因为第一次是要寻找到 id 5 的记录对于 Innodb 来说它就是精确查找不是范围查找。随后找到 id 5 的记录之后就要找 id 5 的记录了此时就变成了范围查找了
2. MySQL 加锁规则
对于 RC 隔离级别加的排他锁X锁是比较好理解的哪里更新就锁哪里。RR 隔离级别加锁有一定的规则
锁规则一共包括两个原则、两个优化和一个 bug
原则 1加锁的基本单位都是 next-key lock。next-key lock临键锁是前开后闭区间原则 2查找过程中访问到的对象才会加锁优化 1索引上的等值查询给唯一索引加锁的时候next-key lock 退化为行锁Record lock优化 2索引上的等值查询向右遍历时且最后一个值不满足等值条件的时候next-key lock 退化为间隙锁Gap lock一个 bug唯一索引上的范围查询会访问到不满足条件的第一个值为止
说明
对于原则 1 说的加锁的基本单位是 Next-Key 锁意思是默认都是先加上 Next-Key之后根据 2 个优化点选择性退化为行锁或间隙锁对于原则 2 说的访问到的对象才会加锁意思是如果直接索引覆盖到了不需要回表那么就不会对聚簇索引加锁。这样的话其他事务就可以对聚簇索引进行操作而不会阻塞
测试数据
CREATE TABLE t (id int NOT NULL,c int NULL DEFAULT NULL,d int NULL DEFAULT NULL,PRIMARY KEY (id) USING BTREE,INDEX c(c) USING BTREE
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ROW_FORMAT Dynamic;
INSERT INTO t VALUES (0, 0, 0);
INSERT INTO t VALUES (5, 5, 5);
INSERT INTO t VALUES (10, 10, 10);
INSERT INTO t VALUES (15, 15, 15);
INSERT INTO t VALUES (20, 20, 20);
INSERT INTO t VALUES (25, 25, 25);3. 七个案例
分7个案例去分析哈
等值查询间隙锁非唯一索引等值锁主键索引范围锁非唯一索引范围锁唯一索引范围锁 bug普通索引上存在等值的例子limit 语句减少加锁范围
3.1 案例一等值查询间隙锁
我们同时开启 A、B、C三个会话事务如下 发现事务 B 会阻塞等待而 C 可以执行成功 分析流程
在事务 A 中要查找 id 7 的记录其查找过程为从左到右查找 id 聚簇索引依次对比 0、5 两个索引发现不对。接着对比 10 这个索引发现 7 10于是停止搜索。根据原则 1默认给其加上一个 Next-Key 锁即 (5, 10]同时根据优化 2这是一个等值查询 (id6)而 id10 不满足查询条件。所以 next-key lock 退化成间隙 Gap 锁因此最终加锁的范围是 (5,10)
3.2 案例二非唯一索引等值锁
按顺序执行事务会话A、B、C如下 发现事务B可以执行成功而 C 阻塞等待 分析流程
在事务 A 中要查找 c5 的记录其中 c 是非唯一索引。其查找过程为从左到右查找 c 索引找到了 c5 的索引根据原则 1对其加 Next-Key 锁即 (0,5]由于普通索引可能重复因此其还会继续往后搜索接着搜索到 10根据原则 2访问到的都要加锁因此再给其加 Next-Key 锁即 (5,10]根据优化2等值判断向右遍历最后一个值 10 不满足 c 5 这个等值条件因此退化成间隙锁 (5,10)根据原则 2 只有访问到的对象才会加锁事务 A 的这个查询使用了覆盖索引没有回表并不需要访问主键索引因此主键索引上没有加任何锁事务会话 B 是对主键 id 的更新因此事务会话 B 的 update 语句不会阻塞
3.3 案例三主键索引范围锁
按顺序执行事务会话A、B、C如下 事务 B插入 id 12 时阻塞插入 id 6 时顺利执行 事务 C阻塞 分析流程
事务 A 开始执行的时候要找到 id 为 10 的记录于是从左到右找到了 id 为 10 的索引。根据原则 1 会给其加 Next-Key 锁即 (5,10]又因为 id 是主键也就是唯一值因此根据优化1索引上的等值查询给唯一索引加锁时next-key lock 退化为行锁Record lock。所以只加了 id10 这个行锁接着继续进行范围查找找到 id15 这一行继续加 Next-Key 锁 (10,15]。这时候 id15 大于 11因此其不再查找
事务会话 A 执行完后加的锁是 id10 这个行锁以及临键锁 next-key lock(10,15]
3.4 案例四非唯一索引范围锁
按顺序执行事务会话 A、B、C如下 发现事务会话 B 和事务会话 C 的执行 SQL 都被阻塞了 分析流程
事务 A 开始执行的时候要找到 id 为 10 的记录根据原则 1 加了 Next-Key 锁即 (5,10]。 由于索引 C 是非唯一索引不符合优化1因此不会退化为行锁接着继续进行范围查找找到 id15 这一行停下来因此还需要加 next-key lock (10,15]
3.5 案例五唯一索引范围锁 bug
按顺序执行事务会话 A、B、C如下 事务 B 阻塞事务 C 阻塞 分析流程
事务 A 开始执行的时候 0、5、10 都不满足条件找到 id 15 的行满足根据原则1会加上next-key lock(10,15]因为 id 是主键即唯一的因此循环判断到 id 15 这一行就应该停止了根据一个 bugInnoDB 会往前扫描到第一个不满足条件的行为止直到扫描到 id 20。而且由于这是个范围扫描因此索引 id 上的 (15,20] 这个 next-key lock 也会被锁上
3.6 案例六普通索引上存在等值的例子
插入一条数据
insert into t values(28,10,66);则 c 索引树如下 c 索引值有相等的但是它们对应的主键是有间隙的。比如c10id10和c10id28之间
按顺序执行事务会话A、B、C如下 事务 B 阻塞事务 C 顺利执行 分析流程
事务 A 开始执行的时候要找到 c 为 10 的记录根据原则 1加一个(c5,id5) 到 (c10,id10)的 next-key lock(5, 10]由于 c 是非唯一索引会继续向右进行范围查找直到碰到 (c15, id15) 这一行循环才结束会加一个 next-key lock(10, 15]。根据优化 2这是一个等值查询向右查找到了不满足条件的行所以会退化成间隙锁 (10, 15)
在索引 c 上的加锁范围就是下图灰色阴影部分的 3.7 案例七limit 语句减少加锁范围
事务A、B执行如下 事务 B 顺利执行 分析流程
事务 A 开始执行的时候要找到 c 为 10 的记录根据原则 1加一个(c5,id5) 到 (c10,id10)的 next-key lock(5, 10]因为明确加了limit 2的限制后因此在遍历到 (c10, id30) 这一行之后满足条件的语句已经有两条循环就结束了
如下图所示
