20241231面试鸭特训营第8天

原创已于 2025-01-01 00:31:19 修改 · 801 阅读

23 ·

CC 4.0 BY-SA版权

文章标签：

#面试 #职场和发展

于 2024-12-31 18:07:26 首次发布

面试鸭特训营专栏收录该内容

30 篇文章

订阅专栏

更多特训营笔记详见个人主页【面试鸭特训营】专栏

241231

1. MySQL 中有哪些锁类型？

共享锁和排他锁（重点）

-- 共享锁（重点）
  -- 允许多个事务并发读取同一资源，但是不允许修改
  -- 只有在释放共享锁后，其他事物才能获得排他锁
-- 排他锁（重点）
  -- 只允许一个事务对资源进行读写
  -- 其他事务在获得排他锁之前无法访问该资源

定义
- 共享锁 (shared locks) ，也叫 S 锁，事务在读取记录的时候获取 S 锁，允许多个事务同时获取 S 锁，且相互之间不会冲突
- 排他锁 (exclusive) ，也叫 X 锁和独占锁，事务在修改记录的时候获取 X 锁，同一时刻只允许一个事务获取 X 锁，其他事务需要阻塞等待
冲突性
- 锁性质共享锁S（读锁）排他锁X（写锁）
  共享锁S（读锁） 不冲突冲突
  排他锁X（写锁） 冲突冲突
- 可以多个同时读，但是只要有一个在写，其他的就不能写也不能读
- 举例说明
  - A 和 B 两个人共同创作一本小说，有 C D E 三个读者
  - A 在写小说第 10086 章的时候，B不能写第 10086 章，C D E 也读不到第 10086 章的内容
  - 但是小说的第 10001 章早都已经写好了，C D E 可以同时阅读第 10001 章的内容

锁性质	共享锁S（读锁）	排他锁X（写锁）
共享锁S（读锁）	不冲突	冲突
排他锁X（写锁）	冲突	冲突

全局锁

作用

保证在进行全库逻辑备份时，不会因为数据的更新或表结构更新，导致拷贝版和原版数据不一致

-- 使用全局锁
flush tables with read lock
-- 释放全局锁
unlock tables
-- 会话断开时全局锁也会被自动释放

特点

开启全局锁后，整个数据库都处于只读状态
- 对数据的增删改操作，比如 insert、delete、update 等语句，会阻塞
- 对表结构的更改操作，比如 alter table、drop table 等语句，会阻塞
缺点
- 如果数据库中数据信息较多，备份会花比较长的时间
- 备份期间，由于加了全局锁，整个数据库会进入只读状态，不允许修改
- 会导致业务的正常开展

举例说明应用场景

现需要对某个电商平台的数据库进行全库数据备份，如果不加全局锁的话可能导致以下情况的发生
时间顺序：备份用户表数据 → 用户下单 → 备份商品表数据
若没有对整个数据库加锁的话，会导致【用户表中用户余额未扣除】，但【商品表中商品余量被扣除】

表级锁

对整个表加锁，其他事务无法对该表进行任何读写操作
适用于需要保证完整性的小型表

元数据锁

-- 用于保护数据库对象（如表和索引）的元数据，防止在进行 DDL 操作时其他事物对这些对象进行修改

属于表级锁，用于保护数据表中的元数据信息的一致性
元数据锁又分为读锁和写锁
- 读锁
  - 当一个事务需要读取表中的元数据时，会获取读锁
  - 多个事务可以同时拥有读锁，且不会相互阻塞
- 写锁
  - 当一个事务需要修改表中的元数据时，会获取写锁
  - 同一时刻最多只能有一个事务占有读锁，其他线程阻塞等待
元数据锁的作用
- 防止并发的 DDL 操作
  - 当一个事务对表进行结构性更改时（如添加一列属性），元数据锁（写锁）会阻止其他事务对该表进行其他操作，直到表结构更改完成
- 防止并发的 DML 操作
  - 当一个事务对表进行数据操作时（如增删改查），元数据锁（读锁）会阻止其他事务对该表进行结构性更改（读锁和写锁无法同时存在）
元数据锁是表锁，为防止和行所产生冲突，当 Innodb 需要加表锁的时候，会先判断一下数据表中是否已经存在行锁，但对数据表逐行遍历的效率太低了，需要借助意向锁来进行判断

意向锁

-- 属于表级锁，目的是为了快速判断表里是否有记录被加锁
-- 用于表示某个事务对某行数据加锁的意图
-- 分为意向共享锁（IS）和意向排他锁（IX）
-- 想加共享锁，先加意向共享锁；想加排他锁，先加意向排他锁
-- 主要用于行级锁与表级锁的结合

意向锁是表级别的锁，又细分为两种
- 共享意向锁 IS（Intention Shares Lock）
  - 当需要对数据表中的某条数据上 S 锁的时候，先给这张表加一个 IS 锁，表明此时表内有 S 锁
- 排他意向锁 IX（Intention Exclusive Lock），也称为独占意向锁
  - 当需要对数据表中的某条数据上 X 锁的时候，先给这张表加一个 IX 锁，表明此时表内有 X 锁
IS 和 IX 的作用就是给这张表打个标记，说明此时这张表内有或者没有行级锁
- 想要给数据表加上一个表级别的 S 锁，如果表上没有 IX ，说明表中没有排他锁，可以直接上表级别的 S 锁
- 想要给数据表加上一个表级别的 X 锁，如果表上没有 IX 和 IS ，说明表中所有记录都没加锁，可以直接上表级别的 X 锁
冲突性
- 锁性质 S X IS IX
  S 不冲突冲突不冲突冲突
  X 冲突冲突冲突冲突
  IS 不冲突冲突不冲突不冲突
  IX 冲突冲突不冲突不冲突

锁性质	S	X	IS	IX
S	不冲突	冲突	不冲突	冲突
X	冲突	冲突	冲突	冲突
IS	不冲突	冲突	不冲突	不冲突
IX	冲突	冲突	不冲突	不冲突

自增锁

属于表级锁
在插入自增列时，加锁以保证自增值的唯一性，防止并发插入导致的冲突
通常在插入操作时被使用，以确保生成的自增 ID 是唯一的

行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁
仅对特定的行加锁，允许其他事物并发访问不同的行
适用于高并发场景

-- 普通的 select 语句是不会对记录加锁的，因为它属于快照读
-- 如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读

select ... lock in share mode; -- 对读取的记录加共享锁
select ... for update; -- 对读取的记录加独占锁

-- 上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放
-- 所以在使用这两条语句的时候，要加上 begin、start transaction 或者 set autocommit = 0

记录锁

锁住当前记录，作用在索引上
Innodb 肯定是有索引的，即使没有主见也会创建隐藏的聚簇索引，所以 记录锁总是锁定索引记录 的
举例说明
- 事务 1 执行 select * from student where name = '学生1' for update ，由于 for update 是排他锁，导致其他事务无法对 name = '学生1' 的这条记录进行增删改操作
- 此时事务 A 还未提交，另一个事务 B 要执行 insert into student (name) value ('学生2') ，由于排他锁的存在，这条语句一定会被阻塞
- 此时事务 A 依然未提交，另一个事务 C 要执行 insert into student (name) value ('学生3') ，这条语句是否会被阻塞得看 name 属性有没有索引
  - 如果 name 没有索引
    - 由于记录锁是加在索引上面的，但是 name 没有索引，就只能去找聚簇索引
    - 但是无法通过 name 属性快速在聚簇索引上找到数据，就只能全表扫描
    - 全表扫描，就会把整张表都给锁了
    - 所以事务 C 会被阻塞
  - 如果 name 有索引
    - 记录锁会加在 name 的索引上，只会锁住 学生2 这一条记录，和 学生3 没半毛钱关系
    - 所以事务 C 不会被阻塞
注意：没有索引的列不要轻易上锁

间隙锁（重点）

-- 针对索引中两个记录之间的间隙加锁，防止其他事物在这个间隙中插入记录，以避免幻读
-- 间隙锁不锁定具体行，而是锁定行与行之间的空间

只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象
给两个数据之间的间隙加锁（给未存在的记录加锁），用于预防幻读
间隙锁的唯一目的是防止其他事务把其他数据插入到间隙中，允许有两个不同的间隙锁同时锁住同一个间隙，因为这两个锁的目的是一致的，这两个锁之间不会冲突
举例说明
如果在 9 和 17 之间间隙加上间隙锁 A ，这时候想要插入 id = 14 的记录，就会被间隙锁 A 给阻塞，从而避免了幻读，也就是实现了给还没有插入进来的记录加锁的需求
如果这时候间隙锁 B 也想给 9 和 17 之间间隙上锁，是允许其上锁的，在 9 和 17 之间间隙这短空间内，无论是只有一个间隙锁还是同时存在多个间隙锁，最终实现的都是 id = 10 ~ id = 16 的记录无法插入进来

临键锁（重点）

-- 是行级锁和间隙锁的结合，
-- 锁定具体行和其前面的间隙，确保在一个范围内不会出现幻读
-- 常用于支持可重复读的隔离级别

临键锁可以理解为记录锁 + 间隙锁
临键锁锁住的是【这条记录】和【它之前的间隙空间】，左开右闭，如 (9, 17]

在这里插入图片描述

举例说明
- 给 id = 17 加上临键锁
- 可以防止 9 和 17 之间间隙发生幻读现象
- 可以防止 id = 17 这条记录发生重复读现象

插入意向锁

-- 一种特殊的间隙锁，用于表示事务打算在某个间隙中插入记录
-- 允许其他事务进行共享锁，但在插入时会阻止其他的排他锁

用于表达对某个线程对某个间隙有插入意向，插入意向锁生成后默认是等待状态，表示某个线程正在等待当前占用所需资源的线程释放它的锁
插入意向锁的唯一目的是表达某个线程对这个间隙有插入意向，允许有两个不同的插入意向锁给同一个间隙加锁，因为这两个锁的目的是一致的，都是标记我正在等待这个间隙被释放，相互之间不会冲突
插入意向锁和间隙锁 / 临键锁的关系
- 有插入意向锁的前提条件是，当前间隙拥有间隙锁（临键锁内也包含间隙锁）
- 若有间隙锁 / 临键锁，可能但不一定有插入意向锁
- 若没有间隙锁 / 临键锁，一定没有插入意向锁
- 若有插入意向锁，一定有间隙锁 / 临键锁
- 若没有插入意向锁，可能但不一定有间隙锁 / 临键锁
以下两种情况不允许同时存在
- 【线程 A 拥有范围是 (9, 17] 的间隙锁】
- 【线程 B 拥有插入范围 (9, 17] 的插入意向锁】

在这里插入图片描述

举例说明
- 现在线程 A 想要插入一条 id = 12 的记录
- 插入之前会先检查是否存在【间隙锁】和【临键锁】，将 9 和 17 之间间隙上锁
- 如果都没有的话，直接插入即可
- 如果有所的话，线程 A 会在 9 和 17 之间间隙添加一个插入意向锁，表明事务 A 想在这个区间内插入一条记录，但是现在这个区间内被其他事务上锁了，事务 A 现在处于等待状态
- 如果这个时候事务 B 想要插入一条 id = 15 的记录，也会在 9 和 17 之间间隙添加一条插入意向锁
- 当间隙锁或临键锁释放后，按照【先来先服务】的顺序获取多个事务的插入意向锁

2. MySQL 事务的二阶段提交是什么？

目的

确保【重做日志Redolog】和【二进制日志binlog】之间的数据一致性
保证在 MySQL 在崩溃恢复过程中，不会出现【数据丢失】或【数据不一致】的情况

实现方案

准备阶段
- 在事务提交时， MySQL 的 Innodb 引擎会先写入 Redolog ，并将其状态标记为 prepare
- prepare 状态表示事务已经准备提交，但还没有真正提交
- 此时的 Redolog 是预提交状态，还未标记为完成提交
提交阶段
- 当 Redolog 的状态变成 prepare 后，MySQL Server 会写入 binlog （记录用户的 DML 操作）
- binlog 写入成功后，MySQL 通知 Innodb ，将 Redolog 的状态改为 commit
- commit 状态表示事务已经提交完成

binlog 和 Redolog 的区别

	Redolog	binlog
所处位置	Innodb 引擎内部	MySQL Server 层
记录内容	记录数据页的物理修改	记录所有数据库的逻辑修改操作
适用场景	崩溃恢复	数据恢复、主从复制、数据备份
发挥作用	帮助 Innodb 在崩溃后通过日志重做未写入数据页的数据修改从而确保数据的持久性	记录 SQL 语句的逻辑修改操作而不是数据页的物理修改
主要特点	固定大小、环形日志	追加写入

3. MySQL 中如果发生死锁应该如何解决？

解决方案

MySQL 自带的死锁检测机制
- 当检测到死锁是，数据库会自动回滚其中一个事务，以解除死锁
- 通常会选择持有资源最少的事务进行回滚
超时等待参数
- 当获取锁的等待时间超过阈值后，就释放锁进行回滚

手动 kill 发生死锁的语句

可以通过命令，手动快速找出被阻塞的线程及其事务 ID ，然后手动 kill 它，以释放资源

手动 kill 操作步骤如下

-- 使用以下命令查看当前正在执行的事务和相关的锁信息
show engine innobd status;
-- 这个命令会输出 Innodb 的状态
-- 包括死锁信息和当前的活动事务
-- 可以在输出信息中找到被阻塞的事务及其线程 ID

-- 还可以用 information_schema 中的 innodb_locks 和 innodb_lock_waits 表，查看当前锁和锁等待情况，得到事务 ID
select * from information_schema.innodb_locks;
select * from information_schema.innodb_locks_waits;
-- 再通过 information_schema 的 innodb_trx 可以找到事务 ID 和线程 ID 的对应关系

一旦确定了需要 kill 的线程 ID ，使用一下命令终止事务

kill <thread_id>;
-- 将 <thread_id> 替换为找到的实际线程 ID

避免方案

避免大事务
- 大事务占据锁的时间长，将大事务拆分成多个小事务，可以快速释放锁，降低死锁产生的概率和避免冲突
调整申请锁的顺序
- 在更新数据的时候要保证获得足够的锁
- 可以先获取影响范围大的锁，比如修改操作，先将排他锁获取到
- 然后再获取共享锁
更改数据库隔离级别
- 可重复读比读已提交多了间隙锁和临键锁
- 利用读已提交替换可重复读，可以降低死锁出现的情况
合理建立索引，减少加锁范围
- 如果索引命中，则会锁住对应行
- 如果索引未命中，就会全表加锁，冲突太大，死锁效率会变高
采用死锁检测机制
适当调整锁等待时长

20241231面试鸭特训营第8天

241231

1. MySQL 中有哪些锁类型？

分类

共享锁和排他锁（重点）

全局锁

作用

特点

举例说明应用场景

表级锁

元数据锁

意向锁

自增锁

行级锁

记录锁

间隙锁（重点）

临键锁（重点）

插入意向锁

2. MySQL 事务的二阶段提交是什么？

目的

实现方案

binlog 和 Redolog 的区别

3. MySQL 中如果发生死锁应该如何解决？

解决方案

避免方案