MySQL 语句：事务和锁定声明

关于

MySQL 通过诸如“SET autocommit”，“START TRANSACTION”，“COMMIT”和“ROLLBACK”之类的语句支持本地事务(在给定的 Client 端会话内)。

START TRANSACTION，COMMIT 和 ROLLBACK 语句

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}

这些语句可控制transactions的使用：

1. “START TRANSACTION” 或 “BEGIN” 开始新 Transaction。
2. “COMMIT” 提交当前事务，使其更改永久生效。
3. “ROLLBACK” 回滚当前事务，取消其更改。
4. “SET autocommit” 禁用或启用当前会话的默认自动提交模式。

默认情况下：MySQL 在启用autocommit模式的情况下运行。这意味着，当不在事务内时，每个语句都是原子的，就像被 START TRANSACTION 和 COMMIT 包围一样。您不能使用 ROLLBACK 撤消效果；但是，如果在语句执行期间发生错误，则会回滚该语句。

关于“禁用自动提交”：

1. 隐式禁用自动提交：

   要隐式禁用单个语句系列的自动提交模式，请使用“START TRANSACTION”语句：

   START TRANSACTION;
   SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
   UPDATE table2 SET summary=@A WHERE type=1;
   COMMIT;
   

   • 使用 START TRANSACTION 时，自动提交保持禁用状态，直到您以 COMMIT 或 ROLLBACK 结束事务。然后，自动提交模式将恢复为之前的状态。
2. 显式禁用自动提交：

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

   SET autocommit=0;
   

   • 通过将 autocommit 变量设置为零来禁用自动提交模式之后，对事务安全表（例如 InnoDB 或 NDB 的表）的更改不会立即变为永久不变。您必须使用“COMMIT”将更改存储到磁盘上，或使用“ROLLBACK”忽略更改。
   • autocommit 是会话变量，必须为每个会话设置。要为每个新连接禁用自动提交模式，请更改服务器系统变量的 autocommit 系统变量。

关于“transaction_characteristic”：

START TRANSACTION 允许几个修饰符来控制 TransactionFeature。要指定多个修饰符，请用逗号分隔：

1. “WITH CONSISTENT SNAPSHOT”修饰符为具有此功能的存储引擎启动“一致性读取”（consistent read）。【这仅适用于InnoDB】
   • 效果与在任何 InnoDB 表中发出 START TRANSACTION，然后发出 SELECT 的效果相同。
   • “WITH CONSISTENT SNAPSHOT”修饰符不会更改当前事务 isolation level，因此仅当当前隔离级别为允许一致读取的级别时，它才会提供一致的快照。允许一致读取的唯一隔离级别是“REPEATABLE READ”。对于所有其他隔离级别，将忽略“WITH CONSISTENT SNAPSHOT”子句。（从 MySQL 5.7.2 开始，忽略“WITH CONSISTENT SNAPSHOT”子句会生成警告）
2. “READ WRITE”和“READ ONLY”修饰符设置事务访问模式。它们允许或禁止更改事务中使用的 table。
   1. “READ WRITE”：如果未指定访问模式，则应用默认模式。除非更改了默认值，否则它是读/写的。不允许在同一语句中同时指定“READ WRITE”和“READ ONLY”。
   2. “READ ONLY”：限制防止事务修改或锁定其他事务可见的事务表和非事务表。
      • 当已知事务为只读时，MySQL 会对 InnoDB 表上的查询启用额外的优化。指定“READ ONLY”可以确保在无法自动确定只读状态的情况下应用这些优化。
      • 【事务仍然可以修改或锁定临时表：可以使用 DML 语句而非 DDL 语句进行更改】

关于“BEGIN [WORK]”：

支持“BEGIN”和“BEGIN WORK”作为“START TRANSACTION”的别名以启动事务。 “START TRANSACTION”是标准的 SQL 语法，是启动临时事务的推荐方法，并且允许“BEGIN”所不允许的修饰符。

• BEGIN 语句不同于使用 BEGIN 关键字来启动“BEGIN ... END”复合语句。后者不开始 Transaction。
• 在所有存储的程序（存储的过程和函数，触发器和事件）中，解析器将“BEGIN [WORK]”视为“BEGIN ... END”块的开头。在这种情况下，请改用“START TRANSACTION”开始 Transaction。

关于“COMMIT、ROLLBACK”：

COMMIT 和 ROLLBACK 支持可选的“WORK”关键字，及“CHAIN”和“RELEASE”子句（CHAIN 和 RELEASE 可用于对事务完成进行附加控制）。“completion_type”系统变量的值确定默认的完成行为。

1. “AND CHAIN”子句使新事务在当前事务结束时立即开始，并且新事务具有与刚刚终止的事务相同的隔离级别。新事务还使用与刚刚终止的事务相同的访问模式（“READ WRITE”或“READ ONLY”）。
2. “RELEASE”子句使服务器在终止当前事务之后断开当前 Client 端会话的连接。

• 包含“NO”关键字将禁止“CHAIN”和“RELEASE”完成，如果将“completion_type”系统变量设置为默认导致链接或释放完成，则这很有用。

关于“事务的使用”：

• 开始事务将导致任何未决事务被提交。
• 开始事务也会导致用“LOCK TABLES”获取的 table 锁被释放，就好像您已执行“UNLOCK TABLES”一样。开始事务不会释放通过“FLUSH TABLES WITH READ LOCK”获取的全局读取锁。
• 为了获得最佳结果，应该仅使用由单个事务安全存储引擎 Management 的 table 来执行事务。否则，可能会出现以下问题：
  1. 如果您使用来自多个事务安全存储引擎（例如InnoDB）的 table，并且事务隔离级别不是“SERIALIZABLE”，则当一个事务提交时，使用相同 table 的另一个正在进行的事务可能只会看到一些由第一个 Transaction 所做的更改。也就是说，使用混合引擎无法保证事务的原子性，并且可能导致不一致。
     • (如果不经常使用混合引擎事务，则可以根据需要使用设置 Transaction 隔离级别将每个事务的隔离级别设置为“SERIALIZABLE”。)
  2. 如果您在事务中使用不安全事务的 table，则无论自动提交模式的状态如何，都将立即存储对这些 table 的更改。
  3. 如果在更新事务中的非事务 table 之后发出“ROLLBACK”语句，则会出现“ER_WARNING_NOT_COMPLETE_ROLLBACK”警告。回滚对事务安全 table 的更改，但不回滚对非事务安全 table 的更改。
• 每个事务都在 COMMIT 上以一个块的形式存储在二进制日志中。回滚的事务不会记录。（exception ：无法回滚对非事务处理 table 的修改，如果回滚的事务包括对非事务处理 table 的修改，则整个事务都将在末尾使用ROLLBACK 语句记录下来，以确保对非事务处理 table 的修改被复制）。
• 您可以使用“SET TRANSACTION”语句更改事务的隔离级别或访问模式。
• 回滚可能是一个缓慢的操作，可能会隐式发生，而无需用户明确要求（例如，发生错误时）。因此，“SHOW PROCESSLIST”在会话的 State 列中显示 Rolling back，不仅用于使用 ROLLBACK 语句执行的显式回滚，而且还用于隐式回滚。
• （在 MySQL 5.7 中，BEGIN，COMMIT 和 ROLLBACK 不受“--replicate-do-db”或“--replicate-ignore-db”规则的影响。）

无法回滚的语句

某些语句无法回滚。通常，这些语句包括数据定义语言（DDL）语句，例如创建或删除数据库的语句，创建，删除或更改 table 或存储例程的语句。

• 您应设计您的 Transaction 不包含此类声明。如果您在无法回滚的事务中早期发出了一个语句，然后又有另一个语句失败，则在这种情况下，通过发出 ROLLBACK 语句无法回滚事务的全部效果。

导致隐式提交的语句

这些语句大多数会在执行后导致隐式提交。目的是在自己的特殊事务中处理每个这样的语句，因为它无论如何都不能回滚。

• 事务控制和锁定语句是例外：如果隐式提交发生在执行之前，则另一个不会发生。【？？？】

1. 定义或修改数据库对象的数据定义语言（DDL 语句）：“DROP INDEX”，“DROP PROCEDURE”，“DROP SERVER”，“DROP TABLE”，“DROP TRIGGER”，“DROP VIEW”，“INSTALL PLUGIN”，“RENAME TABLE”，“TRUNCATE TABLE”，“UNINSTALL PLUGIN”。
   • “ALTER FUNCTION”，“CREATE FUNCTION”和“DROP FUNCTION”与存储函数一起使用时，也会引起隐式提交，但与用户定义的函数一起使用时不会。（“ALTER FUNCTION”只能与存储函数一起使用）
   • 如果使用“TEMPORARY”关键字，则“CREATE TABLE”和“DROP TABLE”语句不提交事务。（这不适用于确实导致提交的临时表，例如“ALTER TABLE”和“CREATE INDEX”上的其他操作）。但是，尽管没有隐式提交发生，但是语句也不能回滚，这意味着使用此类语句会导致事务性违反原子性。例如，如果您使用创建临时 table 然后回滚该事务，则该 table 仍然存在。
   • InnoDB 中的“CREATE TABLE”语句作为单个事务处理。
   • 创建非临时表时，“CREATE TABLE ... SELECT”会在执行语句之前和之后导致隐式提交。（“CREATE TEMPORARY TABLE ... SELECT”没有提交）
2. 隐式使用或修改mysql数据库中表的语句：“ALTER USER”，“CREATE USER”，“DROP USER”，“GRANT”，“RENAME USER”，“REVOKE”，“SET PASSWORD”。
3. Transaction 控制和锁定语句：“BEGIN”，“LOCK TABLES”，“SET autocommit = 1”(如果该值尚未为 1)，“START TRANSACTION”，“UNLOCK TABLES”。
   • 仅当当前已使用“LOCK TABLES”锁定任何 table 以获取非事务性 table 锁时，“UNLOCK TABLES”才会提交事务。 “FLUSH TABLES WITH READ LOCK”的“UNLOCK TABLES”不会发生提交，因为其不获取 table 级锁。
   • 事务不能嵌套：这是您发出“START TRANSACTION”语句或其同义词之一时对任何当前事务执行的隐式提交的结果。
   • 当事务处于 ACTIVE 状态时，不能在 XA 事务中使用导致隐式提交的语句。【？？？】
   • “BEGIN”语句不同于使用 BEGIN 关键字来启动“BEGIN ... END”复合语句。后者不会导致隐式提交。
4. 数据加载语句：“LOAD DATA”。
   • LOAD DATA 仅对使用 NDB 存储引擎的 table 引起隐式提交。
5. 管理类声明：“ANALYZE TABLE”，“CACHE INDEX”，“CHECK TABLE”，“FLUSH”，“LOAD INDEX INTO CACHE”，“OPTIMIZE TABLE”，“REPAIR TABLE”，“RESET”。
6. 复制控制语句：“START SLAVE”，“STOP SLAVE”，“RESET SLAVE”，“CHANGE MASTER TO”。

SAVEPOINT，ROLLBACK TO SAVEPOINT 和 RELEASE SAVEPOINT 语句

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

InnoDB支持 SQL 语句“SAVEPOINT”，“ROLLBACK TO SAVEPOINT”，“RELEASE SAVEPOINT”和“ROLLBACK”的可选“WORK”关键字。

1. “SAVEPOINT”语句设置名称为 identifier 的命名事务保存点。
   • 如果当前事务具有相同名称的保存点，则将删除旧的保存点并设置一个新的保存点。
2. “ROLLBACK TO SAVEPOINT”语句将事务回滚到指定的保存点，而不会终止该事务。设置保存点后，当前事务对行所做的修改将在回滚中撤消，但是 InnoDB 不会不释放保存点之后存储在内存中的行锁。（对于新插入的行，锁信息由该行中存储的事务 ID 携带；锁不会单独存储在内存中。在这种情况下，该行锁将在撤消中释放。）在比命名保存点晚的时间被删除。
   • 如果语句返回以下错误，则表示不存在具有指定名称的保存点：

     ERROR 1305 (42000): SAVEPOINT identifier does not exist
     

3. “RELEASE SAVEPOINT”语句从当前事务的保存点集中删除命名的保存点。没有提交或回滚发生。如果保存点不存在，则发生错误。

• 如果执行未命名保存点的“COMMIT”或“ROLLBACK”，则当前事务的所有保存点都将被删除。
• 当调用存储功能或激活触发器时，将创建一个新的保存点级别。先前级别上的保存点将不可用，因此不会与新级别上的保存点发生冲突。函数或触发器终止时，将释放它创建的所有保存点，并还原先前的保存点级别。

LOCK TABLES 和 UNLOCK TABLES 语句

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}

UNLOCK TABLES