MySQL笔记(7)---事务

1.前言

　　前面具体讲了MySQL中的锁实现的方式，解释了是如何保证数据在并发情况下的可靠性，并提到了事务REPETABLE READ和READ COMMITTED，解释了一下这两种事务的不同。本章讲具体就事务的实现过程进行记录，扫除这块让人疑惑的知识点。

　　事务是数据库区别于文件系统的一个重要特性之一。文件系统中，如果写文件时系统崩溃了，可能文件就被损坏了。虽然有机制将一个文件回退到某个时间点，但对于文件同步问题就无能为力了。事务会把数据从一种一致性状态转换成另一种一致性状态。数据库提交时，要不都成功了，要不都失败了。

　　InnoDB的事务符合ACID的特性：

　　　　原子性  atomicity

　　　　一致性 consistency

　　　　隔离性 isolation

　　　　持久性 durability

　　前一章的锁主要是对事务的隔离性进行了说明，本节主要关注事务的原子性这个概念。

2.认识事务

2.1 概述

　　事务是访问并更新数据库中数据的一个程序执行单元，可以由1到多条SQL组成。在一个事务中，要不全做，要不不做。可以把事务看做是办一件事，有很多个步骤，只要事情搞砸了，所有的步骤都不被认可，好比没干。

　　事务有很严格的定义，必须满足ACID特性，但是由于各种原因，有些时候并没有满足所有的标准，比如之前提到的READ COMMITTED，其就不满足隔离性要求。大部分情况，这样不会造成严重的后果，反而会有性能的提升，所以具体使用哪种事务，给了人们自由选择的能力。

　　原子性：就是要不不做，要不全做。比如转账，先扣自己账户的钱，再往其他人账户上加一笔钱。这个过程是要保证原子性的，不然扣成功了，却转失败了，后果是灾难性的。原子就意味着不可分割，失败了就要还原到最初的状态。

　　一致性：一致性指事务将数据库从一种状态转变成下一种一致的状态。在事务开始之前，结束之后，数据库的完整性约束没有被破坏。比如一个字段是唯一索引，对其进行了修改，事务提交或事务操作后变得非唯一了，这就破坏了一致性要求。

　　隔离性：要求每个读写事务的对象对其他事务的操作能相互分离，即每个事务当前看不到其他事务的操作。

　　持久性：事务一旦提交，结果就是永久性的。磁盘损坏之类的不是该考虑范围。

2.2 分类

　　从事务理论的角度来说，可以把事务分为以下几种类型：

　　　　扁平事务（Flat Transactions)

　　　　带有保存点的扁平事务（Flat Transactions with Savepoints)

　　　　链事务（Chained Transactions)

　　　　嵌套事务（Nested Transactions)

　　　　分布式事务（Distributed Transactions)

　　扁平事务：

　　　　最简单，使用频繁。所有操作都是处于同一层次，由BEGIN WORK开始，由COMMIT WORK 或ROLLBACK WORK结束，操作是原子的。成功完成的占96%，应用程序要求停止的3%，强制终止的为1%。这个是结果的占用百分比。

　　　　扁平事务的主要限制就是不能部分回滚。比如定机票：定了xx->yy->zz的机票，yy->zz的机票预定失败，连xx->yy的都会回滚，显然有些时候这并不是我们希望的。

　　带有保存点的扁平事务：

　　　　这个就是解决上面提到的问题，给了一个保存点，后续失败后可以回到这个保存点。保存点使用SAVE WORK函数创建，出现问题，能够作为内部的重启动点，根据应用逻辑可以选择回到哪一个保存点。

　　　　ROLLBACK WORK : 保存点。

　　链事务：

　　　　可以看成是保存点模式的一种变种。前一种在系统崩溃时，所有保存点都会消失，因为其非持久，事务恢复时要重新执行。

　　　　链事务的思想是：提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式地传给下一个要开始地事务。提交事务和下一个事务开始操作是一个原子操作。这就意味着下一个事务将看到上一个事务地结果，在那个事务中执行一样。

　　　　通过上述描述，可以看出这个与带有保存点的扁平事务不同之处在于：只能回滚到上一个节点，因为之前的已经commit了。第二个就是由于之前commit了，所以之前的锁被释放了。

　　嵌套事务：

　　　　这是一个层次结构框架。由一个顶层事务控制各个层次的事务。下面嵌套的事务被称为子事务，控制每一个局部的变换。

　　　　嵌套事务是一个树，子树可以是嵌套事务，也可以是扁平事务。叶子节点是扁平事务，根节点的是顶层事务，其他的是子事务，前驱是父事务，后继是儿子事务。子事务可以提交和回滚，但是不会立刻生效，除非其父事务已经提交了。所有，所有的子事务都在顶层事务提交之后才真正提交。任意事务回滚都会导致其子事务回滚，所以子事务只有ACI特性，没有D特性。

　　　　在Moss理论中，实际的工作是交给叶子节点完成的，高层事务负责逻辑控制，决定何时调用相关子事务。即使一个系统不支持嵌套事务，也可以通过保存点来模拟嵌套事务。但是用户无法选择哪些锁被子事务继承，哪些需要被父事务保留。保存点中所有的锁都能够被访问和得到。但是嵌套事务中不一样，比如父事务P1，持有对象X和Y的排他锁，调用一个子事务P11，父事务可以决定传递哪些锁，如果P11还有z锁，父事务可以反向继承，持有X、Y、Z的排他锁。调用子事务P12，可以选择持有哪些锁。

　　　　想要实现事务的并行性，就需要真正支持嵌套事务了，保存点是不行的。

　　分布式事务：

　　　　通常是一个在分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点。比如一个事务，涉及到多个数据库的增删，其中有一个出错，所有的数据库实例都要做出相应的反应才行。

　　InnoDB引擎支持扁平事务，带有保存点的事务，链事务和分布式事务。不支持嵌套事务，但是可以试着用带有保存点的事务进行模拟串行的嵌套事务。

3. 事务的实现

　　之前提到了redo页和undo页，这里详细讲一下这些知识。

　　redo log称为重做日志，用来保证事务的原子性和持久性。undo log用来保证事务的一致性。

　　或许会认为undo是redo的逆过程，但是不是。redo和undo的作用都是一种恢复操作，redo恢复提交事务修改的页操作，undo是回滚记录到某个特定版本。因此两者记录的内容不同，redo是物理日志，记录的是页的物理修改操作。undo是逻辑日志，记录每行的记录。

3.1 redo

3.1.1 基本概念

　　重做日志用于实现事务的持久性，由两部分组成：一是内存中的重做日志缓冲，二是重做日志文件。

　　采取Force Log at commit机制实现事务的持久性。即当事务提交时，必须先将事务的日志写入重做日志文件进行持久化，之后才进行commit，最后才算完成。重做日志在InnoDB中由两部分组成：redo log和undo log。redo log保证事务的持久性，undo log帮助事务回滚及MVCC的功能。redo log基本是顺序写的，运行时不需要对redo log文件进行读取操作。undo log是需要随机读写的。

　　重做日志打开并没有使用O_DIRECT选项，所以会先写入文件系统缓存，要确保持久化了，必须调用fsync操作，这个操作的性能取决于磁盘，所以事务的性能由磁盘决定。

　　InnoDB允许用户不强制事务调用fsync操作，而是由最初提到的线程在一个周期中执行fsync操作。这个会提高性能，同时带来了宕机时丢失事务的风险。

　　innodb_flush_log_at_trx_commit用来控制重做日志刷新到磁盘的策略。默认为1，表示提交事务就执行一次fsync操作。0表示事务提交时不进行重做日志操作，这个操作在master thread中完成，其每秒进行一次fsync操作。2表示事务提交时将重做日志写入文件，但只写入文件系统缓存，不进行fsync操作。宕机时会丢失未从文件系统缓存刷入磁盘的那部分数据。50万行数据在0模式可能需要13秒，1模式2分钟，2模式23秒。所以0和2模式可以提高性能，但是事务的ACID特性就无法保证。

　　之前说过MySQL中的二进制日志文件，其用来进行POINT-IN-TIME的恢复和主从复制环境的建立。表面上和重做日志相似，都是记录了数据库的操作日志，但是两者有很大的不同。重做日志是InnoDB产生的，二进制日志是MySQL上层产生的。任何存储引擎的更改都会产生二进制日志。其次，两种日志的记录内容不同，二进制是一种逻辑日志，记录的是对应的SQL语句，InnoDB的重做日志是物理格式日志，记录的是对每个页的修改。此外，二进制日志是在事务提交完成后一次写入，重做日志是在进行中不断被写入。

3.1.2 log block

　　InnoDB中重做日志是512字节进行存储的，这意味着重做日志都是以块的方式进行保存的，称之为重做日志块，每块大小512字节。若一个页中产生的重做日志大于512字节，那么需要分割成多个重做日志块进行存储，因为是512字节，所以日志的写入可以保证原子性，不需要doublewrite技术。

　　重做日志包含日志块头及块尾，当然还有日志本身。日志头一共12字节，尾8个字节，实际可存储大小是492字节。

　　log block header:

　　　　LOG_BLOCK_HDR_NO　　  4字节

　　　　LOG_BLOCK_HDR_DATA_LEN　　2字节

　　　　LOG_BLOCK_FIRST_REC_GROUP　　2字节

　　　　LOG_BLOCK_CHECKPOINT_NO　　　4字节

　　log buffer好似一个数组，NO就是用来标记数组的位置，递增其循环使用。第一位用来判断flush bit，所以最大值是2^31。

　　LEN表明log block所占用的大小，最大就是0x200，512字节占满了。

　　REC_GROUP表示log block中第一个日志的偏移量，如果和LEN相同，表面当前log block不包含新的日志。比如第一个事务使用了500字节，超过了492的最大值，而第二个事务使用了100字节，所以就会有两个block，1事务的尾部和2事务的数据在一个block中。所以第二个block的偏移量为8+12。

　　CHECKPOINT_NO表示最后写入时的检查点4字节的值。

　　log block tailer就一个部分，和LOG_BLOCK_HDR_NO相同。

3.1.3 log group

　　log group是重做日志组，有多个重做日志文件。InnoDB源码中已支持log group镜像功能，但是在ha_innobase.cc文件中禁止了该功能。因此InnoDB存储引擎只有一个log group。

　　log group是一个概念，没有实际存储的物理文件。由多个重做日志文件组成，每个日志文件大小是相同的，在InnoDB 1.2之前，重做日志大小小于4GB，1.2版本提升了大小的限制为512GB。1.1版本就支持大于4GB的重做日志。

　　存储引擎运行过程中，log buffer是按一定的规则将内存中的log block刷新到磁盘：

　　　　事务提交时，log buffer的一半空间被使用，log checkpoint时。

　　对于log block的写入追加在redo log file的最后部分，当一个redo log file被写满时，会写入下一个redo log file，使用的方式是round-robin。

　　虽然log block总是追加在redo log file的最后，但是写入并不都是顺序的，因为除了保存了log block，还保存了一些其他信息，一共占2kb大小，即每个redo log file的前2kb都不保存log block的信息。对于log group的第一个redo log file,其前2kb的部分保存了4个512字节大小的块。

　　　　log file header  　　512

　　　　checkpoint1　　　  512

　　　　空　　　　　　　　512

　　　　checkpoint2　　　  512

　　上述信息只在每个log group的第一个file中保存。其余file 保留空间，但不保存信息。由于这些信息，导致写入不是顺序的，因为需要更新这2KB的数据，这些信息对于InnoDB的恢复操作十分重要。后面的checkpoint的值，设计上是交替写入，这样设计避免了因介质失败导致无法找到可用的checkpoint的情况。

3.1.4 重做日志格式

　　不同的数据库有对应的重做日志格式。此外，InnoDB存储是基于页的，所以重做日志格式也是基于页的。虽然有不同的重做日志格式，但是其有着通用的头部格式。

　　　　redo_log_type:重做日志的类型

　　　　space: 表空间的ID

　　　　page_no:页的偏移量

　　　　redo_log_body：根据重做日志类型不同，会有不同的存储内容，InnoDB1.2版本时，有51种重做日志格式，之后会越来越多。插入和删除的格式就不一样。

3.1.5 LSN

　　这个是Log Sequence Number的缩写，代表的是日志的序列号。占8个字节，单调递增。含义是：

　　　　重做日志的写入字节总量

　　　　checkpoint的位置

　　　　页的版本

　　LSN不仅记录在重做日志中，每个页中的头部有一个值FIL_PAGE_LSN记录了该页的LSN。在页中表示页最后操作的LSN的大小。因为重做日志是每个页的日志，所以可以通过LSN判断一个页是否需要进行恢复操作。如，P1页LSN是10000，数据库启动时检测到重做日志的LSN为13000，并且事务提交了，那么就需要进行恢复操作，将重做日志应用到P1页中。对于LSN小于P1页的LSN不需要进行重做，因为P1的LSN表明其已经操作完这些内容了。

3.1.6 恢复

　　数据库启动时会尝试进行恢复。因为重做日志记录的是物理日志，所以恢复速度比逻辑日志要快。InnoDB也对恢复进行了一定程度的优化，比如顺序读取并行应用重做日志，这样可以进一步提高数据库的恢复速度。

　　由于checkpoint表示已经刷新到磁盘页上的LSN，所以恢复只需要从checkpoint开始的日志部分。

3.2 undo

3.2.1 基本概念

　　重做日志记录了事务的行为，可以很好的通过其对页进行重做操作。但是事务有时候需要回滚，这时就需要undo。所以对数据库修改时，InnoDB存储引擎还会产生undo，回滚时就可以恢复原来的样子。

　　redo存放在重做日志文件中，undo放在数据库内部的一个特殊段中，这个就是undo段，位于共享表空间内。

　　undo并不是将数据库物理地恢复到执行语句或事务之前的样子，其是逻辑日志，是通过逻辑恢复的方式。所有修改被逻辑的取消了，但是数据结构和页本身在回滚后可能不大相同。这是因为在多用户并发系统中，可能有很多个事务对数据记录并发访问，一个事务在修改一个页中的几条数据，另一个事务在修改另外几个数据，不能将一个页回滚到事务开始的样子，之前提到过B+树修改可能会分裂，所以其中一个事务失败，回滚到之前的样子会影响到其他事务的执行。

　　undo的另一个作用就是MVCC，如果用户读取一行记录，被其他事务占用了，可以通过undo读取之前的行版本信息，实现非锁定读取。

　　最后undo是逻辑日志，操作也会产生redo log，这是因为undo log也需要持久性的保护。

3.2.2 undo存储管理

　　InnoDB存储引擎对undo采取段的方式管理，首先有rollback segment，每个回滚段中记录了1024个undo log segment，而在每个undo log segment段中进行undo页申请。共享表空间偏移量为5的页（0，5）记录了所有rollback segment header所在的页，这个页的类型为FIL_PAGE_TYPE_SYS。

　　1.1版本之前，只有一个rollback segment，所以支持的在线事务限制是1024，从1.1版本开始支持128个rollback segment，提高到了128*1024。这些数据依旧存储在共享表空间中。

　　1.2版本开始可以设置：

　　　　innodb_undo_directory: rollback segment文件所在的路径，意味着可以放在共享表以外的位置，默认"."表示当前InnoDB存储引擎的目录

　　　　innodb_undo_logs: 设置rollback segment的个数，默认128，替换了之前的innodb_rollback_segments参数

　　　　innodb_undo_tablespaces: 设置构成rollback segment文件的数量，这样rollback segment可以较平均地分布在多个文件中。可以看见undo前缀的文件，这个就是rollback segment文件了。

　　注意，undo log segment分配页并写入undo log这个过程也会写入重做日志，事务提交时，InnoDB会进行下面两件事：

　　　　将undo log放入列表中，供之后的purge操作

　　　　判断undo页是否可以重用，若可以分配给下个事务使用

　　事务提交后不会立刻删除undo log，因为其他事务需要通过undo log得到行记录之前的版本，所以事务提交时将undo放入一个链表，是否可以删除undo log及其页由Purge线程判断。

　　此外，分配undo页会浪费空间，所以需要重用。具体来说，事务提交时，将undo log放入链表，然后判断使用空间是否小于3/4，若小于表面可以被重用，之后的undo log记录在当前undo log的后面，由于存放undo log的记录的列表是以记录进行组织的，undo页可能存放不同事务的undo log，所以purge操作需要涉及磁盘的离散读取操作，是一个比较慢的过程。

3.2.3 undo log的格式

　　undo log分为：insert undo log和update undo log。

　　insert对于其他事务不可见，所以使用完后可以直接删除，不需要进行purge操作。

　　udpate对应delete和update操作，可能需要提高MVCC机制，所以不能直接删除，放入undo log链表中，等待purge确定。

　　这两种日志结构比较复杂，不进行叙述。

3.2.4 查看undo信息

　　information_schema下面有两张数据字典表：

　　　　INNODB_TRX_ROLLBACK_SEGMENT：查看rollback segment信息

　　　　INNODB_TRX_UNDO：记录事务对应的undo log。

　　delete操作并不是直接删除记录，而只是将记录标记为已删除，记录的最终删除是在purge操作完成的。

　　update操作是分两步实现的，首先将原主键记录标记为删除，需要产生一条TRX_UNDO_DEL_MARK_REC的undo log，之后插入一条新的记录，产生一条TRX_UNDO_INSERT_REC的undo log。

3.3 purge操作

　　上面提到delete和update操作并没有直接删除数据，这些操作都是将页标记成删除状态，最终删除操作是purge操作中完成的。因为InnoDB支持MVCC，所以记录不能在事务提交时立即进行处理，这时其他事务可能正在引用这行。如果这条记录不被任何事务引用，就可以进行真正的删除操作。

　　前面说过为了节省空间，undo的页被设计成：一个页上允许有多个事务的undo log存在，虽然不代表事务在全局过程中的提交顺序，但是后面的事务产生的undo log总在最后。此外，InnoDB还有一个history列表，它根据事务提交的顺序，将undo log进行链接。

　　在执行purge操作过程中，会从history list中找到第一个需要被清理的记录，trx1，清理之后会找其关联的undo log所在的页中是否存在可以被清理的记录，进行清理。找完后会继续找下一个需要被清理的记录，继续清理undo页，查找该页上可以被清理的其他记录。

　　总的来说就是先从history list找到undo log，再从undo page找到undo log，这样做的好处就在于避免大量随机读取操作，提高purge的效率。

　　全局动态参数innodb_purge_batch_size用来设置每次purge操作需要清理的undo page数量。InnoDB 1.2之前的默认值是20，之后默认是300。一般而言，设置的越大回收的undo page也就越多，这样可供重用的undo page就越多，减少了磁盘存储空间与分配的开销。不过，也不能设置的过大，会导致CPU和磁盘IO过于集中于对undo log的处理，性能下降。

　　另一方面，如果压力很大时，不能有效的进行purge操作。那么history list的长度会越来越长。全局动态参数innodb_max_purge_lag用来控制history list的长度，若长度大于这个参数时，会延迟DML操作。默认值是0，不做限制，大于0时计算方法是：

　　　　(history_list.length - innodb_max_purge_lag) * 10 - 5 = delay，单位毫秒

　　延缓的对象是行，不是一个DML操作。比如一个update涉及5行，总延迟时间就是5*delay。

　　InnoDB1.2有一个新的动态参数innodb_max_purge_lag_delay，来控制这个delay的最大值，避免缓慢导致其他SQL线程出现无限制的等待。

3.4 group commit

　　事务非只读操作时，每次提交时会进行一次fsync操作，保证日志写入磁盘。为了提高fsync效率，就提供了group commit的功能，即一次fsync可以刷新确保多个事务日志被写入文件。

　　对于InnoDB而言，事务提交会进行两个阶段的操作：

　　　　修改内存中事务对应的信息，并将日志写入重做日志缓冲

　　　　调用fsync将确保日志都从重做日志缓冲写入磁盘。

　　第2个步骤比1步骤慢，当执行2步骤时，其他事务可以进行1步骤，再进行2步骤，可以将多个事务的重做日志通过一次fsync刷新到磁盘，这样就大大减小了磁盘的压力。

　　在InnoDB1.2之前，开始了二级制日志后，group commit功能会失效，并且在线环境多使用replication环境，因此二进制日志的选项基本为开启状态，这个问题尤为显著。导致这个问题的原因在于：为了保证存储引擎层中的事务和二级制日志的一致性，二者之间使用了两段事务：

　　　　1.当事务提交时InnoDB进行prepare操作

　　　　2.MySQL数据库上层写入二进制日志

　　　　3.InnoDB存储引擎将日志写入重做日志文件

　　　　　　a.修改内存中事务对应的信息，并且将日志写入重做日志缓冲。

　　　　　　b.调用fsync将确保日志都从重做日志缓冲写入磁盘。

　　一旦步骤2完成，就确保了事务的提交，即使在执行步骤3时数据库发生了宕机。每个步骤需要执行一次fsync操作才能保证上下两层数据的一致性。步骤2的fsync由参数sync_binlog控制，步骤3的fsync由参数innodb_flush_log_at_trx_commit控制。

　　问题就出在顺序上面，为了保证二级制写入顺序和InnoDB的事务提交顺序，会使用prepare_commit_mutex锁，第三步的写入重做日志缓冲操作就不能在其他事务执行fsync操作时进行了，破坏了group commit。二级制顺序必须保证，因为备份和恢复时需要顺序的。

　　这个问题在2010年MySQL数据库打会中提出，后面有了解决方案，MySQL 5.6采取了类似的方式实现，称为Binary Log Group Commit（BLGC）。

　　数据库上层进行提交时，先按顺序放入队列中，队列的第一个事务为leader，其他的是follower。BLGC的步骤如下：

　　　　1.flush阶段，将每个事务的二级制日志写入内存中

　　　　2.Sync阶段，将内存的二进制日志刷新到磁盘，多个就只需要一次fsync就能完成，这就是BLGC

　　　　3.commit阶段，leader按照顺序调用存储引擎层事务的提交，InnoDB本就支持group commit，就没问题了。

　　当一组事务进行Commit时，其他的可以进行flush，使得group commit生效，group commit的效果和队列中事务的数量相关，数量越多，效果越好。

　　参数binlog_max_flush_queue_time用来控制flush阶段中等待的时间，即使之前的一组事务完成提交，当前的也不会马上进入sync，等待一段时间。好处就是group commit的事务就更多了，这可能会导致事务响应时间变慢。默认为0，推荐使用这个值。除非有100个连接，并且在不断的写入或更新操作。

4. 事务控制语句

　　MySQL命令行下事务是默认提交的。需要显示的开启一个事务BEGIN、START TRANSACTION或者执行命令SET AUTOCOMMIT=0。

　　事务控制语句：

　　　　START  TRANSACTION | BEGIN：显示地开启一个事务

　　　　COMMIT： 提交事务，修改成为永久的，COMMIT WORK含义类似

　　　　ROLLBACK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改， ROLLBACK WORK含义类似

　　　　SAVEPOINT indentifier：SAVEPOINT允许在事务中创建一个保存点，可以有多个保存点

　　　　RELEASE SAVEPOINT identifier：删除一个事务的保存点，没有保存点时会抛出异常

　　　　ROLLBACK TO [SAVEPOINT] identifier：这个语句和SAVEPOINT一起使用。可以把事务回滚到标记点，之前的工作保留。

　　　　SET TRANSACTION： 设置事务的隔离级别。InnoDB中有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。

　　COMMIT WORK的不同之处在于用来控制事务结束后的行为是CHAIN还是RELEASE，如果是CHAIN就变成了链事务。可以通过参数completion_type进行控制，默认是0，表示没有任何操作，和COMMIT相同。为1时就等同于COMMIT AND CHAIN，表示马上自动开启一个相同隔离级别的事务。为2时等同于COMMIT AND RELEASE，在事务提交后会自动断开与服务器的连接。

　　InnoDB存储引擎中的事务都是原子的，构成事务的语句都会提交或者回滚。延伸单个语句就是要么全部成功，要么完全回滚，因此一条语句失败并抛出异常时，不会导致先前执行的语句自动回滚，由用户自己决定是否对其进行提交或回滚操作。

　　另一个容易犯错的就是ROLLBACK TO SAVEPOINT，虽然有回滚，但是事务并没有结束，需要显示执行COMMIT或者ROLLBACK。

5. 隐式提交的SQL语句

　　以下的SQL语句会产生一个隐式的提交操作，即执行完后会有隐式的COMMIT操作。

　　　　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

6. 对于事务操作的统计

　　由于InnoDB是支持事务的，因此需要考虑每秒请求数QPS，同时要关注每秒事务的处理能力TPS。

　　计算TPS的方法是（com_commit+com_rollback)/time。前提是事务必须是显示提交的，存在隐式事务的提交和回滚（默认autocommit=1），不会计算到com_commit和com_rollback中。

　　还有两个参数handler_commit和handler_rollback用于事务的统计操作。

7. 事务的隔离级别

　　大部分数据库系统都没有提供真正的隔离性，最初可能是因为对这些问题的理解不到位。现在虽然清楚了，但是在正确性和性能之间做了妥协。ISO和ANIS SQL标准制定了4种事务隔离级别，但是很少有遵守这些标准的。标准的隔离级别如下：

　　　　1.READ UNCOMMITTED：称为浏览访问，仅仅针对事务而言的。

　　　　2.READ COMMITTED：称为游标稳定

　　　　3.REPEATABLE READ：2.9999°，没有幻读的保护

　　　　4.SERIALIZABLE：3°隔离，这个是默认的隔离级别

　　INNODB默认的隔离级别是REPEATABLE READ，但是和标准不同的是，其通过Next-key lock锁的算法，避免了幻读的产生。

　　隔离级别越低，事务请求的锁越少或保持锁的时间越短。这也就是为什么大部分数据库的事务隔离级别是READ COMMITTED了。

　　SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL

　　{

　　　　READ UNCOMMITTED

　　　　| READ COMMITTED

　　　　| REPEATABLE READ

　　　　| SERIALIZABLE

　　}

　　也可以通过配置文件，在MySQL启动时就设置默认的隔离级别：

　　　　[mysqld]

　　　　transaction-isolation=READ-COMMITTED

　　查看当前会话的事务隔离级别 select @@tx_isolation

　　在SERIALIABLE的事务隔离级别，InnoDB会对每个SELECT语句后自动加上LOCK IN SHARE MODE，这样对一致性非锁定度不再支持，理论上是符合数据库要求的，即事务是well-formed的，并且是two-phrased的。

　　因为InnoDB默认的REPEATABLE READ就能达到3°的隔离，所以一般事务不需要使用SERIALIABLE的隔离级别，那个主要用于分布式事务。

　　对于READ COMMITTED事务隔离级别下，除了唯一性的约束检查及外键约束的检测需要gap lock，InnoDB不会使用gap lock锁算法。但是，在MySQL5.1中，READ COMMITTED事务隔离级别默认只能工作在replication二进制日志为ROW的格式下，如果是STATEMENT，会出错误。在MySQL5.0版本以前，不支持ROW格式的二进制时，通过innodb_locks_unsafe_for_binlog设置为1可以在二进制日志为STATEMENT下使用READ COMMITTED的事务隔离级别，但是可能会造成主从不一致的现象。

　　　　在READ COMMITTED隔离级别下，没有使用gap lock锁定，导致可以插入数据。

　　　　在STTATEMENT格式记录的是master上产生的SQL语句，所以在master上是先删后插入，但是STATMENT格式的记录却是先插后删，逻辑顺序导致了不一致。

　　可以通过REPEATABLE隔离级别避免这个问题，MySQL5.1版本之后支持了ROW格式，就避免了这个问题。

8 分布式事务

8.1 MySQL数据库分布式事务

　　InnoDB存储引擎提供了对XA事务的支持，并通过XA事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源参与到一个全局的事务中。在使用分布式事务的时候，InnoDB必须设置成SERIALIZABLE。

　　XA事务允许不同数据库之间的分布式事务，比如一台服务是MySQL,一台是Oracle等。只要参与在全局事务中的每个节点都支持XA事务。

　　XA事务由一个或多个资源管理器、一个事务管理器以及一个应用程序组成。

　　　　资源管理器：提供访问事务资源的方法。通常一个数据库就是一个资源管理器

　　　　事务管理器：协调参与全局事务中的各个事务，需要和参与全局事务的所有资源管理器进行通信

　　　　应用程序：定义事务的边界，指定全局事务中的操作。

　　在MySQL中，资源管理器就是MySQL数据库，事务管理器为连接MySQL服务器的客户端。

　　分布式事务使用两段式提交的方式：

　　　　第一阶段，所有参与全局事务的节点都开始准备，告诉事务管理器它们准备好提交了

　　　　第二阶段，事务管理器告诉资源管理器执行ROLLBACK还是COMMIT。如果任何一个节点不能提交，所有节点被告知回滚。

　　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

　　当前JAVA的JTA可以很好的支持MySQL的分布式事务，需要使用分布式事务应该认真参考其API。

8.2 内部XA事务

　　之前讨论的分布式事务是外部事务，即资源管理器是MySQL数据库本身。还有一种分布式事务，其在存储引擎与插件之间，又或者是存储引擎和存储引擎之间，称为内部XA事务。

　　最常见的就是binlog与InnoDB之间的了，由于复制的需要，大部分数据库开启了binlog功能。提交时，先写二进制日志，再写重做日志，这两个操作必须是原子性的。所以使用了XA事务，在提交时，先做一个PREPARE操作，将事务的xid写入，接着进行二级制日志的写入。如果在InnoDB存储引擎提交前，MySQL宕机了，在重启后会检查准备的uxid事务是否提交，没有就会在存储引擎层再进行一次提交操作。

9.长事务

　　顾名思义，就是执行时间较长的事务，比如银行系统，每个阶段就要更新账户利息。对应的账号数量非常大。

　　　　update account set account_total = account_total + (1 + interest_rate)

　　问题在于如果故障了就要重新开始，这个代价太大了，回滚操作就有可能耗时巨大。因此对于长事务的处理，可以转化为小批量的事务来进行处理。当事务发送错误时，只需要回滚一部分数据，然后接着上次已完成的事务继续执行。

原文：https://www.cnblogs.com/lighten/p/9286604.html