MySql 间隙锁整理

MySql InnoDB 事务隔离级别是 RR 的情况下，间隙锁会封锁该条记录相邻两个键之间的空白区域，防止其它事务在这个区域内插入、修改、删除数据，以防止出现幻读现象。

MySql InnoDb 默认开启间隙锁，查看是间隙锁开启状态：

show variables like 'innodb_locks_unsafe_for_binlog';

间隙锁产生条件可以分为以下三种情况：

使用普通索引锁定
使用多列唯一索引（使用一列唯一索引锁定不产生间隙锁）
使用唯一索引锁定多行记录

唯一索引的间隙锁

假如有如下数据表：

CREATE TABLE `test` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

执行 insert 操作

执行插入语句：

INSERT INTO `test` VALUES ('1', '小罗');
INSERT INTO `test` VALUES ('5', '小黄');
INSERT INTO `test` VALUES ('7', '小明');
INSERT INTO `test` VALUES ('11', '小红');

以下区间会被锁住：

(-infinity, 1]
(1, 5]
(5, 7]
(7, 11]
(11, +infinity]

使用唯一索引锁定一行

如果只根据主键或唯一索引锁定一行，不会产生间隙锁，比如如下操作，只对 id=5 这一行加锁，则不会产生间隙锁，也不会阻塞之后的写入。

/* 开启事务1 */
BEGIN;
/* 查询 id = 5 的数据并加记录锁 */
SELECT * FROM `test` WHERE `id` = 5 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/* 事务2插入一条 name = '小张' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小张'); # 正常执行

/* 事务3插入一条 name = '小张' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '小东'); # 正常执行

/* 提交事务1，释放事务1的锁 */
COMMIT;

使用唯一索引或主键索引锁定多行

使用唯一索引或主键索引锁定多行，会产生间隙锁，例如如下操作锁定 [5, 7]，则会产生间隙锁，(5, 7]、(7, 11] 这两个区间，都不可插入数据，其他区间可以正常插入数据。

/* 开启事务1 */
BEGIN;
/* 查询 id 在 7 - 11 范围的数据并加记录锁 */
SELECT * FROM `test` WHERE `id` >= 5 AND `id` <= 7 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/* 事务2插入一条 id = 3，name = '小张1' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (3, '小张1'); # 正常执行

/* 事务3插入一条 id = 4，name = '小白' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小白'); # 正常执行

/* 事务4插入一条 id = 6，name = '小东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (6, '小东'); # 阻塞

/* 事务5插入一条 id = 8， name = '大罗' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '大罗'); # 阻塞

/* 事务6插入一条 id = 9， name = '大东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (9, '大东'); # 阻塞

/* 事务7插入一条 id = 11， name = '李西' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (11, '李西'); # 阻塞

/* 事务8插入一条 id = 12， name = '张三' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (12, '张三'); # 正常执行

/* 提交事务1，释放事务1的锁 */
COMMIT;

锁住的数据不存在的情况

锁住的数据不存在的情况，会产生间隙锁，如下操作锁住一条不存在的数据，产生了间隙锁

/* 开启事务1 */
BEGIN;
/* 查询 id = 3 这一条不存在的数据并加记录锁 */
SELECT * FROM `test` WHERE `id` = 3 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/* 事务2插入一条 id = 3，name = '小张1' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (2, '小张1'); # 阻塞

/* 事务3插入一条 id = 4，name = '小白' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小白'); # 阻塞

/* 事务4插入一条 id = 6，name = '小东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (6, '小东'); # 正常执行

/* 事务5插入一条 id = 8， name = '大罗' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '大罗'); # 正常执行

/* 提交事务1，释放事务1的锁 */
COMMIT;

小结

根据主键或唯一索引对某一条记录加锁，如果记录不存在会产生行锁和间隙锁，如果记录存在，只会产生行锁。
范围查询加锁，会产生间隙锁

普通索引的间隙锁

假如有如下数据表

CREATE TABLE `test1` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `number` int(1) NOT NULL COMMENT '数字', # number 不是唯一值
  PRIMARY KEY (`id`),
  KEY `number` (`number`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

数据写入

执行如下 insert 操作

INSERT INTO `test1` VALUES (1, 1);
INSERT INTO `test1` VALUES (5, 3);
INSERT INTO `test1` VALUES (7, 8);
INSERT INTO `test1` VALUES (11, 12);

以下区间会被锁住：

(-infinity, 1]
(1, 3]
(3, 8]
(8, 12]
(12, +infinity]

使用普通索引

执行如下操作，对 number=3 数据加锁，导致区间 [1,8) 被锁住。

/* 开启事务1 */
BEGIN;
/* 查询 number = 3 的数据并加行锁 */
SELECT * FROM `test1` WHERE `number` = 3 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/* 事务2插入一条 number = 0 的数据 */
INSERT INTO `test1` (`number`) VALUES (0); # 正常执行

/* 事务3插入一条 number = 1 的数据 */
INSERT INTO `test1` (`number`) VALUES (1); # 被阻塞

/* 事务4插入一条 number = 2 的数据 */
INSERT INTO `test1` (`number`) VALUES (2); # 被阻塞

/* 事务5插入一条 number = 4 的数据 */
INSERT INTO `test1` (`number`) VALUES (4); # 被阻塞

/* 事务6插入一条 number = 8 的数据 */
INSERT INTO `test1` (`number`) VALUES (8); # 正常执行

/* 事务7插入一条 number = 9 的数据 */
INSERT INTO `test1` (`number`) VALUES (9); # 正常执行

/* 事务8插入一条 number = 10 的数据 */
INSERT INTO `test1` (`number`) VALUES (10); # 正常执行

/* 提交事务1 */
COMMIT;

使用普通索引锁住不存在的数据

执行如下操作，锁住一条不存在的数据 number=5，(1,3)、(3,8)、(8,12)、(12,+infinity) 区间被阻塞

/* 开启事务1 */
BEGIN;
/* 查询 number = 5 的数据并加记录锁 */
SELECT * FROM `test1` WHERE `number` = 5 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);

/* 事务1插入一条 id = 2， number = 1 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (2, 1); # 阻塞

/* 事务2插入一条 id = 3， number = 2 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (3, 2); # 阻塞

/* 事务3插入一条 id = 6， number = 8 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (6, 8); # 阻塞

/* 事务4插入一条 id = 8， number = 8 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (8, 8); # 正常执行

/* 事务5插入一条 id = 9， number = 9 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (9, 9); # 正常执行

/* 事务6插入一条 id = 10， number = 12 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (10, 12); # 正常执行

/* 事务7修改 id = 11， number = 12 的数据 */
UPDATE `test1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; # 阻塞

/* 提交事务1 */
COMMIT;

小结

在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁
在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通索引排序，再根据唯一索引排序

总结

唯一索引只有锁住多条记录或者一条不存在的记录的时候，才会产生间隙锁
普通索引不管是锁住单条，还是多条记录，都会产生间隙锁

CREATE TABLE test (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `age` int(1) NOT NULL ,
  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`),
  key `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `test` VALUES (1, 10, '赵');
INSERT INTO `test` VALUES (5, 15, '钱');
INSERT INTO `test` VALUES (10, 20, '孙');
INSERT INTO `test` VALUES (15, 25, '李');

# 主键/唯一索引 锁定单条记录 不产生间隙锁 对间隙记录修改不阻塞

begin;

select * from `test` where id=5 for update ;
select sleep(30);

# insert into `test`.`test` values (4, 14, '周'); # 不阻塞
# insert into `test`.`test` values (6, 16, '吴'); # 不阻塞

# 主键/唯一索引 锁定多条记录 产生间隙锁 对间隙记录修改阻塞

begin;

select * from `test` where id>=5 and id<=10 for update ;
select sleep(30);

# insert into `test`.`test` values (3, 13, '郑'); # 不阻塞
# insert into `test`.`test` values (7, 17, '王'); # 阻塞

# 主键/唯一索引 锁住不存在的数据时 产生间隙锁 对间隙记录修改阻塞

begin;

select * from `test` where id =7 for update ;
select sleep(30);

# insert into `test`.`test` values (2, 12, '郑'); # 不阻塞
# insert into `test`.`test` values (8, 18, '王'); # 阻塞

# 普通索引 锁住单行

delete from test.test where id in (2, 3, 4, 6, 7, 8);

begin;
select * from `test` where age = 15 for update ;
select sleep(30);
commit;

# insert into `test`.`test` values (4, 13, '周');  # 阻塞
# insert into `test`.`test` values (6, 18, '吴');  # 阻塞
# insert into `test`.`test` values (12, 22, '郑');  # 不阻塞