MySQL的事务

MySQL的事务

事务的基本概念

提到事务第一个问题就是：什么是事务？在MySQL中事务就是一组原子性【原子在化学中表示最小的粒子，这里就用原子表示不可再分】的SQL操作。或是一个独立的工作单元。事务中的SQL操作要么全部成功！要么全都失败。

举例：银行应用是解释事务必要性的一个经典例子。 常见的两个银行用户转账的例子：现在要从用户A的支票账户转移200元到用户B账户中。那么需要三个步骤：

1. 检查账户的余额高于200元。
2. 从A账户余额中减去200元。
3. 在B账户余额中加上200元。

上述三个操作必须打包在一个事务中，任何一个步骤失败，则必须撤销（回滚）所有的操作。

否则出现的问题：A 账户中的钱扣了，B账户中没有收到。或是A账户中钱没少，B账户钱多了…等等问题

上述例子在MySQL中可以使用start transaction语句开始一个事务，然后使用commit提交事务将修改的数据持久保留，要么使用rollback撤销所有的修改：

# 开启一个事务
start transaction;
# 查询A账户的余额
select balance from Acount;
# 从A账户中减去200元
update Acount set balance = balance - 200;
# 给B账户加200元
update Bcount set balance = balance + 200;
# 提交事务
commit;

在MySQL中，事 务支持是在引擎层实现的。MySQL是一个支持多引擎的系统，但并不是所有的引 擎都支持事务。比如MySQL原生的MyISAM引擎就不支持事务，InnoDB支持事务。这也是MyISAM被InnoDB取代 的重要原因之一。

事务的特性

事务具有的特性可分为四点：简称A C I D

• 原子性（atomicity）:一个事务必须被视为不可分割的最小单位。整个事务中的操作要么全部成功，要么失败回滚，不可能只执行其中一部分操作。
• 一致性（consistency）：数据库总是从一个一致性的状态转换成另一个一致性的状态。对于上述的转账操作，即使在执行步骤2,3之间时系统崩溃，A账户汇总也不会损失200元，因为事务没有提交，所以事务中所做的修改不会保存到数据库中。
• 隔离性（isolation）：通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的，在转账例子中，当步骤2执行完，但是步骤3没有开始时，此时有另一个汇总账户开始运行，则其看到A账户并没有减200元。之所以说“通常”不可见。在讨论隔离级别的时候就了解了。
• 持久性（durability）：一旦事务提交，其所做的修改就会永久的保存到数据库中，此时即使系统崩溃修改的数据也不会丢失。

事务的特性可以确保银行不会弄丢你的钱。在实际的应用中，要实现ACID非常难，一个兼容ACID的系统相比于没有实现ACID的系统通常需要更多的CPU及内存开销。这正是MySQL可以支持多种存储引擎发挥优势的地方，用户可以根据业务是否需要事务处理，来选择合适的存储引擎。获得更高的性能。

事务的隔离级别

事务隔离级别的划分

在上面事务的特性中的隔离性在实际的业务中会因为多事务同时执行导致很多问题。当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（nonrepeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

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

• 读未提交（read uncommitted） ：是指一个事务还没提交时，它做的变更就能被别的事务看到。这也被称为脏读，实际中这个级别很少使用。

• 读提交（read committed）：是指一个事务提交之后，它做的变更才会被其他事务看到。也称为不可重复读，因为两次执行同样的查询得到的结果可能不同。

• 可重复读（repeatable read）：是指一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。该级别保证了在同一个事务中多次读取同样的记录的结果是一致的， 解决了脏读问题，但是无法解决幻读问题。所谓的幻读就是当某个事务在读取某个范围内的记录的时候，另外一个事务又在该范围插入一条数据，当之前的事务再次读取该范围的记录的时候就会产生幻行。是MySQL的默认隔离级别！

• 串行化（serializable ）：顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突 的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

举例说明不同隔离级别下执行事务产生的结果：

假设数据表T中 只有一列，其中一行的值为1：

# 创建表T
create table T( c int ) engine=InnoDB; 
# 插入一行数据
insert into T(c) values(1);

下面是按照时间顺序执行两个事务的行为。

事务A	事务B
启动事务，查询得到值 1	启动事务
	查询得到值 1
	修改操作：将值 1 改成 2
查询得到值 V1
	提交事务B
查询得到值 V2
提交事务 A
查询得到值V3

我们来看看在不同的隔离级别下，事务A会有哪些不同的返回结果，也就是图里面V1、V2、V3 的返回值分别是什么。

• 若隔离级别是“读未提交”， 则V1的值就是2。这时候事务B虽然还没有提交，但是结果已经被 A看到了。因此，V2、V3也都是2。
• 若隔离级别是“读提交”，则V1是1，V2的值是2。事务B的更新在提交后才能被A看到。所以V3的值也是2。
• 若隔离级别是“可重复读”，则V1、V2是1，V3是2。之所以V2还是1，遵循的就是这个要求： 事务在执行期间看到的数据前后必须是一致的。
• 若隔离级别是“串行化”，则在事务B执行“将1改成2”的时候，会被锁住。直到事务A提交后， 事务B才可以继续执行。所以从A的角度看， V1、V2值是1，V3的值是2。

我们可以看到在不同的隔离级别下，数据库行为是有所不同的。

不同隔离级别的视图

在MySQL里，有两个“视图”的概念：

• 一个是view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。 创建视图的语法是create view … ，而它的查询方法与表一样。

• 另一个是InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持RC（Read Committed，读提交）和RR（Repeatable Read，可重复读）隔离级别的实现。

  它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

• 在“可重复读”隔离 级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。

• 在“读提交”隔离级 别下，这个视图是在每个SQL语句开始执行的时候创建的。这里需要注意的是，

• 在“读未提交”隔离 级别下直接返回记录上的最新值，没有视图概念；

• 在“串行化”隔离级别下直接用加锁的方式来避 免并行访问。

事务隔离级别的配置的方式是，将启动参数transaction-isolation的值设置成READ-COMMITTED。你可以用show variables like 'transaction-isolation'来查看当前的值。

事务隔离的实现方案

事务隔离的实现方案有两种，LBCC和MVCC

LBCC – 基于锁的并发控制

LBCC，基于锁的并发控制，英文全称Lock Based Concurrency Control。 就是一个事务去读取一条数据的时候，就上锁，不允许其他事务来操作。

MySQL加锁之后就是当前读。假如当前事务只是加共享锁，那么其他事务就不能有排他锁，也就是不能修改数据；而假如当前事务需要加排他锁，那么其他事务就不能持有任何锁。总而言之，能加锁成功，就确保了除了当前事务之外，其他事务不会对当前数据产生影响，所以自然而然的，当前事务读取到的数据就只能是最新的.

缺点：LBCC方案中，如果我们的业务系统是读多写少的话，这种方案就会极大影响了效率

MVCC – 多版本的并发控制

多版本的并发控制，英文全称：Multi Version Concurrency Control。InnoDB通过为每一行记录添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。但是InnoDB并不存储这些事件发生时的实际时间，相反它只存储这些事件发生时的系统版本号（LSN）。这是一个随着事务的创建而不断增长的数字。每个事务在事务开始时会记录它自己的系统版本号。每个查询必须去检查每行数据的版本号与事务的版本号是否相同。

为了实现MVCC机制，InnoDB内部为每一行添加了两个隐藏列：DB_TRX_ID【存储了插入或更新语句的最后一个事务的事务ID】和DB_ROLL_PTR【回滚指针。回滚指针指向写入回滚段的undo log记录】

在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

MVCC中的快照

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。可能会有疑问：如果库有100G大小生成快照岂不是非常耗时？

实际上，我们并不需要拷贝出这100G的数据。具体的来看快照的实现原理：

InnoDB里面每个事务有一个唯一的事务ID，叫作transaction id【DB_TRX_ID】。它是在事务开始的时候向 InnoDB的事务系统申请的，是按申请顺序严格递增的。而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且 把transaction id赋值给这个数据版本的事务ID，记为row trx_id。同时，旧的数据版本要保留， 并且在新的数据版本中，能够有信息可以直接拿到它。

也就是说，数据表中的一行记录，其实可能有多个版本(row)，每个版本有自己的row trx_id。如下图，就是一条记录被多个事务连续更新后的状态：

图中虚线框里是同一行数据的4个版本，当前最新版本是V4，k的值是22，它是被transaction id为25的事务更新的，因此它的row trx_id也是25。

图2中的三个虚线箭头，就是undo log（下面会有讲述）；而V1、V2、V3并不是物理上真实存在的，而 是每次需要的时候根据当前版本和undo log计算出来的。比如，需要V2的时候，就是通过V4依次执行U3、U2算出来。

InnoDB是怎么定义那个“100G”快照的？

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。

因此，一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我 启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版 本”。
当然，如果“上一个版本”也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数据，它自己还是要认的。

在实现上， InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活 跃”的所有事务ID。“活跃”指的就是，启动了但还没提交。

数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。

这个视图数组把所有的row trx_id 分成了几种不同的情况:

这样，对于当前事务的启动瞬间来说，一个数据版本的row trx_id，有以下几种可能：

1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；

2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；

3. 如果落在黄色部分，那就包括两种情况

a. 若row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见；

b. 若 row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见。

比如，对于图2中的数据来说，如果有一个事务，它的低水位是18，那么当它访问这一行数据 时，就会从V4通过U3计算出V3，所以在它看来，这一行的值是11。

有了这个声明后，系统里面随后发生的更新，是不是就跟这个事务看到的内容无关了呢？ 因为之后的更新，生成的版本一定属于上面的2或者3(a)的情况，而对它来说，这些新的数据版 本是不存在的，所以这个事务的快照，就是“静态”的了。

InnoDB利用了 利用了“ 所有数据都有多个版本 所有数据都有多个版本”的这个特性，实现了 秒级创建快照的能力。

事务日志

事务日志的基本概念

1. 事务日志可以帮助提交事务的效率。
2. 用于记录所有事务以及每个事务对数据库所做的修改。
3. 事务日志是数据库的一个关键组件。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改数据本身持久到磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所有采用事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。
4. 目前大多数存储引擎都是这样实现的，我们通常称之为预写式日志（Write-Ahead Logging）,修改数据需要写两次磁盘。

如果数据的修改已经记录到事务日志并持久化，但数据本身没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具体的恢复方式则视存储引擎而定。

MySQL提供了两种事务型的存储引擎：InnoDB 和NDB Cluster

InnoDB中的事务日志

InnoDB事务日志包括redo log和undo log。redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作，另外还有提供备份，主从复制操作的bin log（二进制日志)。

redo log --重做日志

作用：

确保事务的持久性。redo log日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

redo log是物理格式的日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。

存储方式：innodb存储引擎中，redo log以块为单位进行存储的，每个块占512字节，这称为redo log block。

什么时候产生的？

• 事务开始之后就产生redo log，redo log 不是随着事务的提交才写入的。而是在事务的执行过程中，便开始写入redo log文件中。

什么时候释放：

• 当对应事务的脏页写入到磁盘后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

redo log包括两部分：

一 是内存中的日志缓冲(redo log buffer)，默认大小为8M,Innodb存储引擎先将重做日志写入innodb_log_buffer中，该部分日志是易失性的；通过三种方式写入到重做日志文件(redo log file)：

• 事务没提交时，Master Thread 通过定时方式，每秒一次执行刷新Innodb_log_buffer到重做日志文件。
• 每个事务提交时会将重做日志刷新到重做日志文件。
• 当重做日志缓存可用空间 少于一半时，重做日志缓存被刷新到重做日志文件

二 是磁盘上的重做日志文件(redo log file)，该部分日志是持久的，其redo log是顺序写入redo log file的物理文件中去的。

undo log – 回滚日志

undo log用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

作用：

保证数据的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。

undo log 和redo log 的区别：

undo log和redo log记录物理日志不一样，它是逻辑日志。仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

什么时候产生：

• 事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性

什么时候释放：

• 当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段(segment)中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

什么是非锁定读？

• 当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

存储方式：undo log是采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment。

另外，undo log也会产生redo log，因为undo log也要实现持久性保护。

binlog – 二进制日志

binlog定义：

该日志也和事务息息相关，binlog是Mysql sever层维护的一种二进制日志，与innodb引擎中的redo/undo log是完全不同的日志；其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句，binlog 不会记录不修改数据的语句，例如查询语句，并以"事务【包含了时间、事件开始和结束位置等信息】"的形式保存在磁盘中；

作用：

• 主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的。
• 数据恢复：通过mysqlbinlog工具恢复数据
• 增量备份：所谓的增量备份是备份的一个类型，是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。

什么时候提交？

• 二进制日志只在事务提交的时候一次性写入，提交前的每个二进制日志记录都先binlog cache，提交时写入。所以，对于事务表来说，一个事务中可能包含多条二进制日志事件，它们会在提交时一次性写入。而对于非事务表的操作，每次执行完语句就直接写入。

查看命令：

# 1.mysql默认是没有开启的，开启binlogmy.cnf配置中设置：log_bin="存放binlog路径目录"
# 2.查看：binlog信息查询binlog开启后，可以在配置文件中查看其位置信息，也可以在myslq命令行中查看：
show variables like '%log_bin%';
# 3.查看文件列表
show binary logs;
# 4.查看日志状态
show master status;
# 5.清空binlog日志文件
reset master;

参考：

《高性能MySQL》《MySQL实战45讲(林晓斌)》

https://www.cnblogs.com/f-ck-need-u/p/9010872.html

https://blog.csdn.net/zwx900102/article/details/106544843

https://www.cnblogs.com/myseries/p/10728533.html

https://cloud.tencent.com/developer/article/1032755