MySQL InnoDB 锁

锁的类型 https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

测试表

CREATE TABLE `dev` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `x` int NOT NULL DEFAULT '0' COMMENT 'x',
  `y` int NOT NULL DEFAULT '0' COMMENT 't',
  `z` int NOT NULL DEFAULT '0' COMMENT 'z',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_z` (`z`),
  KEY `idx_y` (`y`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='table';

测试数据

INSERT INTO `dev` (`x`, `y`, `z`) VALUES (2, 22, 222),(4, 44, 444),(6, 66, 666);

版本
select version(); // 输出：8.0.25
select @@SESSION.transaction_isolation; //输出：REPEATABLE-READ

现象1(记录存在的等值查询)：
session 1

begin;
select * from dev where y = 44 for update;

session 2

begin;
INSERT INTO `dev` (`x`, `y`, `z`) VALUES (1, 11, 111); // 成功写入
INSERT INTO `dev` (`x`, `y`, `z`) VALUES (1, 22, 200); //  输出： Lock wait timeout exceeded; try restarting transaction
INSERT INTO `dev` (`x`, `y`, `z`) VALUES (4, 44, 400);//  输出： Lock wait timeout exceeded; try restarting transaction
INSERT INTO `dev` (`x`, `y`, `z`) VALUES (5, 55, 555);//  输出： Lock wait timeout exceeded; try restarting transaction
INSERT INTO `dev` (`x`, `y`, `z`) VALUES (6, 66, 600); // 成功写入 

结论1：
锁是加在索引上，算法为next-key lock，是一个左闭右开的区间，因此y=22行不能新写入。其中y=44不是唯一索引，InnoDB 会继续查找到下一个y不等于44的记录，因为是左闭右开，因此数据y=44不能新写入，而y=66可以新写入。

现象2（记录存在的唯一索引的等值查询）：
session 1

begin;
select * from dev where z = 444 for update;

session 2

begin;
INSERT INTO `dev` (`x`, `y`, `z`) VALUES (3, 33, 333); // 成功写入

结论2：
记录存在的唯一索引的等值查询，next-key lock会优化为 record lock

现象3（记录不存在的等值查询）
select * from dev where y = 40 for update; // 按照之前的结论：锁的区间应该为 [22, 44)(注意是左闭右开），也就是写入y=22,33是失败的，y=44是成功的