MySQL记录锁、间隙锁、临键锁小案例演示

生成间隙(gap)锁、临键(next-key)锁的前提条件是在 RR 隔离级别下。

有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过，详细内容可以看这篇文章一文详解MySQL的锁机制

这篇主要通过小案例来对记录锁、间隙(gap)锁、临键(next-key)锁做一个更好的理解。

这里先给出结论，再来用实际例子证明

1、当使用唯一索引来等值查询的语句时, 如果这行数据存在，不产生间隙锁，而是记录锁。
2、当使用唯一索引来等值查询的语句时, 如果这行数据不存在，会产生间隙锁。
3、当使用唯一索引来范围查询的语句时，对于满足查询条件但不存在的数据产生间隙(gap)锁，如果查询存在的记录就会产生记录锁，加在一起就是临键锁(next-key)锁。
4、当使用普通索引不管是锁住单条，还是多条记录，都会产生间隙锁；
5、在没有索引上不管是锁住单条，还是多条记录，都会产生表锁；

间隙锁会封锁该条记录相邻两个键之间的空白区域，防止其它事务在这个区域内插入、修改、删除数据，这是为了防止出现幻读现象；

间隙的范围？

根据检索条件向下寻找最靠近检索条件的记录值A作为左区间，向上寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B] 左开右闭。

接下来我们开始来验证以上结论

一、数据和环境准备

1、创建表和数据

代码语言：javascript复制

CREATE TABLE `t` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `age` int NOT NULL COMMENT '年龄',
  `mobile` int DEFAULT NULL COMMENT '手机号',
  `name` varchar(8)  DEFAULT NULL COMMENT '名称',
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

id为主键(唯一索引)、age是普通索引、mobile没有加索引

同时插入数据如下。

在进行测试之前，我们先来看看t表中存在的隐藏间隙：

(-∞, 1] (1, 4] (4, 7] (7, supernum]

（其中supernum是数据库维护的最大的值。为了保证间隙锁都是左开右闭原则。）

2、关闭事务默认提交

代码语言：javascript复制

mysql> SHOW VARIABLES LIKE 'autocommit';
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| autocommit    | ON    |
 --------------- ------- 
1 row in set (0.00 sec)

结果显示，autocommit 的值是 ON，表示系统开启自动提交模式。

在 MySQL 中，可以使用 SET autocommit 语句设置事务的自动提交模式，语法格式如下：

代码语言：javascript复制