MySQL事务篇

1 事务介绍

​ 在MySQL中的事务是由存储引擎实现的，而且支持事务的存储引擎不多，我们主要讲解InnoDB存储引
擎中的事务。
​ 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
​ 事务用来管理 DDL、DML、DCL 操作，比如 insert,update,delete 语句，默认是自动提交的。

2 事务四大特性(ACID)

​ Atomicity（原子性）：构成事务的的所有操作必须是一个逻辑单元，要么全部执行，要么全部不执行。
​ Consistency（一致性）：数据库在事务执行前后状态都必须是稳定的或者是一致的。
​ Isolation（隔离性）：事务之间不会相互影响。由锁机制和MVCC机制来实现的
​ MVCC(多版本并发控制)：优化读写性能（读不加锁、读写不冲突）
​ Durability（持久性）：事务执行成功后必须全部写入磁盘。

3 事务开启

​ ① BEGIN 或 START TRANSACTION；显式地开启一个事务；
​ ② COMMIT 或 COMMIT WORK ，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修 改称为永久性的；
​ ③ ROLLBACK 或ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未 提交的修改；

4 InnoDB架构图

039.png 040.png

​ 上图详细显示了InnoDB存储引擎的体系架构。从图中可见，InnoDB存储引擎由内存池、后台线程和磁盘文件三大部分组成。接下来我们就来简单了解一下内存相关的概念和原理。

4.1 InnoDB内存结构

4.1.1Buffer Pool缓冲池

​ InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。但是由于CPU速度和磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池记录来提高数据库的的整体性能。

​ 在数据库中进行读取操作，首先将从磁盘中读到的页放在缓冲池中，下次再读相同的页中时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。

​ 对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为CheckPoint的机制刷新回磁盘。

​ 所以，缓冲池的大小直接影响着数据库的整体性能，可以通过配置参数innodb_buffer_pool_size来设置。

​ 具体来看，缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲(insert buffer)、自适应哈希索引(adaptive hash index)、InnoDB存储的锁信息(lock info)和数据字典信息(data dictionary)。

​ 在架构图上可以看到，InnoDB存储引擎的内存区域除了有缓冲池之外，还有重做日志缓冲和额外内存池。InnoDB存储引擎首先将重做日志信息先放到这个缓冲区中，然后按照一定频率将其刷新到重做日志文件中。重做日志缓冲一般不需要设置的很大，该值可由配置参数innodb_log_buffer_size控制。

4.1.1.1 数据页和索引页

​ Page是Innodb存储的最基本结构，也是Innodb磁盘管理的最小单位。与数据库相关的所有内容都存储在Page结构里。Page分为几种类型，数据页和索引页就是其中最为重要的两种类型。当做增删改时，缓存里的数据页和磁盘里的数据页不一致，该数据页为脏页

4.1.1.2 插入缓冲页(Insert Buffer Page)

​ 我们都知道，在InnoDB引擎上进行插入操作时，一般需要按照主键顺序进行插入，这样才能获得较高的插入性能。当一张表中存在非聚簇的且不唯一的索引时，在插入时，数据页的存放还是按照主键进行顺序存放，但是对于非聚簇索引叶节点的插入不再是顺序的了，这时就需要离散的访问非聚簇索引页，由于随机读取的存在导致插入操作性能下降。

​ InnoDB为此设计了Insert Buffer来进行插入优化。对于非聚簇索引的插入或者更新操作，不是每一次都直接插入到索引页中，而是先判断插入的非聚集索引是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer中。看似数据库这个非聚集的索引已经查到叶节点，而实际没有，这时存放在另外一个位置。然后再以一定的频率和情况进行Insert Buffer和非聚簇索引页子节点的合并操作。这时通常能够将多个插入合并到一个操作中，这样就大大提高了对于非聚簇索引的插入性能。

4.1.1.3 自适应哈希索引(Adaptive Hash Index)

​ InnoDB会根据访问的频率和模式，为热点页建立哈希索引，来提高查询效率。InnoDB存储引擎会监控对表上各个索引页的查询，如果观察到建立哈希索引可以带来速度上的提升，则建立哈希索引，所以叫做自适应哈希索引。

​ 自适应哈希索引是通过缓冲池的B+树页构建而来，因此建立速度很快，而且不需要对整张数据表建立哈希索引。其有一个要求，即对这个页的连续访问模式必须是一样的，也就是说其查询的条件(WHERE)必须完全一样，而且必须是连续的。

4.1.1.4 锁信息(lock info)

​ 我们都知道，InnoDB存储引擎会在行级别上对表数据进行上锁。不过InnoDB也会在数据库内部其他很多地方使用锁，从而允许对多种不同资源提供并发访问。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。关于锁的具体知识我们之后再进行详细学习。

4.1.1.5 数据字典信息(Data Dictionary)

​ InnoDB有自己的表缓存，可以称为表定义缓存或者数据字典。当InnoDB打开一张表，就增加一个对应的对象到数据字典。

​ 数据字典是对数据库中的数据、库对象、表对象等的元信息的集合。在MySQL中，数据字典信息内容就包括表结构、数据库名或表名、字段的数据类型、视图、索引、表字段信息、存储过程、触发器等内容。MySQL INFORMATION_SCHEMA库提供了对数据局元数据、统计信息、以及有关MySQL server的访问信息（例如：数据库名或表名，字段的数据类型和访问权限等）。该库中保存的信息也可以称为MySQL的数据字典。

4.1.2 Double Write双写

​ 介绍double write之前我们有必要了解partial page write 问题 :
InnoDB 的Page Size一般是16KB，其数据校验也是针对这16KB来计算的，将数据写入到磁盘是以Page为单位进行操作的。而计算机硬件和操作系统，在极端情况下（比如断电）往往并不能保证这一操作的原子性，16K的数据，写入4K 时，发生了系统断电/os crash ，只有一部分写是成功的，这种情况下就是 partial page write 问题。很多DBA 会想到系统恢复后，MySQL 可以根据redolog 进行恢复，而mysql在恢复的过程中是检查page的checksum。checksum就是pgae的最后事务号，发生partial page write 问题时，page已经损坏，找不到该page中的事务号，就无法恢复。

041.png

​ 如上图所示，Double Write由两部分组成：
​ ①内存中的double write buffer，大小为2MB；
​ ②物理磁盘上共享表空间连续的128个页，大小也为2MB。

​ 为了解决 partial page write 问题 ，当mysql将脏数据flush到data file的时候, 并不直接写磁盘，而是先使用memcopy 将脏数据复制到内存中的double write buffer ，之后通过double write buffer再分两次，每次写入1MB到共享表空间的物理磁盘上，然后马上调用fsync函数，同步到磁盘上，避免操作系统缓冲写带来的问题。在这个过程中，doublewrite是顺序写，开销并不大。在完成doublewrite写入后，再将double write buffer写入各表空间文件，这时是离散写入。
​ 如果发生了极端情况（断电）操作系统在将页写入磁盘的过程中发生了崩溃，InnoDB再次启动后在恢复过程中，发现了一个Page数据已经损坏，那么此时就可以从共享表空间中的double write中找到该页的一个副本，将其复制到表空间文件中，再应用重做日志进行恢复。

​ 如果说Insert Buffer给InnoDB存储引擎带来了性能上的提升，那么Double Write带给InnoDB存储引擎的是数据页的可靠性。

4.1.3 Redo log Buffer 重做日志缓冲

​ 当缓冲池中的页的版本比磁盘要新时，数据库需要将新版本的页从缓冲池刷新到磁盘。但是如果每次一个页发生变化就进行刷新，那么性能开销是非常大的，于是InnoDB采用了Write Ahead Log策略，即当事务提交时，先写重做日志，然后再择时将脏页写入磁盘。如果发生宕机导致数据丢失，就通过重做日志进行数据恢复。

​ InnoDB存储引擎会首先将重做日志信息先放入重做日志缓冲中，然后再按照一定频率将其刷新到重做日志文件。重做日志缓冲一般不需要设置得很大，因为一般情况每一秒钟都会将重做日志缓冲刷新到日志文件中。可通过配置参数innodb_log_buffer_size控制，默认为8MB。

​ 除了每秒刷新机制之外，每次事务提交时重做日志缓冲也会刷新到日志中。InnoDB是事务的存储引擎，其通过Force Log at Commit机制实现事务的持久性，即当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，然后事务的提交操作完成才算完成。InnoDB的写入机制大致入下图所示。

042.png

​ 为了确保每次日志都写入到重做日志文件，在每次将重做日志缓冲写入重做日志后，必须调用一次fsync操作，将缓冲文件从文件系统缓存中真正写入磁盘。

​ 可以通过innodb_flush_log_at_trx_commit来控制重做日志刷新到磁盘的策略。该参数默认值为1，表示事务提交必须进行一次fsync操作，还可以设置为0和2。0表示事务提交时不进行写入重做日志操作，该操作只在主线程中完成，2表示提交时写入重做日志，但是只写入文件系统缓存，不进行fsync操作。由此可见，设置为0时，性能最高，但是丧失了事务的一致性。

4.1.5 CheckPoint： 检查点

​ 对于内存中的脏页，什么时候，什么情况下，将多少脏页写入磁盘，是由多方面因素决定的。checkpoint的工作之一，就是在一定条件下将内存中的脏页刷新到磁盘。

4.1.5.1 checkpoint的目的

​ ①缩短数据库的恢复时间
​ ②buffer pool空间不够用时，脏页刷新到磁盘，释放buffer pool空间。
​ ③redo log空间不够用时，刷新脏页到磁盘，释放redo log空间。

4.1.5.2 检查点分类

​ 按照checkpoint刷新的方式，MySQL中的checkpoint分为两种，也即sharp checkpoint和fuzzy checkpoint。

​ ①sharp checkpoint（完全检查点）：在关闭数据库的时候，将buffer pool中的脏页全部刷新到磁盘中。
​ ②fuzzy checkpoint（模糊检查点）：数据库正常运行时，在不同的时机，将部分脏页写入磁盘。将部分脏页刷新到磁盘，也是为了避免一次性刷新全部的脏页造成的性能问题。

4.1.5.3 checkpoint发生的时机

​ checkpoint都是将buffer pool中的脏页刷新到磁盘，但是在不同的情况下，checkpoint会被以不同的方式触发，同时写入到磁盘的脏页的数量也不同。
​ ①master thread checkpoint ：在Master Thread中， 以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘，这个过程是异步的。正常的用户线程对数据的操作不会被阻塞。
​ ②flush_lru_list checkpoint ： flush_lru_list checkpoint是在单独的page cleaner线程中执行的。MySQL对缓存的管理是通过buffer pool中的LRU列表实现的，LRU 空闲列表中要保留一定数量的空闲页面，来保证buffer pool中有足够的空闲页面来相应外界对数据库的请求。当这个空间页面数量不足的时候，发生flush_lru_list checkpoint。空闲页的数量由innodb_lru_scan_depth参数表来控制的，因此在空闲列表页面数量少于配置的值的时候，会发生checkpoint，剔除部分LRU列表尾端的页面。
​ ③async/sync flush checkpoint ：Async/Sync Flush checkpoint 发生在重做日志不可用的时候，将buffer pool中的一部分脏页刷新到磁盘中，在脏页写入磁盘之后，事务对应的重做日志也就可以释放了。
​ redo log剩余空间超过25%的时候，无需执行Async/Sync Flush checkpoint。
​ redo log剩余空间不足25%，但是大于10%的时候，执行Async Flush checkpoint，刷新到满足条件1。
​ redo log剩余空间不足10%的时候，执行Sync Flush checkpoint，刷新到满足条件1。
在mysql 5.6之后，不管是Async Flush checkpoint还是Sync Flush checkpoint，都不会阻塞用户的查询进程。
​ ④dirty page too much checkpoint ： dirty page too much checkpoint 是在Master Thread 线程中每秒一次的频率实现的。dirty page too much 意味着buffer pool中的脏页过多，执行checkpoint脏页刷入磁盘，保证buffer pool中有足够的可用页面。dirty page 由innodb_max_dirty_pages_pct配置，innodb_max_dirty_pages_pct 的默认值在innodb 1.0之前是90%，之后是75%

4.2 InnoDB磁盘文件

​ InnoDB的磁盘文件主要分为三大块：一是系统表空间，二是用户表空间，三是redo日志文件和归档文件。二进制文件(binlog)等文件是MySQL Server层维护的文件，所以未列入InnoDB的磁盘文件中。

系统表空间和用户表空间

​ InnoDB系统表空间包含InnoDB数据字典(元数据以及相关对象)以及double write buffer，change buffer，undo logs的存储区域。

​ 系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。系统表空间是一个共享的表空间因为它是被多个表共享的。
​ 系统表空间是由一个或者多个数据文件组成。默认情况下，一个初始大小为10MB，名为ibdata1的系统数据文件在MySQL的data目录下被创建。用户可以使用 innodb_data_file_path 对数据文件的大小和数量进行配置。
innodb_data_file_path 的格式如下：

innodb_data_file_path=datafile1[,datafile2]...

用户可以通过多个文件组成一个表空间，同时制定文件的属性：

innodb_data_file_path = /db/ibdata1:1000M;/dr2/db/ibdata2:1000M:autoextend

这里使用/db/ibdata1和/dr2/db/ibdata2两个文件组成系统表空间。

​ 设置innodb_data_file_path参数之后，所有基于InnoDB存储引擎的表的数据都会记录到该系统表空间中，如果设置了参数innodb_file_per_table，则用户可以将每个基于InnoDB存储引擎的表产生一个独立的用户表空间。用户表空间的命名规则为：表名.ibd。

​ 通过这种方式，用户不用将所有数据都存放于默认的系统表空间中，但是用户表空间只存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的系统表空间中。

下图显示InnoDB存储引擎对于文件的存储方式，其中frm文件是表结构定义文件，记录每个表的表结构定义。

043.png

4.2.1 系统表空间（共享表空间）

​ ①数据字典(data dictionary)：记录数据库相关信息
​ ②double write buffer：解决部分写失败（页断裂）
​ ③insert buffer：内存insert buffer数据，周期写入共享表空间，防止意外宕机
​ ④回滚段(rollback segments)
​ ⑤undo空间：undo页

4.2.1 用户表空间（独立表空间）

​ ①每个表的数据和索引都会存在自已的表空间中
​ ②每个表的结构
​ ③undo空间：undo页 （需要设置）
​ ④double write buffer

4.2.3 重做日志文件和归档文件

​ 默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件，这就是InnoDB的重做日志文件(redo log file)，它记录了对于InnoDB存储引擎的事务日志。 当InnoDB的数据存储文件发生错误时，重做日志文件就能派上用场。InnoDB存储引擎可以使用重做日志文件将数据恢复为正确状态，以此来保证数据的正确性和完整性。
​ 每个InnoDB存储引擎至少有1个重做日志文件组(group)，每个文件组下至少有2个重做日志文件，如默认的ib_logfile0和ib_logfile1。为了得到更高的可靠性，用户可以设置多个镜像日志组，将不同的文件组放在不同的磁盘上，以此来提高重做日志的高可用性。在日志组中每个重做日志文件的大小一致，并以【循环写入】的方式运行。InnoDB存储引擎先写入重做日志文件1，当文件被写满时，会切换到重做日志文件2，再当重做日志文件2也被写满时，再切换到重做日志文件1。

044.png

​ 用户可以使用innodb_log_file_size来设置重做日志文件的大小，这对InnoDB存储引擎的性能有着非常大的影响。如果重做日志文件设置的太大，数据丢失时，恢复时可能需要很长的时间；另一方面，如果设置的太小，重做日志文件太小会导致依据checkpoint的检查需要频繁刷新脏页到磁盘中，导致性能的抖动。

5 事务的ACID特性的具体实现原理

总结来说，事务的隔离性由多版本控制机制和锁实现，而原子性、一致性和持久性通过InnoDB的redo log、undo log和Force Log at Commit机制来实现。

5.1 原子性，持久性和一致性

原子性，持久性和一致性主要是通过redo log、undo log和Force Log at Commit机制机制来完成
的。redo log用于在崩溃时恢复数据，undo log用于对事务的影响进行撤销，也可以用于多版本控
制。而Force Log at Commit机制保证事务提交后redo log日志都已经持久化

5.1 .1 redo log

​ InnoDB对于数据文件和日志文件的刷盘遵守WAL(Write ahead redo log) 和Force-log-at-commit两种规则，二者保证了事务的持久性。
​ WAL要求数据的变更写入到磁盘前，首先必须将内存中的日志写入到磁盘；
​ Force-log-at-commit要求当一 个事务提交时，所有产生的日志都必须刷新到磁盘上，如果日志刷新成功后，缓冲池中的数据刷新到磁盘前数据库发生了宕机，那么重启时，数据库可以从日志中恢复数据。

045.png

​ 如上图所示，InnoDB在缓冲池中变更数据时，会首先将相关变更写入重做日志缓冲中，然后再按时或者当事务提交时写入磁盘，这符合Force-log-at-commit原则；当重做日志写入磁盘后，缓冲池中的变更数据才会依据checkpoint机制择时写入到磁盘中，这符合WAL原则。
​ checkpoint（检查点）表示脏页写入到磁盘的时机，所以检查点也就意味着脏数据的写入。
​ 在checkpoint择时机制中，就有重做日志文件写满的判断。如果重做日志文件太小经常被写满，就会频繁导致checkpoint将更改的数据写入磁盘，导致性能抖动。
​ 操作系统的文件系统是带有缓存的，当InnoDB向磁盘写入数据时，有可能只是写入到了文件系统的缓存中，没有真正的“落袋为安”。
​ InnoDB的innodb_flush_log_at_trx_commit属性可以控制每次事务提交时InnoDB的行为，该参数默认值为1。当属性值为0时，事务提交时，不会对重做日志进行写入操作，而是等待主线程按时写入；当属性值为1时，事务提交时，会将重做日志写入文件系统缓存，并且调用文件系统的fsync，将文件系统缓冲中的数据真正写入磁盘存储，确保不会出现数据丢失；当属性值为2时，事务提交时，也会将日志文件写入文件系统缓存，但是不会调用fsync，而是让文件系统自己去判断何时将缓存写入磁盘。
日志的刷盘机制如下图所示：

046.png

​ innodb_flush_log_at_commit是InnoDB性能调优的一个基础参数，涉及InnoDB的写入效率和数据安全。当参数值为0时，写入效率最高，但是数据安全最低；参数值为1时，写入效率最低，但是数据安全最高；参数值为2时，二者都是中等水平。一般建议将该属性值设置为1，以获得较高的数据安全性，而且也只有设置为1，才能保证事务的持久性。

5.1.2 undoLog

​ 数据库崩溃重启后需要从redo log中把未落盘的脏页数据恢复出来，重新写入磁盘，保证用户的数据不丢失。当然，在崩溃恢复中还需要回滚没有提交的事务。由于回滚操作需要undo日志的支持，undo日志的完整性和可靠性需要redo日志来保证，所以崩溃恢复先做redo恢复数据，然后做undo回滚。

047.png

​ 在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。

048.png

数据和回滚日志的逻辑存储结构

049.png

​ undo log的存储不同于redo log，它存放在数据库内部的一个特殊的段(segment)中，这个段称为回滚段。回滚段位于共享表空间中。undo段中的以undo page为更小的组织单位。undo page和存储数据库数据和索引的页类似。因为redo log是物理日志，记录的是数据库页的物理修改操作。所以undolog（也看成数据库数据）的写入也会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。如上图所示，表空间中有回滚段和叶节点段和非叶节点段，而三者都有对应的页结构。

我们再来总结一下数据库事务的整个流程，如下图所示。

050.png

​ 事务进行过程中，每次sql语句执行，都会记录undo log和redo log，然后更新数据形成脏页，然后redo log按照时间或者空间等条件进行落盘，undo log和脏页按照checkpoint进行落盘，落盘后相应的redo log就可以删除了。此时，事务还未COMMIT，如果发生崩溃，则首先检查checkpoint记录，使用相应的redo log进行数据和undo log的恢复，然后查看undo log的状态发现事务尚未提交，然后就使用undo log进行事务回滚。事务执行COMMIT操作时，会将本事务相关的所有redo log都进行落盘，只有所有redo log落盘成功，才算COMMIT成功。然后内存中的数据脏页继续按照checkpoint进行落盘。如果此时发生了崩溃，则只使用redo log恢复数据。

051.png

5.2 隔离性

5.2.1 事务隔离级别

四种隔离级别（SQL92标准）：
现在来看看MySQL数据库为我们提供的四种隔离级别（由低到高）：
① Read uncommitted (读未提交)：最低级别，任何情况都无法保证。
② Read committed (RC，读已提交)：可避免脏读的发生。
③ Repeatable read (RR，可重复读)：可避免脏读、不可重复读的发生。
（注意事项：InnoDB的RR还可以解决幻读，主要原因是Next-Key锁，只有RR才能使用Next�Key锁）
④ Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
（由MVCC降级为Locking-Base CC）

5.2.2 事务并发问题

在事务的并发操作中可能会出现一些问题：
①丢失更新：两个事务针对同一数据都发生修改操作时，会存在丢失更新问题。
②脏读：一个事务读取到另一个事务未提交的数据。
③不可重复读：一个事务因读取到另一个事务已提交的update或者delete数据。导致对同一条记录读取两次以上的结果不一致。
④幻读：一个事务因读取到另一个事务已提交的insert数据。导致对同一张表读取两次以上的结果不一致。
以上是并发事务过程中会存在的问题，解决更新丢失可以交给应用，但是后三者需要数据库提供事务间的隔离机制来解决。实现隔离机制的方法主要有两种：

​ a.加读写锁
​ b.一致性快照读，即 MVCC

5.2.3 现实场景

​ 管理者要查询所有用户的存款总额，假设除了用户A和用户B之外，其他用户的存款总额都为0，A、B用户各有存1000，所以所有用户的存款总额为2000。但是在查询过程中，用户A会向用户B进行转账操作。转账操作和查询总额操作的时序图如下图所示。

转账和查询的时序图：

052.png

​ 如果没有任何的并发控制机制，查询总额事务先读取了用户A的账户存款，然后转账事务改变了用户A和
用户B的账户存款，最后查询总额事务继续读取了转账后的用户B的账号存款，导致最终统计的存款总额
多了100元，发生错误。

--创建账户表并初始化数据 
create table tacount(id int , aname varchar(100),acount int , primary key(id)); 
alter table tacount add index idx_name(aname); 
insert into tacount values(1,'a',1000);
insert into tacount values(2,'b',1000); 
--设置隔离级读未提交（read-uncommitted） 
mysql> set session transaction isolation level read uncommitted; 
--session 1 
mysql> start transaction ; 
select * from tacount where aname='a'; 
+----+-------+--------+ 
| id | aname | acount | 
+----+-------+--------+ 
| 1 | a | 1000 | 
+----+-------+--------+ 
--session 2 
mysql> start transaction; 
update tacount set acount=1100 where aname='b'; 
--session 1 
mysql> select * from tacount where aname='b'; 
+----+-------+--------+ 
| id | aname | acount | 
+----+-------+--------+ 
| 2 | b | 1100 | 
+----+-------+--------+

5.2.4 使用锁机制(LBCC)可以解决上述的问题。

​ 查询总额事务会对读取的行加锁，等到操作结束后再释放所有行上的锁。因为用户A的存款被锁，导致转账操作被阻塞，直到查询总额事务提交并将所有锁都释放。
使用锁机制：

053.png

​ 但是这时可能会引入新的问题，当转账操作是从用户B向用户A进行转账时会导致死锁。转账事务会先锁住用户B的数据，等待用户A数据上的锁，但是查询总额的事务却先锁住了用户A数据，等待用户B的数据上的锁。

--设置隔离级别为串行化（serializable） 死锁演示 
mysql> set session transaction isolation level serializable; 
--session 1 
mysql> start transaction;
select * from tacount where aname='a'; 
--session 2 
mysql> start transaction ; 
update tacount set acount=900 where aname='b'; 
-- session 1 
mysql> select * from tacount where aname='b'; 
-- session 2 
mysql> update tacount set acount=1100 where aname='a'; 
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

5.2.5 使用MVCC机制可以解决这个问题。

​ 查询总额事务先读取了用户A的账户存款，然后转账事务会修改用户A和用户B账户存款，查询总额事务读取用户B存款时不会读取转账事务修改后的数据，而是读取本事务开始时的数据副本(在RR隔离等级下)。

使用MVCC机制（RR隔离级别下的演示情况）：

054.png

​ MVCC使得数据库读不会对数据加锁，普通的SELECT请求不会加锁，提高了数据库的并发处理能力。借助MVCC，数据库可以实现READ COMMITTED，REPEATABLE READ等隔离级别，用户可以查看当前数据的前一个或者前几个历史版本，保证了ACID中的I特性（隔离性)。

-- 显示当前隔离级别为 REPEATABLE-READ MySQL默认隔离级别 
mysql> select @@tx_isolation; 
-- session 1 
mysql> start transaction ; 
select * from tacount where aname='a'; 
+----+-------+--------+ 
| id | aname | acount | 
+----+-------+--------+ 
| 1 | a | 1000 |
+----+-------+--------+ 
-- session 2
mysql> start transaction; 
update tacount set acount=1100 where aname='a'; 
-- session 1 
mysql> select * from tacount where aname='a'; 
+----+-------+--------+ 
| id | aname | acount | 
+----+-------+--------+ 
| 1 | a | 1000 |
+----+-------+--------+ 
-- session 2 提交事务 
mysql> commit; 
-- session 1 显示在session 1 事务开始时的数据 
mysql> select * from tacount where aname='a';
+----+-------+--------+ 
| id | aname | acount | 
+----+-------+--------+ 
| 1 | a | 1000 | 
+----+-------+--------+ 
-- 设置事务隔离级别为REPEATABLE-COMMITTED 读已提交 
-- session 1 
mysql> set session transaction isolation level read committed; 
mysql> start transaction ; 
select * from tacount where aname='a'; 
+----+-------+--------+ 
| id | aname | acount | 
+----+-------+--------+ 
| 1 | a | 1000 | 
+----+-------+--------+ 
-- session 2 
mysql> set session transaction isolation level read committed; 
mysql> start transaction; 
update tacount set acount=1100 where aname='a';
-- session 1
mysql> select * from tacount where aname='a'; 
+----+-------+--------+ 
| id | aname | acount | 
+----+-------+--------+ 
| 1 | a | 1000 | 
+----+-------+--------+ 
-- session 2 提交事务 
mysql> commit; 
-- session 1 显示最新事务提交后的数据 
mysql> select * from tacount where aname='a'; 
+----+-------+--------+ 
| id | aname | acount | 
+----+-------+--------+ 
| 1 | a | 1100 | 
+----+-------+--------+

5.2.6 InnoDB的MVCC实现

5.2.6.1 什么是MVCC

​ MVCC，Multi-Version Concurrency Control，多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。

​ 如果有人从数据库中读数据的同时，有另外的人写入数据，有可能读数据的人会看到『半写』或者不一致的数据。有很多种方法来解决这个问题，叫做并发控制方法。最简单的方法，通过加锁，让所有的读者等待写者工作完成，但是这样效率会很差。MVCC 使用了一种不同的手段，每个连接到数据库的读者，在某个瞬间看到的是数据库的一个快照，写者写操作造成的变化在写操作完成之前（或者数据库事务提交之前）对于其他的读者来说是不可见的。

​ 基于提升并发性能的考虑，各大数据库厂商的事务型存储引擎一般都同时实现了多版本并发控制（MVCC）。不仅是MySQL，包括Oracle、PostgreSQL等其他数据库系统也都实现了。MVCC就像是Java语言中的接口，各个数据库厂商的实现机制不尽相同。可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只是锁定必要的行。MVCC会保存某个时间点上的数据快照。这意味着事务可以看到一个一致的数据视图，不管他们需要跑多久。这同时也意味着不同的事务在同一个时间点看到的同一个表的数据可能是不同的。前面说到不同的存储引擎的MVCC实现是不同的，典型的有乐观并发控制和悲观并发控制。

​ MVCC实现的读写不阻塞正如其名：多版本并发控制，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本

5.2.6.1 一致性非锁定读

​ 一致性非锁定读(consistent nonlocking read)是指InnoDB存储引擎通过多版本控制(MVCC)读取当前数据库中行数据的方式。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB会去读取行的一个快照。

055.png

​ 上图直观地展现了InnoDB一致性非锁定读的机制。之所以称其为非锁定读，是因为不需要等待行上排他锁的释放。快照数据是指该行的之前版本的数据，每行记录可能有多个版本，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制(Multi Version Concurrency Control, MVVC)。InnoDB是通过undo log来实现MVVC（undo log的两个作用①回滚②让mvcc读历史版本）。undo log本身用来在事务中回滚数据，因此快照数据本身是没有额外开销。此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。

​ 一致性非锁定读是InnoDB默认的读取方式，即读取不会占用和等待行上的锁。但是并不是在每个事务隔离级别下都是采用此种方式。此外，即使都是使用一致性非锁定读，但是对于快照数据的定义也各不相同。

​ 在事务隔离级别RC和RR，InnoDB使用一致性非锁定读。然而，对于快照数据的定义却不同。在RC事务隔离级别下，一致性非锁定读总是读取被锁定行的最新一份快照数据。而在RR事务隔离级别下，则读取事务开始时的行数据版本。

​ 我们下面举个例子来详细说明一下上述的情况。

# session A
mysql> BEGIN;
mysql> SELECT * FROM test WHERE id = 1;

​ 我们首先在会话A中显示地开启一个事务，然后读取test表中的id为1的数据，但是事务并没有结束。于此同时，用户再开启另一个会话B，这样可以模拟并发的操作，然后对会话B做出如下的操作：

# session B
mysql> BEGIN;
mysql> UPDATE test SET id = 3 WHERE id = 1;

​ 在会话B的事务中，将test表中id为1的记录修改为id=3，但是事务同样也没有提交，这样id=1的行其实加了一个排他锁。由于InnoDB在RC和RR事务隔离级别下使用一致性非锁定读，这时如果会话A再次读取id为1的记录，仍然能够读取到相同的数据。此时，RC和RR事务隔离级别没有任何区别。

056.png

如上图所示，当会话B提交事务后，会话A再次运行 SELECT * FROM test WHERE id = 1 的SQL语句时，两个事务隔离级别下得到的结果就不一样了。
对于RC的事务隔离级别，它总是读取行的最新版本，如果行被锁定了，则读取该行版本的最新一个快照。因为会话B的事务已经提交，所以在该隔离级别下上述SQL语句的结果集是空的。
对于RR的事务隔离级别，总是读取事务开始时的行数据，因此，在该隔离级别下，上述SQL语句仍然会获得相同的数据。

5.2.6.2 当前读和快照读

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。

• 快照读，就是读取数据的时候会根据一定规则读取事务可见版本的数据（可能是过期的数据），不用加锁。
• 当前读，读取的是最新版本, 并且对读取的记录加锁，保证其他事务不会再并发的修改这条记录，避免出现安全问题。

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？
以MySQL InnoDB为例：

使用当前读的场景：

​ ①select…lock in share mode (共享读锁)
​ ②select…for update
​ ③update
​ ④delete
​ ⑤insert

使用快照读的场景：

​ 单纯的select操作，不包括上述 select … lock in share mode、select … for update

057.png

5.2.6.3 MVCC 在mysql 中的实现依赖的是 undo log 与 read view 。

​ 多版本并发控制仅仅是一种技术概念，并没有统一的实现标准， 其的核心理念就是数据快照，不同的事务访问不同版本的数据快照，从而实现不同的事务隔离级别。虽然字面上是说具有多个版本的数据快照，但这并不意味着数据库必须拷贝数据，保存多份数据文件，这样会浪费大量的存储空间。InnoDB通过事务的undo日志巧妙地实现了多版本的数据快照。
​ 数据库的事务有时需要进行回滚操作，这时就需要对之前的操作进行undo。因此，在对数据进行修改时，InnoDB会产生undo log。当事务需要进行回滚时，InnoDB可以利用这些undo log将数据回滚到修改之前的样子。

5.2.6.3.1 Undo Log

​ 根据行为的不同 undo log 分为两种 insert undo log和update undo log。
​ insert undo log 是在 insert 操作中产生的 undo log。因为 insert 操作的记录只对事务本身可见，对于其它事务此记录是不可见的，所以 insert undo log 可以在事务提交后直接删除而不需要进行 purge 操作。
​ update undo log 是 update 或 delete 操作中产生的 undo log，因为会对已经存在的记录产生影响，为了提供 MVCC机制，因此 update undo log 不能在事务提交时就进行删除，而是将事务提交时放到入 history list 上，等待 purge 线程进行最后的删除操作。

058.png

​ 为了保证事务并发操作时在写各自的undo log时不产生冲突，InnoDB采用回滚段的方式来维护undo log的并发写入和持久化。回滚段实际上是一种 Undo 文件组织方式。
​ InnoDB行记录有三个隐藏字段：分别对应该行的rowid、事务号db_trx_id和回滚指针db_roll_ptr，其中db_trx_id表示最近修改的事务的id，db_roll_ptr指向回滚段中的undo log。
​

如下图所示（初始状态）：

059.png

​ 当事务2使用UPDATE语句修改该行数据时，会首先使用排他锁锁定该行，将该行当前的值复制到undo
log中，然后再真正地修改当前行的值，最后填写事务ID，使用回滚指针指向undo log中修改前的行。

如下图所示（第一次修改）：

060.png

​ 当事务3进行修改与事务2的处理过程类似。
如下图所示（第二次修改）：

061.png

5.2.6.3.2 事务链表

​ Mysql中的事务在开始到提交过程中，都会被保存到一个叫trx_sys链表中。
​ 事务链表中保存的都是还未提交的事务，事务一旦被提交，则会从事务链表中摘除。
​ RR隔离级别下，在每个事务开始的时候，会将当前系统中所有活跃事务拷贝到一个列表中（read view）。
​ RC隔离级别下，在每条语句开始的时候，会将当前系统中活跃事务拷贝到一个列表中（read view）。
​ 查看事务列表：
​ show engine innodb status;

5.2.6.3.3 read view

​ Read View是事务开启时，当前所有事务的一个集合，这个数据结构中存储了当前Read View中最大的ID及最小的ID。
​ 这就是当前活跃事务列表，如下所示：
​ ct-trx --> trx11 --> trx9 --> trx6 --> trx5 --> trx3;
​ ct-trx 表示当前事务的id，对应上面的read_view数据结构如下，
​ read_view->creator_trx_id = ct-trx;
​ read_view->up_limit_id = trx3;
​ read_view->low_limit_id = trx11;
​ read_view->trx_ids = [trx11, trx9, trx6, trx5, trx3];
​ low_limit_id是“高水位”，即当时活跃事务的最大id，如果读到row的db_trx_id>=low_limit_id，说明这些id在此之前的数据都没有提交，这些数据都不可见。

        if (trx_id >= view->low_limit_id) {
                return(FALSE);
        }
        注：readview 部分源码

​ up_limit_id是“低水位”，即当时活跃事务的最小事务id，如果row的db_trx_id<up_limit_id，说明这些数据在事务创建id的时都已经提交，这些数据均可见。

        if (trx_id < view->up_limit_id) {
                return(TRUE);
        }

​ row的db_trx_id在low_limit_id和up_limit_id之间，则查找该记录的db_trx_id是否在自己事务的read_view->trx_ids列表中，如果在则该记录的当前版本不可见，否则该记录的当前版本可见。

062.png

不同隔离级别ReadView实现方式
read-commited：

函数：ha_innobase::external_lock
if (trx->isolation_level <= TRX_ISO_READ_COMMITTED && trx->global_read_view) { 
/ At low transaction isolation levels we let each consistent read set its own snapshot /        read_view_close_for_mysql(trx);
}

​ 即：在每次语句执行的过程中，都关闭read_view, 重新在row_search_for_mysql函数中创建当前的一份read_view。这样就会产生不可重复读现象发生。
repeatable read：
​ 在repeatable read的隔离级别下，创建事务trx结构的时候，就生成了当前的global read view。使用trx_assign_read_view函数创建，一直维持到事务结束。在事务结束这段时间内 每一次查询都不会重新重建Read View ， 从而实现了可重复读。