Mysql8和Mysql5加锁规则的细微不同

码上得天下

于 2024-03-17 21:30:26 发布

阅读量885

点赞数 17

分类专栏： Mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/meser88/article/details/136788562

版权

Mysql 专栏收录该内容

107 篇文章 11 订阅

订阅专栏

前言

验证版本分别为Mysql5.7.22和Mysql8.0.26

关于Mysql5.7.22的加锁规则请查看 MySQL行锁加锁规则之等值查询和 MySQL行锁加锁规则之范围查询

两个版本的等值查询规则一样，区别在于范围查询的“向右匹配到第一个不符合记录” 的加锁规则，Mysql5.7.22加的是临键锁，而Mysql8.0.26加的是间隙锁。下面对比两个版本范围查询的加锁规则，忽略索引失效场景。

对比

mysql8可通过select * from performance_schema.data_locks 或者SHOW ENGINE INNODB STATUS查看加锁信息
mysql5需要通过SHOW ENGINE INNODB STATUS查看加锁信息

-- 开启锁的监控
SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;

CREATE TABLE `ct` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `abc` int(10) unsigned NOT NULL,
  `abc_uk` int(10) unsigned NOT NULL,
  `remark` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_abc_uk` (`abc_uk`) USING BTREE,
  KEY `idx_abc` (`abc`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
INSERT INTO `ct`
(`id`, `name`, `abc`, `abc_uk`, `remark`) 
VALUES 
(10, '巴西', 10, 10, NULL),
(20, '阿根廷', 20, 20, NULL),
(30, '葡萄牙', 30, 30, NULL),
(40, '法国', 40, 40, NULL);

索引	sql语句	Mysql 5.7.22加锁范围	Mysql 8.0.26加锁范围
主键	BEGIN; SELECT * FROM ct WHERE id<10 FOR UPDATE	临键锁（0,10]	间隙锁（0,10）
	BEGIN; SELECT * FROM ct WHERE id<=10 FOR UPDATE	临键锁（0,10]和10,20]	临键锁（0,10]
	BEGIN; SELECT * FROM ct WHERE id>40 FOR UPDATE	临键锁（40，supremum ]	临键锁（40，supremum ]
	BEGIN; SELECT * FROM ct WHERE id>=40 FOR UPDATE	临键锁（40，supremum ] 和id=40记录锁	临键锁（40，supremum ] 和id=40记录锁
唯一索引	begin; SELECT *from ct where abc_uk <10 for UPDATE;	临键锁（0,10]	临键锁（0,10]
	begin; SELECT *from ct where abc_uk <=10 for UPDATE;	临键锁（0,10]和10,20]；主键id=10记录锁	临键锁（0,10]和10,20]；主键id=10记录锁
	BEGIN; SELECT * FROM ct WHERE abc_uk >40 FOR UPDATE	临键锁（40，supremum ]	临键锁（40，supremum ]
	BEGIN; SELECT * FROM ct WHERE abc_uk >=40 FOR UPDATE	临键锁（20,40]和40,supremum ]；主键id=40记录锁	临键锁（20,40]和40,supremum ]；主键id=40记录锁
普通索引	BEGIN; SELECT * FROM ct WHERE abc<10 FOR UPDATE	临键锁（0,10]	临键锁（0,10]
	BEGIN; SELECT * FROM ct WHERE abc<=10 FOR UPDATE	临键锁（0,10]和10,20]；主键id=10记录锁	临键锁（0,10]和10,20]；主键id=10记录锁
	BEGIN; SELECT * FROM ct WHERE abc >40 FOR UPDATE	临键锁（40，supremum ]	临键锁（40，supremum ]
	BEGIN; SELECT * FROM ct WHERE abc>=40 FOR UPDATE	临键锁（20,40]和40,supremum ]；主键id=40记录锁	临键锁（20,40]和40,supremum ]；主键id=40记录锁

总结

范围查询，两者唯一索引和普通所以的规则还是一样的，区别在主键索引，对于<查询Mysql 5匹配的是临键锁，Mysql 8匹配的间隙锁；对于<=查询，Mysql 5会匹配到第一个不符合的记录加临键锁，而Mysql 8不会匹配到第一个不符合的记录。

扩展

关于“向右匹配到第一个不符合记录”的理解，下面从同一个sql正序与降序加锁范围不同分析。

sql

加锁范围

BEGIN;
SELECT *FROM ct WHERE abc>10 AND abc<40 FOR UPDATE;

主键记录锁id=20和id=30

临键锁（10，20]和（20，30]，（30，40]

BEGIN;
SELECT *FROM ct WHERE abc>10 AND abc<40 ORDER BY abc DESC FOR UPDATE;

主键记录锁id=20和id=30

临键锁（最小值，10]和10，20]（20，30]

间隙锁（30,40）

降序和正序相比，多了临键锁(最小值，10] ，临键锁（30，40]变成了间隙锁（30,40）。这里涉及的就是“向右匹配到第一个不符合记录”的规则，这个向右不是很好理解，我们把它按索引的检索顺序理解,即按索引顺序遍历到最后第一个不符合条件的记录。上面sql正序，从小到大遍历，第一个符合的记录是20，故遍历到第一个不符合的记录40加临键锁（30，40]；降序：是从大到小遍历，第一个符合记录的是30，故遍历到第一个不符合的记录10加临键锁(最小值，10]。