说在前面

在阅读后面的文章之前，请先了解事务的概念，事务的特性以及MySQL的隔离级别。

事务 事务是可以提交或回滚的工作的原子单位。当一个事务对数据库进行多次更改时，要么所有更改在事务提交时成功，要么所有更改在事务回滚时撤消。 InnoDB实现的数据库事务具有首字母缩略词ACID所共有的属性，包括原子性、一致性、隔离性和持久性。 请参阅ACID, commit, isolation level, lock, rollback. MySQL隔离级别： MySQL隔离级别：MySQL 事务隔离级别 · 语雀

语法结构

```sql START TRANSACTION [transaction_characteristic [, transaction_characteristic] …]

transaction_characteristic: { WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY }

BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET autocommit = {0 | 1}

  2. 这些语句提供了对事务使用的控制:
  4. - `START TRANSACTION` 或 `BEGIN` 开始一个新的事务。
  5. - `COMMIT`提交当前事务。
  6. - `ROLLBACK` 回滚当前事务。
  7. - 设置 `autocommit` 将禁用或启用当前会话的默认自动提交模式。
  9. autocommit
  10. > 在每个SQL语句之后引发提交操作的设置。不建议使用这种模式来处理跨越多个语句的InnoDB表。它可以提高InnoDB表上只读事务的性能，最大限度地减少锁定和生成撤销数据的开销，尤其是在MySQL 5.6.4及以上版本中。它也适用于处理不适用事务的MyISAM表。
  13. 默认情况下，MySQL运行时启用了自动提交模式([autocommit](https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_autocommit))。这意味着，如果不在事务中执行其他操作，则每个语句都是原子性的，就好像它被`START TRANSACTION`和`COMMIT`包围一样。您不能使用回滚来撤消效果;但是，如果在语句执行期间发生错误，则回滚语句。
  15. 若要隐式禁用单个语句系列的自动提交模式，请使用 `START TRANSACTION` 语句:
  16. ```sql
  17. START TRANSACTION;
  18. SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
  19. UPDATE table2 SET summary=@A WHERE type=1;
  20. COMMIT;

使用START TRANSACTION，autocommit将保持禁用状态，直到您使用COMMIT或ROLLBACK结束事务。然后自动提交模式恢复到以前的状态。

START TRANSACTION

START TRANSACTION允许几个控制事务特征的修饰符。若要指定多个修饰符，请用逗号分隔它们。

  • WITH CONSISTENT SNAPSHOT修饰符为能够进行读取的存储引擎启动一致的读取。这只适用于InnoDB。其效果与发出一个START TRANSACTION，然后从InnoDB表中执行SELECT是一样的。参见官方文档Section 14.7.2.3, “Consistent Nonlocking Reads”。WITH CONSISTENT SNAPSHOT修饰符不会更改当前事务隔离级别，因此仅当当前隔离级别允许一致读取时，它才提供一致的快照。唯一允许一致读的隔离级别是REPEATABLE READ。对于所有其他隔离级别，将忽略WITH CONSISTENT SNAPSHOT子句。从MySQL 5.7.2开始，当忽略了WITH CONSISTENT SNAPSHOT子句时，将生成一个警告。
  • READ WRITE和READ ONLY修饰符设置事务访问模式。它们允许或禁止对事务中使用的表进行更改。只读限制阻止事务修改或锁定对其他事务可见的事务性和非事务性表;事务仍然可以修改或锁定临时表。
    • 当事务是READ ONLY的时候，MySQL支持对InnoDB表的查询进行额外的优化。指定只读将确保在无法自动确定只读状态的情况下应用这些优化。有关更多信息，请参见Section 8.5.3, “Optimizing InnoDB Read-Only Transactions” 。
    • 如果没有指定访问模式，则应用默认模式。默认为READ WRITE模式。不允许在同一语句中同时指定READ ONLY和READ ONLY。在READ ONLY模式下，仍然可以使用DML语句更改用TEMPORARY关键字创建的表。不允许使用DDL语句进行修改。
    • 有关事务访问模式的更多信息，包括更改默认模式的方法，请参见官方文旦 Section 13.3.6, “SET TRANSACTION Statement”。
    • 如果启用了read_only系统变量，那么使用START TRANSACTION READ WRITE显式地启动一个事务需要SUPER权限。

IMPORTANT许多用于编写MySQL客户机应用程序(如JDBC)的api都提供了自己的启动事务的方法，可以(有时应该)使用这些方法，而不是从客户机发送一个启动事务语句。有关更多信息，请参见官方文档 Chapter 27, Connectors and APIs，连接器和API，或您的API的文档。

要显式禁用自动提交模式，请使用以下语句:

SET autocommit=0;
  • 在通过将autocommit变量设置为0来禁用autocommit模式之后，对事务安全表(例如InnoDB或NDB表)的更改不会立即成为永久性的。必须使用COMMIT将更改存储到磁盘，或者使用ROLLBACK忽略更改。
  • autocommit是一个会话变量，必须为每个会话设置。要禁用每个新连接的自动提交模式，请参见5.1.7节“服务器系统变量”中对自动提交系统变量的描述。
  • BEGIN和BEGIN WORK支持作为启动事务的START TRANSICTION别名。START TRANSACTION是标准的SQL语法，是启动ad-hoc事务的推荐方法，并且允许添加修饰符，而BEGIN不允许。
  • BEGIN语句与以BEGIN...END复合语句开头的BEGIN关键字不同。后者不启动一个事务。参见官方文档Section 13.6.1, “BEGIN … END Compound Statement”。

NOTE在所有stored progra**m(**stored procedures and functions, triggers, and events)中，解析器将BEGIN [WORK]视为BEGIN...END块的开头。在这个上下文中使用START TRANSACTION开始事务。

COMMIT AND ROLLBACK

COMMIT和ROLLBACK支持可选的WORK关键字，CHAIN和RELEASE子句也是如此。CHAIN和RELEASE可用于对事务完成的额外控制。completion_type 系统变量的值决定了默认的完成行为。参见官方文档 Section 5.1.7, “Server System Variables”。

AND CHAIN句导致新事务在当前事务结束时立即开始，并且新事务与刚终止的事务具有相同的隔离级别。新事务也使用与刚终止的事务相同的访问模式 (READ WRITE或READ ONLY)。RELEASE子句导致服务器在终止当前事务后断开当前客户端会话。包含NO 关键字可抑制CHAIN或RELEASE，如果默认情况下 completion_type 系统变量设置开启chaining或release。

开始一个事务会导致提交任何挂起的事务。参加官方文档Section 13.3.3, “Statements That Cause an Implicit Commit”。
开始一个事务还会导致释放使用 LOCK TABLES 获取的表锁，就像您执行了 UNLOCK TABLES 一样。开始一个事务不会释放用 FLUSH TABLES WITH REALOCK 获取的全局读取锁。

BEST PRACTICE为了获得最佳结果，应仅使用由单个事务安全存储引擎管理的表来执行事务。否则，可能会出现以下问题:

  • 如果您使用来自多个事务安全存储引擎 (如 InnoDB) 的表，并且事务隔离级别不是SERIALIZABLE，则可能在一个事务提交时,另一个使用相同表的正在进行的事务将只看到第一个事务所做的一些更改。也就是说，混合引擎不能保证交易的原子性，并且会导致不一致。(如果混合引擎事务不频繁，您可以根据需要使用设置事务隔离级别将每个事务的隔离级别设置为 SERIALIZABLE 。)
  • 如果您在事务中使用非事务安全的表，对这些表的更改将立即存储，而不管自动提交模式的状态如何。
  • 如果在更新事务中的非事务性表后发出 ROLLBACK 语句，则会出现 ER_WARNING_NOT_COMPLETE_ROLLBACK 警告。对事务安全表的更改会回滚，但对非事务安全表的更改不会回滚。

COMMIT时，每个事务都存储在二进制日志中的一个块中。回滚的事务不会被记录。(例外: 对非事务性表的修改不能回滚。如果回滚的事务包括对非事务性表的修改，则在末尾用ROLLBACK语句记录整个事务，以确保对非事务性表的修改被复制。)参见官方文档Section 5.4.4, “The Binary Log”。
您可以使用 SET TRANSACTION 语句更改事务的隔离级别或访问模式。参见官方文档Section 13.3.6, “SET TRANSACTION Statement”。
回滚可能是一个缓慢的操作，可能会在用户没有明确要求的情况下隐式地发生 (例如，发生错误时)。因此，SHOW PROCESSLIST 在会话的STATE列中显示ROLLBACK，不仅用于使用 ROLLBACK 语句执行的显式回滚，也用于隐式回滚。

NOTE在 MySQL 5.7中, BEGIN, COMMIT, 和 ROLLBACK 不受 --replicate-do-db or --replicate-ignore-db 规则影响.

不能回滚的语句

一些语句不能回滚。一般，这些包括数据定义语言 (DDL) 语句，如创建或删除数据库，那些创建、删除或更改表或存储程序(store procedure/even/function/trigger)。
你应该设计不包含这些语句的事务。如果在一个事务中的语句无法回滚，然后另一个语句失败，这将导致事务不能回滚，例如使用ROLLBACK。

https://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.html

导致隐式提交的语句

本节中列出的语句 (以及它们的任何同义词) 隐含地结束当前会话中活动的任何事务，就像您在执行语句之前已经提交一样。
这些语句中的大多数在执行后也会导致隐式提交。目的是在自己的特殊事务中处理每个这样的语句，无论如何都不能回滚。事务控制和锁定语句是例外: 如果隐式提交在执行之前发生，则在执行之后不会发生另一个。

  • 定义或修改数据库对象的Data Definition Language (DDL) 语句。 ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME, ALTER EVENT, ALTER PROCEDURE, ALTER SERVER, ALTER TABLE, ALTER VIEW, CREATE DATABASE, CREATE EVENT, CREATE INDEX, CREATE PROCEDURE, CREATE SERVER, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, DROP DATABASE, DROP EVENT, DROP INDEX, DROP PROCEDURE, DROP SERVER, DROP TABLE, DROP TRIGGER, DROP VIEW, INSTALL PLUGIN, RENAME TABLE, TRUNCATE TABLE, UNINSTALL PLUGIN.
    • 当使用stored functions时，ALTER FUNCTION, CREATE FUNCTION 和 DROP FUNCTION 也会导致一个隐式提交 , 但是user-defined functions不会. (ALTER FUNCTION can only be used with stored functions.)
    • 如果使用TEMPORARY关键字，则 CREATE TABLE 和 DROP TABLE语句不会提交事务。(这不适用于临时表上的其他操作，例如 ALTER TABLE 和 CREATE INDEX 会导致提交事务。)但是，即使没有发生隐式提交，这些语句也不能回滚，这意味着使用此类语句会导致违反事务原子性。例如，如果您使用 CREATE TEMPORARY TABLE，然后回滚事务，则该表仍然存在。
    • InnoDB 中的 CREATE TABLE语句作为单个事务处理。这意味着用户的ROLLBACK不会撤消用户在该事务期间所使用的 CREATE TABLE 语句。
    • 当你创建非临时表时，CREATE TABLE ... SELECT 会在该语句执行前后导致隐式提交。（CREATE TEMPORARY TABLE ... SELECT并不会提交）
  • 隐式使用或修改 mysql 数据库中的表的语句. ALTER USER, CREATE USER, DROP USER, GRANT, RENAME USER, REVOKE, SET PASSWORD。
  • 事务控制和锁定语句。 BEGIN, LOCK TABLES, SET autocommit = 1 (if the value is not already 1), START TRANSACTION, UNLOCK TABLES。
    • 只有当任何表当前已被LOCK TABLE锁定以获取非事务性表锁时，UNLOCK TABLES 才会提交事务。FLUSH TABLES WITH READ LOCK 之后的UNLOCK TABLES 不会发生提交，因为后者语句不会获取表级锁。
    • 事务不能嵌套。这是在发出 START TRANSACTION 语句或其同义词时对任何当前事务执行的隐式提交的结果。
    • 当事务处于活动状态时，不能在 XA 事务中使用导致隐式提交的语句。
  • 数据加载语句. LOAD DATA. LOAD DATA 会导致NDB引擎的表隐式提交。
  • Administrative语句. ANALYZE TABLE, CACHE INDEX, CHECK TABLE, FLUSH, LOAD INDEX INTO CACHE, OPTIMIZE TABLE, REPAIR TABLE, RESET.
  • 备份控制语句. START SLAVE, STOP SLAVE, RESET SLAVE, CHANGE MASTER TO.

    参考：https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT语句

SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

对于ROLLBACK，InnoDB支持SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT语句和可选的WORK关键字。

  • SAVEPOINT：SAVEPOINT 语句使用indentifier的名称设置一个命名事务保存点(NAMED SAVEPOINT)。如果当前事务具有同名的SAVEPOINT，则删除旧的SAVEPOINT并设置新的SAVEPOINT。
  • ROLLBACK TO SAVEPOINT 语句将事务回滚到指定的SAVEPOINT，而不终止事务。在SAVEPOINT后对当前事务的修改在回滚中被撤消，但是 InnoDB 不会释放SAVEPOINT后存储在内存中的行锁。(对于新插入的行，锁信息由存储在行中的事务 ID 携带; 锁不单独存储在内存中。在这种情况下，行锁在撤消中释放。)将删除在晚于NAMED SAVEPOINT的时间设置的SAVEPOINT。
  • RELEASE SAVEPOINT 语句从当前事务的保存点集中删除SAVEPOINT。不会发生提交或回滚。如果保存点不存在，则为错误。
  • 如果执行没有命名保存点的COMMIT或ROLLBACK，则将删除当前事务的所有SAVEPOINT。
  • 当调用存储函数或激活触发器时，将创建新的保存点级别。以前级别的保存点变得不可用，因此不会与新级别的保存点冲突。当函数或触发器终止时，它创建的任何保存点都将被释放，并恢复以前的保存点级别。

    参考：https://dev.mysql.com/doc/refman/5.7/en/savepoint.html