MySQL - 事务 - imklaus's Blog

基本介绍

事务（Transaction）是访问和更新数据库的程序执行单元；事务中可能包含一个或多个 SQL 语句，这些语句要么都执行，要么都不执行，作为一个关系型数据库，MySQL 支持事务。

单元中的每条 SQL 语句都相互依赖，形成一个整体

如果某条 SQL 语句执行失败或者出现错误，那么整个单元就会回滚，撤回到事务最初的状态
如果单元中所有的 SQL 语句都执行成功，则事务就顺利执行

事务的四大特征：ACID

原子性 (atomicity)
一致性 (consistency)
隔离性 (isolaction)
持久性 (durability)

事务的几种状态：

活动的（active）：事务对应的数据库操作正在执行中
部分提交的（partially committed）：事务的最后一个操作执行完，但是内存还没刷新至磁盘
失败的（failed）：当事务处于活动状态或部分提交状态时，如果数据库遇到了错误或刷脏失败，或者用户主动停止当前的事务
中止的（aborted）：失败状态的事务回滚完成后的状态
提交的（committed）：当处于部分提交状态的事务刷脏成功，就处于提交状态

事务管理

基本操作

事务管理的三个步骤

开启事务：记录回滚点，并通知服务器，将要执行一组操作，要么同时成功、要么同时失败
执行 SQL 语句：执行具体的一条或多条 SQL 语句
结束事务（提交|回滚）
- 提交：没出现问题，数据进行更新
- 回滚：出现问题，数据恢复到开启事务时的状态

事务操作：

显式开启事务

sql

START TRANSACTION [READ ONLY|READ WRITE|WITH CONSISTENT SNAPSHOT]; #可以跟一个或多个状态，最后的是一致性读
BEGIN [WORK];

说明：不填状态默认是读写事务

回滚事务，用来手动中止事务
sql
```
ROLLBACK;
```
提交事务，显示执行是手动提交，MySQL 默认为自动提交
sql
```
COMMIT;
```

保存点：在事务的执行过程中设置的还原点，调用 ROLLBACK 时可以指定回滚到哪个点

sql

SAVEPOINT point_name;						#设置保存点
RELEASE point_name							#删除保存点
ROLLBACK [WORK] TO [SAVEPOINT] point_name	#回滚至某个保存点，不填默认回滚到事务执行之前的状态

操作演示

sql

-- 开启事务
START TRANSACTION;

-- 张三给李四转账500元
-- 1.张三账户-500
UPDATE account SET money=money-500 WHERE NAME='张三';
-- 2.李四账户+500
UPDATE account SET money=money+500 WHERE NAME='李四';

-- 回滚事务(出现问题)
ROLLBACK;

-- 提交事务(没出现问题)
COMMIT;

提交方式

提交方式的相关语法：

查看事务提交方式

sql

SELECT @@AUTOCOMMIT;  		-- 会话，1 代表自动提交    0 代表手动提交
SELECT @@GLOBAL.AUTOCOMMIT;	-- 系统

修改事务提交方式

sql

SET @@AUTOCOMMIT=数字;	-- 系统
SET AUTOCOMMIT=数字;		-- 会话

系统变量的操作：

sql

SET [GLOBAL|SESSION] 变量名 = 值;					-- 默认是会话
SET @@[(GLOBAL|SESSION).]变量名 = 值;				-- 默认是系统

sql

SHOW [GLOBAL|SESSION] VARIABLES [LIKE '变量%'];	  -- 默认查看会话内系统变量值

工作原理：

自动提交：如果没有 START TRANSACTION 显式地开始一个事务，那么每条 SQL 语句都会被当做一个事务执行提交操作；显式开启事务后，会在本次事务结束（提交或回滚）前暂时关闭自动提交
手动提交：不需要显式的开启事务，所有的 SQL 语句都在一个事务中，直到执行了提交或回滚，然后进入下一个事务
隐式提交：存在一些特殊的命令，在事务中执行了这些命令会马上强制执行 COMMIT 提交事务
- DDL 语句 (CREATE/DROP/ALTER)、LOCK TABLES 语句、LOAD DATA 导入数据语句、主从复制语句等
- 当一个事务还没提交或回滚，显式的开启一个事务会隐式的提交上一个事务

事务 ID

事务在执行过程中对某个表执行了增删改操作或者创建表，就会为当前事务分配一个独一无二的事务 ID（对临时表并不会分配 ID），如果当前事务没有被分配 ID，默认是 0

说明：只读事务不能对普通的表进行增删改操作，但是可以对临时表增删改，读写事务可以对数据表执行增删改查操作

事务 ID 本质上就是一个数字，服务器在内存中维护一个全局变量：

每当需要为某个事务分配 ID，就会把全局变量的值赋值给事务 ID，然后变量自增 1
每当变量值为 256 的倍数时，就将该变量的值刷新到系统表空间的 Max Trx ID 属性中，该属性占 8 字节
系统再次启动后，会读取表空间的 Max Trx ID 属性到内存，加上 256 后赋值给全局变量，因为关机时的事务 ID 可能并不是 256 的倍数，会比 Max Trx ID 大，所以需要加上 256 保持事务 ID 是一个递增的数字

聚簇索引的行记录除了完整的数据，还会自动添加 trx_id、roll_pointer 隐藏列，如果表中没有主键并且没有非空唯一索引，也会添加一个 row_id 的隐藏列作为聚簇索引

隔离级别

四种级别

事务的隔离级别：多个客户端操作时，各个客户端的事务之间应该是隔离的，不同的事务之间不该互相影响，而如果多个事务操作同一批数据时，则需要设置不同的隔离级别，否则就会产生问题。

隔离级别分类：

隔离级别	名称	会引发的问题	数据库默认隔离级别
Read Uncommitted	读未提交	脏读、不可重复读、幻读
Read Committed	读已提交	不可重复读、幻读	Oracle / SQL Server
Repeatable Read	可重复读	幻读	MySQL
Serializable	可串行化	无

一般来说，隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差

脏写 (Dirty Write)：当两个或多个事务选择同一行，最初的事务修改的值被后面事务修改的值覆盖，所有的隔离级别都可以避免脏写（又叫丢失更新），因为有行锁
脏读 (Dirty Reads)：在一个事务处理过程中读取了另一个未提交的事务中修改过的数据
不可重复读 (Non-Repeatable Reads)：在一个事务处理过程中读取了另一个事务中修改并已提交的数据
可重复读的意思是不管读几次，结果都一样，可以重复的读，可以理解为快照读，要读的数据集不会发生变化
幻读 (Phantom Reads)：在事务中按某个条件先后两次查询数据库，后一次查询查到了前一次查询没有查到的行，数据条目发生了变化。比如查询某数据不存在，准备插入此记录，但执行插入时发现此记录已存在，无法插入

隔离级别操作语法：

查询数据库隔离级别

sql

SELECT @@TX_ISOLATION;			-- 会话
SELECT @@GLOBAL.TX_ISOLATION;	-- 系统

修改数据库隔离级别

sql

SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;

加锁分析

InnoDB 存储引擎支持事务，所以加锁分析是基于该存储引擎

Read Uncommitted 级别，任何操作都不会加锁
Read Committed 级别，增删改操作会加写锁（行锁），读操作不加锁
在 Server 层过滤条件时发现不满足的记录会调用 unlock_row 方法释放该记录的行锁，保证最后只有满足条件的记录加锁，但是扫表过程中每条记录的加锁操作不能省略。所以对数据量很大的表做批量修改时，如果无法使用相应的索引（全表扫描），在 Server 过滤数据时就会特别慢，出现虽然没有修改某些行的数据，但是还是被锁住了的现象（锁表），这种情况同样适用于 RR
Repeatable Read 级别，增删改操作会加写锁，读操作不加锁。因为读写锁不兼容，加了读锁后其他事务就无法修改数据，影响了并发性能，为了保证隔离性和并发性，MySQL 通过 MVCC 解决了读写冲突。RR 级别下的锁有很多种，锁机制章节详解
Serializable 级别，读加共享锁，写加排他锁，读写互斥，使用的悲观锁的理论，实现简单，数据更加安全，但是并发能力非常差
- 串行化：让所有事务按顺序单独执行，写操作会加写锁，读操作会加读锁
- 可串行化：让所有操作相同数据的事务顺序执行，通过加锁实现

参考文章：https://tech.meituan.com/2014/08/20/innodb-lock.html

原子特性

实现方式

原子性是指事务是一个不可分割的工作单位，事务的操作如果成功就必须要完全应用到数据库，失败则不能对数据库有任何影响。比如事务中一个 SQL 语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态

InnoDB 存储引擎提供了两种事务日志：redo log（重做日志）和 undo log（回滚日志）

redo log 用于保证事务持久性
undo log 用于保证事务原子性和隔离性

undo log 属于逻辑日志，根据每行操作进行记录，记录了 SQL 执行相关的信息，用来回滚行记录到某个版本

当事务对数据库进行修改时，InnoDB 会先记录对应的 undo log，如果事务执行失败或调用了 rollback 导致事务回滚，InnoDB 会根据 undo log 的内容做与之前相反的操作：

对于每个 insert，回滚时会执行 delete
对于每个 delete，回滚时会执行 insert
对于每个 update，回滚时会执行一个相反的 update，把数据修改回去

参考文章：https://www.cnblogs.com/kismetv/p/10331633.html

DML 解析

INSERT

乐观插入：当前数据页的剩余空间充足，直接将数据进行插入

悲观插入：当前数据页的剩余空间不足，需要进行页分裂，申请一个新的页面来插入数据，会造成更多的 redo log，undo log 影响不大

当向某个表插入一条记录，实际上需要向聚簇索引和所有二级索引都插入一条记录，但是 undo log 只针对聚簇索引记录，在回滚时会根据聚簇索引去所有的二级索引进行回滚操作

roll_pointer 是一个指针，指向记录对应的 undo log 日志，一条记录就是一个数据行，行格式中的 roll_pointer 就指向 undo log

DELETE

插入到页面中的记录会根据 next_record 属性组成一个单向链表，这个链表称为正常链表，被删除的记录也会通过 next_record 组成一个垃圾链表，该链表中所占用的存储空间可以被重新利用，并不会直接清除数据

在页面 Page Header 中，PAGE_FREE 属性指向垃圾链表的头节点，删除的工作过程：

将要删除的记录的 delete_flag 位置为 1，其他不做修改，这个过程叫 delete mark
在事务提交前，delete_flag = 1 的记录一直都会处于中间状态
事务提交后，有专门的线程将 delete_flag = 1 的记录从正常链表移除并加入垃圾链表，这个过程叫 purge
purge 线程在执行删除操作时会创建一个 ReadView，根据事务的可见性移除数据（隔离特性部分详解）

当有新插入的记录时，首先判断 PAGE_FREE 指向的头节点是否足够容纳新纪录：

如果可以容纳新纪录，就会直接重用已删除的记录的存储空间，然后让 PAGE_FREE 指向垃圾链表的下一个节点
如果不能容纳新纪录，就直接向页面申请新的空间存储，并不会遍历垃圾链表

重用已删除的记录空间，可能会造成空间碎片，当数据页容纳不了一条记录时，会判断将碎片空间加起来是否可以容纳，判断为真就会重新组织页内的记录：

开辟一个临时页面，将页内记录一次插入到临时页面，此时临时页面时没有碎片的
把临时页面的内容复制到本页，这样就解放出了内存碎片，但是会耗费很大的性能资源

UPDATE

执行 UPDATE 语句，对于更新主键和不更新主键有两种不同的处理方式

不更新主键的情况：

就地更新（in-place update），如果更新后的列和更新前的列占用的存储空间一样大，就可以直接在原记录上修改
先删除旧纪录，再插入新纪录，这里的删除不是 delete mark，而是直接将记录加入垃圾链表，并且修改页面的相应的控制信息，执行删除的线程不是 purge，是执行更新的用户线程，插入新记录时可能造成页空间不足，从而导致页分裂

更新主键的情况：

将旧纪录进行 delete mark，在更新语句提交后由 purge 线程移入垃圾链表
根据更新的各列的值创建一条新纪录，插入到聚簇索引中

在对一条记录修改前会将记录的隐藏列 trx_id 和 roll_pointer 的旧值记录到当前 undo log 对应的属性中，这样当前记录的 roll_pointer 指向当前 undo log 记录，当前 undo log 记录的 roll_pointer 指向旧的 undo log 记录，形成一个版本链

UPDATE、DELETE 操作产生的 undo 日志会用于其他事务的 MVCC 操作，所以不能立即删除，INSERT 可以删除的原因是 MVCC 是对现有数据的快照

回滚日志

undo log 是采用段的方式来记录，Rollback Segement 称为回滚段，本质上就是一个类型是 Rollback Segement Header 的页面

每个回滚段中有 1024 个 undo slot，每个 slot 存放 undo 链表页面的头节点页号，每个链表对应一个叫 undo log segment 的段

在以前老版本，只支持 1 个 Rollback Segement，只能记录 1024 个 undo log segment
MySQL5.5 开始支持 128 个 Rollback Segement，支持 128*1024 个 undo 操作

工作流程：

事务执行前需要到系统表空间第 5 号页面中分配一个回滚段（页），获取一个 Rollback Segement Header 页面的地址
回滚段页面有 1024 个 undo slot，首先去回滚段的两个 cached 链表获取缓存的 slot，缓存中没有就在回滚段页面中找一个可用的 undo slot 分配给当前事务
如果是缓存中获取的 slot，则该 slot 对应的 undo log segment 已经分配了，需要重新分配，然后从 undo log segment 中申请一个页面作为日志链表的头节点，并填入对应的 slot 中
每个事务 undo 日志在记录的时候占用两个 undo 页面的组成链表，分别为 insert undo 链表和 update undo 链表，链表的头节点页面为 first undo page 会包含一些管理信息，其他页面为 normal undo page
说明：事务执行过程的临时表也需要两个 undo 链表，不和普通表共用，这些链表并不是事务开始就分配，而是按需分配

隔离特性

实现方式

隔离性是指，事务内部的操作与其他事务是隔离的，多个并发事务之间要相互隔离，不能互相干扰

严格的隔离性，对应了事务隔离级别中的 serializable，实际应用中对性能考虑很少使用可串行化
与原子性、持久性侧重于研究事务本身不同，隔离性研究的是不同事务之间的相互影响

隔离性让并发情形下的事务之间互不干扰：

一个事务的写操作对另一个事务的写操作（写写）：锁机制保证隔离性
一个事务的写操作对另一个事务的读操作（读写）：MVCC 保证隔离性

锁机制：事务在修改数据之前，需要先获得相应的锁，获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁（详解见锁机制）

并发控制

MVCC 全称 Multi-Version Concurrency Control，即多版本并发控制，用来解决读写冲突的无锁并发控制，可以在发生读写请求冲突时不用加锁解决，这个读是指的快照读（也叫一致性读或一致性无锁读），而不是当前读：

快照读：实现基于 MVCC，因为是多版本并发，所以快照读读到的数据不一定是当前最新的数据，有可能是历史版本的数据
当前读：又叫加锁读，读取数据库记录是当前最新的版本（产生幻读、不可重复读），可以对读取的数据进行加锁，防止其他事务修改数据，是悲观锁的一种操作，读写操作加共享锁或者排他锁和串行化事务的隔离级别都是当前读

数据库并发场景：

读-读：不存在任何问题，也不需要并发控制
读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
写-写：有线程安全问题，可能会存在脏写（丢失更新）问题

MVCC 的优点：

在并发读写数据库时，做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了并发读写的性能
可以解决脏读，不可重复读等事务隔离问题（加锁也能解决），但不能解决更新丢失问题（写锁会解决）

提高读写和写写的并发性能：

MVCC + 悲观锁：MVCC 解决读写冲突，悲观锁解决写写冲突
MVCC + 乐观锁：MVCC 解决读写冲突，乐观锁解决写写冲突

参考文章：https://www.jianshu.com/p/8845ddca3b23

实现原理

隐藏字段

实现原理主要是隐藏字段，undo日志，Read View 来实现的

InnoDB 存储引擎，数据库中的聚簇索引每行数据，除了自定义的字段，还有数据库隐式定义的字段：

DB_TRX_ID：最近修改事务 ID，记录创建该数据或最后一次修改该数据的事务 ID
DB_ROLL_PTR：回滚指针，指向记录对应的 undo log 日志，undo log 中又指向上一个旧版本的 undo log
DB_ROW_ID：隐含的自增 ID（隐藏主键），如果数据表没有主键，InnoDB 会自动以 DB_ROW_ID 作为聚簇索引

版本链

undo log 是逻辑日志，记录的是每个事务对数据执行的操作，而不是记录的全部数据，要根据 undo log 逆推出以往事务的数据

undo log 的作用：

保证事务进行 rollback 时的原子性和一致性，当事务进行回滚的时候可以用 undo log 的数据进行恢复
用于 MVCC 快照读，通过读取 undo log 的历史版本数据可以实现不同事务版本号都拥有自己独立的快照数据

undo log 主要分为两种：

insert undo log：事务在 insert 新记录时产生的 undo log，只在事务回滚时需要，并且在事务提交后可以被立即丢弃
update undo log：事务在进行 update 或 delete 时产生的 undo log，在事务回滚时需要，在快照读时也需要。不能随意删除，只有在当前读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除

每次对数据库记录进行改动，都会产生的新版本的 undo log，随着更新次数的增多，所有的版本都会被 roll_pointer 属性连接成一个链表，把这个链表称之为版本链，版本链的头节点就是当前的最新的 undo log，链尾就是最早的旧 undo log

说明：因为 DELETE 删除记录，都是移动到垃圾链表中，不是真正的删除，所以才可以通过版本链访问原始数据

注意：undo 是逻辑日志，这里只是直观的展示出来

工作流程：

有个事务插入 persion 表一条新记录，name 为 Jerry，age 为 24
事务 1 修改该行数据时，数据库会先对该行加排他锁，然后先记录 undo log，然后修改该行 name 为 Tom，并且修改隐藏字段的事务 ID 为当前事务 1 的 ID（默认为 1 之后递增），回滚指针指向拷贝到 undo log 的副本记录，事务提交后，释放锁
以此类推

读视图

Read View 是事务进行读数据操作时产生的读视图，该事务执行快照读的那一刻会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID，用来做可见性判断，根据视图判断当前事务能够看到哪个版本的数据

注意：这里的快照并不是把所有的数据拷贝一份副本，而是由 undo log 记录的逻辑日志，根据库中的数据进行计算出历史数据

工作流程：将版本链的头节点的事务 ID（最新数据事务 ID，大概率不是当前线程）DB_TRX_ID 取出来，与系统当前活跃事务的 ID 对比进行可见性分析，不可见就通过 DB_ROLL_PTR 回滚指针去取出 undo log 中的下一个 DB_TRX_ID 比较，直到找到最近的满足可见性的 DB_TRX_ID，该事务 ID 所在的旧记录就是当前事务能看见的最新的记录

Read View 几个属性：

m_ids：生成 Read View 时当前系统中活跃的事务 id 列表（未提交的事务集合，当前事务也在其中）
min_trx_id：生成 Read View 时当前系统中活跃的最小的事务 id，也就是 m_ids 中的最小值（已提交的事务集合）
max_trx_id：生成 Read View 时当前系统应该分配给下一个事务的 id 值，m_ids 中的最大值加 1（未开始事务）
creator_trx_id：生成该 Read View 的事务的事务 id，就是判断该 id 的事务能读到什么数据

creator 创建一个 Read View，进行可见性算法分析：（解决了读未提交）

db_trx_id == creator_trx_id：表示这个数据就是当前事务自己生成的，自己生成的数据自己肯定能看见，所以此数据对 creator 是可见的
db_trx_id < min_trx_id：该版本对应的事务 ID 小于 Read view 中的最小活跃事务 ID，则这个事务在当前事务之前就已经被提交了，对 creator 可见（因为比已提交的最大事务 ID 小的并不一定已经提交，所以应该判断是否在活跃事务列表）
db_trx_id >= max_trx_id：该版本对应的事务 ID 大于 Read view 中当前系统的最大事务 ID，则说明该数据是在当前 Read view 创建之后才产生的，对 creator 不可见
min_trx_id<= db_trx_id < max_trx_id：判断 db_trx_id 是否在活跃事务列表 m_ids 中
- 在列表中，说明该版本对应的事务正在运行，数据不能显示（不能读到未提交的数据）
- 不在列表中，说明该版本对应的事务已经被提交，数据可以显示（可以读到已经提交的数据）

工作流程

表 user 数据

id		name		age
1		张三		   18

Transaction 20：

sql

START TRANSACTION;	-- 开启事务
UPDATE user SET name = '李四' WHERE id = 1;
UPDATE user SET name = '王五' WHERE id = 1;

Transaction 60：

sql

START TRANSACTION;	-- 开启事务
-- 操作表的其他数据

ID 为 0 的事务创建 Read View：

m_ids：20、60
min_trx_id：20
max_trx_id：61
creator_trx_id：0

只有红框部分才复合条件，所以只有张三对应的版本的数据可以被看到

参考视频：https://www.bilibili.com/video/BV1t5411u7Fg

二级索引

只有在聚簇索引中才有 trx_id 和 roll_pointer 的隐藏列，对于二级索引判断可见性的方式：

二级索引页面的 Page Header 中有一个 PAGE_MAX_TRX_ID 属性，代表修改当前页面的最大的事务 ID，SELECT 语句访问某个二级索引时会判断 ReadView 的 min_trx_id 是否大于该属性，大于说明该页面的所有属性对 ReadView 可见
如果属性判断不可见，就需要利用二级索引获取主键值进行回表操作，得到聚簇索引后按照聚簇索引的可见性判断的方法操作

RC RR

Read View 用于支持 RC（Read Committed，读已提交）和 RR（Repeatable Read，可重复读）隔离级别的实现，所以 SELECT 在 RC 和 RR 隔离级别使用 MVCC 读取记录

RR、RC 生成时机：

RC 隔离级别下，每次读取数据前都会生成最新的 Read View（当前读）
RR 隔离级别下，在第一次数据读取时才会创建 Read View（快照读）

RC、RR 级别下的 InnoDB 快照读区别

RC 级别下，事务中每次快照读都会新生成一个 Read View，这就是在 RC 级别下的事务中可以看到别的事务提交的更新的原因
RR 级别下，某个事务的对某条记录的第一次快照读会创建一个 Read View，将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，使用的是同一个 Read View，所以一个事务的查询结果每次都是相同的
RR 级别下，通过 START TRANSACTION WITH CONSISTENT SNAPSHOT 开启事务，会在执行该语句后立刻生成一个 Read View，不是在执行第一条 SELECT 语句时生成（所以说 START TRANSACTION 并不是事务的起点，执行第一条语句才算起点）

解决幻读问题：

快照读：通过 MVCC 来进行控制的，在可重复读隔离级别下，普通查询是快照读，是不会看到别的事务插入的数据的，但是并不能完全避免幻读
场景：RR 级别，T1 事务开启，创建 Read View，此时 T2 去 INSERT 新的一行然后提交，然后 T1 去 UPDATE 该行会发现更新成功，并且把这条新记录的 trx_id 变为当前的事务 id，所以对当前事务就是可见的。因为 Read View 并不能阻止事务去更新数据，更新数据都是先读后写并且是当前读，读取到的是最新版本的数据
当前读：通过 next-key 锁（行锁 + 间隙锁）来解决问题

持久特性

实现方式

持久性是指一个事务一旦被提交了，那么对数据库中数据的改变就是永久性的，接下来的其他操作或故障不应该对其有任何影响。

Buffer Pool 的使用提高了读写数据的效率，但是如果 MySQL 宕机，此时 Buffer Pool 中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证，所以引入了 redo log 日志：

redo log 记录数据页的物理修改，而不是某一行或某几行的修改，用来恢复提交后的数据页，只能恢复到最后一次提交的位置
redo log 采用的是 WAL（Write-ahead logging，预写式日志），所有修改要先写入日志，再更新到磁盘，保证了数据不会因 MySQL 宕机而丢失，从而满足了持久性要求
简单的 redo log 是纯粹的物理日志，复杂的 redo log 会存在物理日志和逻辑日志

工作过程：MySQL 发生了宕机，InnoDB 会判断一个数据页在崩溃恢复时丢失了更新，就会将它读到内存，然后根据 redo log 内容更新内存，更新完成后，内存页变成脏页，然后进行刷脏

缓冲池的刷脏策略：

redo log 文件是固定大小的，如果写满了就要擦除以前的记录，在擦除之前需要把对应的更新持久化到磁盘中
Buffer Pool 内存不足，需要淘汰部分数据页（LRU 链表尾部），如果淘汰的是脏页，就要先将脏页写到磁盘（要避免大事务）
系统空闲时，后台线程会自动进行刷脏（Flush 链表部分已经详解）
MySQL 正常关闭时，会把内存的脏页都刷新到磁盘上

重做日志

日志缓冲

服务器启动时会向操作系统申请一片连续内存空间作为 redo log buffer（重做日志缓冲区），可以通过 innodb_log_buffer_size 系统变量指定 redo log buffer 的大小，默认是 16MB

log buffer 被划分为若干 redo log block（块，类似数据页的概念），每个默认大小 512 字节，每个 block 由 12 字节的 log block head、496 字节的 log block body、4 字节的 log block trailer 组成

当数据修改时，先修改 Change Buffer 中的数据，然后在 redo log buffer 记录这次操作，写入 log buffer 的过程是顺序写入的（先写入前面的 block，写满后继续写下一个）
log buffer 中有一个指针 buf_free，来标识该位置之前都是填满的 block，该位置之后都是空闲区域

MySQL 规定对底层页面的一次原子访问称为一个 Mini-Transaction（MTR），比如在 B+ 树上插入一条数据就算一个 MTR

一个事务包含若干个 MTR，一个 MTR 对应一组若干条 redo log，一组 redo log 是不可分割的，在进行数据恢复时也把一组 redo log 当作一个不可分割的整体处理
不是每生成一条 redo 日志就将其插入到 log buffer 中，而是一个 MTR 结束后将一组 redo 日志写入

InnoDB 的 redo log 是固定大小的，redo 日志在磁盘中以文件组的形式存储，同一组中的每个文件大小一样格式一样

innodb_log_group_home_dir 代表磁盘存储 redo log 的文件目录，默认是当前数据目录
innodb_log_file_size 代表文件大小，默认 48M，innodb_log_files_in_group 代表文件个数，默认 2 最大 100，所以日志的文件大小为 innodb_log_file_size * innodb_log_files_in_group

redo 日志文件也是由若干个 512 字节的 block 组成，日志文件的前 2048 个字节（前 4 个 block）用来存储一些管理信息，以后的用来存储 log buffer 中的 block 镜像

注意：block 并不代表一组 redo log，一组日志可能占用不到一个 block 或者几个 block，依赖于 MTR 的大小

日志刷盘

redo log 需要在事务提交时将日志写入磁盘，但是比 Buffer Pool 修改的数据写入磁盘的速度快，原因：

刷脏是随机 IO，因为每次修改的数据位置随机；redo log 和 binlog 都是顺序写，磁盘的顺序 IO 比随机 IO 速度要快
刷脏是以数据页（Page）为单位的，一个页上的一个小修改都要整页写入；redo log 中只包含真正需要写入的部分，好几页的数据修改可能只记录在一个 redo log 页中，减少无效 IO
组提交机制，可以大幅度降低磁盘的 IO 消耗

InnoDB 引擎会在适当的时候，把内存中 redo log buffer 持久化（fsync）到磁盘，具体的刷盘策略：

在事务提交时需要进行刷盘，通过修改参数 innodb_flush_log_at_trx_commit 设置：
- 0：表示当提交事务时，并不将缓冲区的 redo 日志写入磁盘，而是等待后台线程每秒刷新一次
- 1：在事务提交时将缓冲区的 redo 日志同步写入到磁盘，保证一定会写入成功（默认值）
- 2：在事务提交时将缓冲区的 redo 日志异步写入到磁盘，不能保证提交时肯定会写入，只是有这个动作。日志已经在操作系统的缓存，如果操作系统没有宕机而 MySQL 宕机，也是可以恢复数据的
写入 redo log buffer 的日志超过了总容量的一半，就会将日志刷入到磁盘文件，这会影响执行效率，所以开发中应避免大事务
服务器关闭时
并行的事务提交（组提交）时，会将将其他事务的 redo log 持久化到磁盘。假设事务 A 已经写入 redo log buffer 中，这时另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么事务 B 要把 redo log buffer 里的日志全部持久化到磁盘，因为多个事务共用一个 redo log buffer，所以一次 fsync 可以刷盘多个事务的 redo log，提升了并发量

服务器启动后 redo 磁盘空间不变，所以 redo 磁盘中的日志文件是被循环使用的，采用循环写数据的方式，写完尾部重新写头部，所以要确保头部 log 对应的修改已经持久化到磁盘

日志序号

lsn (log sequence number) 代表已经写入的 redo 日志量、flushed_to_disk_lsn 指刷新到磁盘中的 redo 日志量，两者都是全局变量，如果两者的值相同，说明 log buffer 中所有的 redo 日志都已经持久化到磁盘

工作过程：写入 log buffer 数据时，buf_free 会进行偏移，偏移量就会加到 lsn 上

MTR 的执行过程中修改过的页对应的控制块会加到 Buffer Pool 的 flush 链表中，链表中脏页是按照第一次修改的时间进行排序的（头插），控制块中有两个指针用来记录脏页被修改的时间：

oldest_modification：第一次修改 Buffer Pool 中某个缓冲页时，将修改该页的 MTR 开始时对应的 lsn 值写入这个属性
newest_modification：每次修改页面，都将 MTR 结束时全局的 lsn 值写入这个属性，所以该值是该页面最后一次修改后的 lsn 值

全局变量 checkpoint_lsn 表示当前系统可以被覆盖的 redo 日志总量，当 redo 日志对应的脏页已经被刷新到磁盘后，该文件空间就可以被覆盖重用，此时执行一次 checkpoint 来更新 checkpoint_lsn 的值存入管理信息（刷脏和执行一次 checkpoint 并不是同一个线程），该值的增量就代表磁盘文件中当前位置向后可以被覆盖的文件的量，所以该值是一直增大的

checkpoint：从 flush 链表尾部中找出还未刷脏的页面，该页面是当前系统中最早被修改的脏页，该页面之前产生的脏页都已经刷脏，然后将该页 oldest_modification 值赋值给 checkpoint_lsn，因为 lsn 小于该值时产生的 redo 日志都可以被覆盖了

但是在系统忙碌时，后台线程的刷脏操作不能将脏页快速刷出，导致系统无法及时执行 checkpoint ，这时需要用户线程从 flush 链表中把最早修改的脏页刷新到磁盘中，然后执行 checkpoint

java

write pos ------- checkpoint_lsn // 两值之间的部分表示可以写入的日志量，当 pos 追赶上 lsn 时必须执行 checkpoint

使用命令可以查看当前 InnoDB 存储引擎各种 lsn 的值：

sql

SHOW ENGINE INNODB STATUS\G

崩溃恢复

恢复的起点：在从 redo 日志文件组的管理信息中获取最近发生 checkpoint 的信息，从 checkpoint_lsn 对应的日志文件开始恢复

恢复的终点：扫描日志文件的 block，block 的头部记录着当前 block 使用了多少字节，填满的 block 总是 512 字节，如果某个 block 不是 512 字节，说明该 block 就是需要恢复的最后一个 block

恢复的过程：按照 redo log 依次执行恢复数据，优化方式

使用哈希表：根据 redo log 的 space id 和 page number 属性计算出哈希值，将对同一页面的修改放入同一个槽里，可以一次性完成对某页的恢复，避免了随机 IO
跳过已经刷新到磁盘中的页面：数据页的 File Header 中的 FILE_PAGE_LSN 属性（类似 newest_modification）表示最近一次修改页面时的 lsn 值，数据页被刷新到磁盘中，那么该页 lsn 属性肯定大于 checkpoint_lsn

参考书籍：https://book.douban.com/subject/35231266/

工作流程

日志对比

MySQL 中还存在 binlog（二进制日志）也可以记录写操作并用于数据的恢复，保证数据不丢失，二者的区别是：

作用不同：redo log 是用于 crash recovery （故障恢复），保证 MySQL 宕机也不会影响持久性；binlog 是用于 point-in-time recovery 的，保证服务器可以基于时间点恢复数据，此外 binlog 还用于主从复制
层次不同：redo log 是 InnoDB 存储引擎实现的，而 binlog 是MySQL的 Server 层实现的，同时支持 InnoDB 和其他存储引擎
内容不同：redo log 是物理日志，内容基于磁盘的 Page；binlog 的内容是二进制的，根据 binlog_format 参数的不同，可能基于SQL 语句、基于数据本身或者二者的混合（日志部分详解）
写入时机不同：binlog 在事务提交时一次写入；redo log 的写入时机相对多元

binlog 为什么不支持崩溃恢复？

binlog 记录的是语句，并不记录数据页级的数据（哪个页改了哪些地方），所以没有能力恢复数据页
binlog 是追加写，保存全量的日志，没有标志确定从哪个点开始的数据是已经刷盘了，而 redo log 只要在 checkpoint_lsn 后面的就是没有刷盘的

更新记录

更新一条记录的过程：写之前一定先读

在 B+ 树中定位到该记录，如果该记录所在的页面不在 Buffer Pool 里，先将其加载进内存
首先更新该记录对应的聚簇索引，更新聚簇索引记录时：
- 更新记录前向 undo 页面写 undo 日志，由于这是更改页面，所以需要记录一下相应的 redo 日志
  注意：修改 undo 页面也是在修改页面，事务只要修改页面就需要先记录相应的 redo 日志
- 然后记录对应的 redo 日志（等待 MTR 提交后写入 redo log buffer），最后进行真正的更新记录
更新其他的二级索引记录，不会再记录 undo log，只记录 redo log 到 buffer 中
在一条更新语句执行完成后（也就是将所有待更新记录都更新完了），就会开始记录该语句对应的 binlog 日志，此时记录的 binlog 并没有刷新到硬盘上，还在内存中，在事务提交时才会统一将该事务运行过程中的所有 binlog 日志刷新到硬盘

假设表中有字段 id 和 a，存在一条 id = 1, a = 2 的记录，此时执行更新语句：

sql

update table set a=2 where id=1;

InnoDB 会真正的去执行把值修改成 (1,2) 这个操作，先加行锁，在去更新，并不会提前判断相同就不修改了

参考文章：https://mp.weixin.qq.com/s/wcJ2KisSaMnfP4nH5NYaQA

两段提交

当客户端执行 COMMIT 语句或者在自动提交的情况下，MySQL 内部开启一个 XA 事务，分两阶段来完成 XA 事务的提交：

sql

update T set c=c+1 where ID=2;

流程说明：执行引擎将这行新数据读入到内存中（Buffer Pool）后，先将此次更新操作记录到 redo log buffer 里，然后更新记录。最后将 redo log 刷盘后事务处于 prepare 状态，执行器会生成这个操作的 binlog，并把 binlog 写入磁盘，完成提交

两阶段：

Prepare 阶段：存储引擎将该事务的 redo 日志刷盘，并且将本事务的状态设置为 PREPARE，代表执行完成随时可以提交事务
Commit 阶段：先将事务执行过程中产生的 binlog 刷新到硬盘，再执行存储引擎的提交工作，引擎把 redo log 改成提交状态

存储引擎层的 redo log 和 server 层的 binlog 可以认为是一个分布式事务，都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致，也有利于主从复制，更好的保持主从数据的一致性

数据恢复

系统崩溃前没有提交的事务的 redo log 可能已经刷盘（定时线程或者 checkpoint），怎么处理崩溃恢复？

工作流程：获取 undo 链表首节点页面的 undo segement header 中的 TRX_UNDO_STATE 属性，表示当前链表的事务属性，事务状态是活跃（未提交）的就全部回滚，如果是 PREPARE 状态，就需要根据 binlog 的状态进行判断：

如果在时刻 A 发生了崩溃（crash），由于此时 binlog 还没完成，所以需要进行回滚
如果在时刻 B 发生了崩溃，redo log 和 binlog 有一个共同的数据字段叫 XID，崩溃恢复的时候，会按顺序扫描 redo log：
- 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，说明 binlog 也已经记录完整，直接从 redo log 恢复数据
- 如果 redo log 里面的事务只有 prepare，就根据 XID 去 binlog 中判断对应的事务是否存在并完整，如果完整可以恢复数据

判断一个事务的 binlog 是否完整的方法：

statement 格式的 binlog，最后会有 COMMIT
row 格式的 binlog，最后会有一个 XID event
MySQL 5.6.2 版本以后，引入了 binlog-checksum 参数用来验证 binlog 内容的正确性（可能日志中间出错）

参考文章：https://time.geekbang.org/column/article/73161

刷脏优化

系统在进行刷脏时会占用一部分系统资源，会影响系统的性能，产生系统抖动

一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长
日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的

InnoDB 刷脏页的控制策略：

innodb_io_capacity 参数代表磁盘的读写能力，建议设置成磁盘的 IOPS（每秒的 IO 次数）
刷脏速度参考两个因素：脏页比例和 redo log 写盘速度
- 参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%，InnoDB 会根据当前的脏页比例，算出一个范围在 0 到 100 之间的数字
- InnoDB 每次写入的日志都有一个序号，当前写入的序号跟 checkpoint 对应的序号之间的差值，InnoDB 根据差值算出一个范围在 0 到 100 之间的数字
- 两者较大的值记为 R，执行引擎按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度
innodb_flush_neighbors 参数置为 1 代表控制刷脏时检查相邻的数据页，如果也是脏页就一起刷脏，并检查邻居的邻居，这个行为会一直蔓延直到不是脏页，在 MySQL 8.0 中该值的默认值是 0，不建议开启此功能

一致特性

一致性是指事务执行前后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。

数据库的完整性约束包括但不限于：实体完整性（如行的主键存在且唯一）、列完整性（如字段的类型、大小、长度要符合要求）、外键约束、用户自定义完整性（如转账前后，两个账户余额的和应该不变）

实现一致性的措施：

保证原子性、持久性和隔离性，如果这些特性无法保证，事务的一致性也无法保证
数据库本身提供保障，例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等
应用层面进行保障，例如如果转账操作只扣除转账者的余额，而没有增加接收者的余额，无论数据库实现的多么完美，也无法保证状态的一致

Q.E.D.

基本介绍 ​

事务管理 ​

基本操作 ​

提交方式 ​

事务 ID ​

隔离级别 ​

四种级别 ​

加锁分析 ​

原子特性 ​

实现方式 ​

DML 解析 ​

INSERT ​

DELETE ​

UPDATE ​

回滚日志 ​

隔离特性 ​

实现方式 ​

并发控制 ​

实现原理 ​

隐藏字段 ​

版本链 ​

读视图 ​

工作流程 ​

二级索引 ​

RC RR ​

持久特性 ​

实现方式 ​

重做日志 ​

日志缓冲 ​

日志刷盘 ​

日志序号 ​

崩溃恢复 ​

工作流程 ​

日志对比 ​

更新记录 ​

两段提交 ​

数据恢复 ​

刷脏优化 ​

一致特性 ​

基本介绍

事务管理

基本操作

提交方式

事务 ID

隔离级别

四种级别

加锁分析

原子特性

实现方式

DML 解析

INSERT

DELETE

UPDATE

回滚日志

隔离特性

实现方式

并发控制

实现原理

隐藏字段

版本链

读视图

工作流程

二级索引

RC RR

持久特性

实现方式

重做日志

日志缓冲

日志刷盘

日志序号

崩溃恢复

工作流程

日志对比

更新记录

两段提交

数据恢复

刷脏优化

一致特性