MySQL的锁机制 - 间隙锁

间隙锁是封锁索引记录中的间隔，或是第一条索引记录之前的范围，又或是最后一条索引记录之后的范围。

1、间隙锁打开设置

首先查看 innodb_locks_unsafe_for_binlog 是否禁用

SHOW variables LIKE 'innodb_locks_unsafe_for_binlog';
-- 结果：
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   |
+--------------------------------+-------+
1 row in set (0.00 sec)

其默认值为OFF，即启用间隙锁。若需要停止间隙锁，可在my.cnf配置文件下添加参数：

[mysqld]
innodb_locks_unsafe_for_binlog = 1

2、测试环境

测试环境：MySQL，InnoDB，RR（默认的隔离级别）

3、间隙锁的产生

3.1、唯一索引的间隙锁

---

对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁；如果记录存在，则只会产生记录锁。
对于查找某一范围内的查询语句，会产生间隙锁。

数据准备：

-- 建表语句
CREATE TABLE `test` (
  `id` int(1) primary key AUTO_INCREMENT,
  `name` varchar(8)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 测试数据
INSERT INTO `test` VALUES ('1', '小罗');
INSERT INTO `test` VALUES ('5', '小黄');
INSERT INTO `test` VALUES ('7', '小明');
INSERT INTO `test` VALUES ('11', '小红');

test表产生间隙为：

(-∞, 1], (1, 5], (5, 7], (7, 11], (11, +∞)

• 使用唯一索引来搜索并给某一行记录加锁

# SESSION1
BEGIN;
SELECT * FROM `test` WHERE `id` = 5 FOR UPDATE;
SELECT SLEEP(30); -- 延迟30秒执行，防止锁释放
# SESSION2
INSERT INTO `test` (`id`, `name`) VALUES (4, '小张'); # 正常执行
# SESSION3
INSERT INTO `test` (`id`, `name`) VALUES (8, '小东'); # 正常执行
# SESSION1
COMMIT;

总结：只使用记录锁，不会产生间隙锁。上诉的案例，由于主键是唯一索引，而且是只使用一个索引查询，并且只锁定一条记录，所以以上的例子，只会对 id = 5 的数据加上记录锁，而不会产生间隙锁。

• 使用唯一索引锁定多行记录

# SESSION1
BEGIN;
SELECT * FROM `test` WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;
SELECT SLEEP(30);
# SESSION2
INSERT INTO `test` (`id`, `name`) VALUES (3, '小张1'); # 正常执行
# SESSION3
INSERT INTO `test` (`id`, `name`) VALUES (4, '小白'); # 正常执行
# SESSION4
INSERT INTO `test` (`id`, `name`) VALUES (6, '小东'); # 阻塞
# SESSION5
INSERT INTO `test` (`id`, `name`) VALUES (8, '大罗'); # 阻塞
# SESSION6
INSERT INTO `test` (`id`, `name`) VALUES (9, '大东'); # 阻塞
# SESSION7
INSERT INTO `test` (`id`, `name`) VALUES (11, '李西'); # 阻塞
# SESSION8
INSERT INTO `test` (`id`, `name`) VALUES (12, '张三'); # 正常执行
# SESSION1
COMMIT;

总结：上诉案例中，(5, 7]、(7, 11] 这两个区间，都不可插入数据，其它区间，都可以正常插入数据。所以我们可以得出结论：当我们给 (5, 7] 这个区间加锁的时候，会锁住 (5, 7]、(7, 11] 这两个区间。

• 锁住不存在的数据

# SESSION1
BEGIN;
SELECT * FROM `test` WHERE `id` = 3 FOR UPDATE;
SELECT SLEEP(30);
# SESSION2
INSERT INTO `test` (`id`, `name`) VALUES (2, '小张1'); # 阻塞
# SESSION3
INSERT INTO `test` (`id`, `name`) VALUES (4, '小白'); # 阻塞
# SESSION4
INSERT INTO `test` (`id`, `name`) VALUES (6, '小东'); # 正常执行
# SESSION5
INSERT INTO `test` (`id`, `name`) VALUES (8, '大罗'); # 正常执行
# SESSION1
COMMIT;

总结：上诉案例中可以看出，指定查询某一条记录时，如果这条记录不存在，会产生间隙锁。

3.2、普通索引的间隙锁

---

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

数据准备

-- 建表语句
CREATE TABLE `test1` (
  `id` int(1) primary key AUTO_INCREMENT,
  `number` int(1) NOT NULL COMMENT '数字',
  KEY `number` (`number`) USING BTREE -- 普通索引
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- 测试数据
INSERT INTO `test1` VALUES (1, 1);
INSERT INTO `test1` VALUES (5, 3);
INSERT INTO `test1` VALUES (7, 8);
INSERT INTO `test1` VALUES (11, 12);

test表产生间隙为：

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

案例说明：

# SESSION1
BEGIN;
SELECT * FROM `test1` WHERE `number` = 3 FOR UPDATE;
SELECT SLEEP(30);
# SESSION2
INSERT INTO `test1` (`number`) VALUES (0); # 正常执行
# SESSION3
INSERT INTO `test1` (`number`) VALUES (1); # 被阻塞
# SESSION4
INSERT INTO `test1` (`number`) VALUES (2); # 被阻塞
# SESSION5
INSERT INTO `test1` (`number`) VALUES (4); # 被阻塞
# SESSION6
INSERT INTO `test1` (`number`) VALUES (8); # 正常执行
# SESSION7
INSERT INTO `test1` (`number`) VALUES (9); # 正常执行
# SESSION8
INSERT INTO `test1` (`number`) VALUES (10); # 正常执行
# SESSION1
COMMIT;

我们会发现有些语句可以正常执行，有些语句被阻塞了。我们再来看看我们表中的数据：

这里可以看到，number [1, 8)范围的间隙中，插入语句都被阻塞了，而不在这个范围内的语句，正常执行，这就是因为有间隙锁的原因。
我们再进行以下的测试，方便我们更好的理解间隙锁的区域（我们要将数据还原成原来的那样）：

# SESSION1
BEGIN;
SELECT * FROM `test1` WHERE `number` = 3 FOR UPDATE;
SELECT SLEEP(30);
# SESSION2
INSERT INTO `test1` (`id`, `number`) VALUES (2, 1); # 阻塞
# SESSION3
INSERT INTO `test1` (`id`, `number`) VALUES (3, 2); # 阻塞
# SESSION4
INSERT INTO `test1` (`id`, `number`) VALUES (6, 8); # 阻塞
# SESSION5
INSERT INTO `test1` (`id`, `number`) VALUES (8, 8); # 正常执行
# SESSION6
INSERT INTO `test1` (`id`, `number`) VALUES (9, 9); # 正常执行
# SESSION7
INSERT INTO `test1` (`id`, `number`) VALUES (10, 12); # 正常执行
# SESSION8
UPDATE `test1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; # 阻塞
# SESSION1
COMMIT;

这里有一个奇怪的现象：

• 事务4添加 id = 6，number = 8 的数据，给阻塞了；
• 事务5添加 id = 8，number = 8 的数据，正常执行了。
• 事务8将 id = 11，number = 12 的数据修改为 id = 11， number = 5的操作，给阻塞了；

这是为什么呢？我们来看看下边的图，大家就明白了。

从图中可以看出，当 number 相同时，会根据主键 id 来排序，所以：

• 事务4添加的 id = 6，number = 8，这条数据是在 （3, 8） 的区间里边，所以会被阻塞；
• 事务5添加的 id = 8，number = 8，这条数据则是在（8, 12）区间里边，所以不会被阻塞；
• 事务8的修改语句相当于在 （3, 8） 的区间里边插入一条数据，所以也被阻塞了。