MySQL 啥时候会用记录锁,啥时候会用间隙锁,啥时候又会用 Next-Key 锁呢?今天我们就来做一些测试,弄清楚这个问题。
文章思维导图
影响因素
在开始之前,我们需要声明的是:本文所有测试及结论的前提均是在「可重复读」隔离级别下,以及 Innodb 存储疫情下。
根据网上资料,我们大概可以知道,影响其使用哪种行级锁的因素有:
-
索引类型(聚簇索引、唯一二级索引、普通二级索引)
-
匹配类型(精确匹配、唯一匹配、范围匹配)
-
事务隔离级别
-
是否开启 Innodb_locks_unsafe_for_binlog 系统变量
-
记录是否被标记删除
-
具体的执行语句类型(SELECT、INSERT、DELETE、UPDATE)
为了让文章相对易懂一些,我准备重点测试索引类型与匹配类型两个影响因素。对于其他的影响因素,我将不做改动。例如:事务隔离级别固定为「可重复读」,Innodb_locks_unsafe_for_binlog 固定为 false。而第 5、6 点相对来说简单一些,则我们会简单带过。
针对上面几个影响因素,我们指定了几个测试实验,分别是:
-
聚簇索引 + 精确匹配
-
聚簇索引 + 范围匹配
-
唯一二级索引 + 精确匹配
-
唯一二级索引 + 范围匹配
-
普通二级索引 + 精确匹配
-
普通二级索引 + 范围匹配
// 表结构
CREATE TABLE `test`.`price_test` (
`id` BIGINT(64) NOT NULL AUTO_INCREMENT,
`price` INT(4) NULL,
PRIMARY KEY (`id`));
// 表中数据
1, apple, 10
2, orange, 30
50, perl, 60
聚簇索引 + 精确匹配
为了测试「聚簇索引 + 精确匹配」下加锁的类型,我们采用如下的测试方法。
事务 A 执行下面命令:
begin;
select * from price_test where id = 2 for update;
执行 show engine innodb status\G;
查看锁信息如下图所示。
可以看到,其是对 id 为 2 的索引加了一个记录锁。
此时事务 B 执行下面命令:
beign;
update price_test set price = 25 w