MySQL技术内幕：事务（事务控制语句、隐式提交的SQL 语句、事务的隔离级别、分布式事务等）

asds事务(Transaction) 是数据库区别于文件系统的重要特性之一。在文件系统中，如果正在写文件，但是操作系统突然崩溃了，这个文件就很有可能被破坏。当然，有一些机制可以把文件恢复到某个时间点。不过，如果需要保证两个文件同步，这些文件系统可能就显得无能为力了。这正是数据库系统引入事务的主要目的：事务会把数据库从一种一致状态转换为另一种一致状态。在数据库提交工作时，可以确保要么所有修改都已经保存了，要么所有修改都不保存。

asdsInnoDB 存储引擎中的事务完全符合ACID 的特性。ACID 是以下4 个词的缩写：

ddss①、原子性(atomicity) asddss②、一致性(consistency) asddss③、隔离性(isolation) asddss④、持久性 (durability)

asdsadasdasdasdsadasdasdasdsadassdasdsadasdasdsadasdsadassadasdas————《MySQL技术内幕INNODB存储引擎》

---

事务控制语句

ssdss在MySQL 命令行的默认设置下，事务都是自动提交(auto commit) 的，即执行SQL 语句后就会马上执行COMMIT 操作。因此要显式地开启一个事务需使用命令BEGIN 、START TRANSACTION, 或者执行命令SET AUTOCOMMIT = 0, 禁用当前会
话的自动提交。
ssdsd 注 1：每个数据库厂商自动提交的设置都不相同，每个DBA 或开发人员需要非常明白这一点，这对之后的SQL 编程会有非凡的意义。

ssdss事务控制语句如下图：

ssdss注 2：START TRANSACTION 、BEGIN 语句都可以在MySQL 命令行下显式地开启一个事务。但是在存储过程中， MySQL 数据库的分析器会自动将BEGIN 识别为BEGIN …END, 因此在存储过程中只能使用START TRANSACTION 语句来开启一个事务。

ssdss注 3：COMMIT 和COMMIT WORK 语句基本是一致的，都是用来提交事务。不同之处在于COMMIT WORK 用来控制事务结束后的行为是CHAIN 还是RELEASE 的。如果是CHAIN 方式，那么事务就变成了链事务。用户可以通过参数completion_type 来进行控制：

ssddsss①、该参数默认为0, 表示没有任何操作。在这种设置下COMMIT 和COMMIT WORK 是完全等价的。

ssddsss②、当参数completion_type 的值为 1 时， COMMIT WORK 等同于COMMIT AND CHAIN, 即链事务，表示马上自动开启一个相同隔离级别的事务。

ssddsss③、参数completion_type为2 时， COMMIT WORK 等同于COMMIT AND RELEASE 。在事务提交后会自动断开与服务器的连接。

ssdss事务的两大注意点：

sewsdss①、构成事务的每条语句都会提交（成为永久）（除非SET AUTOCOMMIT = 0），或者所有语句都回滚。这种保护还延伸到单个的语句。一条语句要么完全成功，要么完全回滚（注意，这里说的是语句回滚）。因此一条语句失败并抛出异常时，并不会导致先前巳经执行的语句自动回滚。所有的执行都会得到保留，必须由用户自己来决定是否对其进行提交或回滚的操作。

sewsdss②、对于 ROLLBACK TO SAVEPOINT：虽然有ROLLBACK, 但其并不是真正地结束一个事务，因此即使执行了ROLLBACK TO SAVEPOINT, 之后也需要显式地运行COMMIT 或ROLLBACK 命令。即ROLLBACK TO SAVEPOINT命令并不真正地结束事务。

隐式提交的SQL 语句

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

ssdss注 4：在Microsoft SQL Server 数据库中，即使是DDL 也是可以回滚的。这和InnoDB 存储引擎、Oracle 这些数据库完全不同。另外，TRUNCATE TABLE 语句是DDL, 因此虽然和对整张表执行DELETE 的结果是一样的，但它是不能被回滚的（这又是和Microsoft SQL Server 数据不同的地方）。

对于事务操作的统计

ssdss由于InnoDB 存储引擎是支持事务的，因此InnoDB 存储引擎的应用需要在考虑每秒请求数 (Question Per Second, QPS) 的同时，应该关注每秒事务处理的能力 (Transaction Per Second, TPS) 。

ssdss计算TPS 的方法是 (com_ commit + com_rollback) /time 。但是利用这种方法进行计算的前提是：所有的事务必须都是显式提交的，如果存在隐式地提交和回滚（默认autocommit= 1) , 不会计算到com_commit 和com_rollback 变量中。

ssdssMySQL 数据库中另外还有两个参数 handler_commit 和handler_rollback 用于事务的统计操作。但是我注意到这两个参数在MySQL 5 . 1 中可以很好地用来统计InnoDB 存储引擎显式和隐式的事务提交操作，但是在InnoDB Plugin 中这两个参数的表现有些“怪异“，并不能很好地统计事务的次数。所以，如果用户的程序都是显式控制事务的提交和回滚，那么可以通过com_commit 和com_rollback 进行统计。如果不是，那么情况就显得有些复杂。

事务的隔离级别

ssdssISO 和ANIS SQL 标准制定了四种事务隔离级别的标准，但是很少有数据库厂商遵循这些标准。比如Oracle 数据库就不支持READ UNCOMMITTED 和REPEATABLE READ 的事务隔离级别。

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

sss①、READ UNCOMMITTED ssdss②、READ COMMITTEDssdss③、REPEATABLE READssdss④、SERIALIZABLE

ssdssSQL 和SQL2 标准的默认事务隔离级别是SERIALIZABLE 。InnoDB 存储引擎默认支持的隔离级别是REPEATABLE READ, 但是与标准SQL不同的是， InnoDB 存储引擎在REPEATABLE READ 事务隔离级别下，使用Next-Key Lock 锁的算法，因此避免幻读的产生。这与其他数据库系统（如Microsoft SQL Server数据库）是不同的。所以说， InnoDB 存储引擎在默认的REPEATABLE READ 的事务隔离级别下巳经能完全保证事务的隔离性要求，即达到SQL 标准的SERIALIZABLE 隔离级别。

ssdss注 5：理论上RR级别是无法解决幻读的问题, 但是由于InnoDB引擎的RR级别还使用了MVCC, 所以也就避免了幻读的出现!(这里需要再看)

ssdss注 6：隔离级别越低， 事务请求的锁越少或保持锁的时间就越短。这也是为什么大多数数据库系统默认的事务隔离级别是READ COMMITTED 。

ssdss注 7：大部分的用户质疑SERIALIZABLE 隔离级别带来的性能问题，但是根据Jim Gray 在《Transaction Processing 》一书中指出，两者的开销儿乎是一样的，甚至SERIALIZABLE 可能更优！！！因此在InnoDB 存储引擎中选择REPEATABLE READ 的事务隔离级别并不会有任何性能的损失。同样地，即使使用READ COMMITTED 的隔离级别，用户也不会得到性能的大幅度提升。

ssdss在InnoDB 存储引擎中，可以使用以下命令来设置当前会话或全局的事务隔离级别：

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

ssdss如果想在MySQL 数据库启动时就设置事务的默认隔离级别，那就需要修改MySQL的配置文件，在[mysqld] 中添加如下行：

[mysqld] transaction -isolation = READ-COMMITTED

ssdss查看当前会话的事务隔离级别，可以使用：

mysql>SELECT @@tx_ isolation\G;

ssdss查看全局的事务隔离级别，可以使用：

mysql>SELECT @@global.tx_isolation\G;

ssdss在SERIALIABLE 的事务隔离级别， InnoDB 存储引擎会对每个SELECT 语句后自动加上LOCK IN SHARE MODE, 即为每个读取操作加一个共享锁。因此在这个事务隔离级别下，读占用了锁，对一致性的非锁定读不再予以支持。这时，事务隔离级别SERIALIZABLE 符合数据库理论上的要求，即事务是well-formed 的，并且是twophrased的。SERIALIABLE 的事务隔离级别主要用于 InnoDB 存储引擎的分布式事务。

ssdss在READ COMMITTED 的事务隔离级别下，除了唯一性的约束检查及外键约束的检查需要gap lock, InnoDB 存储引擎不会使用gap lock 的锁算法。

分布式事务

MySQL 数据库分布式事务

ssdssInnoDB 存储引擎提供了对XA 事务的支持，并通过XA 事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源(transactional resources) 参与到一个全局的事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对于事务原有的ACID 要求又有了提高。

ssdss注 8：在使用分布式事务时， InnoDB 存储引擎的事务隔离级别必须设置为SERIALIZABLE 。

ssdssXA 事务允许不同数据库之间的分布式事务，如一台服务器是MySQL 数据库的，另一台是Oracle 数据库的，又可能还有一台服务器是SQL Server 数据库的，只要参与在全局事务中的每个节点都支持XA 事务。

ssdsseg：跨行，异地银行转账，一定需要使用分布式事务来保证数据（钱）的安全。

ssdssXA 事务由一个或多个资源管理器(Resource Managers) 、一个事务管理器( Transaction Manager) 以及一个应用程序( Application Program) 组成：

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

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

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

ssdss在MySQL 数据库的分布式事务中，资源管理器就是MySQL 数据库，事务管理器为连接MySQL 服务器的客户端，如下图：

ssdss分布式事务使用两段式提交(two-phase commit) 的方式：

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

ssddsdsss在第二阶段，事务管理器告诉资源管理器执行ROLLBACK 还是COMMIT 。如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚。可见与本地事务不同的是，分布式事务需要多一次的PREPARE 操作，待收到所有节点的同意信息后，再进行COMMIT 或是ROLLBACK 操作。

ssdss通过参数innodb_support_ xa 可以查看是否启用了XA 事务的支持（默认为ON ) :

mysql> SHOW VARIABLES LIKE ' innodb_support_xa ' \G;

内部XA 事务

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

ssdss最为常见的内部 XA 事务存在于 binlog 与lnnoDB 存储引擎之间。由于复制的需要，因此目前绝大多数的数据库都开启了binlog 功能。在事务提交时，先写二进制日志，再写InnoDB 存储引擎的重做日志。对上述两个操作的要求也是原子的，即二进制日志和重做日志必须同时写入。若二进制日志先写了，而在写入InnoDB 存储引擎时发生了宕机，那么slave 可能会接收到master 传过去的二进制日志并执行，最终导致了主从不一致的情况。

ssdsseg：如下左图，如果执行完①、②后在步骤③之前MySQL 数据库发生了宕机，则会发生主从不一致的情况。为了解决这个问题（下右图）， MySQL 数据库在 binlog 与 InnoDB 存储引擎之间采用XA 事务。当事务提交时， InnoDB 存储引擎会先做一个PREPARE 操作，将事务的xid 写入，接着进行二进制日志的写入，如果在InnoDB 存储引擎提交前， MySQL 数据库宥机了，那么MySQL 数据库在重启后会先检查准备的UXID 事务是否已经提交，若没有，则在存储引擎层再进行一次提交操作。

不好的习惯

在循环中提交（不建议）

ssdss在曾经使用过的数据库中，对事务的要求总是尽快地进行释放，不能有长时间的事务；其次，很多人可能担心存在Oracle 数据库中由于没有足够undo 产生的Snapshot Too Old 的经典问题。MySQL 的 lnnoDB 存储引擎没有上述两个问题，因此程序员不论从何种角度出发，都不应该在一个循环中反复进行提交操作，不论是显式的提交还是隐式的提交。

使用自动提交（不建议）

ssdss自动提交并不是一个好的习惯，MySQL 数据库默认设置使用自动提交(autocommit), 可以使用语句 SET autocommit = 0 来改变当前自动提交的方式。也可以使用START TRANSACTION, BEGIN 来显式地开启一个事务。在显式开启事务后，在默认设置下（即参数completion_type 等于0), MySQL 会自动地执行SET AUTOCOMMIT = 0 的命令，并在COMMIT 或ROLLBACK 结束一个事务后执行SET AUTOCOMMIT = 1。

ssdss注：对于不同语言的API. 自动提交是不同的。MySQL C API 默认的提交方式是自动提交，而MySQL Python API 则会自动执行SET AUTOCOMMIT=O, 以禁用自动提交。因此在选用不同的语言来编写数据库应用程序前，应该对连接MySQL 的API 做好研究。

使用自动回滚（不建议）

ssdssInnoDB 存储引擎支持通过定义一个 HANDLER 来进行自动事务的回滚操作，如在一个存储过程中发生了错误会自动对其进行回滚操作。

ssdss自动回滚操作带来的问题：不知道发生了错误，更不知道发生了什么样的错误。在程序中控制事务的好处是，用户可以得知发生错误的原因。

长事务

ssdss长事务(Long-Lived Transactions), 顾名思义，就是执行时间较长的事务。这时这个事务可能需要非常长的时间来完成，这取决于数据库的硬件配置。由于事务ACID 的特性，这个操作被封装在一个事务中完成。这就产生了一个问题，在执行过程中， 当数据库或操作系统、硬件等发生问题时，重新开始事务的代价变得不可接受。数据库需要回滚所有已经发生的变化，而这个过程可能比产生这些变化的时间还要长。因此，对于长事务的问题，有时可以通过转化为小批量（mini batch) 的事务来进行处理。当事务发生错误时，只需要回滚一部分数据，然后接着上次已完成的事务继续进行。

ssdss每完成一个小事务，将完成的结果存放在batchcontext 表中，表示已完成批量事务的最大账号ID 。若事务在运行过程中产生问题，需要重做事务，可以从这个已完成的最大事务ID 继续进行批量的小事务，这样重新开启事务的代价就显得比较低，也更容易让用户接受。batchcontext 表的另外一个好处是，在长事务的执行过程中，用户可以知道现在大概巳经执行到了哪个阶段。
ssdss注：在执行长事务的时候，在其中可以人为地加上了一个共享锁，以保证在事务的处理过程中，没有其他的事务可以来更新表中的数据，这是有意义的，并且也是非常有必要的操作。

💖感谢各位的暴击三连~💖