MySQL 语句：事务和锁定声明

来自Wikioe
Eijux（讨论 | 贡献）2021年4月14日 (三) 04:01的版本 （建立内容为“category:MySQL == 关于 == MySQL 通过诸如“SET autocommit”，“START TRANSACTION”，“COMMIT”和“ROLLBACK”之类的语句支持本地事务…”的新页面）
(差异) ←上一版本 | 最后版本 (差异) | 下一版本→ (差异)
跳到导航 跳到搜索


关于

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。







取自“http://wiki.eijux.com/index.php?title=MySQL_语句：事务和锁定声明&oldid=2340”