MySQL 事务的实现原理

一、事务的特点

  • 原子性：事务中的所有操作作为一个整体像原子一样不可分割，要么全部成功，要么全部失败。
  • 一致性：事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态。一致性状态是指：
  1. 系统的状态满足数据的完整性约束(主码，参照完整性，check约束等)
  2. 系统的状态反应数据库本应描述的现实世界的真实状态，比如转账前后两个账户的金额总和应该保持不变
  • 隔离性：并发执行的事务不会相互影响，其对数据库的影响和他们串行执行时一样。比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转入钱。
  • 持久性：事务一旦提交，其对数据库的更新就是持久的。对数据库的所有更新将被保存到数据库中，不能回滚，任何事务或系统故障都不会导致数据丢失。

二、事务的实现原理

  1. 事务的原子性是通过undo log（回滚日志）实现的
  2. 事务的持久性是通过redo log 来实现的
  3. 事务的隔离性是通过（读写锁+MVCC）来实现的
  4. 事务的一致性是通过原子性，持久性，隔离性来实现的！！！

 

三、MySQL基本架构图

redo log、undo log是属于innodb存储引擎的日志；binlog、error log、relay log是属于server层的日志

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能，比如存储过程、触发器、视图等。

存储引擎层负责数据的存储和提取。InnoDB、MyISAM、Memory 等多个存储引擎。InnoDB 在5.5.5后成为默认存储引擎

 

server层讲解

连接器

负责与客户端建立链接、获取权限、位置和管理链接

mysql -h$ip -P$port -u$user -p

用户成功连接之后，连接器会到权限表里面查出该用户所拥有的权限。之后该连接之内的权限判断都会依赖已经督导的权限。即：如果修改已经连接的用户权限需要重新连接之后才能生效。

如果连接之后，客户端长时间没有操作，连接器会自动断开。这给时间是由参数wait_timeout控制的，默认时间为8小时。

mysql中长连接是指 连接成功之后，如果客户端持续有请求，则一直使用统一给连接。短链接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

大量使用长连接，内存占用过大的问题解决办法：

  • 定期断开长连接、或者在执行一个占用较大内存查询之后，断开连接
  • Mysql5.7 之后的版本，可以使用mysql_reset_connection来重新初始化连接资源

查询缓存

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。
参数 query_cache_type 设置成 DEMAN,对默认SQL语句都不使用查询缓存，对于要使用查询缓存的语句，使用 SQL_CACHE 显示指定，例如

SELECT SQL_CACHE * FROM T WHERE ID=10;

1

MYSQL 8.0 后不支持查询缓存

 

分析器

1.词法分析：从"SELECT" 识别是查询语句，把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”
2.语法分析：根据与语法规则判断SQL语句是否满足MySQL语法
一般语法错误会提示第一个出错位置，要关注的是紧接“use near”的内容

 

优化器

优化器在表里有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序

 

执行器

判断是否有权限对表的操作，如果有则调用引擎接口，进行数据处理。

 

四、日志

undo log

  • Undo Log是为了实现事务的原子性，在mysql数据库innodb存储引擎中，还用undo log来实现多版本并发控制（简称：MVCC）
  • 在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了rollback语句，系统可以利用undo log中的备份将数据恢复到事务开始之前的状态。
    • 注意：undo log是逻辑日志，根据逻辑计算出实际的值，而不需要在每一个状态的时候都存储一份数据。可以理解为：
    • 当delete一条记录时，undo log中会记录一条对应的insert记录
    • 当insert一条记录时，undo log中会记录一条对应的delete记录
    • 当update一条记录时，它记录一条对应相反的update记录

redo log

和undo log相反，redo log记录的是新数据的备份,而不是逻辑。在事务提交之前，只要将Redo log持久化即可，不需要将数据持久化，当系统崩溃时，虽然数据没有持久化，但是Redo log已经持久化了。系统可以根据Redo log的内容，将所有数据恢复到最新的状态

为什么需要redo log?

事务的持久性，具体指只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态。实现持久性的方法并不是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。因为这样会有严重的性能问题，体现为：

  1. 因为Innodb是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！
  2. 一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机 IO 写入性能太差！

因此mysql设计了redo log，具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了 (相对而言文件更小并且是顺序 IO)。

基本概念

redo log包括两部分：一个是内存中的日志缓冲 (redo log buffer)，另一个是磁盘上的日志文件 (redo log file)。mysql每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL(Write-Ahead Logging) 技术。

在计算机操作系统中，用户空间 (user space) 下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间 (kernel space) 缓冲区 (OS Buffer)。因此，redo log buffer写入redo log file实际上是先写入OS Buffer，然后再通过系统调用fsync()将其刷到redo log file中，过程如下：

三种写入方式(将redo log buffer写入redo log file的时机),可以通过innodb_flush_log_at_trx_commit参数配置

0：先写到log buffer，每秒钟写到磁盘里。如果在0.99秒时突然mysql崩溃，会丢失1秒的数据

1：当提交时，直接写入磁盘中。有性能问题，效率较低

2：写到os buffer，每秒钟写入磁盘。和0的区别是，如果是整个服务器断电，这俩都丢；如果只是mysql进程挂了，2仍然会调用fsync()写入到磁盘中去

 

 

记录形式

类比《孔乙己》的掌柜记账 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。因为更新数据时写磁盘的操作是随机写，这部分的IO消耗很大，而通过组提交（多个事务的变更统一写磁盘）的方式可以提升系统的吞吐量。
redo log 是InnoDB 引擎特有的，从开头写到末尾又回开头循环写，有crash-safe能力

 write pos 是当前记录的位置，一边写一边后移，check point 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件

启动innodb的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志 (如binlog) 要快很多。重启innodb时，首先会检查磁盘中数据页的LSN，如果数据页的LSN小于日志中的LSN，则会从checkpoint开始恢复。还有一种情况，在宕机前正处于checkpoint的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时会出现数据页中记录的LSN大于日志中的LSN，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

 

何时会擦除redo log并更新到数据文件中

  1. 系统空闲时
  2. redo log文件没有空闲空间时，即write pos追上check point的时候
  3. MySQL Server正常关闭时

磁盘文件

默认是两个文件，存在datadir目录中，文件名分别为ib_logfile0和ib_logfile1。datadir可以通过命令select @@datadir;查看：

mysql> select @@datadir;
+---------------------------------------+
| @@datadir                             |
+---------------------------------------+
| c:\wamp64\bin\mysql\mysql5.7.21\data\ |
+---------------------------------------+

文件数量和每个文件的大小可以通过变量innodb_log_files_in_group和innodb_log_file_size来设置，这两个变量都是只读变量，只能通过在配置文件中修改并重启的方式生效。redo log文件的总大小（innodb_log_file_size * innodb_log_files_in_group）一般建议配置为可以处理一个小时写操作的量，数值越大则通过checkpoint刷新的次数越少，就越能降低磁盘IO。

配置文件（my.cnf/my.ini）：

[mysqld]
innodb_log_files_in_group=4
innodb_log_file_size=1073741824

查看变量：

mysql> show variables like 'innodb_log_file%';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| innodb_log_file_size      | 1073741824 |
| innodb_log_files_in_group | 4          |
+---------------------------+------------+

查看磁盘文件：

C:\wamp64\bin\mysql\mysql5.7.21\data
λ ls -alh | grep ib_log
-rw-r--r-- 1 username 1049089 1.0G Apr 24 10:45 ib_logfile0
-rw-r--r-- 1 username 1049089 1.0G Apr 24 10:44 ib_logfile1
-rw-r--r-- 1 username 1049089 1.0G Apr 24 10:44 ib_logfile2
-rw-r--r-- 1 username 1049089 1.0G Apr 24 10:45 ib_logfile3

 

binlog

binlog用于记录数据库执行的写入性操作 (不包括查询) 信息，以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志。

binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

binlog使用场景

在实际应用中，binlog的主要使用场景有两个，分别是主从复制和数据恢复。

  1. 主从复制：在Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重放binlog从而达到主从数据一致。

  2. 数据恢复：通过使用mysqlbinlog工具来恢复数据。

binlog刷盘时机

对于InnoDB存储引擎而言，只有在事务提交时才会记录biglog，此时记录还在内存中，那么biglog是什么时候刷到磁盘中的呢？mysql通过sync_binlog参数控制biglog的刷盘时机，取值范围是0-N：

  • 0：不去强制要求，由系统自行判断何时写入磁盘；

  • 1：每次commit的时候都要将binlog写入磁盘；

  • N：每 N 个事务，才会将binlog写入磁盘。

从上面可以看出，sync_binlog最安全的是设置是1，这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能。

binlog 日志格式

binlog日志有三种格式，分别为STATMENT、ROW和MIXED。

在 MySQL 5.7.7之前，默认的格式是STATEMENT，MySQL 5.7.7之后，默认值是ROW。日志格式通过binlog-format指定。

  • STATMENT 基于SQL语句的复制 (statement-based replication, SBR)，每一条会修改数据的 sql 语句会记录到binlog中。优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 从而提高了性能；缺点：在某些情况下会导致主从数据不一致，比如执行sysdate()、slepp()等。

  • ROW 基于行的复制 (row-based replication, RBR)，不记录每条 sql 语句的上下文信息，仅需记录哪条数据被修改了。优点：不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题；缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨

  • MIXED 基于STATMENT和ROW两种模式的混合复制 (mixed-based replication, MBR)，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

update语句的执行流程

InnoDB存储引擎的表更新数据时update语句的执行流程图。浅绿色的流程在InnoDB内部执行，深绿色的部分在MySQL Server层的执行器中执行。图片以下条sql语句为例。

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

 

 执行流程图注解

（1）更新语句经过 解析，优化 生成执行计划，交由执行器调用存储引擎接口（注：执行器会多次调用存储引擎接口，并不是一次完成）

（2）查询旧值，先去内存缓冲区查看是否有数据，如果没有，从磁盘中加载到内存，并将旧值写入到 Undo log 日志中，用于回滚数据

（3）更新内存中的数据（注：磁盘仍为旧数据）

（4）将操作写入到Redo Log 中，此时处于prepare阶段

（5）Redo Log根据刷盘策略刷到磁盘

（6）准备提交事务，写入binlog 日志（注：binlog也有自己的刷盘策略）

（7）把本次更新对应的binlog文件名称和这次 更新的binlog日志在文件里的位置，都写入到redo log日志文件里去，之后在redo log日志文件里写入一个commit标 记。

（8）事务完成

其实到此数据更新就算完成了，当服务器空闲时，会将redo.log日志更新至表中

当使用过程中突然断电，redolog会将已经写入的数据按照处于prepare的数据先写入binlog再commit，最后写入数据库表中，保证数据不会丢失

 

如上图所示，redo log的写入分为两个阶段（prepare和commit），这个称作两阶段提交，保证了数据的正确性。下面我们从上图4个可能发生异常关闭的时间点来分析InnoDB如何在MySQL启动时做崩溃恢复。

Point A

如果服务器异常关闭发生在Point A以及之前的时间点，这个时候redolog 和 binlog都没有任何记录，事务还未提交，不会造成任何影响。

Point B

当服务器启动的时候发现redo log里处于prepare状态的记录，这个时候需要检查binlog是否完整包含此条redo log的更新内容（通过全局事务ID对应），发现binlog中还未包含此事务变更，则丢弃此次变更。

Point C

和Point B基本相同，只不过此时发现binlog中包含redo log的更新内容，此时事务会进行提交。

Point D

binlog中和数据库中均含有此事务的变更，没有任何影响。

 

binlog和redo log的区别

1.redo log是InnoDB引擎特有的；binlog是Server层实现的，所有引擎都可以使用
2.redo log是物理日志，记录的是 “在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如 “给ID=2这一行的c字段加1”
3.redo log是 循环写的，空间固定会用完；binlog是 可以追加写入的。“追加写”是指binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

4. redo log适用于崩溃恢复 (crash-safe)，binlog适用于主从复制和数据恢复

sync_binlog 参数设置成1的时候，表示每次事务的binlog都持久化到磁盘，可以保证MySQL异常重启后binlog不丢失

五、事务的隔离级别

隔离性与隔离级别

隔离的越严实，效率就会越低。可重复读是 MySQL 的默认级别

SQL标准的事务隔离级别(低==>高)：

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

 

 

事务并发可能出现的情况

不同的隔离级别可能导致不同的并发异常，如下

 

 

 

脏读

一个事务读到了另一个未提交事务修改过的数据

可重复读

可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据更新（UPDATE）操作。

不可重复读

对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操作。

幻读

幻读是针对数据插入（INSERT）操作来说的。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行

 

举例：

create table T(c int) engine=InooDB;
insert into T(c) values(1);

 

 

在不同隔离级别下V1、V2、V3的值

隔离级别

值返回

读未提交

V1=2,V2=2,V3=2

读提交

V1=1,V2=2,V3=2

可重复读

V1=1,V2=1,V3=2

串行化

V1=1,V2=1,V3=2

串行化：直接用加锁的方式避免并行访问，事务B执行“将1改成2”的时候，会被锁住，直到事务A提交后，事务B才可以继续执行 实现上，数据库会创建视图，访问的时候一视图的逻辑结果为准

可重复读：在事务启动时创建，整个事务存在期间都用这个视图

读提交：在每个SQL语句开始执行时创建

读未提交：直接返回记录上的最新值

配置方式： 将启动参数 transaction-isolation 的值设置成 READ-COMMITTED

隔离级别的实现原理

使用MySQL的默认隔离级别（可重复读）来进行说明。

每条记录在更新的时候都会同时记录一条回滚操作（回滚操作日志undo log）。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）。即通过回滚（rollback操作），可以回到前一个状态的值。

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

 

 

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

同时你会发现，即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的。

尽量不要使用长事务，长事务意味着系统会存在很老的事务视图，由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间

提问：回滚操作日志（undo log）什么时候删除？

MySQL会判断当没有事务需要用到这些回滚日志的时候，回滚日志会被删除。

提问：什么时候不需要了？

 

当系统里没有比这个回滚日志更早的read-view的时候。

事务的启动方式

事务启动方式

  1. 显式启动事务语句，begin 或 start transaction。配套的提交语句是commit，回滚语句是rollback
  2. set autocommit=0，这个命令会将线程的自动提交关掉，意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。可能会导致意外的长事务

可以在 information_schema 库的 innodb_trx 这个表中查询长事务

查看当前会话隔离级别

方式1

mysql> show variables like 'transaction_isolation';
+-----------------------+--------------+
| Variable_name  | Value |
+-----------------------+--------------+
| transaction_isolation | SERIALIZABLE |
+-----------------------+--------------+

方式二

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| SERIALIZABLE            |
+-------------------------+

设置隔离级别

方式1：通过set命令

SET [GLOBAL|SESSION] TRANSACTIONISOLATIONLEVELlevel;
其中level有4种值：
level: {
     REPEATABLEREAD
   | READCOMMITTED
   | READUNCOMMITTED
   | SERIALIZABLE
}

 

关键词：GLOBAL

SETGLOBALTRANSACTIONISOLATIONLEVELlevel;
* 只对执行完该语句之后产生的会话起作用
* 当前已经存在的会话无效

 

关键词：SESSION

SET SESSION TRANSACTION ISOLATION LEVEL level;
* 对当前会话的所有后续的事务有效
* 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务
* 如果在事务之间执行，则对后续的事务有效。

 

无关键词

SET TRANSACTION ISOLATION LEVEL level;
* 只对当前会话中下一个即将开启的事务有效
* 下一个事务执行完后，后续事务将恢复到之前的隔离级别

 

* 该语句不能在已经开启的事务中间执行，会报错

方式2：通过服务启动项命令

可以修改启动参数transaction-isolation的值

比方说我们在启动服务器时指定了--transaction-isolation=READ UNCOMMITTED，那么事务的默认隔离级别就从原来的REPEATABLE READ变成了READ UNCOMMITTED。

避免长事务

你现在知道了系统里面应该避免长事务，如果你是业务开发负责人同时也是数据库负责人，你会有什么方案来避免出现或者处理这种情况呢？

首先，从应用开发端来看：

  1. 确认是否使用了 set autocommit=0。这个确认工作可以在测试环境中开展，把MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成 1。
  2. 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉。
  3. 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

其次，从数据库端来看：

  1. 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
  2. Percona 的 pt-kill 这个工具不错，推荐使用；
  3. 在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
  4. 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便

 

posted @ 2021-04-12 15:02  九鹤  阅读(950)  评论(0)  编辑  收藏  举报