自学内容网 自学内容网

【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 重复读 以下的级别,就不会有 间隙锁 ,这个大家可以自己试试哦。

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


原文地址:https://blog.csdn.net/zhangyue0503/article/details/137944133

免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!