MySQL事务

基础概念

事务（Transaction）是访问和更新数据库的程序执行单元；
事务中可能包含一条或多条sql语句，这些语句要么都执行，要么都不执行

逻辑架构和存储引擎

MySQL服务器从逻辑架构上分为三层：

1. 第一层：处理客户端连接、授权认证等
2. 第二层：服务器层，负责查询语句的解析、优化、缓存及内置函数的实现、存储过程等
3. 第三层：存储引擎，负责MySQL中数据的存储和提取。MySQL中服务器层不管理事务，事务由存储引擎实现
   MySQL支持事务的存储引擎包括InnoDB、NDB Cluster等，InnoDB使用最多；
   MyIsam、Memory等不支持事务

提交与回滚

# 开始事务
start transaction;
# 一条或多条业务sql
# 提交事务
commit;

# 如果执行发生异常，会调用 rollback，回滚所有已执行成功的SQL（也可以在事务中直接使用rollback回滚）

自动提交（autocommit）

• MySQL中默认采用自动提交autocommit

• 关闭自动提交

• 自动提交autocommit参数是针对连接的，在一个连接中修改了该参数，不会对其他连接产生影响
• 开启自动提交，若没有显式start transaction开始事务，每一条SQL都会被当作一个事务提交
• 关闭自动提交，所有的SQL都在一个事务中，直到执行了commit或rollback，该事务结束，同时开始另外一个事务

特殊操作

• 在MySQL中，存在一些特殊的命令，如果在事务中执行了这些命令，会马上强制执行commit提交事务；
  如DDL语句(create table/drop table/alter/table)、lock tables语句等
• 常用的select、insert、update、delete命令，都不会强制提交事务

ACID

• 原子性（Atomicity）
• 一致性（Consistency）
• 隔离性（Isolation）
• 持久性（Durability）

• 按严格的标准，只有同时满足ACID特性才是事务；但是在各大数据库厂商的实现中，真正满足的非常少
• 与其说ACID是事务必须满足的条件，不如说它们是衡量事务的四个维度

原子性

定义

Atomicity，一个事务是一个不可分割的工作单位，包含的操作要么全部成功，要么全部失败

实现原理

undo log

• MySQL事务日志：二进制日志（binlog）、错误日志、查询日志、慢查询日志等。
  InnoDB存储还提供了两种事务日志：redo log（重做日志）和undo log（回滚日志）

• InnoDB实现回滚，靠的就是undo log

  当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback导致事务需要回滚，便可以利用undo log的信息将数据回滚到修改之前的状态

• undo log属于逻辑日志，记录的是sql执行的相关信息；发生回滚时，InnoDB会根据undo log记录的内容执行“相反”的操作
  （如：insert => delete；update => 相反的update）

持久性

定义

Durability，事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响

实现原理：redo log

redo log和undo log都属于InnoDB的事务日志

redo log背景：

InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）

问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，如何保证事务的持久性？

解决：

• 当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；
• 当事务提交时，会调用fsync接口对redo log进行刷盘。
• 如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。
  redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool

问题：redo log提交事务时才进行刷盘，为什么比刷脏更快？

1. 随机IO vs. 顺序IO：刷脏是随机IO，每次修改的数据位置随机，但写redo log是顺序IO（追加操作）
2. 数据量不同：刷脏是以*数据页（Page）*为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；redo log中只包含真正需要写入的部分，无效IO大大减少

redo log与binlog

对比项	redo log	binlog
作用	用于crash recovery，保证DB宕机也不影响持久性	1. 用于point-in-time recovery，保证服务器可以基于时间点恢复数据； 2. 主从复制
层次	由InnoDB存储引擎实现	由MySQL服务器层实现，支持InnoDB和其他存储引擎
内容	物理日志，内容基于磁盘的Page	二进制，根据binlog_format参数不同，可能基于sql、数据本身或二者混合
写入时机	写入时机多元（默认情况下事务提交时也会刷盘）	事务提交时写入

• 默认情况下，事务提交会对redo log进行刷盘；参数innodb_flush_log_at_trx_commit可修改此策略
• redo log其他刷盘时机：master线程每秒一次刷盘（提高commit速度）

隔离性

定义

Isolation，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
严格的隔离性，对应了事务隔离级别中的Serializable (可串行化)，但实际应用中出于性能方面的考虑很少会使用可串行化

隔离性追求的是并发情形下事务之间互不干扰。简单分为两方面：

• (一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性
• (一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性

锁机制

• 隔离性要求同一时刻只能有一个事务对数据进行写操作，InnoDB通过锁机制来保证这一点。

• 事务在修改数据之前，需要先获得相应的锁；
• 获得锁之后，事务便可以修改数据；
• 事务操作期间，锁定数据，其他事务如果需要修改数据，需要等待当前事务提交或回滚释放锁

• 按照粒度，分为表锁、行锁和其他介于二者之间的锁

  • 由于加锁本身需要消耗资源（获得锁、检查所、释放锁都需要消耗资源），在锁定数据较多的情况下可以使用表锁节省资源
  • MySQL不同的存储引擎支持的锁不同（MyIsam只支持表锁；InnoDB同时支持表锁和行锁，处于性能考虑，绝大多数情况下都是用行锁）

• 查看锁信息（InnoDB，MySQL 8.0.20）

  # 查看锁
  select * from performance_schema.data_locks;
  select * from performance_schema.data_lock_waits;
  # 查看 InnoDB 整体状态，包含锁
  show engine innodb status;
  

• MySQL中锁也分为排它锁（写锁）、共享锁（读锁）等

脏读、不可重复读和幻读

MySQL脏读、幻读和不可重复读_yangcx的专栏-CSDN博客

事务隔离级别

MySQL脏读、幻读和不可重复读_yangcx的专栏-CSDN博客

查看MySQL（8.0.20）会话隔离级别：

MVCC

在SQL标准中，Repeatable Read是无法避免幻读的，但InnoDB实现的Repeatable Read避免了幻读

可重复读（Repeatable Read）解决脏读、不可重复读、幻读等问题，使用的是MVCC(Multi-Version Concurrency Control)，即多版本的并发控制协议（在同一时刻，不同的事务读取到的数据可能是不同的）

最大特点是读不加锁，因此读写不冲突，并发性能好；InnoDB实现MVCC，多个版本数据可以共存，主要基于以下技术及数据结构：

1. 隐藏列：InnoDB中每行数据都有隐藏列，隐藏列中包含了本行数据的事务id、指向undo log的指针等

2. undo log版本链：每条undo log也会指向更早版本的undo log，形成一条版本链

3. ReadView：通过隐藏列和版本链，MySQL可以将数据恢复到指定版本；但是具体要恢复到哪个版本，则需要根据ReadView来确定

   ReadView，是指事务（A）在某一时刻给整个事务系统（trx_sys）打快照，之后再进行读操作时，会将读取到的数据中的事务id与trx_sys快照比较，从而判断数据对该ReadView是否可见，即对事务A是否可见。trx_sys中的主要内容及判断可见性方法如下：

   • low_limit_id：生成ReadView时系统中应该分配给下一个事务的id。如果数据的事务id大于等于low_limit_id，则对该ReadView不可见
   • up_limit_id：生成ReadView时当前系统中活跃的读写事务中最小的事务id。如果数据的事务id小于up_limit_id，则对该ReadView可见
   • rw_trx_ids：生成ReadView时当前系统中活跃的读写事务的事务id列表。如果数据的事务id在low_limit_id和up_limit_id之间，则需要判断事务id是否在rw_trx_ids中：如果在，说明生成ReadView时事务仍在活跃中，因此数据对ReadView不可见；如果不在，说明生成ReadView时事务已经提交了，因此数据对ReadView可见

Repeatable Read隔离级别对脏读、幻读的解决：

1. 脏读

事务A在T3时刻读取余额前会生成ReadView，由于此时事务B没有提交仍然活跃，因此其事务id一定在ReadView的rw_trx_ids中，根据规则，事务B的修改对ReadView不可见；接着事务A根据指针指向的undo log查询上一版本信息并作为查询结果，避免脏读。

2. 不可重复读

   

   事务A在T2时刻读取余额，生成ReadView。此时事务B分两种情况：1. 事务已开始未提交，此时其事务id在ReadView的rw_trx_ids中；2. 事务B还未开始，此时其事务id大于等于ReadView的low_limit_id。无论哪种情况，根据规则，事务B的修改对ReadView都不可见。

   事务A在T5时刻再次读取余额时，根据T2生成的ReadView对数据进行可见性判断，结果为对事务B的修改不可见；因此事务A根据指针指向的undo log查询上一版本数据作为结果，避免不可重复读。

3. 幻读

   

   与避免不可重复读非常类似。

扩展

1. 读已提交（Read Commit，RC）隔离级别下的非加锁读

   RR与RC都使用了MVCC，主要区别在于：

   • RR在事务开始后第一次执行select前创建ReadView，直到事务提交不会再创建；
     RR可以避免脏读、幻读、不可重复读
   • RC每次select前都会建立一个新的ReadView，因此如果事务A第一次select之后，事务B对数据进行了修改并提交，那么事务A第二次select时会重新建立新的ReadView，因此事务B的修改对事务A是可见的
     RC隔离级别可以避免脏读，但是无法避免不可重复读和幻读

2. 加锁读与next-key lock

   按照是否加锁，MySQL读分两种：

   1. 非加锁读：快照读、一致性读，使用普通的select语句（使用MVCC避免脏读、幻读、不可重复读）
   2. 加锁读：查询时进行加锁（加锁后其他事务无法对加锁数据进行写操作，避免了脏读和不可重复读）
      通过next-key lock避免幻读

   next-key lock是行锁的一种，实现相当于record lock（记录锁）+gap lock（间隙锁）；

   不仅会锁住记录本身（record lock），还会锁住一个范围（gap lock）

   #共享锁读取
   select...lock in share mode
   #排它锁读取
   select...for update
   

总结

概括来说，InnoDB实现的RR，通过锁机制（包含next-key lock）、MVCC（包括数据的隐藏列、基于undo log的版本链、ReadView）等，实现了一定程度的隔离性，可以满足大多数场景的需要。

但是并不能完全保证隔离：

1. 如果在事务中第一次读取采用非加锁读，第二次读取采用加锁读，则如果在两次读取之间数据发生了变化，两次读取到的结果不一样，因为加锁读时不会采用MVCC

2. 图示

   

一致性

定义

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

数据库的完整性约束包括但不限于：实体完整性（如行的主键存在且唯一）、列完整性（如字段的类型、大小、长度符合要求）、外键约束、用户自定义完整性

实现

一致性是事务追求的终极目标

实现一致性，除了数据库层面的保障，同时需要在应用层面进行实现

实现措施包括：

1. 保证原子性、持久性和隔离性（这些特性无法保证，一致性也无法保证）
2. 数据库本身提供保障（数据类型校验、数据长度校验等）
3. 应用层面保障

总结

• 原子性：语句要么全执行，要么全不执行，是事务最核心的特性，事务本身就是以原子性来定义的；实现主要基于undo log
• 持久性：保证事务提交后不会因为宕机等原因导致数据丢失；实现主要基于redo log
• 隔离性：保证事务执行尽可能不受其他事务影响；InnoDB默认的隔离级别是RR，RR的实现主要基于锁机制（包含next-key lock）、MVCC（包括数据的隐藏列、基于undo log的版本链、ReadView）
• 一致性：事务追求的最终目标，一致性的实现既需要数据库层面的保障，也需要应用层面的保障

参考资料：

• 深入学习MySQL事务：ACID特性的实现原理 - 编程迷思 - 博客园 (cnblogs.com)