https://zhuanlan.zhihu.com/p/48269420

前言

前面小编已经学习了 共享/排它锁 和 自增锁 这两种锁的使用和注意事项，今天这篇文章，小编就来分享下近来学习的记录锁、间隙锁、临键锁这三种锁。
注：小编觉得这是极其重要的一篇文章，跟前边的自增锁也有一定的关联

记录锁(Record Locks)

记录锁是 封锁记录，记录锁也叫行锁，例如：
SELECT * FROM `test` WHERE `id`=1 FOR UPDATE;
它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。

记录锁、间隙锁、临键锁都是排它锁，而记录锁的使用方法跟之前的一篇文章 共享/排它锁 里的排它锁介绍一致，这里就不详细多讲。

间隙锁(Gap Locks)（重点）

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

产生间隙锁的条件（RR事务隔离级别下；）：

  1. 使用普通索引锁定；
  2. 使用多列唯一索引；
  3. 使用唯一索引锁定多行记录。

以上情况，都会产生间隙锁，下面是小编看了官方文档理解的：
对于使用唯一索引来搜索并给某一行记录加锁的语句，不会产生间隙锁。（这不包括搜索条件仅包括多列唯一索引的一些列的情况；在这种情况下，会产生间隙锁。）例如，如果id列具有唯一索引，则下面的语句仅对具有id值100的行使用记录锁，并不会产生间隙锁：

SELECT * FROM child WHERE id = 100 FOR UPDATE;
这条语句，就只会产生记录锁，不会产生间隙锁。

打开间隙锁设置
首先查看 innodb_locks_unsafe_for_binlog 是否禁用：
show variables like ‘innodb_locks_unsafe_for_binlog’;
查看结果：
image.png
innodb_locks_unsafe_for_binlog：默认值为OFF，即启用间隙锁。因为此参数是只读模式，如果想要禁用间隙锁，需要修改 my.cnf（windows是my.ini） 重新启动才行。
# 在 my.cnf 里面的[mysqld]添加 [mysqld] innodb_locks_unsafe_for_binlog = 1

唯一索引的间隙锁

测试环境：
环境：MySQL，InnoDB，默认的隔离级别（RR）
数据表：
CREATE TABLE `test` ( `id` int(1) NOT NULL AUTO_INCREMENT, `name` varchar(8) DEFAULT NULL, PRIMARY KEY (`id`) ) 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. (-infinity, 1]
  2. (1, 5]
  3. (5, 7]
  4. (7, 11]
  5. (11, +infinity]

只使用记录锁，不会产生间隙锁
我们现在进行以下几个事务的测试：
/ 开启事务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;
上诉的案例，由于主键是唯一索引，而且是只使用一个索引查询，并且只锁定一条记录，所以以上的例子，只会对 id = 5 的数据加上记录锁，而不会产生间隙锁。

产生间隙锁
我们继续在 id 唯一索引列上做以下的测试：
/ 开启事务1 / BEGIN; / 查询 id 在 7 - 11 范围的数据并加记录锁 / SELECT * FROM `test` WHERE `id` BETWEEN 5 AND 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;
从上面我们可以看到，(5, 7]、(7, 11] 这两个区间，都不可插入数据，其它区间，都可以正常插入数据。所以我们可以得出结论：当我们给 (5, 7] 这个区间加锁的时候，会锁住 (5, 7]、(7, 11] 这两个区间。

我们再来测试如果我们锁住不存在的数据时，会怎样：
/ 开启事务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;
我们可以看出，指定查询某一条记录时，如果这条记录不存在，会产生间隙锁。

结论

  1. 对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁，如：WHERE id = 5 FOR UPDATE;
  2. 对于查找某一范围内的查询语句，会产生间隙锁，如：WHERE id BETWEEN 5 AND 7 FOR UPDATE;

普通索引的间隙锁

数据准备
创建 test1 表：
# 注意：number 不是唯一值 CREATE TABLE `test1` ( `id` int(1) NOT NULL AUTO_INCREMENT, `number` int(1) NOT NULL COMMENT ‘数字’, PRIMARY KEY (`id`), KEY `number` (`number`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
在这张表上，我们有 id number 这两个字段，id 是我们的主键，我们在 number 上，建立了一个普通索引，为了方便我们后面的测试。现在我们要先加一些数据：
INSERT INTO `test1` VALUES (1, 1); INSERT INTO `test1` VALUES (5, 3); INSERT INTO `test1` VALUES (7, 8); INSERT INTO `test1` VALUES (11, 12);
在进行测试之前，我们先来看看test1表中 number 索引存在的隐藏间隙：

  1. (-infinity, 1]
  2. (1, 3]
  3. (3, 8]
  4. (8, 12]
  5. (12, +infinity]

案例说明
我们执行以下的事务（事务1最后提交），分别执行下面的语句：
/ 开启事务1 / BEGIN; / 查询 number = 5 的数据并加记录锁 / 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;
我们会发现有些语句可以正常执行，有些语句被阻塞了。我们再来看看我们表中的数据：
image.png
执行之后的数据
这里可以看到，number (1 - 8) 的间隙中，插入语句都被阻塞了，而不在这个范围内的语句，正常执行，这就是因为有间隙锁的原因。我们再进行以下的测试，方便我们更好的理解间隙锁的区域（我们要将数据还原成原来的那样）：
/ 开启事务1 / BEGIN; / 查询 number = 5 的数据并加记录锁 / SELECT * FROM `test1` WHERE `number` = 3 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;
我们来看看结果：
image.png
执行后的数据
这里有一个奇怪的现象：

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

这是为什么呢？我们来看看下边的图，大家就明白了。
image.png
隐藏的间隙锁图
从图中可以看出，当 number 相同时，会根据主键 id 来排序，所以：

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

结论

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

临键锁(Next-key Locks)

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。

注：临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

本文要点

  1. 记录锁、间隙锁、临键锁，都属于排它锁；
  2. 记录锁就是锁住一行记录；
  3. 间隙锁只有在事务隔离级别 RR 中才会产生；
  4. 唯一索引只有锁住多条记录或者一条不存在的记录的时候，才会产生间隙锁，指定给某条存在的记录加锁的时候，只会加记录锁，不会产生间隙锁；
  5. 普通索引不管是锁住单条，还是多条记录，都会产生间隙锁；
  6. 间隙锁会封锁该条记录相邻两个键之间的空白区域，防止其它事务在这个区域内插入、修改、删除数据，这是为了防止出现 幻读 现象；
  7. 普通索引的间隙，优先以普通索引排序，然后再根据主键索引排序（多普通索引情况还未研究）；
  8. 事务级别是RC（读已提交）级别的话，间隙锁将会失效。

编辑于 2018-12-12 10:20
数据库
MySQL
赞同 182喜欢收藏申请转载
60 条评论
分享

文章被以下专栏收录

image.png

代码改变世界

Write the Code ， Change the world ！

推荐阅读

详解 MySql InnoDB 中的三种行锁（记录锁、间隙锁与临键锁）前言 InnoDB 通过 MVCC 和 NEXT-KEY Locks，解决了在可重复读的事务隔离级别下出现幻读的问题。MVCC 我先挖个坑，日后再细讲，这篇文章我们主要来谈谈那些可爱的锁。 什么是幻读？ 幻读是…范松伟MySQL中的锁（表锁、行锁）锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、…编码前线发表于编码前线Mysql里的锁(排它锁、共享锁、行锁、表锁、间隙锁、临键锁、意向锁)在了解mysql中的各种锁之前你还需要了解这些1、悲观锁乐观锁： https://zhuanlan.zhihu.com/p/315378712、mysql索引原理B+ Tree: https://zhuanlan.zhihu.com/p/277006173、事务的ACID特性…勤劳的小手发表于必须深入理…通过各种简单案例，让你彻底搞懂 MySQL 中的锁机制与 MVCC不剪发的T…发表于MySQL…

60 条评论

切换为时间排序
发布
写下你的评论…
happyxhw2019-12-01/ 查询 id 在 7 - 11 范围的数据并加记录锁 /
SELECT * FROM test WHERE id BETWEEN 5 AND 7 FOR UPDATE;

BEGIN;
/ 查询 number = 5 的数据并加记录锁 /
SELECT * FROM test1 WHERE number = 3 FOR UPDATE;

用点心可好，糊弄人呢？30回复踩 举报

  • 知行回复happyxhw01-17是的，这么明显的错误2回复踩 举报
  • 风中一匹狼回复happyxhw03-28哈哈 我以为我眼花了赞回复踩 举报
  • helloworld2020-07-10记录锁、间隙锁、临键锁，都属于排它锁？？？前三种锁是锁类型，而排他锁是锁模式，两者并没有从属关系。10回复踩 举报
  • 知乎用户2021-03-06讲清了“是什么”，可是“为什么”是一点都没讲。唉，完全不懂为什么，自闭了。7回复踩 举报
  • 铜锣烧回复知乎用户03-15为了解决幻读，比如可重复读级别下，你有记录1,4，无2,3，你select不到2，但是其他事务插入了2，你下次select for update当前读的时候就看到了2，这就是幻读，但是2无记录，你没法针对一行不存在的记录加锁，所以引入间隙锁，锁住间隙，让其他事务无法插入22回复踩 举报
  • TristerAnne2021-02-03为什么要用SELECT SLEEP(30);来防止锁释放呢?只要事务不提交锁就不会释放啊6回复踩 举报
  • 张星城2018-12-07非常好实验清楚明白!间隙锁的影响讲解十分清楚.
    看到个小瑕疵,全文第二段sql:
    SELECT * FROM child WHERE id = 100;
    需要手动加锁,否则是快照读,不会产生记录锁3回复踩 举报
  • 咖啡屋小罗 (作者) 回复张星城2018-12-10谢谢，晚点修改1回复踩 举报
  • 从人间路过2021-01-19gap locks不是独占锁。4回复踩 举报
  • 安小乐2020-06-30您好，我用您的例子试了一下。where id between 5 and 7 for update;会把[5,11]加锁。两边都是闭区间。请问这是什么原因呢？2回复踩 举报
  • 北风回复安小乐2021-05-30where id between 5 and 7 for update; # 查询的是[5,7]的id
    加锁时会将[5,7]和[7,11]加锁赞回复踩 举报
  • 知乎用户回复北风2021-06-09为什么不是[1,11]或者[5,7], 而是[5,11]呢赞回复踩 举报

展开其他 2 条回复

  • Stone2019-08-22范围查找加间隙锁可以理解，为什么精确查找也需要加间隙锁呢？1回复踩 举报
  • 秦少康回复Stone02-18因为不是唯一索引，所以值可能会出现多个，所以需要锁上面那条值得next_key和本条数据的next_key，所以需要加赞回复踩 举报
  • Dirtybomb2019-05-27那么 怎么确定间隙锁的范围
    比如 BETWEEN 5 AND 7 FOR UPDATE如何确定(5,7],(7,11] 这个区间内加间隙锁了1回复踩 举报
  • 陈Pana回复Dirtybomb2020-04-04这个只会加5-7，他的SQL和上面描述不符6回复踩 举报
  • 卖火柴的小男孩回复Dirtybomb2020-10-13我自己实验了一下…innodb，mysql8.0；当查询id between 5 and 7的时候5，6，7不能被操作，1234和8910都是没有被锁住的；4回复踩 举报

查看全部 10 条回复

  • 张无忌2020-06-11为什么么 between 5 and 7 要锁8赞回复踩 举报
  • 安小乐回复张无忌2020-06-30会把[5,11]闭区间都给锁住。但是我不明白这是为什么。赞回复踩 举报
  • 庄周公回复张无忌2020-10-22我试了一下并不会锁8，但是我也没有找到innodb_locks_unsafe_for_binlog，不过我的mysql事务隔离级别也是RR赞回复踩 举报

展开其他 1 条回复

  • 知乎用户2020-05-07不错赞回复踩 举报
  • deppwang2019-12-06为什么要用「小编」这个称呼呢，第一眼感觉像是在网上摘抄总结的 。。。赞回复踩 举报
  • 咖啡屋小罗 (作者) 回复deppwang2020-03-13对呀，都是网上看的一些心得1回复踩 举报
  • 芝士先生回复咖啡屋小罗 (作者)2021-06-23东抄一点，西抄一点就来发，本来锁机制都够麻烦了，还是别误导新人了吧1回复踩 举报
  • 咖啡屋小罗 (作者)2019-08-17高性能MySQL，这本书不错赞回复踩 举报
  • 知乎用户2019-08-15相关的内容，有书籍推荐吗？赞回复踩 举报
  • 知乎用户回复知乎用户2020-12-03事物信息系统赞回复踩 举报
  • 福尔魔猪2019-08-05示例不错，总结的还可以更细一些，例如间隙锁的范围区间。赞回复踩 举报
  • 咖啡屋小罗 (作者) 回复福尔魔猪2019-08-17谢谢评价~感觉自己有些地方没理清~赞回复踩 举报
  • Jgos2019-06-06RR为什么要防止幻读啊, 不是串行化才要防止幻读吗赞回复踩 举报
  • nanlai回复Jgos2020-07-01使用串行化并发度就低了啊，在RR级别解决幻读，还可以保证并发度，就更好了啊1回复踩 举报
  • 肖廷回复Jgos2020-08-25现在公司都用RC赞回复踩 举报
  • 橡皮擦2019-03-11应该还有唯一索引与普通索引的增长性相反的时候，比如你最后那个例子中的(1,1)换成(7,1),（7,8)换成(1,8)，这个时候对于端点处的插入就是内侧可以插入进来，外侧不行，也就是(6,8)可以，(8,8)不行。赞回复踩 举报
  • 咖啡屋小罗 (作者) 回复橡皮擦2019-03-20这个，我还没测试，嘻嘻赞回复踩 举报
  • 林蒙2021-10-13乱糟糟的 容易产生误解赞回复踩 举报
  • 知乎用户2021-03-27写的很棒，非常感谢！！有一些小错误但瑕不掩瑜image.png赞回复踩 举报
  • 狂奔的显卡2021-01-04大佬还在吗，问下多列的联合唯一索引，间隙锁的区间是怎么分的啊，我自己试，感觉全都能锁住啊赞回复踩 举报

1