MySQL几道高频面试题解析
一条 update 语句的生命历程
在有关 MySQL 的面试时，是不是曾经有被问到过：一条 update 语句的生命历程是怎样的？今天就来聊聊这一面试题。
这篇文章通过这条语句进行讲解：
1 | update test set a=5 where id = 10; |
一条 SQL 语句在的执行，总的来说可以分为：Server 层和存储引擎层（本节只聊 InnoDB），下面来仔细聊聊这些过程。
1 Server 层
1.1 连接层
负责跟客户端建立连接、账号密码验证、获取权限、维持和管理连接。
1.2 分析器
在通过验证以后，分析器会对该语句分析，判断是否语法有错误等。
1.3 优化器
选择索引，生成执行计划。
1.5 执行器
根据优化器生成的执行计划，调用存储引擎 API 执行 SQL。
二 、InnoDB 引擎层
2.1 事务执行
读取数据页面
进入 InnoDB 引擎层后，首先会判断该 SQL 涉及到的数据页是否存在于 BP(buffer pool)中；
如果不存在则通过 B+Tree 读取到磁盘的数据页，然后加载到 BP：
通过二分法查找该页对应的记录
- 通过 space id 和 page no 哈希计算之后把 索引页加载到指定的 buffer pool instance 中
- 判断 free list 是否存在可用空闲页( Innodb_buffer_pool_pages_free、 Innodb_buffer_pool_wait_free )，没有则淘汰脏页或者 lru list 的 old 页
- 把数据页 copy 到 free list 中，然后加载到 lru list 的 old 区的 midpoint（头部）
加锁
尝试给对应行记录加上排他锁，过程如下：
- 对应行记录的行锁是否被其他事务占用，占用则进入锁等待；
- 进入锁等待之后，同时判断会不会由于自己的加入导致了死锁；
- 检测到没有锁等待和不会造成死锁之后，行记录加上排他锁；
写逻辑 undo log
将修改前的记录写入undo中；
修改当前行的值，填写事务编号；
使用回滚指针指向 undo log 中的修改前的行，构建回滚段，用于回滚数据和实现 MVCC 的多版本。
写 redo log buffer
先判断 redo log buffer 是否够用，不够用则等待，可通过 Innodb_log_waits 值查看;
对应行记录的字段值做更新操作，并把修改操作记录到 redo log buffer 中；
对应数据页面加入 flush list 链表中。
写 binlog cache
修改的信息会以对应 event 格式写入 binlog cache 中。
写 change buffer
如果此次 update 操作涉及到二级索引的修改，则写入 change buffer page 。
2.2 事务提交
InnoDB 存储引擎事务提交分为 prepare、commit 两阶段提交
redo log prepare
将 redo log buffer 刷新到磁盘文件中，用于崩溃恢复；刷盘的方式由 innodb_flush_log_at_trx_commit 决定（未标记commit），存储引擎层处于 prepare 状态.
binlog write & fsync
执行器把 binlog cache 里的完整事务和 redo log prepare 中的 XID event 写入到 binlog 中；
发送 binlog_cache 里的 event 到 slave 并等待（异步模式不等待） slave ack ；
执行 fsync 刷盘(大事务的话这步非常耗时)，并清空 binlog cache；# binlog 刷盘的方式由 sync_binlog 决定。
redo log commit
commit 阶段，由于之前该事务产生的 redo log 已经 sync 到磁盘了，所以这步只是在 redo log 里标记 commit，表明事务提交成功。半同步模式下如果收不到 slave ack 此步骤会处于等待状态。
事务提交成功，释放行记录持有的排他锁。
刷新脏页
数据库按照一定的策略执行刷脏页的操作。
2.3 事务回滚
如果事务因为异常或者被显式的回滚了，则借助 undo log 中的数据来进行恢复:
- 对于 in-place(原地)更新，将数据回滚到最老版本；
- 对于 delete + insert 方式进行的，标记删除的记录清理删除标记，同时把插入的聚集索引和二级索引记录也会被直接删除。
当前读和快照读的区别
本节内容就来聊聊这个话题，首先从快照读开始：
1 普通读
1.1 定义
普通读（也称快照读，英文名：Consistent Read），就是单纯的 SELECT 语句，不包括下面这两类语句：
1 2 | SELECT ... FOR UPDATE SELECT ... LOCK IN SHARE MODE |
普通读的执行方式是生成 ReadView，直接利用 MVCC 机制来进行读取，并不会对记录进行加锁。
小贴士
对于 SERIALIZABLE 隔离级别来说，如果 autocommit 系统变量被设置为OFF，那普通读的语句会转变为锁定读，和在普通的 SELECT 语句后边加 LOCK IN SHARE MODE 达成的效果一样。
1.2 实现方式
普通读是通过 undo log + MVCC 来实现的，具体我们再仔细聊聊：
下图右侧黄色部分是数据：一行数据记录，主键 ID 是 10，object = 'Goland' ，被 update 更新为 object = 'Python' 。
事务会先使用“排他锁”锁定该行，将该行当前的值复制到 undo log 中，然后再真正地修改当前行的值，最后填写事务的 DB_TRX_ID ，使用回滚指针 DB_ROLL_PTR 指向 undo log 中修改前的行。
这里解释一下 DB_TRX_ID 和 DB_ROLL_PTR 所代表的含义：
- DB_TRX_ID : 6 字节 DB_TRX_ID 字段，表示最后更新的事务 id ( update , delete , insert ) 。此外，删除在内部被视为更新，其中行中的特殊位被设置为将其标记为已软删除。
- DB_ROLL_PTR : 7 字节回滚指针，指向前一个版本的 undo log 记录，组成 undo 链表。如果更新了行，则撤消日志记录包含在更新行之前重建行内容所需的信息。
小贴士
insert undo log 只在事务回滚时需要, 事务提交就可以删掉了。update undo log 包括 update 和 delete , 回滚和快照读都需要。
2 当前读
聊完快照读，再聊聊当前读（也称锁定读，Locking Read）。
2.1 定义
当前读，读取的是最新版本，并且需要先获取对应记录的锁，如以下这些 SQL 类型：
1 2 3 4 5 | select ... lock in share mode 、 select ... for update、 update 、delete 、insert |
当然，获取什么类型的锁取决于当前事务的隔离级别、语句的执行计划、查询条件等因素。例如，要 update 一条记录，在事务执行过程中，如果不加锁，那么另一个事务可以 delete 这条数据并且能成功 commit ，就会产生冲突了。所以 update 的时候肯定要是当前读，得到最新的信息并且锁定相应的记录。
2.2 实现方式
当前读是通过 next-key 锁(行记录锁+间隙锁)来是实现的。
这里补充下行锁的 3 种算法：
行锁（Record Lock）：锁直接加在索引记录上面。
间隙锁（Gap Lock）：是 Innodb 为了解决幻读问题时引入的锁机制，所以只有在 Read Repeatable 、Serializable 隔离级别才有。
Next-Key Lock ：Record Lock + Gap Lock，锁定一个范围并且锁定记录本身 。
下面通过一个例子来说明当前读的实现方式，例如下面这条 SQL：
1 | delete from T where age = 7; |
进行下面的实验：
测试可知 delete from T where age = 7; 语句在 age 上的加锁区间为 (4,10) ,图解如下：
InnoDB 怎么做表空间迁移
1 迁移场景
在工作中经常会碰到单独迁移、复制或者备份某一张表的需求，一般可以通过逻辑/物理备份来实现。但是在 5.6.6+ 的版本中我们还可以用到一种基于表空间迁移的快速方法，本节内容就来聊聊这一操作。
2 实战
实验环境：两个 MySQL 5.7.22 版本数据库实例。
测试步骤如下：
2.1 在源库创建一张测试表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | mysql> use test; Database changed mysql> create table tmp_table(id int primary key,b varchar(20))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.07 sec) mysql> insert into tmp_table select 1,'a'; Query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into tmp_table select 2,'b'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from tmp_table; +----+------+ | id | b | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) |
2.2 生成元数据文件
在源库上运行 FLUSH TABLES … FOR EXPORT 锁定表并生成 .cfg 元数据文件
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> flush tables tmp_table for export; -- 注意：这一步会表锁，不能 insert、update Query OK, 0 rows affected (0.01 sec) ~ root# cd usr/local/mysql/data/test ~ root# ls -l tmp* -rw-r----- 1 _mysql _mysql 402 Nov 2 19:15 tmp_table.cfg -- 生成的 .cfg 文件 -rw-r----- 1 _mysql _mysql 8580 Nov 2 19:15 tmp_table.frm -rw-r----- 1 _mysql _mysql 98304 Nov 2 19:15 tmp_table.ibd |
2.3 创建目标表并丢弃现有表空间
在目标数据库创建相同的表，并丢弃现有的表空间
1 2 3 4 5 | mysql> create table tmp_table(id int primary key,b varchar(20))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.04 sec) mysql> ALTER TABLE tmp_table DISCARD TABLESPACE; ---丢弃现有表空间 |
注意：※约束条件、字符集等等也必须一致，建议使用 show create table tmp_table; 来获取创建表的 SQL，否则在新服务器上导入表空间的时候会提示 1808 错误。
2.4 将元数据文件复制到目标实例
将 .ibd 文件和 .cfg 元数据文件从源实例复制到目标实例：
1 2 3 | ~ root# scp tmp_table.ibd tmp_table.cfg root@目标ip:/usr/local/mysql/data/test ---传输到目标数据库的test schema目录 ~ root# chown mysql.mysql tmp_table.cfg tmp_table.ibd --在目标数据库中授权 |
2.5 在源数据库解锁 tmp_table 表
1 2 3 | mysql> unlock table; Query OK, 0 rows affected (0.01 sec) |
2.6 在目标数据库中导入 tmp_table 表空间
在目标数据库中导入 tmp_table 表空间，然后查看表是否正确
1 2 3 4 5 6 7 8 9 10 | mysql> alter table tmp_table import tablespace; Query OK, 0 rows affected (0.23 sec) mysql> select * from tmp_table; +----+------+ | id | b | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) |
注意：
ALTER TABLE … IMPORT TABLESPACE 特性并不强制对导入的数据施加外键约束。如果表之间有外键约束，那么所有表都应该在相同的（逻辑的）点上导出。在这种情况下，您将停止更新表，提交所有事务，在表上获得共享锁，然后执行导出操作。
RR 隔离级别下真的不会产生幻读吗
幻读（Phantom Read），简单的说，指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。
很多书籍或者文章都说 MySQL 通过 next-key lock 是解决了幻读的，但真的是那样吗？我们不妨来验证下（本节实验均在 RR 隔离级别下进行的）。
1 准备环境
1 2 3 4 5 6 7 8 9 10 11 | mysql> create table ord (id int,b varchar(10))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.04 sec) mysql> insert into ord select 1,'a'; Query OK, 1 row affected (0.02 sec) mysql> insert into ord select 2,'b'; Query OK, 1 row affected (0.01 sec) mysql> insert into ord select 3,'c'; Query OK, 1 row affected (0.01 sec) |
2 场景一
复习一下 next-key lock 的含义：
当 sql 语句按照范围查询非唯一索引列，并且有数据命中的时候会给索引加锁，锁住命中索引项的前一个索引到命中索引项的后一个索引之间的一个左开右闭区间
结论：
select * from user 是快照读，是 MVCC 避免了幻读。
select * from user lock in share mode，当前读，触发了幻读。-- select .... for update; 同理
换句话说：MySQL innodb 在 RR 隔离下一样会出现幻读，next-key lock 和 MVCC 只解决了部分幻读的场景。
2 场景二
结论：
由于 update 语句采用的是当前读，会对 A 线程中新增的行数据加锁、修改数据以及事物 ID，导致 B 线程出现幻读。
3 总结
由于 MySQL 对于普通读（简单 select 语句）是通过 MVCC 获取快照数据，而 select ... for update、select ... lock in share mode、update 、delete 等操作采用的是当前读，所以会造成在某些场景出现幻读的情况。
因此为了保证操作安全，可以采用事物开始时手动加锁来解决（select ... for update、select ... lock in share mode 等）。
MVCC 怎么实现的
1 从一个实验讲起
在说 MVCC（Multi-Version Concurrency Control，多版本并发控制）原理之前，先一起看看一个例子。
创建一张测试表并写入测试数据：
进行实验：
1 2 3 4 5 6 7 8 9 10 | create database likecolumn; use likecolumn; CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `idx_c` (`a`) ) ENGINE=InnoDB CHARSET=utf8mb4; insert into t1(a,b) values (1,1),(2,2); |
| 序号 | session1 | session2 |
|---|---|---|
| 1 | set session transaction_isolation='READCOMMITTED';/ 设置会话隔离级别为 RC/ | set session transaction_isolation='READCOMMITTED';/ 设置会话隔离级别为 RC/ |
| 2 | select * from t1; | |
| 3 | begin; | |
| 4 | update t1 set b=666 where a=1; | |
| 5 | begin; | |
| 6 | select * from t1; | |
| 7 | commit; | |
| 8 | select * from t1; | |
| 9 | commit; |
这里解释一下上面的实验过程，在 session1 开启一个事务更新了 a=1 这行记录，但还没提交的情况下，在 session2 中，满足 a=1 这条记录，b 的值还是原始值 1，而不是 session1 更新之后的 666，那么在数据库层面，这是怎么实现的呢？
其实 InnoDB 就是通过 MVCC 和 UNDO LOG 来实现的。
2 什么是 MVCC
MVCC， 即多版本并发控制。MVCC 的实现，是通过保存数据在某个时间点的快照来实现的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
也就是上面实验第 6 步中，为什么 session2 查询的结果还是 session1 修改之前的记录。
3 MVCC 的实现原理
对于 InnoDB ，聚簇索引记录中包含 3 个隐藏的列：
- ROW ID：隐藏的自增 ID，如果表没有主键，InnoDB 会自动按 ROW ID 产生一个聚集索引树。
- 事务 ID：记录最后一次修改该记录的事务 ID。
- 回滚指针：指向这条记录的上一个版本。
我们拿上面的例子，对应解释下 MVCC 的实现原理，如下图：
如图，首先 insert 语句向表 t1 中插入了一条数据，a 字段为 1，b 字段为 1， ROW ID 也为 1 ，事务 ID 假设为 1，回滚指针假设为 null。当执行 update t1 set b=666 where a=1 时，大致步骤如下：
- 数据库会先对满足 a=1 的行加排他锁；
- 然后将原记录复制到 undo 表空间中；
- 修改 b 字段的值为 666，修改事务 ID 为 2；
- 并通过隐藏的回滚指针指向 undo log 中的历史记录；
- 事务提交，释放前面对满足 a=1 的行所加的排他锁。
在前面实验的第 6 步中，session2 查询的结果是 session1 修改之前的记录，这个记录就是来自 undolog 中。
因此可以总结出 MVCC 实现的原理大致是：
InnoDB 每一行数据都有一个隐藏的回滚指针，用于指向该行修改前的最后一个历史版本，这个历史版本存放在 undo log 中。如果要执行更新操作，会将原记录放入 undo log 中，并通过隐藏的回滚指针指向 undo log 中的原记录。其它事务此时需要查询时，就是查询 undo log 中这行数据的最后一个历史版本。
MVCC 最大的好处是读不加锁，读写不冲突，极大地增加了 MySQL 的并发性。通过 MVCC，保证了事务 ACID 中的 I（隔离性）特性。
复制的演进历程
在很多场景下，MySQL 的高可用都是借助主从复制实现的，而 MySQL 复制不断的演进，也使得她越来越受欢迎。这一节内容就来聊聊 MySQL 复制的演进。
1 三种日志格式对复制的影响
1.1 开始支持复制
MySQL 从 3.23 版本开始支持复制，但是在 5.1.5 之前只支持 statement 格式的复制，尽管这种模式下，binlog 日志量相对比较少，但是涉及到跨库更新、或者使用结果不确定的函数时，比如 UUID()，容易出现主从数据不一致的情况。
1.2 开始支持 Row 格式的复制
从 MySQL 5.1.5 开始，新增了 Row 格式，日志中会记录每一行数据被修改的形式，因此 Row 格式下的复制，主从之间的数据一致性保障得到了大幅度提升，但是缺点是 binlog 日志量相对 statement 较多。
1.3 新增 mixed 格式
从 MySQL 5.1.8 开始新增 mixed 格式，当可能造成主从数据不一致的 SQL 时，binlog 使用 row 格式记录，否则使用 statement 格式记录。显然这种格式下，日志量是介于 statement 和 row 格式之间的。
2 半同步复制
2.1 异步复制
传统的 MySQL 复制为异步复制，其原理如下：
- 在主库开启 binlog 的情况下；
- 如果主库有变更操作，会记录到 binlog 中；
- 主库通过 IO 线程把 binlog 里面的内容传给从库的中继日志（relay log）中；
- 主库给客户端返回 commit 成功（这里不管从库是否已经收到了事务的 binlog）；
- 从库的 SQL 线程负责读取它的 relay log 里的信息并应用到从库数据库中。
其过程如下图：
在异步复制下，假如配置了自动切换的前提下，主库突然宕机，然后从提升为主时，原来主库上可能有一部分已经完成提交的数据还没来得及发送到从库，就可能产生数据丢失。为了解决这个问题，在 MySQL 5.5 版本中引入了半同步复制。下面来看下半同步复制的原理。
2.2 半同步复制
半同步复制原理如下：
- 在主库开启 binlog 的情况下；
- 如果主库有增删改的语句，会记录到 binlog 中；
- 主库通过 IO 线程把 binlog 里面的内容传给从库的中继日志（relay log）中；
- 从库收到 binlog 后，发送给主库一个 ACK，表示收到了；
- 主库收到这个 ACK 以后，才能给客户端返回 commit 成功；
- 从库的 SQL 线程负责读取它的 relay log 里的信息并应用到从库数据库中。
其过程如下图：
跟传统异步复制相比，半同步复制保证了所有客户端发送过确认提交的事务，从库都已经收到这个日志了。
但是这种模式下，实际上主库已经将该事务 commit 到事务引擎层，只是在等待返回而已，而此时其他 session 已经可以看到数据发生了变化，如果此时主库宕机，有可能从库还没写 Relay log，就会发生其他 session 切换前后查询的数据不一致的情况。
因此，从 MySQL 5.7 开始，引入了增强半同步复制（无损复制）。
2.3 增强半同步复制
增强半同步复制（也叫无损复制）是在半同步复制基础上做了微调，即主库写数据到 binlog 后，就开始等待从库的应答 ACK，直到至少一个从库写入 Relay log 并进行数据落盘后，才返回给主库消息，通知主库可以执行 commit 操作了，然后主库开始提交到事务引擎层。
但是，增强半同步复制其实也是存在问题的，假设有一个事务写 binlog 后 crash 了，事务还没有发送给从库，这时从库提升为主库，对客户端来说，数据没问题的，因为主库还没给客户端返回 commit；但是当老的主库恢复后，由于这个事务的 binlog 已经写入磁盘了，因此没办法回滚，在 crash recover 的机制下，会把这些事务重新提交，这就导致老的主库比新的主库多事务的情况。
因此又出现了一种复制形式--组复制。
3 组复制
MySQL 5.7 推出了组复制（MySQL Group Replication，简称：MGR），是基于内置的主从复制的架构实现的，主要在事务提交的过程中，嵌入单独的 binlog 封装逻辑，并通过专门的复制通道进行数据传输，
Group Replication 复制插件使用 Paxos 协议的原子广播特性，来保证在集群内的大多数节点都能接收到数据包，当数据节点接收到 write set 之后，每个节点上按照相同的规则对事务进行排序，并进行冲突检测。
对于 master，当冲突检测通过之后，数据变更写入自身的 binlog 中，然后进行存储引擎层的提交（如果发现事务冲突，则进行事务回滚）；
对于 slave，当冲突检测通过之后，就把主库发来的 binlog 写入自身的 relay log 中，然后 sql 线程读取 relay log 进行重放，并把重放的 binlog 日志写入自身的 binlog 中，然后存储引擎内部进行提交（如果发现事务冲突，则丢弃主库发送过来的 binlog 日志） 。
那么组复制如何处理故障转移的呢？
假设 3 个节点的集群，当写节点 crash 之后，集群内部重新选举一个节点作为新的写节点，整个写请求故障转移过程都是 Group Replication 内部自动完成（当然，写请求具体发送到哪个节点，还是需要更新 DNS 解析或者使用其它插件），解决了某个节点挂了如何踢掉或者恢复后如何加入集群（异步复制和半同步复制都需要人为干预或者依赖其他插件）。
4 并行复制
4.1 MySQL 5.6 的并行复制
在传统的复制模式下，我们也许经常会遇到主从延迟的场景。这是因为在 MySQL 5.6 之前，MySQL 只支持单线程复制。
从 MySQL 5.6 版本开始，支持并行复制策略，但是只支持库级别的。如果表都集中在一个 DB 里，或者热点表集中在一个库中，那就没有什么效果了。
4.2 MySQL 5.7 的并行复制
由参数：slave-parallel-type 控制并行复制策略。
配置为 DATABASE，表示使用 MySQL 5.6 版本的按库并行策略；
配置为 LOGICAL_CLOCK，同时处于 prepare 状态的事务，在备库执行时是可以并行的；处于 prepare 状态的事务，与处于 commit 状态的事务之间，在备库执行时也是可以并行的。
4.3 MySQL 5.7.22 的并行复制
MySQL 5.7.22 版本里，MySQL 增加了一个新的并行复制策略，基于 WRITESET 的并行复制。
相应地，新增了一个参数 binlog-transaction-dependency-tracking，用来控制是否启用这个新策略。这个参数的可选值有以下三种。
- COMMIT_ORDER，表示的就是前面介绍的，根据同时进入 prepare 和 commit 来判断是否可以并行的策略。
- WRITESET，表示的是对于事务涉及更新的每一行，计算出这一行的 hash 值，组成集合 writeset。如果两个事务没有操作相同的行，也就是说它们的 writeset 没有交集，就可以并行。
- WRITESET_SESSION，是在 WRITESET 的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序。
5 基于 GTID 的复制
通过 sql_slave_skip_counter 跳过事务和通过 slave_skip_errors 忽略错误的方法，虽然都最终可以建立从库 B 和新主库 A’的主备关系，但这两种操作都很复杂，而且容易出错。MySQL 5.6 版本引入了 GTID，解决了这个问题。并且 GTID 复制模式，让我们配置主从更加简单。基于 GTID 的复制配置及维护后续再细讲。
死锁的几种场景
其它请参考：https://www.xmmup.com/mysqlsuoxilie.html/4
这一节内容就来聊聊高频面试题：MySQL 有哪些死锁场景？
首先一起来复习一下死锁的概念：死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。
下面我们通过几个实验，来验证几种死锁场景。
1 环境准备
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | use martin; drop table if exists dl; CREATE TABLE `dl` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `idx_c` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `dl_insert` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY (`id`), unique key `uniq_a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; insert into dl(a,b,c) values (1,1,1),(2,2,2); drop table if exists dl_1; create table dl_1 like dl; insert into dl_1 select * from dl; |
2 同一张表下的死锁
| session1 | session2 |
|---|---|
| begin; | begin; |
| select * from dl where a=1 for update;…1 row in set (0.00 sec) | select * from dl where a=2 for update;…1 row in set (0.00 sec) |
| select from dl where a=2 for update;/ SQL1 */（等待） | |
| （session2 提示死锁回滚后，SQL1 成功返回结构） | select * from dl where a=1 for update;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
| commit; | commit; |
session1 在等待 session2 释放 a=2 的行锁，而 session2 在等待 session1 释放 a=1 的行锁。两个 session 互相等待对方释放资源，就进入了死锁状态。
3 不同表下的死锁
| session1 | session2 |
|---|---|
| begin; | begin; |
| select * from dl where a=1 for update; … 1 row in set (0.00 sec) | select * from dl_1 where a=1 for update; … 1 row in set (0.00 sec) |
| select from dl_1 where a=1 for update;/ SQL2 */ 等待 | |
| （session2 提示死锁回滚后，SQL1 成功返回结构） | select * from dl where a=1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
| commit; | commit; |
这个实验也是两个 session 互相等待对方释放资源，就进入了死锁状态。
4 间隙锁下的死锁
| session1 | session2 |
|---|---|
| set session transaction_isolation='REPEATABLE-READ'; 设置会话隔离级别为 RR / | set session transaction_isolation='REPEATABLE-READ'; 设置会话隔离级别为 RR / |
| begin; | begin; |
| select * from dl where a=1 for update; … 1 row in set (0.00 sec) | select * from dl where a=2 for update; … 1 row in set (0.00 sec) |
| insert into dl(a,b,c) values (2,3,3);/ SQL1 / 等待 | |
| （session2 提示死锁回滚后，SQL1 成功返回结果） | insert into dl(a,b,c) values (1,4,4);/ SQL2 / ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
| commit; | commit; |
由于 RR 隔离级别下存在间隙锁，可以知道 SQL1 需要等待 a=2 获得的间隙锁，而 SQL2 需要等待 a=1 获得的间隙锁，两个 session 互相等待对方释放资源，就进入了死锁状态。
5 INSERT 语句的死锁
| session1 | session2 | session3 |
|---|---|---|
| begin; | ||
| insert into dl_insert(a,b,c) value (3,3,3); | ||
| insert into dl_insert(a,b,c) value (3,3,3);/ 等待 / | insert into dl_insert(a,b,c) value (3,3,3);/ 等待 / | |
| rollback; | 执行成功 | ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
这里需要注意的是，a 字段有唯一索引。当 session1 执行完 insert 语句，会在索引 a=3 上加记录锁，当 session2 执行同样的 insert 语句时，唯一键冲突，加上读锁；同样 session3 也会加上读锁。
当 session1 回滚，session2 和 session3 都试图继续执行插入操作，都要加上写锁。此时两个 session 都要等待对方的行锁，因此出现了死锁。
一些死锁场景就介绍到这里，当然，也欢迎各位补充其他的一些死锁场景。
xtrabackup物理备份和mysqldump 逻辑备份的区别
在工作中，MySQL 的备份是一项重点工作，而备份策略及备份方式，也是我们经常讨论的话题，在面试中也少不了一些关于备份相关问题的讨论。
MySQL 的备份工具有很多，本节内容就选逻辑备份工具中使用最多的 mysqldump 和物理备份工具中使用最多的 xtrabackup 来进行对比讲解。
1 mysqldump 备份恢复原理
首先来聊聊 mysqldump 的备份原理：
- 与 server 建立连接，并初始化 session；
- 执行 FTWRL(flush tables with read lock)；
- 设置当前会话隔离级别为 RR；
- 开启事务并创建快照；
- 获取当前 binlog 位置；
- 解锁所有表；
- 对指定的库和表进行 dump；
- 备份完成。
而使用 mysqldump 备份出的文件要进行恢复，实际就是执行 SQL 文件的过程，这里就不展开讲解了。
2 Xtrabackup 备份恢复原理
再来看看 xtrabackup 的备份原理：
- 开启 redo log 拷贝线程，从最新的检查点开始顺序拷贝 redo log；
- 拷贝事务引擎表的数据文件；
- 等到事务引擎数据文件拷贝结束，通知调用 FTWRL；
- 备份非事务引擎数据文件及其他文件；
- 获取 binlog 位点信息；
- 停止复制 redo log；
- 解锁 UNLOCK TABLES；
- 释放锁，备份结束；
- 备份完成。
同时也讲下 xtrabackup 的恢复原理：
- 模拟 MySQL 进行 recover，将 redo log 回放到数据文件中；
- 等到 recover 完成，重建 redo log；
- 将数据文件复制到 MySQL 数据目录；
- 完成还原。
恢复的目的实际可以看成把备份的数据恢复到一个一致性位点的过程，那么怎么保证事务引擎和非事务引擎在同一个位点呢？
又回到备份时的逻辑，因为非事务引擎是在执行 FTWRL 后进行的数据文件拷贝，这个过程数据库处于只读的，因此非事务引擎对应的就是 FTWRL 的位点。
而 InnoDB 的 idb 文件拷贝是在 FTWRL 前做的，拷贝出来的不同表的 idb 文件最后更新时间点很可能不一样，但是 InnoDB 的 redo log 是从备份开始一直持续拷贝的，拷贝一直持续到 FTWRL 后，所以最终通过应用 redo log 的 idb 数据位点也是和 FTWRL 一致的。
3 两者的区别
看完两个工具的备份原理，再来聊聊它们的区别：
3.1 加锁时间
两个工具都会对 MySQL 加全局读锁，但是 mysqldump 在备份开始的时候加的；而 xtrabackup 是在拷贝完事务引擎表的数据文件后，再加的全局读锁。
3.2 备份恢复时间
由于 mysqldump 备份时，实际是去数据库中执行：
1 | select * from table_name; |
恢复是在数据库里导入备份出的 SQL 语句。
而 xtrabackup 备份时，是拷贝的物理文件；
恢复时直接复制物理文件。
因此 xtrabackup 备份恢复的时间要比 mysqldump 短很多。
3.3 适用场景
正是因为 mysqldump 备份时产生表结构和数据的 SQL 语句，因此其适用于数据量较少、跨版本数据库备份恢复、单库单表备份等场景。
而 xtrabackup 适用于大数据量、整库备份等场景。
