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

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

间隙锁(Gap Lock)

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

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

也就是使用“锁定读”的方式查询user表中id列在[5, 10]区间内的数据。如果我们只单单锁住id = 5和 id = 10这两条行记录，是不行的，因为其他事务有可能会插入id = 7这样的数据行，当我们再次使用“锁定读”来查询时，就能查到id = 7的记录。也就是说我们没法对表中并不存在的数据行进行锁定。
间隙锁(Gap Lock)如同其名称一样，它锁定的并不是某行记录，而是行与行之间的某个间隙。能够保证锁定这个间隙之后，其他事务不能在这个间隙里插入任何行记录。
image-20230613214924737
如上示意图中，在id = 5和id = 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锁定的范围是一个左开右闭区间：(, ]。示意图如下：
image-20230613215007242
在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_NAME是user，LOCK_TYPE是TABLE，LOCK_MODE是IS，意思就是说，在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_MODE是S, REC_NOT_GAP，LOCK_DATA是5，这是什么意思呢？其实这就表明对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,GAP，LOCK_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值为X，LOCK_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”，原创技术文章第一时间推送。

posted @ 2023-06-14 10:47  Dmego  阅读(600)  评论(0)  编辑  收藏  举报