MySQL-进阶篇-锁（全局锁、表级锁、行级锁）

1. 锁概述

锁是计算机协调多个进程或线程并发访问某一资源的机制

在数据库中，除了传统的共享计算资源（CPU、RAM、IO）以外，数据也是需要争抢的共享资源

如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素，锁对数据库尤其重要

---

MySQL 中的锁，按照锁的粒度分，分为以下三类

1. 全局锁：锁定数据库中的所有表
2. 表级锁：每次操作锁住整张表
3. 行级锁：每次操作锁住对应的行数据

2. 全局锁

2.1 介绍

全局锁就是对整个数据库实例加锁，加上全局锁后，整个数据库将处于只读状态，后续的 DML 语句、DDL 语句，以及更新操作的事务提交语句都将被阻塞

全局锁的典型的使用场景是做全库的数据备份，需要对所有的表进行锁定，从而获取一致性视图，保证数据的一致性和完整性

2.2 数据备份

接下来我们演示一下数据库的数据备份操作

第一步：获取全局锁

flush tables with read lock;

第二步：使用 mysqldump 工具做数据库的备份（注意，是在 Linux 终端中运行）

mysqldump -u root -p123456 > /tmp/blog.sql

运行指令后会有一个警告，因为我们将密码显式地展现出来了

mysqldump: [Warning] Using a password on the command line interface can be insecure.

第三步：释放全局锁

unlock tables;

2.3 使用全局锁造成的问题

数据库中加全局锁，是一个比较重的操作，存在以下问题：

1. 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停止
2. 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟现象的发生

---

在 InnoDB 引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加全局锁的一致性数据备份

mysqldump --single-transaction -u root -p123456 > /tmp/blog.sql

3. 表级锁

表级锁，每次操作都会锁住整张表，锁定粒度大，发生锁冲突的概率最高，并发度最低，应用在 MyISAM、InnoDB、BDB 等存储引擎中

对于表级锁，主要分为以下三类:

1. 表锁
2. 元数据锁（Meta Data Lock，MDL)
3. 意向锁

3.1 表锁

对于表锁，分为两类：

1. 表共享读锁（Read Lock，读锁）

2. 表独占写锁（Write Lock，写锁）

3.1.1 语法

使用表锁的语法：

• 加锁：lock table 表01 read 表02 write;
• 释放锁：unlock tables;（与 MySQL 服务器断开连接也会释放锁）

3.1.2 读锁

读锁的特点：

• 阻塞其他写操作：如果一个事务已经获得了某个表的读锁，其他任何试图对该表进行写操作的事务将会被阻塞，直到持有读锁的事务释放锁
• 不会阻塞其他读操作：表锁的读锁不会阻塞其他事务对同一表的读操作。这意味着多个事务可以同时获取表锁的读锁，并且它们之间不会相互阻塞

---

读锁的示例图

如果在开启读锁的事务中执行 DML 或 DDL 语句，会报错

lock tables tb_user read;

unlock tables;

3.1.3 写锁

写锁的特点：

• 排他性：写锁是排他的，这意味着在同一时刻，只有一个事务可以获得给定资源上的写锁。其他任何事务或会话都不能同时对该资源进行写操作
• 阻塞其他写操作：如果一个事务已经获得了某个资源上的写锁，其他任何试图对该资源进行写操作的事务将会被阻塞，直到持有写锁的事务释放锁
• 阻塞读操作：同样，如果一个事务获得了写锁，其他试图读取该资源的操作（在某些事务隔离级别下）也会被阻塞，直到写锁被释放

---

写锁的示例图

3.1.4 读锁和写锁的区别

读锁不会阻塞其他客户端的读，但是会阻塞其它客户端的写

写锁既会阻塞其他客户端的读，也会阻塞其他客户端的写

3.2 元数据锁（Meta Data Lock，MDL）

元数据锁的加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上

元数据锁的主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作（为了避免 DML 语句和 DDL 语句之间的冲突，保证读写操作的正确性）

更简单的理解方式就是：在对表进行增删查改操作的时候，不能更改表的结构

那什么是元数据呢，大家可以简单地理解为表结构

---

MySQL 5.5 引入了元数据锁，当对一张表进行增删改查的时候，加元数据读锁（共享锁），当对表结构进行变更操作的时候，加元数据写锁（排它锁）

---

如何查看元数据锁呢，可以运行以下指令

select object_type, object_schema, object_name, lock_type, lock_duration, lock_status
from performance_schema.metadata_locks;

如果出现以下错误，说明当前用户没有访问 performance_schema 数据库的权限

SELECT command denied to user ‘wuyanzu’@‘127.0.0.1’ for table ‘metadata_locks’

3.3 意向锁

3.3.1 案例引入

我们先来看以下场景

现在有一张 employee 表，线程 A 想更新 id 为 3 的记录，在默认的 MySQL 隔离级别下，执行 update 语句，而且是根据主键更新，会自动对 id 为 3 的记录加上行锁

此时又来了一个线程 B，线程 B 想要获取表锁，大家想一下，线程 B 能直接拿到表锁吗？实际上，线程 B 不能直接拿到表锁，因为表锁与行锁之间会有冲突

线程 B 在获取表锁的时候，需要先根据 employee 表中是否有行锁以及行锁的类型来判断能不能加表锁，要判断是否有行锁，需要一条一条数据扫描下来，看看有没有行锁以及有行锁的话是什么类型的行锁，性能比较低

为了避免执行 DML 语句时，加的行锁与加的表锁冲突，InnoDB 引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少取得表锁之前的检查

我们来看一下，有了意向锁之后，加锁过程是怎样的

线程 A 在执行 update 语句的时候，会先针对这一行加上行锁，接着再为表加上一个意向锁，线程 B 要想获取表锁的话，只需要检查表中意向锁的情况，通过意向锁的情况来判定是否能够获取表锁，如果当前表的意向锁是与线程 B 要获取的表锁是兼容的话，直接获取表锁，如果当前表的意向锁是与线程 B 要获取的表锁不兼容，线程 B 就会一直处于阻塞状态，直到线程 A 释放行锁和意向锁

大家会发现，当引入了意向锁之后，线程 B 想要获取表锁的话，就不用逐行扫描数据，可以直接根据表的意向锁的情况来判断能否获取表锁

3.3.2 意向锁的分类

意向锁分为两种：

1.意向共享锁（IS，Intention Shared Lock）：由语句 select … lock in share mode 添加

2.意向排他锁（IX，Intention Exclusive Lock）：由 insert、update、delete、select … for update 添加

---

意向锁与表锁的互斥情况：

• 意向共享锁：与表锁的共享锁兼容，与表锁的排它锁互斥
• 意向排他锁：与表锁的共享锁及表锁的排它锁都互斥

但意向锁之间不会互斥，怎么理解呢

理解意向锁之间不互斥的关键在于了解不同类型的意向锁以及它们的作用：

1. 意向共享锁 (IS): 当事务想要获取一个表上的共享锁（读取数据）时，它会先申请一个意向共享锁。这个锁表示事务有意向在表的某一行或某些行上获取共享锁
2. 意向排他锁 (IX): 当事务想要获取一个表上的排他锁（写入数据）时，它会先申请一个意向排他锁。这个锁表示事务有意向在表的某一行或某些行上获取排他锁

意向锁的特点：

• 非独占性：多个事务可以同时持有同一表上的 IS 锁，因为这些事务都只是表明了读取某些行的意图，并没有实际锁定任何特定的行
• 兼容性：IS 锁和 IX 锁之间是不互斥的。这意味着如果一个事务已经持有了 IS 锁，其他事务仍然可以获取 IX 锁，反之亦然
• 辅助作用：意向锁本身并不直接锁定任何行，而是作为辅助锁来帮助系统决定是否授予更具体的锁类型（如 S 锁或 X 锁）

例子说明：

• 假设事务 T1 获取了一个表上的 IS 锁，这表示 T1 有意向读取该表的某些行
• 另一个事务 T2 也可以获取该表上的 IX 锁，表示 T2 有意向更新该表的某些行
• 在这种情况下，T1 和 T2 都可以在不冲突的情况下进行操作，只要他们不尝试在同一行上执行互斥的操作

总结：

意向锁之间不互斥是因为它们仅仅表达了事务对于数据操作的“意向”，而不是直接对数据进行锁定。意向锁的存在是为了后续更细粒度的锁请求做准备，并且它们的设计目标是为了减少锁之间的等待，提高并发处理能力

---

可以通过以下 SQL 语句查看意向锁和行锁的加锁情况

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;

4. 行级锁

4.1 介绍

行级锁，每次操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最低，并发度最高，应用在 InnoDB 存储引擎中

InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁，对于行级锁，主要分为三类

第一类：行锁（Record Lock），锁定单个行记录的锁，防止其他事务对此行进行 update 和 delete 操作，在 RC、RR 隔离级别下都支持

第二类：间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保案引记录间隙不变，防止其他事务在这个间隙进行 insert 操作，产生幻读，在 RR 隔离级别下都支持

第三类：临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙 Gap，在 RR 隔离级别下支持

4.2 行锁

InnoDB 实现了以下两种类型的行锁：

1. 共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁
2. 排他锁(X)：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁

---

共享锁和排他锁之间的兼容情况

---

在进行增删查改语句的时候，所加的行锁类型情况如下

---

默认情况下，InnoDB 引擎在 REPEATABLE READ 事务隔离级别运行，InnoDB 使用 Next-Key Locks 锁（临键锁）进行搜索和索引扫描，以防止幻读

• 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁
• lnnoDB 的行锁是针对于索引加的锁，不通过索引条件检索数据，那么 InnoDB 将对表中的所有记录加锁，此时就会升级为表锁

4.2.1 测试行锁之间的互斥性

接下来我们来演示一下行锁，用两个客户端分别连接 MySQL 服务器，用 student 表来测试

student 表的数据如下，其中 id 为主键，name 字段和 no 字段没有建立索引

在第一个连接中开启一个新事务

begin;

接着运行以下 SQL 语句

select *
from student
where id = 1;

当执行以上 SQL 语句后，到底有没有对 id 为 1 的这一行数据加锁呢

我们在第二个连接中运行以下 SQL 语句查看表的加锁情况

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;

可以看到 data_locks 表中没有任何数据，说明简单的 select 语句不会加上行锁

---

我们在第一个连接中执行以下 SQL 语句

select *
from student
where id = 1 lock in share mode;

在第二个连接中查看是否加了锁

可以看到 data_locks 表中有两条数据，第一条数据的 lock_type 字段为 TABLE，表明加的是表锁， lock_mode 字段是 IS，表明加的是意向共享锁

我们重点关注第二条数据，第二条数据的 lock_type 字段为 RECORD，表明加的是行锁，lock_mode 字段是 S 和 REC_NOT_GAP，S 代表共享锁，REC_NOT_GAP代表没有间隙

---

那共享锁和共享锁之间能不能兼容呢，可以

我们在第二个连接中开启一个新事务

begin;

执行同样的 SQL 语句

select *
from student
where id = 1 lock in share mode;

接着在第二个连接中再次查看 student 表的加锁情况

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;

可以看到，此时 student 表中有四条记录，说明两个共享锁之间是兼容的

---

我们提交第二个连接的事务后，再次在第二个连接中开启一个新事务

接着在第二个连接中运行以下 SQL 语句

update student
set name = '李四'
where id = 3;

可以发现，执行 SQL 语句时并没有阻塞，SQL 语句也执行成功了

我们在第二个连接中运行以下 SQL 语句（操作 id = 1 的数据）

update student
set name = '王五'
where id = 1;

可以发现，光标一直在闪动，说明处于阻塞状态，因为第一个连接已经对 id 为 1 的数据添加了共享锁，第二个连接想要对 id 为 1 的数据添加排他锁，而共享锁和排他锁之间是有冲突的，所以第二个连接需要等待第一个连接释放共享锁后才能获取到排他锁

由于等待获取排他锁的时间过长，第二个连接出现了以下错误（如果光标一直在闪烁，可以通过 CTRL + C 快捷键中断 SQL 语句操作）

---

我们提交第一个连接中的事务后，在第二个连接中再次查看 student 表中的加锁情况

可以发现，第一个连接提交事务后，第二个连接获取到了 id 为 3 这一行数据的排他锁

在第二个连接中提交事务，再次查看 student 表中的加锁情况

发现锁已经释放了，但 id 为 1 的这一行数据的 name 字段并没有被修改成王五

4.2.2 测试行锁升级为表锁的情况

我们分别在第一个连接和第二个连接中开启事务，接着在第一个连接中执行以下 SQL 语句

update student
set name = 'Lei'
where name = '李四';

按理说，执行 SQL 语句后会对 id 为 3 的这一行数据添加一个行锁

我们在第二个连接中执行以下 SQL 语句

update student
set name = '吴彦祖'
where id = 4;

发现 SQL 语句在执行时被阻塞了，按理说，第一个连接锁的是 id 为 3 的这一行数据，为什么会影响到第二个连接修改 id 为 4 的数据呢

因为第一个连接更新数据的时候，是根据 name 字段进行更新的，而 name 字段没有建立索引，所以会锁住表中的所有记录，导致行锁升级为表锁

我们提交第一个连接的事务后，第二个连接的更新操作就立刻完成了

---

接下来我们提交第二个连接的事务，然后为 name 字段建立一个索引

create index index_student_name on student (name);

再次重复以上测试过程，发现第一个连接更新 id 为 3 的这一行数据时不会再影响到第二个连接修改 id 为 4 的数据

4.3 间隙锁&临键锁

默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB 使用 Next-Key Locks 锁进行搜索和索引扫描，以防止幻读

1. 间隙锁（Gap Locks）：用于防止幻读，对表中的一个范围进行锁定
2. 临键锁（Next-Key Locks）：是间隙锁和行锁的组合，用于防止幻读、修改或删除范围内的行

---

• 间隙锁的唯一目的就是防止其他事务插入间隙
• 间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁

针对索引上的等值查询、唯一索引上的范围查询以及普通索引上的范围查询的三种情况，间隙锁和临键锁的具体情况有所不同：

1. 索引上的等值查询（唯一索引）：给不存在的记录加锁时，优化为间隙锁
2. 索引上的等值查询（普通索引）：向右遍历至最后一个值不满足查询需求时，临键锁（Next-Key Locks）退化为间隙锁
3. 索引上的范围查询（唯一索引）：遍历到不满足条件的第一个值为止

需要注意的是，间隙锁和临键锁的产生还取决于事务的隔离级别，在某些隔离级别下，如 READ COMMITTED，间隙锁和临键锁可能不会自动产生

---

在演示间隙锁和临键锁之前，我们先准备一个新的 teacher 表，表结构和数据如下

/*Navicat Premium Data TransferSource Server         : Source Server Type    : MySQLSource Server Version : 80037 (8.0.37-0ubuntu0.22.04.3)Source Host           : Source Schema         : blogTarget Server Type    : MySQLTarget Server Version : 80037 (8.0.37-0ubuntu0.22.04.3)File Encoding         : 65001Date: 30/08/2024 19:01:15
*/SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`age` int NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE,INDEX `teacher_id_index`(`id` ASC) USING BTREE,INDEX `index_teacher_age`(`age` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 26 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '张三', 1);
INSERT INTO `teacher` VALUES (3, '李四', 3);
INSERT INTO `teacher` VALUES (7, 'Amy', 7);
INSERT INTO `teacher` VALUES (8, '王五', 8);
INSERT INTO `teacher` VALUES (11, '赵六', 11);
INSERT INTO `teacher` VALUES (19, '钱七', 19);
INSERT INTO `teacher` VALUES (25, '老八', 25);SET FOREIGN_KEY_CHECKS = 1;

teacher 表的数据如下

---

接下来我们开始测试

在第一个连接中开启事务，然后在第一个连接中运行以下 SQL 语句

update teacher
set name = 'Jane Doe'
where id = 5;

由于 id 为 5 的这一行数据并不存在，InnoDB 会怎样加锁呢，是把 id 为 5 的这一行数据锁住了吗，实际上并不是

InnoDB 会对 3 和 8 之间的间隙加上一个间隙锁，具体的范围是(3, 8)，也就是左开右开的区间，不含包 3 和 8

在第二个连接中查看 teacher 表的加锁情况

可以看到，teacher 表有两把锁，一把是表锁，一把是间隙锁，间隙锁使用的索引为主键

既然锁的是 id 范围在 (3, 8) 之间的数据，那我插入一条 id 为 7 的数据会怎么样呢

我们在第二个连接中开启一个事务，接着在第二个连接中执行以下 SQL 语句

insert into teacher
values (7, 'Amy', 30);

可以发现 SQL 处于阻塞状态，因为第一个连接的事务给 id 范围在 (3, 8) 之间的数据加了一个间隙锁，在第一个连接的事务提交之前，第二个连接的插入操作都会被阻塞

---

当我们进行等值查询的时候，如果不是唯一索引，会发生什么现象呢

我们在加行锁的时候，是针对索引加的锁，而索引是 B+Tree 数据结构，B+Tree 的叶子结点形成的是一个双向链表

以上图为例，假如我们要根据二级索引的值是否等于 18 来查询，并且给二级索引 等于 18 这行数据添加一个共享锁，此时是仅仅把二级索引等于 18 的这条记录给锁住就完事了吗？并不是的，因为当前的二级索引没有唯一性约束，那以后是不是就有可能在 18 的左边插入一条二级索引为 18 的记录，也有可能在 18 的右边插入一条二级索引为 18 的记录

所以，InnoDB 会对 (16, 18) 之间的间隙和 (18, 29) 之间的间隙加锁

---

接下来我们用 teacher 表来演示一下

首先为 age 字段建立一个普通的非唯一索引

create index index_teacher_age on teacher (age);

我们在第一个连接中开启事务，接着运行以下 SQL 语句

select *
from teacher
where age = 3 lock in share mode;

接着在第二个连接中查询 teacher 表的加锁情况

可以发现有四条记录，第一行是表的意向锁，我们重点关注后面三行

第二行的 lock_data 为 3, 3，lock_mode 为 S，是一个临键锁，表示要把 age 为 3 的这条记录以及 age 在 (1,3) 区间的间隙锁住（第一个 3 是 二级索引，也就是 age 字段，第二个 3 是主键索引，也就是 id 字段）

第三行的 lock_data 为 3，且索引类型为主键，表示锁住了 id 为 3 对应的记录

第四行的 lock_data 为 7, 7，lock_mode 含有 GAP，是一个间隙，表示要把 age 在 (3, 7) 区间的间隙锁住

最后提交第一个连接中的事务

---

我们在第一个连接中开启事务，然后在第一个连接中执行以下 SQL 语句

select *
from teacher
where id >= 19 lock in share mode

接着在第二个连接中查询 teacher 表的加锁情况

可以看到有 4 行记录，第一行是表的意向锁，我们重点关注后面三行

第二行表明对 id 为 19 的这一行记录加了一个行锁

第三行表示加了一个临键锁，lock_data 的 supremum pseudo-read 属性可以理解为正无穷大，锁的是 (25, +∞) 之间的间隙

第四行表示加了一个临键锁，锁的是 (19, 25) 之间的间隙和 id = 25 对应的记录

---

间隙锁锁的是间隙，不包含对应的数据记录，而临键锁既会锁住数据记录，也会锁定数据记录之前（或之后）的间隙

其实间隙锁和临键锁不用刻意记忆，关键是搞清楚 InnoDB 引擎为什么要加上间隙锁和临键锁