【MySQL】MySQL锁（三）元数据锁与间隙锁

MySQL锁（三）元数据锁与间隙锁

在上篇文章中，我们就提到过 元数据锁 和 间隙锁 这两个名词，不知道有没有吊起大家的胃口。这俩货又是干嘛的呢？别急，我们一个一个来看。

元数据锁

元数据锁，又叫 MDL 锁，它是用于保护 DDL 语句的。什么是 DDL 语句？这个是基础知识哦，就是 CREATE/DROP/ALTER 之类的语句，或者说是除了增删改查之外的语句。

首先要明白一点，这些 DDL 语句都是针对整个表的，会对整个表的结构或全部数据产生影响，因此，它们必然是 表锁 级别的。

-- 事务1
mysql> select * from test_user3;

-- 修改表结构 alter table 阻塞
mysql> alter table test_user3 add column d int;

-- 事务2 
mysql> update test_user3 set username='abc' where id = 1;
-- 阻塞，与 MDL 冲突

-- 事务1 修改结束

-- 事务2 操作成功
Query OK, 0 rows affected (42.72 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_user3;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| username   | varchar(200)     | YES  | MUL | NULL    |                |
| password   | varchar(200)     | YES  |     | NULL    |                |
| salt       | char(4)          | YES  |     | NULL    |                |
| created_at | datetime         | YES  | MUL | NULL    |                |
| updated_at | int(11)          | YES  |     | NULL    |                |
| status     | tinyint(4)       | YES  |     | NULL    |                |
| gender     | tinyint(4)       | YES  | MUL | NULL    |                |
| d          | int(11)          | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

上面已经说的很清楚了，因为要对表结构或者整表数据进行操作，MDL锁 自然要锁住整个表。不过这里是个读锁，写入无法进行，读取还是可以的。因此，小伙伴们在生产环境进行 DDL 操作的时候一定要小心啊，最好在半夜进行，这就是程序员的悲哀啊！

间隙与临键锁

上回我们已经见过了 行锁 ，也可以叫做 记录锁 的使用。在分析锁的情况时，我们也提到过了 间隙锁 。

间隙锁（GAP）其实就是封锁索引记录中的间隔，比如说主键不连续的数据插入。假设现在有两条记录，id 分别为 5 和 10 ，那么在 5-10 中间就是我们的数据间隙，这里就是 间隙锁 发挥的地方。

临键锁（Next-key Lock），是一个新的概念，但它其实是 记录锁 和 间隙锁 的结合，也是 MySQL 默认的 行锁 。什么意思呢？间隙锁指的是 5-10，但不包括 5 和 10，也就是一个 开区间 (5, 10)。而 临键锁 则是一个前闭后开区间，把 5 包括进来 [5, 10) 。

为什么又说 临键锁 是默认的行锁呢？其实在默认情况下，行锁 就是 临键锁 ，它会锁自己以及附近的数据，但是，如果是主键或者唯一索引，会退化成 记录锁 ，也就是我们习惯说的那个 “行锁” ，而在大部分情况下，普通的间隙空值操作也会退化为 间隙锁 ，只有在一些条件下才会产生 临键锁 。因此，间隙 和 记录 这两种锁其实都是 临键锁 的退化版本，或者说是简易版本。注意，这个退化仅限于主键是由一个列组成的，如果是多个列组成的，则不会发生退化。

间隙锁 和 临键锁 都是为了解决一个问题，那就是 幻读 的问题。如果不记得这个概念了，就赶紧回到之前的文章复习一下吧 MySQL事务的问题：脏读、幻读、不可重复读https://mp.weixin.qq.com/s/mQ4LxwZkVbBQ4-i8g2qrkA 。

间隙锁的产生有三种情况，我们分别来看一下。

主键唯一

在这里我们尝试给不存在的记录加锁时，就会优化为间隙锁。

-- 事务1
mysql> select * from tran_innodb;
+----+------+------+
| id | name | age  |
+----+------+------+
| 19 | Joe2 |   14 |
| 23 | Joe2 |   18 |
+----+------+------+
11 rows in set (0.00 sec)

mysql> begin;
mysql> update tran_innodb set name = joe3 where id = 15;
-- 注意这里没有记录为 15 的数据

-- 事务2
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+-----------+-----------+
| blog_test     | tran_innodb | NULL       | TABLE     | IX        | NULL      |
| blog_test     | tran_innodb | PRIMARY    | RECORD    | X,GAP     | 16        |
+---------------+-------------+------------+-----------+-----------+-----------+
2 rows in set (0.05 sec)
-- 可以看到 lock_mode 中显示的是 X,GAP 产生了间隙锁

mysql> insert into tran_innodb(id,name,age) values(14,'Joe2',13);
-- 阻塞

-- 事务1 提交
mysql> commit;

-- 事务3
mysql> insert into tran_innodb(id,name,age) values(14, 'Joe2', 13);
-- 阻塞

注释中说得很明确，事务1去更新一条不存在的记录 15 ，这个 id 是位于 13 和 16 之间的，事务2 和 事务3 同时也无法增加记录为 14 的数据，这就是因为在 13 和 16 之间产生了间隙锁。

其实在这里，事务2 虽然也产生了阻塞，但是当 事务1 提交之后，事务2 会马上拿到锁，事务3 还是会在阻塞状态，紧接着 事务2 提交后，事务3 就会插入失败。

-- 事务2 提交
mysql> commit;

-- 事务3
ERROR 1062 (23000): Duplicate entry '14' for key 'tran_innodb.PRIMARY'

非唯一索引（普通索引）

普通索引产生的间隙和主键索引是一样的。

-- 事务1
mysql> select * from tran_innodb;
+----+------+------+
| id | name | age  |
+----+------+------+
| 19 | Joe2 |   14 |
| 23 | Joe2 |   18 |
+----+------+------+
10 rows in set (0.00 sec)

-- 加排它锁
begin;
mysql> select * from tran_innodb where age = 16 lock in share mode;

-- 事务2
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+-----------+-----------+
| blog_test     | tran_innodb | NULL       | TABLE     | IS        | NULL      |
| blog_test     | tran_innodb | idx_age    | RECORD    | S,GAP     | 18, 18    |
+---------------+-------------+------------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

-- 事务2 插入数据
mysql> insert into tran_innodb(id,name,age) values(24,'Joe2',15);

-- 事务3 插入数据
mysql> insert into tran_innodb(id,name,age) values(31,'Joe2',11);
Query OK, 1 row affected (0.00 sec)

-- 事务4 插入数据
mysql> insert into tran_innodb(id,name,age) values(32,'Joe2',13);
Query OK, 1 row affected (0.00 sec)
-- 阻塞

在针对普通索引的情况下，也会产生 间隙锁 这个锁和 主键 就没什么关系了，而是根据索引字段的情况产生阻塞。

假设我们把锁正好打在一个存在的数据上，会发生什么？注意，普通索引是非唯一的，可能有多条数据会加锁。

-- 事务1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tran_innodb where age = 18 lock in share mode;
+----+------+------+
| id | name | age  |
+----+------+------+
| 18 | Joe2 |   18 |
| 23 | Joe2 |   18 |
+----+------+------+
2 rows in set (0.01 sec)

-- 事务1
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+------------------------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data              |
+---------------+-------------+------------+-----------+---------------+------------------------+
| blog_test     | tran_innodb | NULL       | TABLE     | IS            | NULL      |
| blog_test     | tran_innodb | idx_age    | RECORD    | S             | 18, 18    |
| blog_test     | tran_innodb | idx_age    | RECORD    | S             | 18, 23    |
| blog_test     | tran_innodb | PRIMARY    | RECORD    | S,REC_NOT_GAP | 23        |
| blog_test     | tran_innodb | PRIMARY    | RECORD    | S,REC_NOT_GAP | 18        |
| blog_test     | tran_innodb | idx_age    | RECORD    | S,GAP         | 25, 29    |                 |
+---------------+-------------+------------+-----------+---------------+------------------------+
6 rows in set (0.00 sec)

感觉一下加了好多锁啊，注意看，现在 age 从 18 到 24 全部被锁，整个区间范围内都上了 S 锁。另外还包括 18 自己的普通记录锁，整个合起来就形成了前闭后开的 [18, 25) 临键锁 。需要注意的是，lock_data 表示的是锁住的当前数据和主键，不是区间范围哦，我一开始就以为它是锁的区间范围，结果其实是 数据键,主键 的意思。

如果是唯一索引进行等值加锁的话，其实就只是一个行锁了，为啥呢？唯一的值嘛，就一条，给这一行锁上就行啦。

范围查询

最后就是范围查询的间隙锁。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tran_innodb where id > 33 lock in share mode;
+----+------+------+
| id | name | age  |
+----+------+------+
| 35 | Joe2 |   25 |
+----+------+------+
1 row in set (0.00 sec)

-- 事务2
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+------------------------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data              |
+---------------+-------------+------------+-----------+-----------+------------------------+
| blog_test     | tran_innodb | NULL       | TABLE     | IS        | NULL                   |
| blog_test     | tran_innodb | PRIMARY    | RECORD    | S         | supremum pseudo-record |
| blog_test     | tran_innodb | PRIMARY    | RECORD    | S         | 35                     |
+---------------+-------------+------------+-----------+-----------+------------------------+
3 rows in set (0.00 sec)

mysql> insert into tran_innodb(id,name,age) values(45,'Joe2',10);
-- 阻塞

普通范围内的我们就不说了，在这里我们的范围是 id 整个大于30的，在 35 之后就没有数据了，其实现在形成的区间就是 (30, 无穷大) 。此时产生的记录锁中，有一条的 lock_data 就是 supremum pseudo-record ，它表明的就是到无穷大的记录间隙都被锁了。因此，后面我们插入一条 id 为 45 的数据也会阻塞。

因此，在 更新/删除 数据时，如果是范围条件，即使有索引，也会锁很多间隙，特别是 id 或数据不连续（普通索引）的情况下。插入数据的情况稍微好一点，毕竟我们会更习惯于自增主键的形式插入，但也要注意 自增锁 的情况，这个锁我们下回再说。

总结

是不是感觉 间隙锁 非常复杂？的确，它真的是很复杂，也是高级码农们面试的时候最容易被问到的。为啥呢？它要解决的可是 幻读 问题啊，也就是我们事务隔离问题中最麻烦的那个问题。因此，如果事务隔离级别是 REPEATABLE-READ 重复读 以下的级别，就不会有 间隙锁 ，这个大家可以自己试试哦。

最后，还有几个锁相关的知识，我们下回见。