事务篇

数据库事务概述

• 事务是数据库区别于文件系统的重要特性之一。当我们有了事务就可以让数据库始终保持一致性，同时还可以通过事务机制恢复到某个时间点，这样可以保证已提交到数据库的修改不会因为系统崩溃而丢失。

存储引擎支持情况

• show engines ;

  可见，MySQL中只有InnoDB是支持事务的

  

基本概念

• 事务：一组逻辑操作单元（一组DML操作），使数据从一种状态变换到另一种状态。
• 事务处理的原则：保证所有事务都作为 一个工作单元 来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交( commit )，那么这些修改就 永久 地保存下来；要么数据库管理系统将 放弃 所作的所有 修改 ，整个事务回滚( rollback )到最初状态。

事务的ACID特性

• 原子性（Atomicity）：事务是一个不可分割的工作单位，要么同时成功，要么同时失败
• 一致性（Consistency）：事务执行前后，数据从一个合法性状态变换到另外一个合法性状态。这里的合法性状态指的是业务上的合法性。
• 隔离性（Isolation）：事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
• 持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的 ，接下来的其他操作和数据库故障不应该对其有任何影响。持久性是通过事务日志保证的

ACID四大特性中，原子性是基础，隔离性是手段，一致性是约束条件，持久性是我们的目的。

事务的状态

事务是一个抽象的概念，对应着一个或者多个数据库操作，MySQL根据这些操作所执行的不同阶段把事务大致划分成几个状态：

• Active：事务对应的数据库操作正在执行过程中时，我们就说该事务处在 活动的 状态。
• Partially Committed：当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处在 部分提交的 状态。
• Failed：当事务处在 活动的 或者 部分提交的 状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在 失败的 状态。
• Aborted：如果事务执行了一部分而变为 失败的 状态，那么就需要把已经修改的事务中的操作还原到事务执行前的状态。换句话说，就是要撤销失败事务对当前数据库造成的影响。我们把这个撤销的过程称之为 回滚 。当 回滚 操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了 中止的 状态。
• Committed：当一个处在 部分提交的 状态的事务将修改过的数据都 同步到磁盘 上之后，我们就可以说该事务处在了 提交的 状态。

  

如何使用事务

显式事务

1. 开启事务
   • BEGIN;
   • STRAT TRANSACTION：可以添加修饰符
     • READ ONLY ：标识当前事务是一个 只读事务 ，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。（对于临时表CREATE TEMPORARY TABLE来说，由于只在当前会话中可见，开启只读事务是可以增删改的）
     • READ WRITE（默认）：标识当前事务是一个 读写事务 ，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。
     • READ ONLY和READ WRITE只能二选一，都可以搭配 WITH CONSISTENT SNAPSHOT ：启动一致性读。
2. 一系列DML操作
3. 提交事务（COMMIT;）或者回滚事务（ROLLBACK;）
   • SAVEPOINT保存点，可以在一个事务中定义保存点。当事务比较复杂的时候，并且出现了回滚操作，并不一定只能回滚到事务开启之前的状态。我们可以回滚到保存点接着进行后面的操作。注意：回滚到保存点是中间状态，后面还是需要提交或者回滚的
   • 创建保存点：SAVEPOINT 保存点名称
   • 删除保存点：RELEASE SAVEPOINT 保存点名称

隐式事务

• MySQL中的系统变量autocommit

• autocommit表示自动提交。我们每一个DML语句都是一个独立的事务，执行完之后都会自动提交

  SHOW VARIABLES LIKE 'autocommit';

  

  SET autocommit = FALSE;关闭自动提交，只针对DML操作生效

• 当我们手动开启事务，即使autocommit开启，也需要我们手动提交（回滚）事务

隐式提交数据的情况

• 数据定义语言：DDL，包括CREATE、ALTER、DROp数据库、表、视图的时候，就会隐式提交前边语句所属的事务。
• 使用或修改mysql库中的表
• 前一个事务还没提交，后一个事务开始了。那么前一个事务就会隐式提交。
• Lock、Unlock Table的时候也会隐式提交前面语句所属的事务
• Load Data加载数据的时候，也会隐式提交之前语句的事务

使用示例

• 事务演示

  

  此时，在另一个Session中是查不到数据的，只能在当前Session中查询到数据

  

  执行提交

  

  回滚

  

  因为不手动开启事务的话，autocommit会自动提交DML操作。

  也就是第一个insert的事务已经提交了，第二个insert事务执行出现了错误，rollback只针对第二个insert

• SAVEPOINT

  

  回滚到保存点

  

  注意此时事务并未结束，需要根据具体的业务进行后续操作

  

事务隔离级别

MySQL是多线程服务的，也就是说MySQL可以同时处理多个事务。事务具有隔离性，也即某个事务在对某个数据进行访问时，其他事务应该排队等待，当事务提交之后，其他事务才可以继续访问这个数据。但是这样对性能影响太大，我们既想保证事务的隔离性，又想让服务器处理访问同一个数据的多个事务时性能尽可能的高，就需要在二者（隔离性和并发性）之间进行权衡。是为了解决并发事务访问同一个数据时造成的问题而出现的。

数据准备

CREATE TABLE student1
(
    studentno INT,
    name      VARCHAR(20),
    class     varchar(20),
    PRIMARY KEY (studentno)
) Engine = InnoDB
  CHARSET = utf8;
INSERT INTO student1 VALUES(1, 'ChasingWind', '1班');

数据并发的问题

分析在并发进行同一个数据访问的时候可能出现的问题。

• 脏写（Dirty Write）：对于两个并发的事务A和事务B，如果事务A修改了另一个**未提交**事务B修改过的数据，就以为这发生了脏写。

  

  导致的问题：当事务A提交之后，事务B回滚了，那么A读取到的数据本应该是张三，但是事务B回滚了，将会回滚到两个事务开始之前的值。

  注意：这里只是说明脏写的现象。因为在默认的情况下，MySQL默认隔离级别也不会出现脏写的现象

  

• 脏读（Dirty Read）：对于两个并发的事务A和事务B，如果事务A读取了已经被事务B更新但还没有被提交的数据就成为脏读。

  因为如果事务B回滚，那么事务A读取的内容就是临时且无效的。就相当于事务A读取到了一个不存在的数据

  

• 不可重复读（Non-Repeatable Read）：事务A读取一个数据，事务B更新了这个数据，然后事务A再次读取这个数据，两次读取的值不相同就表示发生了不可重复读。

  

  说明：这里的事务B，其实是隐式事务。也就是UPDATE之后就COMMIT了，而在事务A中，不同时间读取同一个值（一直处于同一个事务中）都不一样。

• 幻读（Phantom）：事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据 称为幻读

  

  注意：事务B更新之后也提交了。我们把事务A读取到的新插入的数据称为幻影记录

SQL中的4种隔离级别

• 对于并发性和隔离性同时保证情况下的上述4种可能出现的问题，就需要对二者进行权衡。这些问题有轻重缓急之分：按照严重程度从重到轻排序：脏写>脏读>不可重复读>幻读
• 我们愿意舍弃一部分隔离性来换取一部分性能在这里就体现在：设立一些隔离级别，隔离级别越低，并发问题发生的就越多。 SQL标准中设立了4个 隔离级别 ：
  • READ UNCOMMITTED ：读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。
  • READ COMMITTED ：读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可重复读、幻读问题仍然存在。
  • REPEATABLE READ ：可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍然存在。这是MySQL的默认隔离级别。
  • SERIALIZABLE ：可串行化，确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避免脏读、不可重复读和幻读。
• 对于脏写，是最基础的保障，无论哪一种隔离级别都可以保证不会脏写。
• 不同隔离级别和并发性能的关系

  

MySQL支持的4种隔离级别

• 上面只是SQL标准的4种隔离级别。对于不同的数据库，支持的隔离级别不一样：Oracle只支持READ COMMITTED和SERIALIZABLE，MySQL上述4种都支持。

• MySQL默认的隔离级别为REPEATABLE READ

  SHOW VARIABLES LIKE 'transaction_isolation';

  

• 修改MySQL隔离级别
  GLOBAL和SESSION的区别

  SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别' 
  #其中，隔离级别格式： 
  READ-UNCOMMITTED
  READ-COMMITTED 
  REPEATABLE-READ 
  SERIALIZABLE
  

  • GLOBAL全局范围，但是当前会话无效，之后开启的会话生效
  • SESSION当前会话有效

MySQL不同隔离级别的演示

• 读未提交隔离级别下的脏读

  此时MySQL事务隔离级别是READ-UNCOMMITTED

  

  假如此时事务A回滚了，那么事务B读取到的未提交的数据就是临时且无效的

  

• 读已提交解决脏读，未解决不可重复读

  

• 可重复读隔离级别解决脏读、不可重复读，存在幻读

  解决脏读，不可重复读

  

  幻读演示

  

MySQL事务日志

事务有4种特性：原子性、一致性、隔离性和持久性。那么事务的四种特性到底是基于什么机制实现呢？

• 事务的隔离性由锁机制实现。
• 事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。
  • REDO LOG 称为 重做日志 ，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。
  • UNDO LOG 称为 回滚日志 ，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。
• Redo Log和Undo Log的区别与联系
  • Redo Log和Undo Log都是存储引擎层生成的
  • Redo Log记录的是物理级别上的页的修改操作，比如在页号X偏移量Y上写入了数据Z。主要为了保证数据的可靠性。
  • Undo Log记录的是逻辑操作的日志。比如对某一行数据进行了INSERT操作，那么Undo Log就会记录一条与之相反的Delete操作。主要用于事务的回滚（借助记录的逆操作）和一致性非锁定读（Undo Log回滚记录到某个特定的版本—MVCC，多版本并发控制）

Redo Log

• InnoDB存储引擎中是以页为单位来管理存储空间的。在真正访问页之前，需要把磁盘上的页缓存到内存中的**Buffer Pool**之后才可以进行访问。所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页（内存中和磁盘中同一个页不相同）会以一定的频率（CheckPoint机制）刷入磁盘。通过Buffer Pool缓冲池解决内存和磁盘之间速度的差距。

为什么需要Redo Log

• 由于CheckPoint机制并不是每一次内存中的变更都会立即刷新到磁盘。就有可能导致逻辑层面事务提交了，内存中也做了变更，然后MySQL服务器宕机了，此时内存中的数据还未刷到磁盘中，导致并不能保持持久性。
• 以上简单的解决办法：内存出现了修改就立即同步到磁盘，但是存在很多问题：
  1. 修改量与刷新磁盘工作量不成比例：InnoDB是以页为单位进行管理的，也就是说即使我们只修改一个字节，也需要加载一整个页进内存
  2. 随机IO比较慢：一个事务可能包含很多语句，可能导致一个事务中涉及到多个不相邻的页面。刷新到磁盘的时候出现随机IO。
• 另一个解决方法：我们只是想让已经提交了的事务对数据库中数据所做的修改永久生效，即使后来系统崩溃，在重启后也能把这种修改恢复出来。所以我们其实没有必要在每次事务提交时就把该事务在内存中修改过的全部页面刷新到磁盘，只需要把 修改 了哪些东西 记录一下 就好。比如，某个事务将系统表空间中 第10号 页面中偏移量为 100 处的那个字节的值 1 改成 2 。我们只需要记录一下：将第0号表空间的10号页面的偏移量为100处的值更新为 2 。
• InnoDB存储引擎的事务采用了WAL技术（Write-Ahead Logging），思想就是先写日志，再写磁盘，只有日志写入成功，才算事务提交成功。这里的日志就是Redo Log，即使宕机了，也可以通过Redo Log来恢复，保证事务的持久性

Redo Log的好处

1. Redo Log降低了刷盘频率，以一定频率刷新到磁盘
2. Redo Log占用的空间非常小，因为只记录某个位置做什么修改

Redo Log特点

1. Redo Log是顺序写入磁盘的。一个事务中可能包含多个SQL操作，每执行一条语句，就可能产生若干条Redo Log，这些日志是按照产生的顺序写入磁盘的，也就是顺序IO，比随机IO快。
2. 事务执行过程中，Redo Log不断记录。

Redo Log的组成

• Redo Log可以简单分为两部分：
  • Redo Log缓冲区 (Redo Log Buffer) ，保存在内存中，是易失的
  • Redo Log文件 (Redo Log File) ，保存在硬盘中，是持久的。

• Redo Log缓冲区 (Redo Log Buffer)
  MySQL服务器启动的时候会向操作系统申请一片大小为innodb_log_buffer_size的连续内存空间。这片内存空间被划分为若干个连续的Redo Log Block，一个Redo Log Block占用512字节大小

  

  Redo Log Buffer默认16M，最大4096M，最小1M

  

• Redo Log文件 (Redo Log File)

  ib_logfile0和ib_logfile1就是Redo Log文件，我们从Redo Log恢复的时候使用的就是这个文件

  可以看到两个文件大小相同，是因为刚开始就已经申请这么大的大小，后面进行追加。

  

Redo Log整体流程

Redo Log Buffer刷盘策略

• 这里的刷盘策略指的是将Redo Log Buffer中的数据刷盘到Redo Log File中
• Redo Log Buffer刷盘到Redo Log File的过程并不是真正的刷到磁盘中去，只是刷入到文件系统缓存 （Page Cache）中去（这是现代操作系统为了提高文件写入效率做的一个优化），真正的写入会交给系统自己来决定（比如Page Cache足够大了）。那么对于InnoDB来说就存在一个问题，如果交给系统来同步，同样如果系统宕机，那么数据也丢失了（虽然整个系统宕机的概率还是比较小的）。

• 针对只刷盘到Page Cache存在的问题，MySQL提供innodb_flush_log_at_trx_commit参数设置，该参数控制Commit提交事务时，如何将 Redo Log Buffer 中的日志刷新到 Redo Log File 中。它支持三种策略：

  1. 设置为0 ：表示每次事务提交时不进行刷盘操作。（系统默认master thread每隔1s进行一次重做日志的同步）

     

  2. 设置为1 ：表示每次事务提交时都将进行同步刷盘操作（刷到磁盘）（ 默认值 ）

  3. 设置为2 ：表示每次事务提交时都只把 Redo Log Buffer 内容写入 Page Cache，不进行同步。由OS自己决定什么时候同步到磁盘文件。

不同刷盘策略演示

1. innodb_flush_log_at_trx_commit设置为0
   • 每1秒进行一次日志的刷盘操作，因此可能丢失1s的数据
   • 后台线程是将缓冲池的数据刷新到磁盘中
2. innodb_flush_log_at_trx_commit设置为1
   • 事务一提交，就会将Redo Log Buffer同步刷新到磁盘
   • 同时，如果事务持续时间较长，后台线程也会每隔一秒将Redo Log Buffer中的数据刷新到磁盘
   • 这种策略，只要事务提交成功，Redo Log就一定在硬盘中，不会有数据丢失。
3. innodb_flush_log_at_trx_commit设置为2
   • 只将数据刷新到Page Cache中，等待后续OS刷新到磁盘
   • 可能存在OS宕机导致未刷新到磁盘的风险。

三种对比：

• 设置为1花费时间最多
• 设置为1最安全

Redo Log Buffer写入过程

• MySQL把对底层页面汇总的一次原子访问的过程称为一个Mini-Transaction，简称mtr。比如，向某个索引对应的B+树中插入一条记录的过程就是一个mtr。

• 一个mtr可以包含一组redo日志，在进行崩溃恢复时，这一组日志作为一个不可分割的整体。

  

• Redo Log写入Redo Log Buffer

  顺序写入Log Buffer的Log Block Body中，并且提供一个全局变量buf_free记录当前已经记录到哪个block的哪个偏移量

  

  每个mtr产生一组Redo Log，那么这一组Redo Log是一个整体。

  并不是每生成一条Redo Log，就将其写入到Redo Log Buffer中，而是每个mtr产生的日志先暂存起来，等到mtr结束的时候，将产生的一组日志复制到Log Buffer中。

  比如一个事务包含2个mtr，对于两个事务产生的日志情况如下

  

  由于事务可以是并发执行的，所以在Log Buffer中可能的写入顺序如下

  每当一个mtr执行完，就会将对应的一组Redo Log复制到Log Buffer中

  

• Redo Log Block的结构
  一个Redo Log Block由日志头、日志体、日志尾组成。
  

Redo Log File

1. 相关参数设置
   • innodb_log_group_home_dir：指定Redo Log文件组所在的路径，默认值./，表示在数据库的数据目录下。MySQL的默认数据目录（ var/lib/mysql ）
   • innodb_log_files_in_group：指明Redo Log File的个数，命名方式如：ib_logfile0，iblogfile1… iblogfilen。默认2个，最大100个
   • innodb_log_file_size：单个 Redo Log 文件设置大小，默认值为 48M 。

2. 日志文件组

   • 磁盘上的Redo Log File不止一个，而是以一个日志文件组的形式出现的。这些文件以ib_logfile[x]的形式进行命名，每个文件的大小是一样的

   • 在将Redo Log写入日志文件时，是从ib_logfile0开始写入的，ib_logfile0写满了就写进ib_logfile1。如果最后一个写满了，就重新回到ib_logfile0接着写

     

   • 如何避免覆盖未刷盘的Redo Log？
     在整个日志文件中还有两个重要的属性write pos和checkpoint

     • write pos：每次Redo Log刷到Redo Log File中，就会后移。表示当前在日志文件中的位置
     • checkpoint：当日志文件中记录的内容被刷新到磁盘后，就表示当前位置之前的内容已经可以被覆盖了。

由于日志文件组是循环使用的方式，可能存在write pos 追上 checkpoint ，表示日志文件组满了，这时候不能再写入新的 redo log记录，MySQL 得停下来，清空一些记录，把 checkpoint 推进一下。

Undo Log

Redo Log是事务持久性的保证，Undo Log是事务原子性的保证。在事务中更新数据（增删改）的前置操作其实是要先写入一个 Undo Log 。

如何理解Undo Log

• 事务需要保证原子性，也就是事务操作要么全部完成，要么什么都不做。当事务执行到一半出现错误或者回滚，就需要把数据更改成原先的样子。
• 每当我们要对一条记录做改动（增删改）时，都会将改动之前的逆操作记录下来。
  • INSERT之前，记录下这条记录，并生成DELETE语句
  • DELETE之前，记录下这条记录，并生成INSERT语句
  • UPDATE之前，记录下这条记录的旧值，并生成一条相反的**UPDATE**语句，更新为旧值
• MySQL将这些为了回滚而记录的日志称为Undo Log。查询的时候不会记录
• Undo Log也需要持久性的保护，所以Undo Log也会产生Redo Log

Undo Log作用

1. 回滚数据：将数据逻辑回滚到未修改之前的样子
2. MVCC（多版本并发控制）：当用户读取一行记录时，如果这行记录被其他事务占用，当前事务可以通过Undo Log读取未修改之前的行版本信息，实现非锁定读取。

Undo Log存储结构

• 回滚段与Undo页

  • InnoDB对Undo Log的管理采用的是段的方式，也就是回滚段（Rollback Segment）。每个回滚段记录了1024个Undo Log Segment（根据页面大小的不同而不同，16KB是1024），而在每一个Undo Log Segment中进行Undo页的申请。每一个事务申请一个Undo Log Segment，类似于分段锁

    

  • innodb_undo_directory:Undo Log目录，默认./，表示在数据库的数据目录下。MySQL的默认数据目录（ var/lib/mysql ）

  • innodb_undo_logs：设置rollback segment的个数，默认值128
• Undo页重用
  • 当我们开启一个事务需要写Undo Log的时候，就得先去Undo Log Segment中找到一个空闲的位置，当有空闲Segment的时候，就去申请Undo页，在这个申请到的Undo页中进行Undo Log的写入。
  • 但是每一个事务都分配一个页，是很浪费空间的。事实上Undo页是可重用的。当事务提交之后，并不会立刻回收Undo页，会判断当前Undo页使用空间是否小于3/4，是的话，当前Undo页就可以被重用，否则会被回收。
• 回滚段与事务
  1. 每个事务只会使用一个回滚段，一个回滚段在同一时刻可能会服务于多个事务（每个事务对应于一个Undo Log Segement）。
  2. 当一个事务开始的时候，会指定一个回滚段，在事务进行的过程中，当数据被修改时，原始的数据会被复制到回滚段。
  3. 在回滚段中，事务会不断填充盘区，直到事务结束或所有的空间被用完。如果当前的盘区不够用，事务会在段中请求扩展下一个盘区，如果所有已分配的盘区都被用完，事务会覆盖最初的盘区或者在回滚段允许的情况下扩展新的盘区来使用。
  4. 回滚段存在于undo表空间中，在数据库中可以存在多个undo表空间，但同一时刻只能使用一个undo表空间。
  5. 当事务提交时，InnoDB存储引擎会做以下两件事情：
     • 将undo log放入列表中，以供之后的purge操作（purge的连个作用：清理Undo页和清理Page中带有Delete_Bit标识的数据行）
     • 判断undo log所在的页是否可以重用，若可以分配给下个事务使用
• 回滚段中的数据分类
  • 未提交的回滚数据(uncommitted undo information)
  • 已经提交但未过期的回滚数据(committed undo information) ：关联的事务已经提交，但是还在undo retention参数设置的时间内
  • 已经提交并过期的数据(expired undo information) ：关联的事务已提交并且保存时间超过了undo retention参数设置的时间。

事务提交之后不能立即删除Undo Log以及Undo Log所在的页。因为MVCC，其他事务可以通过Undo Log读取未修改之前的行版本信息，实现非锁定读取。

Undo Log类型

• Insert Undo Log：针对Insert操作产生的Undo Log。只对事务本身可见，所以此Undo Log可以在事务提交之后删除
• Update Undo Log：针对Delete和Update产生的Undo Log。因为其他事务可能通过MVCC机制从Undo Log中读取数据，所以不能再事务提交之后立马删除。

Undo Log生成过程

• 在之前的行格式中，每一行数据出除了数据本身还有几个隐藏列

  • DB_ROW_ID：表中没有显式定义主键并且也没有唯一索引，那么InnoDB会自动为表添加一个row_id的隐藏列作为主键
  • DB_TRX_ID：事务ID，当对某条记录发生变更时，将事务ID写入
  • DB_ROLL_PTR：回滚指针，本质上是指向Undo Log的指针。

    

• Insert操作：插入的数据会生成一条Insert Undo Log，并且回滚指针会指向它。Undo Log会记录Undo Log的序号、插入的主键和列的值，那么在Rollback的时候，就可以通过主键值把对应的数据删除即可

  

• Update操作：将未修改前的记录写入新的Undo Log。让回滚指针指向新的Undo Log。而新的Undo Log会指向旧的Undo Log。