MySQL中都有哪些锁?

MySQL中都有哪些锁

为什么需要锁

在计算机系统中,锁(Lock)是一种同步机制,用于控制对共享资源的访问。它确保在任何给定时间内只有一个线程能够访问受保护的共享资源,从而避免了由并发访问导致的数据竞争和不一致问题。
同样,在数据库系统中,锁也扮演着重要角色,是其与文件系统不同的关键特性之一。数据库中存储的数据也是一种供多用户访问的共享资源。为了在多用户(多线程)访问的场景下保证数据的一致性、事务的隔离性以及提高数据库的并发性能等,MySQL实现了各种不同的锁机制。接下来,让我们来详细聊一聊这些锁。

按访问方式分类

假设我们对数据库的操作全是操作,在并发访问下也不会出现数据一致性问题。出现问题的原因是我们对数据库还有另一个重要的操作,那就是。正是由于操作会改变数据,才会导致一系列问题的产生。但是如果我们不加以区分,对于所有的操作都加“互斥锁”,那么原先那些可以并发执行的读-读操作就会被阻塞。影响数据库并发访问性能。
基于此,MySQL中实现了一种由两种类型的锁组成的锁系统,即读写锁。读写锁允许多个线程同时获取读锁,以实现读操作的并发执行,而对于写操作则会独占地获取写锁。

共享锁

共享锁(Shared Lock),又称为读锁S锁;它允许多个事务同时获取锁并读取同一份数据。当一个事务获取共享锁后,其他事务也可以获取相同的共享锁。
共享锁之间是兼容的,多个事务可以并发地持有共享锁,在进行读取操作时也不会对其他事务造成阻塞。

排他锁

排他锁(Exclusive Lock),又称为写锁独占锁X锁;它只允许一个事务获取并持有该锁。当一事务获取到X锁后,其他事务无法同时获取X锁或者S锁,必须等待X锁的释放。
X锁可以防止并发修改操作引起的数据冲突问题。
依据共享锁和排他锁的特性,我们可以得出两者之间的兼容性列表:

兼用性 X 锁 S 锁
X 锁 不兼容 不兼容
S 锁 不兼容 兼容

按锁的粒度分类

在MySQL中,根据数据所在的维度,可以大致分为数据库级别、表级别和行级别。在这些维度上加锁会有很大的区别,MySQL在这些维度上分别提供了不同的锁实现。

全局锁

全局锁的加锁和解锁

全局锁,也就是对整个数据库实例进行加锁,MySQL提供了一个加全局读锁的命令:

flush tables with read lock

也就是 FTWRL的全称。当执行这条命令后,整个MySQL数据库就处于只读状态。此时对于任何数据的写操作,或者表结构的修改操作都会被阻塞。在这个状态下只允许查询操作。
我们可以执行如下命令来手动释放全局锁;

unlock tables

或者直接断开会话,全局锁也会被自动释放。

全局锁的使用场景

全局锁的典型使用场景只有一种,那就是做全库的逻辑备份。因为在加全局锁期间,全库数据和表结构不会被修改,也就保证了备份数据的一致性。
但是使用全局锁来做全库备份也存在一些问题:

  • 如果我们备份时间很长,那么数据库就会有很长一段时间内不能更新数据,这将会严重影响业务
  • 如果我们在从库上做备份,同样的我们在备份期间就不能及时处理主库同步过来的binlog,这将会导致主从延迟

有些人会想到,既然做全库逻辑备份,只要将数据库设置为只读就行,那为什么不直接 set global readonly = true,让整个数据库实例处于只读模式。的确,这样也能做全库备份,但是这和 FTWRL没有实质区别,对业务的影响还是很大。而且在某些情况下,还会存在用户权限不够、或者readonly值用来做其他主从库区分等情况。所以一般也不建议使用这种方式。
那么我们一般怎样来做全库逻辑备份呢?
MySQL官方提供一个逻辑备份工具,叫作mysqldump。我们可以在其后加一个--single-transaction参数来指定做全库逻辑备份时,使用一致性快照读。这样就避免了加全局锁的操作。例如:

-- 使用一致性快照读的方式,逻辑备份 userDb 数据库到 userBackup.sql
mysqldump -u root -p --single-transaction userDb > userBackup.sql

但是有一点要注意的是,这种全库逻辑备份的方式只适用于数据库中所有表的存储引擎都是InnoDB的。

表级锁

在MySQL中,表级别的锁大概有四种,每种使用的场景都不一样。

表锁

表锁(Table Lock),也就是对某张表加锁。具体来说,表锁按访问方式,可以分为共享表锁(S锁) 和排他表锁(X锁)。
假设我们要加锁的表是user,那么就可以使用下面的语句来加锁:

-- 给 user 表加 共享读锁
lock tables user read

-- 给 user 表加 独占写锁
lock tables user write

FTWRL一样,解锁使用的也是unlock tables语句来释放当前会话下所有的表锁。另外如果退出会话的话,表锁也会被自动释放。
在没有出现更细粒度的锁之前,MySQL一般都是使用表锁来处理并发。而现在,我们不推荐使用表锁,因为InndoDB存储引擎提供了更加细粒度的行锁支持,处理并发时性能更好。

元数据锁(MDL)

假设我们在访问数据的同时,另一个用户对表结构进行了修改,新增了一列,我们查询出来的数据不包含这一列,这肯定是不行的。元数据锁(Metadata Lock) 正是用来处理这一类问题。
元数据锁不需要我们显示的进行声明和释放,当访问一张表时,它会被自动加上。具体来说:

  • 当我们对表数据进行CRUD时,会自动加上元数据读锁(S锁)
  • 当我们对表结构进行修改时,会自动加上元数据写锁(X锁)

读锁和写锁的兼容性和前面表格中列的一样。需要注意的时,元数据锁在语句执行完之后不会立马释放,而是等到事务提交之后,才会释放
虽然说元数据锁不需要用户手动来操作申请和释放,但是在某些场景下,也会导致问题发生。假设某个表有比较频繁的查询请求,并且有超时重试机制,在中途如果存在表结构的修改操作,那么很有可能会出现元数据写锁与元数据读锁相互等待,而造成查询阻塞的现象。

意向锁

MySQL的InnoDB存储引擎是支持多粒度锁定的,也就是说支持行级锁和表级锁共存。为了实现这一特性,InnoDB设计了意向锁(Intention Lock)这一表级锁。其作用就是为了指明在当前这个事务中,接下来会对这个表中的一些行加什么锁。意向锁也分为两类:

  • 意向共享锁(IS Lock):当事务想要获取一张表中某几行的行级共享锁(S锁)时,MySQL会先自动获取该表的意向共享锁。
  • 意向排他锁(IX Lock):当事务想要获取一张表中某几行的行级排他锁(X锁)时,MySQL会先自动获取该表的意向排他锁。

首先,我们要理解MySQL中的行锁和表锁为什么不能共存,怎样才能共存?我们知道,如果对一张表加上了表级写锁,那么我们就能对该表中的所有行进行修改。如果此时在另外一个事务中,还能对该表中的某几行加行级写锁,这是不被允许的。因为如果同时操作这几行数据,就有可能出现数据一致性问题。
那么,在给表加表级X锁之前,如何知道这个表中某些行是否已经加上了行级锁呢 ,一个简单的解决方法是遍历表中的所有行,一行行去判断。但是这种方法只适用表数据少情况,如果表数据量非常大,遍历一遍全表数据效率很低。
给表加意向锁之后,就能很好的解决这个问题:

  • 在事务获取表中行级S锁之前,必须先获取该表的意向共享锁(IS Lock)或者更强级别的锁
  • 在事务获取表中行级X锁之前,必须先获取该表的意向排他锁(IX Lock)

意向锁与意向锁之间是不冲突的,也就是说互相兼容,但是意向锁和表锁之间会存在不兼容问题,具体的兼容性如下表:

表级锁兼容性 S Lock IS Lock X Lock IX Lock
S Lock 兼容 兼容 不兼容 不兼容
IS Lock 兼容 兼容 不兼容 兼容
X Lock 不兼容 不兼容 不兼容 不兼容
IX Lock 不兼容 兼容 不兼容 兼容

依据上面的兼容性,我们就能保证行锁和表锁能够安全的共存。例如,当一个事务在申请一张表的某几行的行级S锁之前,会先申请该表的意向共享锁(IS Lock)。如果另外一个事务想要申请该表的表级S锁,因为和 IS Lock是兼容的,所以会获取锁成功,两者共存。但是如果想要申请的是该表的表级X锁,就会因为不兼容而被迫阻塞。
也就是说,通过意向锁,能够非常快速的判断表中的行加了什么锁。

自增锁

我们在创建表时,ID这一列通常会声明 AUTO_INCREMENT属性,表示该列是自动递增的。之后我们在进行插入时,可以不用指定ID列的值,MySQL会自动且递增的给该列赋值。
对于MySQL提供的这一功能,我们应该会有如下一些疑问:

  • 自增的值保存在什么地方?
  • 一定能保证连续递增吗,会不会出现不连续情况?
  • 自增是如何实现的,如何保证值不会重复?

自增的值保存在什么地方?我们应该能想到的是,在每次插入数据时,MySQL能够自动进行赋值和自增,缓存在内存中的概率性最大。
的确如此,在 MySQL 7 及之前,自增值保存内存里面,并且没有进行持久化。这也就产生一个问题,当数据库重启后,第一次打开表时,MySQL会找到这个表中自增列的当前最大值maxId,然后将maxId + 1作为这个表的自增值。但是这个自增值不一定和重启之前值一样。
举例来说,假设在重启之前,将这个表中自增列为25的最大一条记录删除了,当我们进行插入时,自增的值并不会回退到25,而是使用26。但是在重启之后,因为查询到自增列的当前最大值maxId = 24,自增值回退到了25
在 MySQL 8 版本后,自增值增加了持久化能力,记录在undo_log里面,重启后,靠undo_log进行恢复,也就不会出现之前的问题了。
自增的值会不会出现不连续的现象?要回答这个问题,首先要知道MySQL是如何给一条未指定自增列的插入SQL自动赋值和递增自增值的。具体来说分为下面几步:

  • 当MySQL发现插入SQL未指定自增列的值时,先从内存获取当前的自增值 inc
  • 修改插入SQL,指定自增列的值为inc
  • 将内存中当前的自增值进行+1操作
  • 继续执行SQL,进行插入

假设在最后一步执行SQL,进行插入时出现了Duplicate key error。那么事务就会进行回滚。该行插入失败。但是我们发现自增列的值inc却已经进行了+1操作。下一次再进行插入时,获取到的自增列的值和数据库中已经存在的自增列的值就会不连续。因为上一次的事务插入的行因为失败回滚了。
为什么在事务回滚时,不一起把自增列的值一起回退了。回退之后不就能保证自增值递增且连续了。我们可以想一下,如果回退了,是不是就会更有可能出现Duplicate key error问题,因为你不能保证自增之后,其他事务是否已经使用了自增之后的值。而且解决这个问题的成本也比较高,所以MySQL中的自增值,只保证了自增,没有保证连续
前面说了这么多,还有最后一个关键问题:自增是如何实现的,如何保证值不会重复?其实在 MySQL InnoDB 存储引擎的内存结构里面,对于每张包含自增列的表,都维护了一个自增长计数器(auto-increment counter),每当进行插入时,就会对这个计数器进行+1操作,而这个操作则是由AUTO-INC锁,也就是自增锁来实现的。
自增锁它是一种特殊的表锁。在对计数器进行+1操作前加上,这样就保证了并发自增的安全性,不会出现重复现象。为了提供插入的性能,自增锁并不会等到事务结束才会释放,而是在插入完成之后就立即释放了
但是自增锁在批量插入时,会存在一定程度的性能问题,所以 MySQL在 5.1.22 版本中引入了一个新策略,新增参数innodb_autoinc_lock_mode来切换自增长的实现,这个参数有 3 个取值:

  • 0:MySQL 5.1.22 版本之前的实现方式,采用AUTO-INC这种表锁的方式来实现自增列的自增长。
  • 1:MySQL 7 及之前的默认值,对于普通insert操作,采用更加轻量级的互斥量(mutex)来实现计数器的自增。而对于insert ... select这种批量插入,还是采用 AUTO-INC锁来实现。
  • 2:MySQL 8 的默认值(在binlog_format使用默认值row时),对于所有的insert操作,都采用更加轻量级的互斥量(mutex)来实现计数器的自增。

最后,对于自增还有一个要说的点是:自增的规则是什么?假设有一张表user, 其中id字段是自增的,一般我们会使用如下方式来进行插入:

insert into user (id, name, age) values ('Dmego', 25);

也就是说如果我们不写id这一列值,MySQL会默认给赋上。除了这样写,其实还有几种方式:

-- 指定 null,表示该列值使用自增值
insert into user (id, name, age) values (null, 'Dmego', 25);
-- 指定 0,表示该列值使用自增值
insert into user (id, name, age) values (0, 'Dmego', 25);

其中指定值 0还有一个特殊的情况需要注意一下,就是不能在启用了NO_AUTO_VALUE_ON_ZEROSQL 模式下使用。具体可以参考MySQL的官方手册说明
id列自增的前提下,手动指定id列的值行吗?是可以的,但是有些区别。假设目前的自增值是inc,手动指定的id列值是Y,有如下规则:

  • 如果Y < inc ,则id还是会使用自增值inc
  • 如果Y >= inc,则 id会使用手动指定的值Y,并且自增值inc会变成Y + 1

行级锁

MyISAM存储引擎只有表锁,是不支持行级锁的,而InnoDB存储引擎不仅支持事务,还支持更高效和细粒度的行级锁。总的来说,共有三种重要的行级锁机制。

行锁(Record Lock)

我们知道,MySQL使用多版本并发控制(MVCC) 解决了不可重复读问题。并且保证了读-写不会产生冲突,也没有使用锁。对于普通的 select ... 操作,使用的就是 MVCC,这种读取也叫做“快照读”或者“一致性读”;也就是说,读取的数据来自于一致性视图,也就是历史数据。
如果查询都是这样,不就不需要行级锁了吗?其实,在很多业务场景下,“快照读”并不能满足需求,并且也不能解决丢失更新幻读等事务类问题。此时就需要读取最新的数据并进行加锁后再处理。这种读取也被称为“锁定读”。
InnoDB存储引擎中,对某一行加的锁被称为行锁(Record Lock),根据访问方式不同,行锁有S锁X锁之分,从具体的查询语句来看:

-- 对查询的记录加 S 锁
select ... lock in share mode
-- 对查询的记录加 X 锁
select ... for update

另外,当我们在执行UPDATEDELETE等操作带WHERE查询条件时,在内部其实也会使用“锁定读”的方式先将需要的行记录查询出来,再加上X锁,最后才进行修改操作。
行锁在需要的时候就会被加上,但是不是语句执行完后就立马释放了,而是等到事务提交之后才会被释放。这也就是两阶段锁协议(2PL)。依据这个特性,我们可以有得出下面一条使用经验:如果事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

间隙锁(Gap Lock)

在介绍间隙锁之前,首先得说一下为什么需要间隙锁?在事务中,我们知道会有幻读这个问题,简单来讲,就是在一个事务中,在不同时间段,对于同一范围内的查询,查询到的数据条数不一样。好像出现“幻觉”一样。而间隙锁正是为了避免幻读问题而出现的。
举个例子,有一条范围查询的SQL语句是这样写:

select * from user where id <= 5 and id >= 10 for update;

也就是使用“锁定读”的方式查询user表中id列在[5, 10]区间内的数据。如果我们只单单锁住id = 5id = 10这两条行记录,是不行的,因为其他事务有可能会插入id = 7这样的数据行,当我们再次使用“锁定读”来查询时,就能查到id = 7的记录。也就是说我们没法对表中并不存在的数据行进行锁定。
间隙锁(Gap Lock)如同其名称一样,它锁定的并不是某行记录,而是行与行之间的某个间隙。能够保证锁定这个间隙之后,其他事务不能在这个间隙里插入任何行记录。


如上示意图中,在id = 5id = 10两行记录之间,存在区间(5, 10),间隙锁正是锁住这个区间。其他事务无法在这个区间内插入任何行,一直到事务结束后,间隙锁被释放。
在上图中,有些人可能会注意到,id列的首尾是两个没有值的列,这其实这是InnoDB存储引擎在数据页中插入的两条伪记录:

  • Infimum记录:表示该页中最小的记录
  • Supremum记录:表示该页中最大的记录

那么这两个记录标出来有什么用呢?其实是想说两种特殊的范围查询情况:

-- 查询 id 值小于 5 的所有记录
select * from user where id < 5;
-- 查询 id 值大于 25 的所有记录
select * from user where id > 25;

在查询id < 5的所有记录时,查询的区间是 (-∞,5),在使用间隙锁锁定这个区间时,锁定的范围就是(infimum, 5);同理,在查询id > 25的所有记录时,锁定的范围是(25, supermum)。标出这两个伪记录,也是为了更方便理解“间隙”这个概念。
间隙锁是否有S锁X锁之分呢?其实是有的,但是并没有什么区别,也没有不兼容的情况。因为我们要理解间隙锁的目的:锁定某个区间,其他事务不能在这个区间插入任何行记录,避免幻读。因此不管你在这个区间加多少个间隙锁,其本质目的是一样的,所以不会出现冲突。

临键锁(Next-key Lock)

临键锁(Next-Key Lock) 其实就是行锁(Record Lock) 和间隙锁(Gap Lock) 的组合。在锁定一个区间的同时,会使用行锁锁定这个区间的右边界行记录,也就是说,Next-key Lock锁定的范围是一个左开右闭区间:(, ]。示意图如下:


在MySQL中,加锁的基本单位就是Next-Key Lock。后续在分析一条SQL执行到底加了什么锁时,再详细说一下Next-Key Lock

插入意向锁(Insert Intention Lock)

在行级锁里面,其实还有一种锁,叫作插入意向锁,从名称上看,应该属于意向锁一类,但它其实是一个行级锁。那么插入意向锁有什么作用呢?也没什么大用,只是因为InnoDB存储引擎规定:在事务阻塞等待期间,必须生成锁结构。所谓的锁结构其实就是在内存中的实体表现。
假设我们要在某个区间要插入一条记录时,发现这个区间上正好被一个Gap Lock锁住。此时这个插入操作就会被阻塞。在阻塞等待时,必须要生成一个锁结构,这个就是插入意向锁
插入意向锁也可以看作是一种特殊的间隙锁,锁住的是一个点。表明有事务想要在该区间的这个位置插入记录,但是被该区间的Gap Lock阻塞了,现在处于等待状态。

隐式锁

在内存中,生成一个锁结构并维护它并不容易,所以减少锁结构的生成,对性能也有提升。“隐式锁”就是这个目的。但是“隐式锁”并不是真正的锁
当我们在进行插入操作前,如果没有其他事务对这条记录或者这条记录所在的间隙加锁,那么就可以不用生成一个锁结构。如果执行过程中,别的事务需要对这条记录或者这条记录所在间隙加锁时,就会先给执行插入操作的事务生成一个锁结构,然后再自己生成一个锁结构,最后进入等待状态。
能够这样做的原因是有事务ID(trx_id)这个隐藏列的存在。如果记录上的trx_id和当前事务ID不一样,那么就说明需要阻塞等待,也就相当于加了一个隐藏的锁。
通过上面的描述,我们可以看出,“隐式锁”其实起到了延迟生成锁结构的好处,在一些场景下,甚至可以避免生成锁结构。

如何查看加了哪些锁

前面长篇大论都只是停留在理论上。在实际操作数据库时,我们该如何查看和分析执行的SQL加了哪些锁呢?下面就通过例子来实践一下。
以下是举例说明所用的表结构和初始化语句,需要注意的是,测试基于的MySQL的版本是8.0.32,如果使用其他版本可能会有些差异。

CREATE TABLE user (
  id int NOT NULL,
  number int NOT NULL,
  age int DEFAULT NULL,
  score int DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY idx_number (number),
  KEY idx_age (age)
) ENGINE=InnoDB;

INSERT INTO user VALUES (1, 201, 19, 80);
INSERT INTO user VALUES (5, 206, 13, 95);
INSERT INTO user VALUES (10, 210, 15, 94);
INSERT INTO user VALUES (15, 214, 17, 98);
INSERT INTO user VALUES (20, 218, 21, 90);

查看表级锁

表锁

使用client连接到MySQL之后,我们可以在命令行执行lock tables user read 语句来给表加一个S锁。然后可以通过下面的操作来查询出user表上是否存在表锁:

-- 查看当前打开的表中,是否存在正在使用的。
show open tables where in_use > 0;

如果上面语句执行有返回记录,例如返回如下信息,就说明user表正在使用,很有可能出现了锁表的情况。

mysql> show open tables where in_use > 0 ;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | user  |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

元数据锁

Session A中,执行如下SQL给表中某些行加上行级X锁

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

mysql> select * from user where id > 5;
+----+--------+------+-------+
| id | number | age  | score |
+----+--------+------+-------+
| 10 |    210 |   15 |    94 |
| 15 |    214 |   17 |    98 |
| 20 |    218 |   21 |    90 |
+----+--------+------+-------+
3 rows in set (0.00 sec)

Session B中,我们使用alter命令试图给user表加一个name字段,但是我们会发现这个命令会被阻塞。

alter table user add column name varchar(32);

新开一个Session C命令行,使用show processlist可以看到类似下面的返回:

mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 5
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 48369
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
...
*************************** 7. row ***************************
     Id: 64
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 2
  State: Waiting for table metadata lock
   Info: alter table user add column name varchar(32)
7 rows in set (0.00 sec)

可以看到,alter语句阻塞的原因是:Waiting for table metadata lock。也就是等待元数据锁(MDL)释放。为什么会阻塞等待,其实我们前面已经说过了,总结来说就是:

  • Session A先开启了一个事务,然后进行select操作,此时MySQL默认给表user加上了元数据S锁,并且事务未提交,元数据S锁还没被释放。
  • Session B中执行alter操作前,会先申请表user的元数据X锁。但是S锁X锁是不兼容的,所以Session B出现了阻塞等待现象。

意向锁

Session A中执行执行如下SQL,给表中id = 10这行记录加上S锁

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

mysql> select * from user where id = 5 lock in share mode;
+----+--------+------+-------+
| id | number | age  | score |
+----+--------+------+-------+
|  5 |    206 |   13 |    95 |
+----+--------+------+-------+
1 row in set (0.00 sec)

Session B中,执行如下命令,查询当前数据库中锁情况:

mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140409843394792:1192:140410012859648
ENGINE_TRANSACTION_ID: 421884820105448
            THREAD_ID: 104
             EVENT_ID: 35
        OBJECT_SCHEMA: test
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140410012859648
            LOCK_TYPE: TABLE
            LOCK_MODE: IS
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
....
2 rows in set (0.00 sec)

可以看到,第一行记录中,OBJECT_NAMEuserLOCK_TYPETABLELOCK_MODEIS,意思就是说,在user这张表上,存在表级锁,具体来说是意向共享锁(IS Lock)。
如果我们把Session A中的查询语句换成for update,也就是给表中id = 10这行记录加上X锁,那么在Session B中查询出来的记录的LOCK_MODE字段值就会变成IX,也就是意向排他锁(IX Lock)。

查看行级锁

行锁

和上一节查询意向锁操作一样,其实在Session B里面,查询出来的记录有2条,前面把第2条省略了,该记录如下:

mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
....(IS 锁记录,省略)
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140409843394792:130:4:3:140410014072352
ENGINE_TRANSACTION_ID: 421884820105448
            THREAD_ID: 104
             EVENT_ID: 35
        OBJECT_SCHEMA: test
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140410014072352
            LOCK_TYPE: RECORD
            LOCK_MODE: S,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5
2 rows in set (0.00 sec)

可以看到,这里的LOCK_TYPE变成了RECORD,也就是行;LOCK_MODES, REC_NOT_GAPLOCK_DATA5,这是什么意思呢?其实这就表明对id = 5这一行记录加了行级S锁。同理,如果Session A的查询换成for update。这里的LOCK_MODE也会变成X,REC_NOT_GAP

间隙锁

我们在Session A中,执行如下SQL,使用“锁定读”的方法查询id(-∞, 1)范围内的数据:

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

mysql> select * from user where id < 1 for update;
Empty set (0.00 sec)

Session B中,执行如下命令,查询当前数据库中锁情况:

mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
...(IX 锁记录,省略)
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140409843394792:130:4:2:140410014072352
ENGINE_TRANSACTION_ID: 115043
            THREAD_ID: 104
             EVENT_ID: 54
        OBJECT_SCHEMA: test
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140410014072352
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1
2 rows in set (0.00 sec)

我们可以看到在第2行记录中,LOCK_MODE值为X,GAPLOCK_DATA值为1。也就是区间(infimum, 1)被加上了间隙锁(Gap Lock)。

临键锁

前面说过,临键锁(Next-Key Lock) 其实就是行锁(Record Lock) 和间隙锁(Gap Lock) 的组合。也就是不仅会锁定一个区间间隙,还会锁定该间隙的右边界值。
Session A中,执行如下SQL来查询id值在(1,5] 范围内的数据:

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

mysql> select * from user where id > 1 and id <= 5 for update;
+----+--------+------+-------+
| id | number | age  | score |
+----+--------+------+-------+
|  5 |    206 |   13 |    95 |
+----+--------+------+-------+
1 row in set (0.00 sec)

Session B中,我们执行如下命令,查询当前数据库中锁情况:

mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
...(IX 锁记录,省略)
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140409843394792:130:4:3:140410014072352
ENGINE_TRANSACTION_ID: 115046
            THREAD_ID: 104
             EVENT_ID: 69
        OBJECT_SCHEMA: test
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140410014072352
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5
2 rows in set (0.00 sec)

我们可以看到第2行记录中,LOCK_MODE值为XLOCK_DATA值为5。也就是区间(1, 5]被加上了临键锁(Next-Key Lock)。
通过上面的实践与分析,对于使用:

select * from performance_schema.data_locks\G;

语句来查看加了什么锁,我们可以根据LOCK_MODE的值进行如下总结如下:

LOCK_MODE IS IX S X S,GAP X,GAP S,REC_NOT_GAP X,REC_NOT_GAP
加锁情况 意向共享锁 意向排他锁 临键锁S锁 临键锁X锁 间隙锁S锁 间隙锁X锁 行级S锁 行锁X锁

参考

  • 《MySQL技术内幕:innodb存储引擎》第2版
  • 《极客时间:MySQL实战45讲》
  • 《MySQL是怎样运行的:从根儿上理解MySQL》
  • MySQL 8.0 Reference Manual

欢迎关注我的公众号“Dmego”,原创技术文章第一时间推送。

热门相关:我的治愈系游戏   重生当学神,又又又考第一了!      横行霸道