MySQL三大日志及事务隔离级别

一只IT攻城狮

已于 2023-02-24 13:50:06 修改

阅读量1.1k

点赞数 3

分类专栏：《数据库问题解决方案》系列文章标签： mysql innodb mvcc binlog 事务隔离级别

于 2021-03-24 11:13:49 首次发布

本文链接：https://blog.csdn.net/weixin_47061482/article/details/115163442

版权

《数据库问题解决方案》系列专栏收录该内容

30 篇文章 5 订阅

订阅专栏

在这里插入图片描述

文章目录

一、MYSQL核心日志模块
二、WAL技术
三、事务隔离级别

一、MYSQL核心日志模块

1、redo log（重做日志）

redo log也称为事务日志由InnoDB存储引擎层产生。记录的是数据库中每个页的修改，而不是某一行或某几行修改成怎样，可以用来恢复提交后的物理数据页（恢复数据页，且只能恢复到最后一次提交的位置，因为修改会覆盖之前的）。
当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。
Redolog顺序写，可以组提交，还有其他优化，性能高。（实质也是磁盘文件）
在这里插入图片描述

1）redo log三种状态分别是：

存在 redo log buffer 中，物理上是在 MySQL 进程内存中；
写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面，
持久化到磁盘，对应的是 hard disk。

事务在执行过程中，生成的 redo log 是要先写到 redo log buffer 的。日志写到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是持久化到磁盘的速度就慢多了。

2）redo log 的写入策略

InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：

设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的 redo log 写入到磁盘中。一种是，redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。另一种是，并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。
通常我们说MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。

2、undo log（回滚日志）

顾名思义，主要就是提供了回滚的作用，但其还有另一个主要作用，就是多个行版本控制(MVCC)，保证事务的原子性。在数据修改的流程中，会记录一条与当前操作相反的逻辑日志到undo log中（可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录），如果因为某些原因导致事务异常失败了，可以借助该undo log进行回滚，保证事务的完整性，所以undo log也必不可少。
Eg:假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录.

在这里插入图片描述

3、binlog（归档日志）

binlog在MySQL的server层产生，不属于任何引擎，主要记录用户对数据库操作的SQL语句（除了查询语句）。之所以将binlog称为归档日志，是因为binlog不会像redo log一样擦掉之前的记录循环写，而是一直记录（超过有效期才会被清理），如果超过单日志的最大值（默认1G，可以通过变量 max_binlog_size 设置），则会新起一个文件继续记录。
正是由于binlog有归档的作用，所以binlog主要用作主从同步和数据库基于时间点的还原，或者一些其他功能推送下游系统（如解析binlog推送待办，累计风险保额）。不会记录select，但是update没变化也会记录。
事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。每个线程有自己 binlog cache，但是共用同一份 binlog 文件。

在这里插入图片描述

图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。
图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。
write 和 fsync 的时机，是由参数 sync_binlog 控制的：

sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

查看与配置binlog格式:

show variables like 'binlog_format'

查询结果：
在这里插入图片描述

4、redo log 和 binlog 是怎么关联起来的?

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。
MySQL 怎么知道 binlog 是完整的?
一个事务的 binlog 是有完整格式的：statement 格式的 binlog，最后会有 COMMIT；row 格式的 binlog，最后会有一个 XID event。另外，在 MySQL 5.6.2 版本以后，还引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 的结果来发现。所以，MySQL 还是有办法验证事务 binlog 的完整性的。

二、WAL技术

(1)(3) 称为事务的两阶段提交.整个流程称之为WAL技术，即write ahead logging技术它的关键点就是先写日志，再写磁盘。

在这里插入图片描述

保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe.（奔溃恢复能力）
WAL 机制主要得益于两个方面：redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；组提交机制，可以大幅度降低磁盘的 IOPS 消耗。

三、事务隔离级别

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

1、SQL 标准的事务隔离级别包括：

读未提交（read uncommitted）：读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交（read committed）：读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
可重复读（repeatable read）：可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化（serializable ）：串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

show VARIABLES like '%isolation%'【事务隔离级别】

在这里插入图片描述

不同隔离级别可能导致的问题：

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
读未提交	可能	可能	可能
读已提交	不可能	可能	可能
可重复读	不可能	不可能	可能
可串行化	不可能	不可能	不可能

小结：InnoDB存储引擎默认的事务隔离级别为 REPEATABLE READ；
SQL Server 数据库为 READ COMMITTED;
Oracle数据库同样也是 READ COMMITTED;

2、MySQL 的事务启动方式：

MYSQL 事务处理主要有两种方法：
1）用 BEGIN, ROLLBACK, COMMIT来实现
• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认
2）直接用 SET 来改变 MySQL 的自动提交模式:
• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

【注】begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。

3、MVCC(多版本并发控制)

在这里插入图片描述
不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。

“快照”在 MVCC 里是怎么工作的？
InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。
在这里插入图片描述
这样，对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
如果落在黄色部分，那就包括两种情况:
- a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
- b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见;

InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。
即一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

版本未提交，不可见；
版本已提交，但是是在视图创建后提交的，不可见；
版本已提交，而且是在视图创建前提交的，可见。

从图中可以看到，第一个有效更新是事务 C，把数据从 (1,1) 改成了 (1,2)。这时候，这个数据的最新版本的 row trx_id 是 102，而 90 这个版本已经成为了历史版本。第二个有效更新是事务 B，把数据从 (1,2) 改成了 (1,3)。这时候，这个数据的最新版本（即 row trx_id）是 101，而 102 又成为了历史版本。现在事务 A 要来读数据了，它的视图数组是[99,100]。当然了，读数据都是从当前版本读起的。所以，事务 A 查询语句的读数据流程是这样的：找到 (1,3) 的时候，判断出 row trx_id=101，比高水位大，处于红色区域，不可见；接着，找到上一个历史版本，一看 row trx_id=102，比高水位大，处于红色区域，不可见；再往前找，终于找到了（1,1)，它的 row trx_id=90，比低水位小，处于绿色区域，可见。这样执行下来，虽然期间这一行数据被修改过，但是事务 A 不论在什么时候查询，看到这行数据的结果都是一致的，所以我们称之为一致性读。
这里就用到了这样一条规则：更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。

事务的可重复读的能力是怎么实现的？可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

InnoDB 的行数据有多个版本，每个数据版本有自己的 row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性。对于可重复读，查询只承认在事务启动前就已经提交完成的数据；对于读提交，查询只承认在语句启动前就已经提交完成的数据；而当前读，总是读取已经提交完成的最新版本。