MySQL 七.事务

MySQL 七. InnoDB事务

事务是数据库和区别于文件系统的重要特性之一;事务是把数据库数据从一种状态转移到另一种状态.在数据库提交事务,确保事务内的数据修改要不全部写入,要不全部不保存; 在上一章节中说到了ACID属性 就不再这里写了;

7.1 认识事务 7.1.1 概述

ACID 详解 略

7.1.2 事务的分类

• 扁平事务 Flat Transactions
• 带有保存点的扁平事务 Flat Transactions with SavePoints
• 链事务 Chained Transactions
• 嵌套事务 Nested Transactions
• 分布式事务 Distributed Transactions

扁平事务是事务类型是事务类型里面最简单的一种,但是在实际生产过程中也是用的最多,最频繁的一种.在此事务中,所有操作处于同一个层次,begin开始 ,commit 结束;

下面是扁平事务的三种结果

• 正常完成 96%
• 应用程序要求停止事务 3%
• 超时事务回滚 1%

带有保存点的扁平事务 ,有的事务比较大,不能因为其中一个操作的失败而全部失败,保存点就允许,回到事务开始之后的一个状态,保存点 使用 SAVE WORK 来建立;可以建立多个,程序处理失败是,可以自由选择回复到之前建立的SavePoint

链事务 可视为保存点模式的变种 只能回复到最近的一个保存点

嵌套事务 是一个层次结构框架.由一个顶层事务控制着各个层次的事务.顶层事务下面子事务 subtransaction ,控制着每一个局部的变换

分布式事务 通常是一个分布式环境下运行的扁平事务;

7.2 事务的实现

事务的隔离性在上一章说了是由锁来实现的. redo log保证事务的原子性和持久性 .undo log 保证事务的一致性

7.2.1 redo

1.基本概念

重做日志有两部分 ,一部分是内存里面的redo log buffer,一部分是重做日志文件 redo log file ,redo log来实现事务的持久性;在InnoDB事务提交的时候,通过 Force log at commit 机制:commit的操作必须是在所有日志持久化到重做日志文件的时候才算完成,redo log是顺序写入的;为了确保日志写入磁盘,每次写入需要调用一次fsync ;fsync 新能取决于磁盘,所以磁盘的性能决定了事务提交的性能;前面介绍过,InnoDB可以手动设置fsync 调用的时机,不强制每次提交事务的时候调用,这样性能会有很大的提高,但是如果数据库突然宕机,就会丢失最后的事务数据;

在MySQL数据库中还有一种二进制日志binlog ,是用来point in time (PIT) 恢复数据的或者是replication主从复制数据的,从表面上看都是记录了对数据库的操作,但是两者有着本质的区别;

binlog 是MySQL数据库级别的日志文件,不只是InnoDB事务操作会生成这种日志,其他的存储引擎也会,是数据库层面的日志,其次日志记录的形式是不一样的,binlog 是逻辑日志 是记录的 SQL 语句,而redo log 是物理日志 是记录的对每个数据页的操作 ;最后binlog 是在事务提交时写入一次;

2. log block

在InnoDB里面 ,重做日志都是以512 byte 存储,意味着重做日志缓存、重做日志文件都是以块（block）保存的，称之为重做日志块（redo log block）;如果一个页中产生的重做日志数量大于512字节,那么需要分割为多个重做日志块进行存储。此外，由于重做日志块的大小和磁盘扇区大小一样，都是512字节，因此重做日志的写入可以保证原子性，不需要doublewrite技术

log block header 12 字节 ; log block tailer 8 字节 ; 所以可以存储的就492字节

名称	大小	作用
LOG_BLOCK_HDR_NO	4	log buffer是由log block组成， 用来标记这个数组中的位置,4个字节,第一位是flush bit ,所以最大2G
LOG_BLOCK_HDR_DATA_LEN	2	log block所占用的大小。 写满时，该值为0x200，表示使用全部占用512字节
LOG_BLOCK_FIRST_REC_GROUP	2	log block中第一个日志所在的偏移量。如果该值的大小和LOG_BLOCK_HDR_DATA_LEN相同，则表示当前log block不包含新的日志。
LOG_BLOCK_CHECKPOINT_NO	4	该log block最后被写入时的CHECKPOINT第4字节的值。

如事务T1的重做日志1占用762字节，事务T2的重做日志占用100字节。由于每个log block实际只能保存492个字节，因此其在log buffer中的情况应如图7-8所示 因此该log block的LOG_BLOCK_FIRST_REC_GROUP为282（270+12）

log block tailer

名称	大小	作用
LOG_BLOCK_TRL_NO	4	LOG_BLOCK_HDR_NO 一样的值

3. log group

log group 是一个逻辑上的概念,没有实际的对应文件,它是由多个重做日志文件组成,每个日志文件的大小是一样的, 重做日志文件就是在log buffer 中的log block ,根据下面的运行规则 会把log buffer 里面的log block 刷新到文件磁盘中

• 事务提交的时候
• 当log buffer 的空间被使用了一半
• log checkpoint的时候

log block 会append 追加在 redo log file 后面 当一个 redo log file 被写满的时候,会写下一个redo log file 采用 aroun-robin ;

redo log file除了保存log buffer刷新到磁盘的log block，还保存了一些其他的信息，这些信息一共占用2KB大小

名称	大小 byte	作用
log file header	512
checkpoint1	512
空	512
checkpoint2	512

上面的信息仅在每个log group的第一个redo log file中进行存储。log group中的其余redo log file仅保留这些空间，但不保存上述信息;因为保存了这些信息，就意味着对redo log file的写入并不是完全顺序的,因为其除了log block的写入操作，还需要更新前2KB部分的信息

4.重做日志

InnoDB 重做日志文件的管理是基于数据页的,不同的存储引擎有着通用的重做日志头部

• redo_log_type：重做日志的类型。

• space：表空间的ID。

• page_no：页的偏移量。

redo log body的部分，根据重做日志类型的不同，会有不同的存储内容，例如，对于页上记录的插入和删除操作

到InnoDB1.2版本时，一共有51种重做日志类型。随着功能不断地增加，相信会加入越来越多的重做日志类型

5. LSN
Log Sequence Number ， 日志序列号。在InnoDB存储引擎中，LSN占用8字节，并且单调递增。

• 重做日志写入的总量

• checkpoint的位置

• 页的版本

LSN表示事务写入重做日志的字节的总量。例如当前重做日志的LSN为1 000，有一个事务T1写入了100字节的重做日志，那么LSN就变为了1100，若又有事务T2写入了200字节的重做日志，那么LSN就变为了1 300。可见LSN记录的是重做日志的总量，其单位为字节。
LSN不仅记录在重做日志中，还存在于每个页中。在每个页的头部，有一个值FIL_PAGE_LSN，记录了该页的LSN。在页中，LSN表示该页最后刷新时LSN的大小。因为重做日志记录的是每个页的日志，因此页中的LSN用来判断页是否需要进行恢复操作。例如，页P1的LSN为10 000，而数据库启动时，InnoDB检测到写入重做日志中的LSN为13 000，并且该事务已经提交，那么数据库需要进行恢复操作，将重做日志应用到P1页中。同样的，对于重做日志中LSN小于P1页的LSN，不需要进行重做，因为P1页中的LSN表示页已经被刷新到该位置。

6.恢复

由于checkpoint表示已经刷新到磁盘页上的LSN，因此在恢复过程中仅需恢复checkpoint开始的日志部分。对于图7-12中的例子，当数据库在checkpoint的LSN为10 000时发生宕机，恢复操作仅恢复LSN 10 000～13 000范围内的日志

可以看到记录的是页的物理修改操作，若插入涉及B+树的split，可能会有更多的页需要记录日志。此外，由于重做日志是物理日志，因此其是幂等的。幂等的概念如下：

f(f(x)) = f(x)

有的DBA或开发人员错误地认为只要将二进制日志的格式设置为ROW，那么二进制日志也是幂等的。这显然是错误的，举个简单的例子，INSERT操作在二进制日志中就不是幂等的，重复执行可能会插入多条重复的记录。而上述INSERT操作的重做日志是幂等的

7.2.2 undo

1.基本概念

前面介绍过的redo 是事务对数据页的物理修改记录,作用是事务的持久化,但是事务的回滚他是无法处理的,这个时候需要undo log ,所以在一个数据库的修改事务执行的时候,不仅仅会生成undo log 还会生成redo log,当事务执行失败或者执行回滚就会使用undo log ;

与redo不同，undo存放在数据库内部的一个特殊段（segment）中，这个段称为undo段（undo segment）。undo段位于共享表空间内,undo 是逻辑日志 ,不同于redo ,因为数据库可能并发的情况比较多,同事处理的事务较多,不会将数据页恢复到事务执行之前的模式,这样的代价太大了,因为这可能会影响其他数据页的事务操作;

对于每个INSERT，InnoDB存储引擎会完成一个DELETE；对于每个DELETE，InnoDB存储引擎会执行一个INSERT；对于每个UPDATE，InnoDB存储引擎会执行一个相反的UPDATE，将修改前的行放回去。
除了回滚操作，undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。
最后也是最为重要的一点是，undo log会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护

2. undo存储管理

首先InnoDB存储引擎有rollback segment，每个回滚段种记录了1024个undo log segment，而在每个undo log segment段中进行undo页的申请;1.1版本开始InnoDB支持最大128个rollback segment，故其支持同时在线的事务限制提高到了128*1024;从InnoDB1.2版本开始，可通过参数对rollback segment做进一步的设置。这些参数包括：

• innodb_undo_directory
• innodb_undo_logs
• innodb_undo_tablespaces

参数innodb_undo_directory用于设置rollback segment文件所在的路径。这意味着rollback segment可以存放在共享表空间以外的位置，即可以设置为独立表空间。该参数的默认值为“.”，表示当前InnoDB存储引擎的目录。
参数innodb_undo_logs用来设置rollback segment的个数，默认值为128。在InnoDB1.2版本中，该参数用来替换之前版本的参数innodb_rollback_segments。
参数innodb_undo_tablespaces用来设置构成rollback segment文件的数量，这样rollback segment可以较为平均地分布在多个文件中

注: 事务在undo log segment分配页并写入undo log的这个过程同样需要写入重做日志。

事务提交后并不能马上删除undo log及undo log所在的页。这是因为可能还有其他事务需要通过undo log来得到行记录之前的版本。故事务提交时将undo log放入一个链表中，是否可以最终删除undo log及undo log所在页由purge线程来判断

若为每一个事务分配一个单独的undo页会非常浪费存储空间,在InnoDB设计中对undo页可以进行重,事务提交时,首先将undo log放入链表中,然后判断undo页的使用空间是否小于3/4，若是则表示该undo页可以被重用，之后新的undo log记录在当前undo log的后面。因此purge操作需要涉及磁盘的离散读取操作,

3. undo log格式
在InnoDB存储引擎中，undo log分为：

• insert undo log
  insert undo log是指在insert操作中产生的undo log。因为insert操作的记录，只对事务本身可见，对其他事务不可见（这是事务隔离性的要求），故该undo log可以在事务提交后直接删除。不需要进行purge操作。图中*表示对存储的字段进行了压缩。insert undo log开始的next记录的是下一个undo log(2 个字节)的位置 type_cmpl占用一个字节，记录的是undo的类型，对于insert undo log，该值总是为11。undo_no记录事务的ID，table_id记录undo log所对应的表对象。这两个值都是在压缩后保存的。接着的部分记录了所有主键的列和值。在进行rollback操作时，根据这些值可以定位到具体的记录，然后进行删除即可。

• update undo log

  update undo log记录的是对delete和update操作产生的undo log。该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。update undo log记录内容更多，占用空间更大。next、start、undo_no、table_id与之前介绍的insert undo log部分相同。这里的type_cmpl，由于update undo log本身还有分类，故其可能的值如下：

  • 12TRX_UNDO_UPD_EXIST_REC更新non-delete-mark的记录
  • 13 TRX_UNDO_UPD_DEL_REC将delete的记录标记为not delete
  • 14 TRX_UNDO_DEL_MARK_REC将记录标记为delete
    接着的部分记录update_vector信息，update_vector表示update操作导致发生改变的列。每个修改的列信息都要记录的undo log中。对于不同的undo log类型，可能还需要记录对索引列所做的修改。

4.查询 undo 信息

7.2.3 purge

前面一节说过了 ,数据的delete的操作不是直接的删除操作,而是吧数据哪一行的deleted flag = 1 ,并没有真正的删除掉数据,数据还是在b+树 上面,同时行数据上的辅助索引没有任何的处理,真正删除的动作是延迟的 最终是在purge操作里面完成的;

purge 面对的是update和delete ,删除或者更新不能在事务完成后立刻执行,因为数据库支持MVCC ,可能有其他事务也在处理,purge 会判断数据上是否还有事务引用,没有的话就可以执行真正的更新或者删除

一个undo 页上面允许有多个事务的undo 操作存在,后面的事务肯定在数据页的后面,但是这并不是表名在全局的事务 undo 处理的过程中是按照事务的先后顺序来的; InnoDB会内部维护一个事务处理的链表

比如表第一个处理trx1 ,回去找trx1 所在的 undo 页 处理trx1 后 发现 还有 trx3. 这个时候处理trx3 ,trx3 处理完后trx5 发现trx5 对于的数据还有其他的事务占用; 会回到history list ,trx4 ,==. trx4 所在的undo 数据页;

好处: 避免随机读写.提高purge 效率

Variable_name	Value
innodb_purge_batch_size	300

每次purge 清楚的redo page ,清理的多 就表示下次可用的就多,但是,清理的资源消耗就比较大

Variable_name	Value
innodb_max_purge_lag (控制history 的长度 长度大的时候 延缓DML 操作dalay 毫秒)	0
innodb_max_purge_lag_delay ( 延缓DML 操作 的最大时间)	0

delay = ((length(history list) - innodb_max_purge_lag)*10) - 5

注意:delay 是每个 delay/行 若 DML rows = 10 那么久延缓 10* delay 这个值不能超过innodb_max_purge_lag_delay

7.2.4 commit group

在InnoDB 里面事务的提交会有两个阶段

1. 修改内存中事务的信息 ,undo log写入 undo log buffer
2. 调用fsync 确保 undo log buffer 数据写入磁盘 (磁盘交互 性能制约事务的并发)

在InnoDB 1.2 版本前 开启replication 二进制日志 binlog 开启性能影响是更大的 为了保值InnoDB 的redo log 和 数据库层面的binlog的一致性,两者之间使用了二进制事务 有三个阶段

1. 事务提交后 InnoDB变成prepare 状态
2. MySQL数据库层面开始写入binlog 日志 ,可能是多次写binlog 参数sync_binlog控制 一个事务之间binlog的写入次数
3. 上面的innoDB 提交的两个过程

所以说要保证两个日志的顺序的一致性(事务的提交顺序和 binlog 写入顺序保持一致,备份需要),最少是需要两次的fsync 才可以,而binlog 是事务的连续性,则undo log 的组提交就失效了

InnoDB通过prepare_coimmit_mutex 来保证事务提交顺序和binlog 的一致性

上述过程可以看到每次只能有一个事务在操作binlog 其性能是相对较低的 但是在MySQL5.6 版本后

在MySQL上层对事务提交的时候顺序放入一个队列 ,队列中的第一个事务称为leader，其他事务称为follower

1. Flush : 事务的binlog写入内存
2. Sync : binlog 日志刷盘 如果是多个事务就是一次fsync 就写入了
3. Commit : leader根据顺序调用存储引擎层事务的提交，InnoDB存储引擎本就支持group commit

队列中仅有一个事务，那么可能效果和之前差不多，甚至会更差。但当提交的事务越多时，group commit的效果越明显，数据库性能的提升也就越大,binlog_max_flush_queue_time用来控制Flush阶段中等待的时间;带来一次性跟多的binlog 数据量 ,但是导致事务的响应时间变慢;

7.3 事务控制语句

• START TRANSACTION|BEGIN：显式地开启一个事务。
• COMMIT：要想使用这个语句的最简形式，只需发出COMMIT。也可以更详细一些，写为COMMIT WORK，不过这二者几乎是等价的。COMMIT会提交事务，并使得已对数据库做的所有修改成为永久性的。
• ROLLBACK：要想使用这个语句的最简形式，只需发出ROLLBACK。同样地，也可以写为ROLLBACK WORK，但是二者几乎是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
• SAVEPOINT identifier∶SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT。
• RELEASE SAVEPOINT identifier：删除一个事务的保存点，当没有一个保存点执行这句语句时，会抛出一个异常。
• ROLLBACK TO[SAVEPOINT]identifier：这个语句与SAVEPOINT命令一起使用。可以把事务回滚到标记点，而不回滚在此标记点之前的任何工作。例如可以发出两条UPDATE语句，后面跟一个SAVEPOINT，然后又是两条DELETE语句。如果执行DELETE语句期间出现了某种异常情况，并且捕获到这个异常，同时发出了ROLLBACK TO SAVEPOINT命令，事务就会回滚到指定的SAVEPOINT，撤销DELETE完成的所有工作，而UPDATE语句完成的工作不受影响。
• SET TRANSACTION：这个语句用来设置事务的隔离级别。InnoDB存储引擎提供的事务隔离级别有：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。
• START TRANSACTION、BEGIN语句都可以在MySQL命令行下显式地开启一个事务。但是在存储过程中，MySQL数据库的分析器会自动将BEGIN识别为BEGIN…END，因此在存储过程中只能使用START TRANSACTION语句来开启一个事务。

7.4 隐式的提交 SQL 语句

• DDL语句：ALTER DATAbase…UPGRADE DATA DIRECTORY NAME，ALTER EVENT，ALTER PROCEDURE，ALTER TABLE，ALTER VIEW，CREATE DATAbase，CREATE EVENT，CREATE INDEX，CREATE PROCEDURE，CREATE TABLE，CREATE TRIGGER，CREATE VIEW，DROP DATAbase，DROP EVENT，DROP INDEX，DROP PROCEDURE，DROP TABLE，DROP TRIGGER，DROP VIEW，RENAME TABLE，TRUNCATE TABLE。

• 用来隐式地修改MySQL架构的操作：CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD。

• 管理语句：ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE。
  注意　我发现Microsoft SQL Server的数据库管理员或开发人员往往忽视对于DDL语句的隐式提交操作，因为在Microsoft SQL Server数据库中，即使是DDL也是可以回滚的。这和InnoDB存储引擎、Oracle这些数据库完全不同。
  另外需要注意的是，TRUNCATE TABLE语句是DDL，因此虽然和对整张表执行DELETE的结果是一样的，但它是不能被回滚的（这又是和Microsoft SQL Server数据不同的地方）。

7.5 对于事务操作的统计

考虑每秒请求数（Question Per Second，QPS）

每秒事务处理的能力（Transaction Per Second，TPS）

注意:计算TPS的方法是（com_commit+com_rollback）/time。前提是：所有的事务必须都是显式提交的，如果存在隐式地提交和回滚（默认autocommit=1），不会计算到com_commit和com_rollback变量中

7.6 事务的隔离级别

SQL标准定义的四个隔离级别为：

• READ UNCOMMITTED

• READ COMMITTED

• REPEATABLE READ

• SERIALIZABLE

  据了解，大部分的用户质疑SERIALIZABLE隔离级别带来的性能问题，但是根据Jim Gray在《Transaction Processing》一书中指出，两者的开销几乎是一样的，甚至SERIALIZABLE可能更优!!!因此在InnoDB存储引擎中选择REPEATABLE READ的事务隔离级别并不会有任何性能的损失。同样地，即使使用READ COMMITTED的隔离级别，用户也不会得到性能的大幅度提升。
  在InnoDB存储引擎中，可以使用以下命令来设置当前会话或全局的事务隔离级别：

SET[GLOBAL|SESSION]TRANSACTION ISOLATION LEVEL
{
READ UNCOMMITTED
|READ COMMITTED
|REPEATABLE READ
|SERIALIZABLE
}

7.7 分布式事务 7.7.1　MySQL 数据库分布式事务

InnoDB存储引擎提供了对XA事务的支持，并通过XA事务来支持分布式事务的实现。

分布式事务指的是允许多个独立的事务资源（transactional resources）参与到一个全局的事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对于事务原有的ACID要求又有了提高。另外，在使用分布式事务时，InnoDB存储引擎的事务隔离级别必须设置为SERIALIZABLE。

XA事务由一个或多个资源管理器（Resource Managers）、一个事务管理器（Transaction Manager）以及一个应用程序（Application Program）组成。

• 资源管理器 RM：提供访问事务资源的方法。通常一个数据库就是一个资源管理器。
• 事务管理器 TM：协调参与全局事务中的各个事务。需要和参与全局事务的所有资源管理器进行通信。
• 应用程序 AP：定义事务的边界，指定全局事务中的操作。
  在MySQL数据库的分布式事务中，资源管理器就是MySQL数据库，事务管理器为连接MySQL服务器的客户端。图7-22显示了一个分布式事务的模型。

分布式事务使用两段式提交（two-phase commit）的方式。在第一阶段，所有参与全局事务的节点都开始准备（PREPARE），告诉事务管理器它们准备好提交了。在第二阶段，事务管理器告诉资源管理器执行ROLLBACK还是COMMIT。如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚。可见与本地事务不同的是，分布式事务需要多一次的PREPARE操作，待收到所有节点的同意信息后，再进行COMMIT或是ROLLBACK操作。

XA 语句

XA{START|BEGIN}xid[JOIN|RESUME]
XA END xid[SUSPEND[FOR MIGRATE]]
XA PREPARE xid
XA COMMIT xid[ONE PHASE]
XA ROLLBACK xid
XA RECOVER

7.7.2　内部XA事务

MySQL数据库中还存在另外一种分布式事务，其在存储引擎与插件之间，又或者在存储引擎与存储引擎之间，称之为内部XA事务

MySQL数据库通过内部XA事务保证主从数据一致

7.8　不好的事务习惯 7.8.1　在循环中提交

不应该在一个循环中反复进行提交操作，不论是显式的提交还是隐式的提交

7.8.2　使用自动提交

自动提交并不是一个好的习惯;在编写应用程序开发时，最好把事务的控制权限交给开发人员，即在程序端进行事务的开始和结束。同时，开发人员必须了解自动提交可能带来的问题

7.8.3　使用自动回滚

在程序中控制事务的好处是，用户可以得知发生错误的原因。

7.9 长事务

长事务(Long-Lived Transactions)，顾名思义，就是执行时间较长的事务

优化:通过批量处理小事务来完成大事务的逻辑
存储引擎与插件之间，又或者在存储引擎与存储引擎之间，称之为内部XA事务

MySQL数据库通过内部XA事务保证主从数据一致