标题:【MySQL】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://mp.weixin.qq.com/s/mZ2xdzhto9s1wBoYE8-7-A

    搜索
    关注