MySQL 有哪些锁？

全局锁

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

全局锁的使用场景是在对 DB 做全量备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

基本语法：

flush tables with read lock; -- 加锁，确保当前数据库是要备份的数据库

unlock tables; -- 解锁

一般使用 mysqldump 工具做备份：

mysqldump -h <主机地址> -u <用户名> -p <待备份数据库名称> > <路径>

加全局锁是一个很重的操作：

主库加全局锁，整个加锁过程都不允许 insert、update、delete，业务基本停摆
从库加全局锁，整个加锁过程都不允许同步主库的 binlog，会造成主从延迟

有没有什么方式避免呢？

mysqldump 提供了一个 --single-transcation 选项，用于 不加锁 获取数据一致性备份，

--single-transcation 选项的原理是基于 MVCC 的，在整个备份期间使用同一个 Readview，保证数据一致性

表级锁

表锁 Table Lock

表锁会锁住一张特定的表，有两种类型：

表 共享锁
表 独占锁

可以简单将共享锁看作读锁，独占锁看作写锁，二者之间的互斥性与读写锁是一致的

由于表锁锁定粒度太大，一般不使用

元数据锁 MDL

元数据锁 MDL 主要用于锁住一张表的元数据，保证读写的正确性

MDL 由 MySQL 自己控制，在访问一张表的时候会自动锁上

MDL 也有两种类型：

当我们对一张表进行 CRUD 时，会加上 MDL 读锁
当我们尝试修改一张表的元数据时（即 alter 操作），会加上 MDL 写锁

二者之间的互斥性与读写锁一致

注意，在同一个事务内，即使加了 MDL 读锁，也不会影响 alter 操作：

意向锁

表锁与行锁之间也是满足：读读共享、读写互斥、写写互斥的

假设表内有一个 X Record Lock，那么就不能加上表共享锁、表独占锁

因此，在加表锁之前，需要检查表内有没有行锁

但是表内可能有很多行数据，如果每一行都去判断，会浪费很多时间

因此出现了意向锁：

意向共享锁：说明行内存在共享锁
意向独占锁：说明行内存在独占锁

在给某一行加共享锁之前，先加上意向共享锁；在给某一行加独占锁之前，先加上意向独占锁；

AUTO-INC 锁

在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

与其它锁不同，AUTO-INC 锁在数据 插入完毕后就会立即释放，而不是事务结束后

行级锁

Record Lock

实验：select、update、delete、insert 的行锁、意向锁情况

可以使用：

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

来查看加锁情况

先来看看 select：

普通的 select 是快照读，不加锁：

select ... lock in share mode 会对当前行加上 行锁（共享锁 S），并在加行锁前，给表加上 意向共享锁（IS）

加这个锁就好像告诉其它事务：“我要读取这行的数据了，为了保证数据一致性，你们只可以读，不要修改哈”

S, REC_NOT_GAP 的含义就是 Record Lock

select ... for update 会对当前行加上 行锁（独占锁 X），并在加行锁前，给表加上 意向独占锁（IX）

加这个锁就好像告诉其它事务：“我读取这行的数据是为了后续的更新，也就是要写数据，为了保证数据一致性，你们既不要读，也不要修改”

再来看看 update：

update 是当前读，会为当前行加上 行锁（独占锁 X），并在加行锁前，给表加上 意向独占锁（IX）

再来看看 delete：

delete 与 update 一样，也是当前读，会为当前行加上 行锁（独占锁 X），并在加行锁前，给表加上 意向独占锁（IX）

这里就不再演示了

最后来看看 insert：

insert 与 delete、update 一样，也是当前读，会为当前行加上 行锁（独占锁 X），并在加行锁前，给表加上 意向独占锁（IX）

X 锁呢？

这里就涉及到之前讲的 AUTO-INC 锁了

AUTO-INC 锁是特殊的表锁机制，锁不是在一个事务 提交后才释放，而是在 执行完插入语句后就会立即释放。

Gap Lock

间隙锁会将一个「间隙」锁住，避免其它事务在这个「间隙」插入数据，产生幻读现象

间隙锁之间是可以共存的，即两个事务可以同时持有包含相同范围的间隙锁，并不存在互斥关系

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from Inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

间隙锁仅存在于 RR 隔离级别

Next-Key Lock

间隙锁的范围是「左开右开」区间，而临键锁的范围是「左开右闭」区间

假设有一个临键锁的范围是 (114, 514]，那么除了不允许其它事务在这个「间隙」插入数据外，还不允许修改 514 这一行的数据

可以将 Next-Key Lock 看作 Gap Lock + Record Lock

MySQL 是怎么加锁的？

MySQL 加锁的原则：避免幻读现象的发生，保证一个 SQL 语句在一个事务期间的执行结果不受其它事务干扰

因此，在分析如何加锁时，我们只需要看看加了锁以后，会不会有幻读现象发生即可

下面的实验基于 MySQL8.1，RR 隔离级别

create table user
(
    id       int auto_increment
        primary key,
    name     varchar(10)  not null,
    sex      char         null,
    phoneNum varchar(20)  not null,
    email    varchar(128) null,
    age      int          null,
    constraint idx_email
        unique (email),
    constraint idx_phone
        unique (phoneNum)
);

create index idx_age
    on user (age);

create index idx_name
    on user (name);

唯一索引等值查询

记录存在

可以看到，只加了 Record Lock

我们来分析一下：

根据加锁原则：避免幻读发生

对于当前这个 SQL 语句，只要不允许其它事务修改（或删除）id = 1 这一行的数据，就完全可以 避免幻读 发生了，因此，只需要加 Record Lock

再来看看以二级唯一索引作为条件的情况：

可以看到，除了对二级索引加了 Record Lock 以外，还对主键索引加了 Record Lock

分析一下：

行锁是针对索引加的锁，这里为了 避免幻读，在对 idx_phone 加了 Record Lock 后，还要对查询到的记录的主键索引项加 Record Lock，避免其它事务通过 where id = 1 修改这一行的数据造成幻读

记录不存在

分析一下：

由于 id = 2 这一行并不存在，为了 避免幻读，只能锁住 (1,5) 之间的间隙，避免其它事务插入 id = 2、3、4 的数据行

因此，这里 InnoDB 加了一个 间隙锁

再来看看以二级唯一索引作为条件的情况：

由于 email = '15' 这一行并不存在，为了 避免幻读，只能锁住 ('10','20') 之间的间隙

为什么这次没有对主键索引加行锁？

因为只需要对 idx_email 加锁，其它事务就无法插入 email ('10','20') 这个间隙，可以避免幻读

唯一索引范围查询

查询 id > 25 的数据，为了 避免幻读，需要锁住 (25, 30]，(30, +∞] 的数据，避免其它事务插入或者删除这个区间的数据，造成幻读

因此，这里 InnoDB 加了两个 临键锁

如果是 id >= 25，还要额外加一个 记录锁，锁住 id = 25 这一行的数据，避免其它事务修改、删除，造成幻读：

再来看看以二级唯一索引作为范围条件的情况：

除了通过三个临键锁锁住 ('50', +∞] 的间隙以外，还有两个对 主键索引 加的 记录锁，避免其它事务 通过 id 修改 id = 25、30 的数据，造成幻读

非唯一索引等值查询

记录存在

可以发现，加了三个行锁：

临键锁 (10, 13]
间隙锁 (13, 16)
记录锁 id = 13

为了 避免幻读，需要保证其它事务：

不能删除 age = 13 的数据：记录锁 保证
不能插入 age = 13 的数据：临键锁 + 间隙锁保证

为什么有了临键锁还要创建间隙锁？

临键锁事实上无法完全保证 age = 13 的数据不被插入，这点可以通过 lock_data 中的 13, 5 来判断：

如果插入的 age 对应的 id < 5，不允许插入

如果插入的 age 对应的 id > 5，允许插入

因此，为了完全保证 age = 13 的数据不被插入，需要加一个间隙锁 (13, 16)

2024.3.3 更新

对主键索引加锁的原因是：SQL 语句没有使用到覆盖索引，也就是说，还 要访问主键索引 来获取 name、sex 等字段的值

为了防止幻读，就需要对主键索引加锁

如果修改成 select id, age from user where age = 13 lock in share mode;，就不会对主键索引加锁

但是，如果修改成 select id, age from user where age = 13 for update;，仍然会 对主键索引加锁

这是因为 MySQL 会认为要修改这一行的数据，会顺便给主键索引上满足条件的行加上锁

记录不存在

为了 避免幻读，加上 (10, 13) 的间隙锁，避免其它事务插入 age = 11、12 的数据

非唯一索引范围查询

加了三个临键锁和两个记录锁，基于上面的分析，这个结果也就不意外了，故不再分析

行锁升级

行锁是针对索引加的锁，如果不通过索引字段作为查询依据，那么 InnoDB 会为每一行加上行锁（Next-Key Lock），就好像整张表都被锁住了一样，可以理解为 行锁升级成表锁

例如，对于 User 表，只有 id 有主键索引，其它字段均没有索引

在以 id 为检索条件下：

只对 id = 1 这一行加了 Record Lock

在以 sex 为检索条件下：

对 每一行 都加了 S 型的 Next-Key Lock，可以看成行锁升级成表锁

这种情况一定要避免，防止 update、insert、delete 语句被阻塞，导致业务停摆

如何避免？

首先我们在执行当前读时，加上 where 条件，并且保证走了索引（用 explain 查看）

如果实在怕忘记，可以将 MySQL 里的 sql_safe_updates 参数设置为 1，开启安全更新模式。

If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause. (Specifically, UPDATE statements must have a WHERE clause that uses a key or a LIMIT clause, or both. DELETE statements must have both.) This makes it possible to catch UPDATE or DELETE statements where keys are not used properly and that would probably change or delete a large number of rows. The default value is 0.

翻译过来：

对于 update：

使用 where，并且 where 条件中必须有索引列；
使用 limit；
同时使用 where 和 limit，此时 where 条件中可以没有索引列；

对于 delete：同时使用 where 和 limit，此时 where 条件中可以没有索引列；

2024.3.3 更新：

delete 的时候，尽量加上 limit，不仅可以确保数据的安全（避免删除过多的数据），还可以 减少加锁的范围，提高并发度

MVCC + Gap Lock 是否完全解决幻读问题？

前面在「事务篇」提到过：MVCC 解决了快照读的幻读问题

那么幻读问题是否完全解决了呢？

很遗憾，并没有

经过上文的分析，我们发现 Record Lock + Gap Lock + Next-Key Lock 可以 避免绝大多数幻读 现象的发生

但还有一种幻读，在 RR 隔离级别下，还是无法避免：

这种幻读的出现场景在于：快照读与当前读 混合使用，看起来二者的查询结果不一致

那如何避免呢？

避免混合使用快照读与当前读

如果能保证一个事务中：

只使用快照读，那么 MVCC 完全可以解决幻读
只使用当前读，那么 Record Lock + Gap Lock + Next-Key Lock 也完全可以避免幻读

在开启事务后，迅速加锁

如果确实要混合使用，那么可以在事务开始后，立即加锁：

避免其它事务插入 id > 30 的数据，造成幻读

使用串行化隔离级别

实在不行，就使用串行化隔离级别吧，也可以保证避免幻读

这是 MySQL 官方的文档，提到了：串行化可以阻止幻读现象的发生，而 RR、RC、RU 都允许幻读现象的发生

死锁

insert 是怎么加行锁的？

首先要了解一个概念：MySQL 加锁时，是 先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁

insert 是当前读，但是与 update、delete 不同，insert 正常执行时，不会生成锁结构，而是加一个 隐式锁

什么是隐式锁？

当事务需要加锁时，如果这个锁 不可能发生冲突，InnoDB 会 跳过加锁环节，这种机制称为隐式锁。隐式锁是 InnoDB 实现的一种延迟加锁机制，其特点是只有在可能发生冲突时才加锁，从而减少了锁的数量，提高了系统整体性能。

那什么时候，insert 会将隐式锁转化为显式锁？

如果 insert 的位置 有间隙锁
如果 insert 冲突

来看第一种情况：

当 insert 的位置 有间隙锁 时，隐式锁转换为显式锁，即 INSERT_INTENTION

An Insert intention lock is a type of gap lock set by Insert operations prior to row Insertion. This lock signals the intent to Insert in such a way that multiple transactions Inserting into the same index gap need not wait for each other if they are not Inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to Insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with Insert intention locks prior to obtaining the exclusive lock on the Inserted row, but do not block each other because the rows are nonconflicting.

insert 的显式锁是一种特殊的间隙锁，它锁住的是一个点

insert 的显式锁的应用在 并发场景 下，如果有多个并发的 insert 操作，即使它们插入的是同一个「间隙」，只要 插入的点不同，无需互相等待，保证了并发性能

来看第二种情况：

如果 主键索引冲突，插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加 S 型记录锁

如果 二级唯一索引冲突，插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加 S 型 Next-Key 锁

为什么插入失败要加锁？

感觉主要原因还是 防止「幻读」

插入失败，给主键 id 对应行加上 S Record Lock，可以 避免其它事务删除这一行数据 ，从而避免幻读（这里的幻读指：第一次插入失败，第二次却插入成功）

死锁的产生

来看一个产生死锁的例子：

有一个 t_student 表：

CREATE TABLE `t_student` (
  `id` int NOT NULL,
  `no` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `score` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

表内数据如下：

INSERT INTO t_student (id, no, name, age, score) VALUES
(15, 'S0001', 'Bob', 25, 24),
(18, 'S0002', 'Alice', 24, 77),
(20, 'S0003', 'Jim', 24, 5),
(30, 'S0004', 'Eric', 23, 91),
(37, 'S0005', 'Tom', 22, 22),
(49, 'S0006', 'Tom', 25, 83),
(50, 'S0007', 'Rose', 23, 89);

按照如下顺序并发执行两个事务：

图片来自小林 coding

time 1 阶段

可以看到，事务 A 上了一个间隙锁 (20, 30)

time 2 阶段

可以看到，事务 B 也上了一个间隙锁 (20, 30)

注意：间隙锁之间互相可以共存

time 3 阶段

事务 A 的 insert 操作被阻塞了，因为 insert 的间隙锁与事务 B 的间隙锁冲突

time 4 阶段

出现了死锁：事务 A 在等待事务 B 释放间隙锁，事务 B 也在等待事务 A 释放间隙锁，二者循环等待，产生死锁

如何避免死锁

那怎么避免死锁呢？

预防死锁产生

经过上面的示例，可以总结 MySQL 出现死锁的场景：

两个事务都加了间隙锁
两个事务分别向对方的间隙插入数据，产生 insert_intention 锁
由于间隙锁与 insert_intention 锁冲突，二者循环等待，产生死锁

因此，可以在编写 SQL 语句的时候想想有没有死锁产生的风险，特别是 加了间隙锁后，尝试 insert 数据，这个操作并发执行有可能产生死锁

提前释放锁

当死锁产生时，事务可以释放自己手中的资源，以达到解除死锁

例如，当检测到死锁产生，可以回滚事务（此时会释放所有持有的锁），然后重新执行事务

MySQL 有一个选项 innodb_deadlock_detect，开启后就会检测死锁，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行

例如对于上面的示例，开启这个选项后，执行结果如下：

这样就避免了死锁，该选项默认开启：

mysql> show variables like 'innodb_deadlock_detect';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+
1 row in set (0.01 sec)

MySQL 有哪些锁？

简要介绍 MySQL 存在的锁，以及锁机制

Table of Contents

全局锁

表级锁

表锁 Table Lock

元数据锁 MDL

意向锁

AUTO-INC 锁

行级锁

Record Lock

实验：select、update、delete、insert 的行锁、意向锁情况

Gap Lock

Next-Key Lock

MySQL 是怎么加锁的？

唯一索引等值查询

记录存在

记录不存在

唯一索引范围查询

非唯一索引等值查询

记录存在

记录不存在

非唯一索引范围查询

行锁升级

如何避免？

MVCC + Gap Lock 是否完全解决幻读问题？

死锁

insert 是怎么加行锁的？

死锁的产生

如何避免死锁