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的使用：

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

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

关于“禁用自动提交”：

隐式禁用自动提交：
要隐式禁用单个语句系列的自动提交模式，请使用“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 结束事务。然后，自动提交模式将恢复为之前的状态。
显式禁用自动提交：
要显式禁用自动提交模式，请使用以下语句：
```
SET autocommit=0;
```
- 通过将 autocommit 变量设置为零来禁用自动提交模式之后，对事务安全表（例如 InnoDB 或 NDB 的表）的更改不会立即变为永久不变。您必须使用“COMMIT”将更改存储到磁盘上，或使用“ROLLBACK”忽略更改。
- autocommit 是会话变量，必须为每个会话设置。要为每个新连接禁用自动提交模式，请更改服务器系统变量的 autocommit 系统变量。

关于“transaction_characteristic”：

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

“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”子句会生成警告）
“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”系统变量的值确定默认的完成行为。

“AND CHAIN”子句使新事务在当前事务结束时立即开始，并且新事务具有与刚刚终止的事务相同的隔离级别。新事务还使用与刚刚终止的事务相同的访问模式（“READ WRITE”或“READ ONLY”）。
“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 语句无法回滚事务的全部效果。

导致隐式提交的语句

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

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

定义或修改数据库对象的数据定义语言（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”没有提交）
隐式使用或修改mysql数据库中表的语句：“ALTER USER”，“CREATE USER”，“DROP USER”，“GRANT”，“RENAME USER”，“REVOKE”，“SET PASSWORD”。
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”复合语句。后者不会导致隐式提交。
数据加载语句：“LOAD DATA”。
- LOAD DATA 仅对使用 NDB 存储引擎的 table 引起隐式提交。
管理类声明：“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”。

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”关键字。

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

MySQL 使 Client 端会话能够显式地获取 table 锁，以便与其他会话协作访问 table，或者防止其他会话在会话需要互斥访问期间修改 table。

会话只能为其自身获取或释放锁。一个会话无法获取另一会话的锁，也不能释放另一会话持有的锁。

NOTE：

“LOCK TABLES”：
- 在更新 table 时，锁可用于模拟事务或提高速度。
- LOCK TABLES 明确获取当前 Client 端会话的 table 锁。可以为基本 table 或视图获取 table 锁。您必须具有 LOCK TABLES 特权和 SELECT 特权才能锁定每个对象。
- 对于视图锁定，LOCK TABLES 将视图中使用的所有基本 table 添加到要锁定的 table 集中，并自动锁定它们。【从 MySQL 5.7.32 开始，LOCK TABLES检查视图定义器是否对基于视图的 table 具有适当的特权】
- 如果使用 LOCK TABLES 显式锁定 table，则触发器中使用的任何 table 也会隐式锁定。
“UNLOCK TABLES”：
- UNLOCK TABLES 明确释放当前会话持有的所有 table 锁。 LOCK TABLES 在获取新锁之前隐式释放当前会话持有的所有 table 锁。
- UNLOCK TABLES 的另一个用途是释放通过“FLUSH TABLES WITH READ LOCK”语句获取的全局读取锁定，这使您可以锁定所有数据库中的所有 table。【？？？】
- table 锁只能防止其他会话进行不适当的读取或写入。
  1. 持有“WRITE”锁的会话可以执行 table 级操作，例如“DROP TABLE”或“TRUNCATE TABLE”。
  2. 对于持有“READ”锁的会话，不允许“DROP TABLE”和“TRUNCATE TABLE”操作。

以下讨论仅适用于非 TEMPORARY 表。

TEMPORARY 表允许“LOCK TABLES”(但被忽略)。可以通过创建 table 的会话自由访问该 table，而不考虑可能执行了哪些其他锁定。无需锁定，因为没有其他会话可以看到该 table。

表锁的获取

表锁的释放

表锁与事务的交互

LOCK TABLES 与 Triggers

表锁定的限制和条件

XA 事务

SET TRANSACTION 语句

SET [GLOBAL | SESSION] TRANSACTION
    transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic: {
    ISOLATION LEVEL level
  | access_mode
}

level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

access_mode: {
     READ WRITE
   | READ ONLY
}

该语句指定 transaction 的 Feature。它采用由逗号分隔的一个或多个 Feature 值的列表。每个 Feature 值设置事务 isolation level 或访问模式。

隔离级别用于 InnoDB 表上的操作。
访问模式指定事务是以读/写还是只读模式运行。

此外，SET TRANSACTION 可以包含可选的“GLOBAL”或“SESSION”关键字以指示语句的范围。

Transaction 隔离级别

要设置事务隔离级别，请使用“ISOLATION LEVEL level”子句。不允许在同一“SET TRANSACTION”语句中指定多个“ISOLATION LEVEL”子句。

默认隔离级别为“REPEATABLE READ”。其他允许的值为“READ COMMITTED”，“READ UNCOMMITTED”和“SERIALIZABLE”。
【有关隔离级别的信息，请参见：InnoDB 存储引擎下，“锁和事务模型”中的“事务隔离级别”相关内容。】

Transaction 访问模式

要设置事务访问模式，请使用“READ WRITE”或“READ ONLY”子句。不允许在同一“SET TRANSACTION”语句中指定多个访问模式子句。

“READ WRITE”：（默认模式）允许对事务中使用的表进行读写。
- 可以使用“SET TRANSACTION”和“READ WRITE”的访问模式来明确指定此模式。
“READ ONLY”：禁止更改 table。
- 这可以使存储引擎能够进行性能改进，这在不允许写入的情况下是可能的。
- 在只读模式下，仍然可以使用 DML 语句更改用 TEMPORARY 关键字创建的 table。与永久 table 一样，不允许使用 DDL 语句进行更改。

也可以使用“START TRANSACTION”语句为单个事务指定“READ WRITE”和“READ ONLY”访问模式。

Transaction Feature 范围

可以为当前会话或仅针对下一个事务全局设置事务 Feature：

使用“GLOBAL”关键字：
- 该声明适用于所有后续会话。
- 现有会话不受影响。
使用“SESSION”关键字：
- 该声明适用于当前会话中执行的所有后续事务。
- 该语句在事务中是允许的，但不会影响当前正在进行的事务。
- 如果在事务之间执行，则该语句将覆盖设置命名特征的下一个事务值的任何前面的语句。【？？？】

没有任何“SESSION”或“GLOBAL”关键字：

该语句仅适用于会话中执行的下一个单个事务。
随后的事务将恢复为使用命名 Feature 的会话值。

事务中的如下声明是不允许的：【？？？】

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.02 sec)

mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ERROR 1568 (25001): Transaction characteristics can't be changed
while a transaction is in progress

NOTE：

更改全局事务 Feature 需要“SUPER”特权。任何会话都可以自由更改其会话 Feature（即使在事务中间），也可以更改其下一个事务的 Feature（在该事务开始之前）。
要在服务器启动时设置全局隔离级别，请在命令行或选项文件中使用“--transaction-isolation=level”选项。
- 【此选项的 level 值使用破折号而不是空格，因此允许的值为“READ-UNCOMMITTED”，“READ-COMMITTED”，“REPEATABLE-READ”或“SERIALIZABLE”。】
要在服务器启动时设置全局事务访问模式，请使用“--transaction-read-only”选项。默认值为“OFF”(读/写模式)，但对于只读模式为“ON”。
在运行时，可以使用“SET TRANSACTION”语句间接设置全局，会话和下一个事务作用域级别的 Feature。也可以使用“SET”语句直接设置它们，以将值分配给“transaction_isolation”和“transaction_read_only”系统变量：
- 【在 MySQL 5.7.20 之前，请使用“tx_isolation”和“tx_read_only”而不是 transaction_isolation 和 transaction_read_only。】
- “SET TRANSACTION”允许使用可选的“GLOBAL”和“SESSION”关键字来设置不同范围级别的 TransactionFeature。
- 用于为“transaction_isolation”和“transaction_read_only”系统变量分配值的“SET”语句具有用于在不同作用域级别设置这些变量的语法。
  语法如下：

TransactionFeature 的“SET TRANSACTION”语法
Syntax	受影响的 Feature 范围
SET GLOBAL TRANSACTION transaction_characteristic	Global
SET SESSION TRANSACTION transaction_characteristic	Session
SET TRANSACTION transaction_characteristic	仅下一个 Transaction

TransactionFeature 的“SET”语法
Syntax	受影响的 Feature 范围
SET GLOBAL var_name = value	Global
SET @@GLOBAL.var_name = value	Global
SET SESSION var_name = value	Session
SET @@SESSION.var_name = value
SET var_name = value
SET @@var_name = value	仅下一个 Transaction

可以在运行时检查事务 Feature 的全局和会话值：

SELECT @@GLOBAL.transaction_isolation, @@GLOBAL.transaction_read_only;
SELECT @@SESSION.transaction_isolation, @@SESSION.transaction_read_only;

MySQL 语句：事务和锁定声明

目录

关于

START TRANSACTION，COMMIT 和 ROLLBACK 语句

无法回滚的语句

导致隐式提交的语句

SAVEPOINT，ROLLBACK TO SAVEPOINT 和 RELEASE SAVEPOINT 语句

LOCK TABLES 和 UNLOCK TABLES 语句

表锁的获取

表锁的释放

表锁与事务的交互

LOCK TABLES 与 Triggers

表锁定的限制和条件

XA 事务

SET TRANSACTION 语句

Transaction 隔离级别

Transaction 访问模式

Transaction Feature 范围

导航菜单

MySQL 语句：事务和锁定声明

关于

START TRANSACTION，COMMIT 和 ROLLBACK 语句

无法回滚的语句

导致隐式提交的语句

SAVEPOINT，ROLLBACK TO SAVEPOINT 和 RELEASE SAVEPOINT 语句

LOCK TABLES 和 UNLOCK TABLES 语句

表锁的获取

表锁的释放

表锁与事务的交互

LOCK TABLES 与 Triggers

表锁定的限制和条件

XA 事务

SET TRANSACTION 语句

Transaction 隔离级别

Transaction 访问模式

Transaction Feature 范围

导航菜单

搜索