Next-Key Lock实战探究

最新推荐文章于 2025-10-20 11:30:00 发布

原创最新推荐文章于 2025-10-20 11:30:00 发布 · 354 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

数据库专栏收录该内容

5 篇文章

订阅专栏

探究原因

默认情况下，InnoDB工作在可重复读隔离级别下，并且会以Next-Key Lock的方式对数据行进行加锁。而我目前使用的数据库引擎基本都是InnoDB,所以有必要对此引擎的默认加锁方式进行探究。

官方描述

在这里插入图片描述

探究使用的数据库建表语句以及数据初始化sql

CREATE TABLE base_goods (
  id bigint(20) NOT NULL DEFAULT '0',
  goods_code varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '商品表',
  goods_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '商品名称',
  price decimal(16,4) DEFAULT NULL COMMENT '价格',
  ynflag bit(1) DEFAULT NULL COMMENT '是否启用 0否 1是',
  create_user varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人',
  create_time timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
  update_user varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '修改人',
  version_num int(11) DEFAULT NULL COMMENT '版本号',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='商品表';

INSERT INTO `base_goods` (`id`, `goods_code`, `goods_name`, `price`, `ynflag`, `create_user`, `create_time`, `update_time`, `update_user`, `version_num`) VALUES (1, '1', '戴尔外星人', 1.0000, '', 'yaksha', '2020-5-25 14:49:01', '2020-5-25 14:49:01', 'yaksha', 0);
INSERT INTO `base_goods` (`id`, `goods_code`, `goods_name`, `price`, `ynflag`, `create_user`, `create_time`, `update_time`, `update_user`, `version_num`) VALUES (2, '2', '华为p30', 1.0000, '', 'yaksha', '2020-5-25 15:09:00', '2020-5-25 15:09:00', 'yaksha', 0);
INSERT INTO `base_goods` (`id`, `goods_code`, `goods_name`, `price`, `ynflag`, `create_user`, `create_time`, `update_time`, `update_user`, `version_num`) VALUES (5, '5', '苹果10x', 1.0000, '', 'yaksha', '2020-5-25 15:14:27', '2020-5-25 15:14:27', 'yaksha', 0);
INSERT INTO `base_goods` (`id`, `goods_code`, `goods_name`, `price`, `ynflag`, `create_user`, `create_time`, `update_time`, `update_user`, `version_num`) VALUES (7, '7', '小米鞋', 2.0000, '', 'yaksha', '2020-5-25 19:14:30', '2020-5-25 19:14:30', 'yaksha', 0);
INSERT INTO `base_goods` (`id`, `goods_code`, `goods_name`, `price`, `ynflag`, `create_user`, `create_time`, `update_time`, `update_user`, `version_num`) VALUES (8, '8', '妈个鸡键盘', 3.0000, '', 'yaksha', '2020-5-25 19:22:09', '2020-5-25 19:22:09', 'yaksha', 0);
INSERT INTO `base_goods` (`id`, `goods_code`, `goods_name`, `price`, `ynflag`, `create_user`, `create_time`, `update_time`, `update_user`, `version_num`) VALUES (9, '9', '老六你是共产党', 6.0000, '', 'yaksha', '2020-6-15 14:59:28', '2020-6-15 14:59:28', 'yaksha', 0);
INSERT INTO `base_goods` (`id`, `goods_code`, `goods_name`, `price`, `ynflag`, `create_user`, `create_time`, `update_time`, `update_user`, `version_num`) VALUES (13, '13', '13', 8.0000, '', 'yaksha', '2020-10-15 22:48:16', '2020-10-15 22:48:16', 'yaksha', 0);
INSERT INTO `base_goods` (`id`, `goods_code`, `goods_name`, `price`, `ynflag`, `create_user`, `create_time`, `update_time`, `update_user`, `version_num`) VALUES (17, '17', '9', 9.0000, '', 'yaksha', '2020-10-15 22:57:23', '2020-10-15 22:57:23', 'yaksha', 0);
INSERT INTO `base_goods` (`id`, `goods_code`, `goods_name`, `price`, `ynflag`, `create_user`, `create_time`, `update_time`, `update_user`, `version_num`) VALUES (19, '21', '21', 10.0000, '', 'yaksha', '2020-10-15 23:19:23', '2020-10-15 23:19:23', 'yaksha', 0);
INSERT INTO `base_goods` (`id`, `goods_code`, `goods_name`, `price`, `ynflag`, `create_user`, `create_time`, `update_time`, `update_user`, `version_num`) VALUES (25, '27', '27', 11.0000, '', 'yaksha', '2020-10-15 23:27:28', '2020-10-15 23:27:28', 'yaksha', 0);

初始化后表内数据
在这里插入图片描述

探究配置

每次进行探究的时候，数据都会还原为执行完建表语句和初始化sql之后的状态。

探究1（通过不带索引的字段查找数据）

开启一个事务，执行下列语句查看锁的情况

START TRANSACTION;
select * from base_goods t where t.goods_code = '5' for UPDATE;

查询锁的情况

select * from performance_schema.data_locks where OBJECT_SCHEMA ='bak_xcx';

此表的相关列解释详见mysql官网
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-data-locks-table.html
在这里插入图片描述

可以看出来是bak_xcx数据库中的base_goods表中存在锁，分别是table(表)级别的IX(意向排他锁和)RECORD(行)级别的X(排他锁)，锁的间隙节点数据保存再列LOCK_DATA中(其中的supremum pseudo-record为最大值间隙)
对比表中数据也可得出此时的锁真实锁的数据是整张表。即如果通过未加索引的列进行上锁时锁的将是整张表。

探究2（通过带索引的字段查找能命中数据）

先给goods_code列加上索引

	ALTER TABLE `base_goods` ADD INDEX INDEX_CODE ( `goods_code` ) ;

开启一个事务，执行下列语句查看锁的情况

START TRANSACTION;
select * from base_goods t where t.goods_code = '5' for UPDATE;

查询锁的情况

select * from performance_schema.data_locks where OBJECT_SCHEMA ='bak_xcx';

在这里插入图片描述
上锁的数据为goods_code为“5”（主键为5）的间隙锁和id为5的行锁，以及goods_code为“7”（主键为7）的间隙锁。此时锁住的数据为goods_code为“2”id为2到goods_code为“7”id为7的间隙即goods_code的(2,7]，因为b+tree的结构导致id在(2,7)内的goods_code为2或者7的数据也插入不进去。
而又因为mysql的优化
索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁所以此时锁的间隙改为goods_code的(2,7)，id的(2,7)。

下面是相关间隙数据插入测试的情况

INSERT INTO `base_goods` VALUES ( 0, '2', '周末吃火锅', 3.0000, '', 'yaksha', '2020-5-25 19:22:09', '2020-5-25 19:22:09', 'yaksha', 0 ); -- 成功,脱离了id和goods_code间隙
INSERT INTO `base_goods` VALUES ( 4, '3', '周末吃火锅', 3.0000, '', 'yaksha', '2020-5-25 19:22:09', '2020-5-25 19:22:09', 'yaksha', 0 ); -- 失败，属于goods_code间隙和id间隙
INSERT INTO `base_goods` VALUES ( 6, '7', '周末吃火锅', 3.0000, '', 'yaksha', '2020-5-25 19:22:09', '2020-5-25 19:22:09', 'yaksha', 0 );-- 失败，属于id间隙
INSERT INTO `base_goods` VALUES ( 10, '7', '周末吃火锅', 3.0000, '', 'yaksha', '2020-5-25 19:22:09', '2020-5-25 19:22:09', 'yaksha', 0 );-- 成功 ,脱离了id和goods_code间隙