MySQL事务 - Step1 - 基础理论和概念

内容纲要

一、引入

MySQL通过诸如SET autocommit、START TRANSACTION、COMMIT和ROLLBACK等语句来支持本地事务（在给定的客户端会话内）。

而XA事务支持使MySQL能够参与分布式事务。

二、知识点总结

2.1 事务的基本概念

事务（Transaction）是计算机科学和数据库管理中的一个基本概念，特别是在关系数据库中。事务可以定义为一系列操作，这些操作要么全部成功，要么全部失败。它是数据库管理的一个重要特性，确保数据的完整性和一致性。

2.2 特性（ACID：原子性、一致性、隔离性、持久性）

一个事务通常遵循以下四个基本特性，合称为ACID特性：

原子性（Atomicity）：事务被视为一个不可分割的最小工作单位，整个事务中的所有操作要么完全执行，要么完全不执行。这意味着如果事务中的某个操作失败，整个事务将回滚到开始状态。
- 含义：原子性确保事务中的操作要么全部完成，要么完全不发生。它类似于化学中的“原子”概念，即不可分割。
- 实现：如果事务的一部分操作执行失败，整个事务会回滚（撤销）到开始状态。所有操作要么完整地成功，要么都不会发生。
一致性（Consistency）：事务必须使数据库从一个一致的状态转变到另一个一致的状态。一致性确保事务执行的结果是数据库规则的遵守者，例如数据的完整性约束。
- 含义：一致性确保事务将数据库从一个有效状态转变到另一个有效状态。事务结束时，所有的数据规则、完整性约束等都必须得到满足。
- 实现：事务执行过程中，数据库系统会检查是否遵守了所有的完整性约束。只有满足所有规则的事务才被提交。
隔离性（Isolation）：数据库允许多个并发事务同时对其数据执行读写和修改操作。隔离性确保并发执行的事务不会互相干扰，每个事务都像在一个隔离的环境中执行一样。
- 含义：隔离性是指当多个事务同时运行时，一个事务的操作不应该影响其他事务的运行。
- 实现：数据库系统提供了不同级别的隔离，如读未提交（Read Uncommitted）、读提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。这些隔离级别提供了不同程度的隔离，以防止诸如脏读、不可重复读和幻读等问题。
4个隔离级别
- REPEATABLE READ（默认）
- READ COMMITTED
- READ UNCOMMITTED
- SERIALIZABLE
持久性（Durability）：一旦事务完成，对数据的更改就是永久的，即使系统发生故障也不会丢失。这通常是通过将事务记录到某种形式的非易失性存储器来实现的。
- 含义：持久性意味着一旦事务被提交，它对数据库的更改就是永久的，即使系统发生故障也不会丢失。
- 实现：通常通过将更改记录到持久存储设备（如硬盘）来实现。在发生故障时，系统可以使用日志（log）来恢复已提交的事务。

2.3 基础命令（START TRANSACTION, COMMIT, ROLLBACK）

在数据库管理系统中，基础命令 START TRANSACTION, COMMIT, 和 ROLLBACK 用于控制事务的开始、结束和撤销。它们是实现ACID特性的关键命令。以下是每个命令的详细说明：

START TRANSACTION:
- 用途：这个命令用来开始一个新的事务。在执行此命令后，随后的数据库操作（如INSERT、UPDATE、DELETE等）会被视为该事务的一部分。
- 示例：
```
START TRANSACTION;
```
- 注意点：在一些数据库系统中，例如MySQL，当执行任何修改数据的SQL语句时，如果当前没有活动事务，系统会自动启动一个新事务。
COMMIT:
- 用途：COMMIT 命令用于提交当前事务，使得事务中所有的更改都成为数据库的一部分。一旦事务提交，所做的更改就是永久性的。
- 示例：
```
COMMIT;
```
- 注意点：提交事务后，所有的锁定资源都会被释放，其他事务就可以访问这些数据了。
ROLLBACK:
- 用途：如果事务中的某个操作失败，ROLLBACK 命令可以撤销事务中的所有操作，回到事务开始之前的状态。
- 示例：
```
ROLLBACK;
```
- 注意点：与 COMMIT 类似，执行 ROLLBACK 后，所有的锁定资源也会被释放。

这些命令是理解和控制数据库事务的基础，它们直接关联到ACID特性，帮助确保数据库的完整性和一致性。不同的数据库管理系统可能会有细微的差异，但这些基本概念是普遍适用的。

三、原文翻译

13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements

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}

上面这些语句提供了对事务使用的控制：
• START TRANSACTION或BEGIN启动一个新的事务。
• COMMIT提交当前事务，使其更改变得永久。
• ROLLBACK回滚当前事务，取消其更改。
• SET autocommit禁用或启用当前会话的默认自动提交模式。

默认情况下，MySQL以自动提交模式运行。这意味着，除非在事务内，否则每个语句都是原子性的，就好像被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结束事务。然后，自动提交模式会恢复到先前的状态。

START TRANSACTION允许使用多个修饰符来控制事务特性。要指定多个修饰符，请用逗号分隔它们。

使用WITH CONSISTENT SNAPSHOT修饰符会为支持此功能的存储引擎启动一致性读取。这仅适用于InnoDB存储引擎。其效果等同于发出一个START TRANSACTION，然后从任何InnoDB表中进行SELECT操作。可参阅第15.7.2.3节，“一致性非锁定读取”。WITH CONSISTENT SNAPSHOT修饰符不会更改当前的事务隔离级别，因此仅在当前隔离级别允许一致性读取时，才提供一致性快照。唯一允许一致性读取的隔离级别是REPEATABLE READ。对于所有其他隔离级别，将忽略WITH CONSISTENT SNAPSHOT子句。当忽略WITH CONSISTENT SNAPSHOT子句时会生成警告。
READ WRITE 和 READ ONLY 修饰符用于设置事务的访问模式，它们允许或禁止对事务中使用的表进行更改。READ ONLY 限制阻止事务修改或锁定对其他事务可见的事务性和非事务性表，但允许事务仍然修改或锁定临时表。

当事务被知道是只读的时候，MySQL会对InnoDB表的查询进行额外的优化。指定 READ ONLY 可确保在不能自动确定只读状态的情况下应用这些优化。有关更多信息，请参阅第8.5.3节，“优化InnoDB只读事务”。

如果未指定访问模式，则使用默认模式。除非默认模式已更改，否则默认为读/写。不允许在同一语句中同时指定 READ WRITE 和 READ ONLY。

在只读模式下，仍然可以使用DML语句更改使用TEMPORARY关键字创建的表。与永久表一样，不允许使用DDL语句进行更改。

有关事务访问模式的其他信息，包括如何更改默认模式的方法，请参阅第13.3.7节，“SET TRANSACTION语句”。

如果启用了read_only系统变量，则使用START TRANSACTION READ WRITE显式启动事务需要CONNECTION_ADMIN权限（或不推荐使用的SUPER权限）。

重要
许多用于编写MySQL客户端应用程序的API（例如JDBC）提供了它们自己的方法来启动事务，有时应该使用这些方法，而不是从客户端发送START TRANSACTION语句。有关更多信息，请参阅第29章，“连接器和API”，或您API的文档。

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

SET autocommit=0;

在通过将autocommit变量设置为零将自动提交模式禁用后，对于支持事务的表（如InnoDB或NDB），对数据的更改不会立即生效。您必须使用COMMIT将更改存储到磁盘上，或者使用ROLLBACK来忽略更改。

autocommit是一个会话变量，必须为每个会话设置。要为每个新连接禁用自动提交模式，请参阅第5.1.8节“服务器系统变量”中的autocommit系统变量的描述。

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

提示
在所有存储程序（存储过程和函数、触发器和事件）中，解析器将BEGIN [WORK]视为BEGIN ... END块的开始。在这种情况下，要开始一个事务，请改用START TRANSACTION而不是BEGIN。

可选的WORK关键字在COMMIT和ROLLBACK中得到支持，还有CHAIN和RELEASE子句。CHAIN和RELEASE可以用于额外控制事务的完成。completion_type系统变量的值决定了默认的完成行为。有关更多信息，请参阅第5.1.8节，“服务器系统变量”。

使用AND CHAIN子句会在当前事务结束后立即启动一个新事务，新事务具有与刚刚终止的事务相同的隔离级别。新事务还使用与刚刚终止的事务相同的访问模式（READ WRITE或READ ONLY）。使用RELEASE子句会在终止当前事务后断开当前客户端会话的连接。包括NO关键字会抑制CHAIN或RELEASE完成，如果completion_type系统变量默认设置为导致链式或释放完成时，这可能会有用。

开始一个事务会导致任何挂起的事务被提交。有关更多信息，请参阅第13.3.3节，“导致隐式提交的语句”。

开始一个事务还会导致使用LOCK TABLES获取的表锁被释放，就像执行了UNLOCK TABLES一样。开始一个事务不会释放使用FLUSH TABLES WITH READ LOCK获取的全局读锁。

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

如果您在事务中使用来自多个事务安全的存储引擎（例如InnoDB）的表，并且事务隔离级别不是SERIALIZABLE，那么当一个事务提交时，使用相同表的另一个正在进行的事务可能只会看到第一个事务所做的一部分更改。也就是说，在混合引擎情况下，事务的原子性无法得到保证，可能会导致不一致性。如果混合引擎的事务不频繁，您可以使用SET TRANSACTION ISOLATION LEVEL根据需要在每个事务中将隔离级别设置为SERIALIZABLE。
如果您在事务中使用不支持事务的表，那么对这些表的更改会立即保存，而不考虑自动提交模式的状态。
如果您在事务中更新了一个非事务性的表，然后发出ROLLBACK语句，会产生一个ER_WARNING_NOT_COMPLETE_ROLLBACK警告。事务安全的表的更改将被回滚，但非事务安全的表的更改不会被回滚。

每个事务在提交时以一块的形式存储在二进制日志中。被回滚的事务不会被记录。（例外情况：对于非事务性表的修改无法被回滚。如果一个被回滚的事务包括对非事务性表的修改，那么整个事务会在末尾使用ROLLBACK语句记录下来，以确保对非事务性表的修改会被复制。）详细信息请参阅第5.4.4节，“二进制日志”。

您可以使用SET TRANSACTION语句更改事务的隔离级别或访问模式。请参阅第13.3.7节，“SET TRANSACTION语句”。

回滚可能是一个较慢的操作，它可能会在用户没有明确要求的情况下隐式发生（例如，当发生错误时）。因此，SHOW PROCESSLIST在会话的State列中显示"Rolling back"，不仅用于由ROLLBACK语句明确执行的回滚操作，还用于隐式回滚操作。

提示
在MySQL 8.0中，BEGIN、COMMIT和ROLLBACK语句不受--replicate-do-db或--replicate-ignore-db规则的影响。这意味着这些语句将始终在复制过程中执行，而不受指定的数据库规则限制。这一改变可以确保事务的一致性，并减少了潜在的复制问题。

当InnoDB执行事务的完全回滚时，事务设置的所有锁都会被释放。如果事务中的单个SQL语句由于错误（例如重复键错误）而回滚，那么该语句设置的锁将在事务保持活动状态时保留。这是因为InnoDB以一种格式存储行锁，使其事后无法确定哪个语句设置了哪个锁。

如果事务中的SELECT语句调用了一个存储函数，而存储函数中的语句失败，那么该语句会回滚。如果随后对事务执行ROLLBACK操作，整个事务都会回滚。

13.3.2 Statements That Cannot Be Rolled Back

不能回滚的语句。

有些语句无法回滚。一般来说，这些语句包括数据定义语言（DDL）语句，例如创建或删除数据库、创建、删除或修改表格或存储过程等。

因此，在设计事务时，应该避免包含这些不可回滚的语句。如果在事务的早期发出了一个无法回滚的语句，然后稍后另一个语句失败，那么在这种情况下，无法通过发出ROLLBACK语句来完全回滚事务的全部效果。所以在事务中需要特别小心处理不可回滚的语句，确保它们的影响不会对整个事务产生不可逆的影响。

13.3.3 Statements That Cause an Implicit Commit

导致隐式提交的语句。

ChatGPT
在MySQL中，一些SQL语句会触发隐式提交，这意味着它们会结束当前事务，并将更改永久保存到数据库中。这些语句通常用于管理数据库结构或执行其他需要立即生效的操作。通常情况下，这些语句包括数据定义语言（DDL）语句，如CREATE TABLE、ALTER TABLE和DROP TABLE等。

隐式提交的语句可以影响事务的一致性，因此在编写事务时需要小心处理它们，以确保符合您的业务需求。

此部分列出的语句（以及它们的任何同义词）会隐式结束当前会话中活动的任何事务，就好像在执行该语句之前执行了COMMIT一样。大多数这些语句在执行后也会导致隐式提交。其意图是在其自己的特殊事务中处理每个这样的语句。

但事务控制和锁定语句是例外：如果在执行之前发生了隐式提交，那么在执行之后就不会再发生一次隐式提交。这意味着在执行这些特定语句之前会结束当前事务，但不会在执行之后再次提交。

定义或修改数据库对象的数据定义语言（DDL）语句
```
ALTER EVENT,
ALTER FUNCTION, ALTER PROCEDURE, ALTER SERVER, ALTER TABLE, ALTER TABLESPACE,
ALTER VIEW, CREATE DATABASE, CREATE EVENT, CREATE FUNCTION, CREATE INDEX, CREATE
PROCEDURE, CREATE ROLE, CREATE SERVER, CREATE SPATIAL REFERENCE SYSTEM, CREATE
TABLE, CREATE TABLESPACE, CREATE TRIGGER, CREATE VIEW, DROP DATABASE, DROP EVENT,
DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP ROLE, DROP SERVER, DROP SPATIAL
REFERENCE SYSTEM, DROP TABLE, DROP TABLESPACE, DROP TRIGGER, DROP VIEW, INSTALL
PLUGIN, RENAME TABLE, TRUNCATE TABLE, UNINSTALL PLUGIN.
```
如果使用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.
```
UNLOCK TABLES只有在当前已经使用LOCK TABLES锁定了任何表来获取非事务性表锁时才会提交事务。对于FLUSH TABLES WITH READ LOCK之后的UNLOCK TABLES不会发生提交，因为后者语句不会获取表级别的锁定。

事务不能嵌套。这是因为当您发出START TRANSACTION语句或其同义词之一时，对于任何当前事务都会执行隐式提交的结果。这会导致当前事务的结束，因此不能在一个事务内嵌套另一个事务。

在XA事务处于活动状态时，不能在其中使用导致隐式提交的语句。BEGIN语句与启动BEGIN ... END复合语句的BEGIN关键字的用法不同。后者不会导致隐式提交。请参阅第13.6.1节，“BEGIN ... END复合语句”。
数据加载语句
LOAD DATA语句只会对使用NDB存储引擎的表导致隐式提交。对于其他存储引擎的表，LOAD DATA语句不会引发隐式提交。

管理性语句

ANALYZE TABLE, CACHE INDEX, CHECK TABLE, FLUSH, LOAD INDEX INTO CACHE, OPTIMIZE TABLE, REPAIR TABLE, RESET (but not RESET PERSIST).

复制控制语句

START REPLICA, STOP REPLICA, RESET REPLICA, CHANGE REPLICATION SOURCE TO, CHANGE MASTER TO

在MySQL 8.0.22中，SLAVE关键字被替换为REPLICA。这个更改是为了避免使用具有潜在含义的词汇，并使数据库更加包容和尊重多元文化。因此，在MySQL 8.0.22及以后的版本中，应该使用REPLICA来代替以前的SLAVE关键字，以描述数据库的复制从属角色。

13.3.4 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements

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携带；锁不会单独存储在内存中。在这种情况下，行锁在撤销时被释放。）在保存点之后设置的保存点将被删除。

如果ROLLBACK TO SAVEPOINT语句返回以下错误，表示不存在具有指定名称的保存点：

ERROR 1305 (42000): SAVEPOINT identifier does not exist

RELEASE SAVEPOINT语句从当前事务的保存点集中删除指定的命名保存点。它不会导致提交或回滚操作。如果保存点不存在，则会产生错误。

如果执行COMMIT或不指定保存点名称的ROLLBACK，将删除当前事务的所有保存点。

在调用存储函数或触发器被激活时会创建一个新的保存点级别。以前级别上的保存点将不再可用，因此不会与新级别上的保存点发生冲突。当函数或触发器终止时，它所创建的任何保存点都会被释放，以及之前的保存点级别会恢复。

13.3.5 LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Statements

锁定实例进行备份和解锁实例语句。

LOCK INSTANCE FOR BACKUP
UNLOCK INSTANCE

LOCK INSTANCE FOR BACKUP获取一个实例级别的备份锁，允许在在线备份期间执行DML操作，同时阻止可能导致不一致快照的操作。

执行LOCK INSTANCE FOR BACKUP语句需要BACKUP_ADMIN权限。当执行从早期版本升级到MySQL 8.0时，具有RELOAD权限的用户会自动被授予BACKUP_ADMIN权限。

多个会话可以同时持有备份锁。

UNLOCK INSTANCE释放当前会话持有的备份锁。如果会话终止，也会释放该会话持有的备份锁。

LOCK INSTANCE FOR BACKUP阻止文件的创建、重命名或删除。REPAIR TABLE、TRUNCATE TABLE、OPTIMIZE TABLE和帐户管理语句会被阻塞。有关详细信息，请参阅第13.7.1节，“帐户管理语句”。还会阻止修改未记录在InnoDB重做日志中的InnoDB文件的操作。

LOCK INSTANCE FOR BACKUP允许只影响用户创建的临时表的DDL操作。实际上，在持有备份锁的情况下，属于用户创建的临时表的文件可以被创建、重命名或删除。还允许创建二进制日志文件。

不应该在LOCK INSTANCE FOR BACKUP语句对实例生效的情况下发出PURGE BINARY LOGS，因为这违反了备份锁的规则，会从服务器中删除文件。从MySQL 8.0.28开始，这是不允许的。

通过LOCK INSTANCE FOR BACKUP获取的备份锁与事务锁和由FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK获取的锁是独立的，允许以下一系列语句：

LOCK INSTANCE FOR BACKUP;
FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK;
UNLOCK TABLES;
UNLOCK INSTANCE;

FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK;
LOCK INSTANCE FOR BACKUP;
UNLOCK INSTANCE;
UNLOCK TABLES;

lock_wait_timeout设置定义了LOCK INSTANCE FOR BACKUP语句在放弃之前等待获取锁的时间量。

13.3.6 LOCK TABLES and UNLOCK TABLES Statements

锁表和解锁表语句。

LOCK TABLES
 tbl_name [[AS] alias] lock_type
 [, tbl_name [[AS] alias] lock_type] ...
lock_type: {
 READ [LOCAL]
 | [LOW_PRIORITY] WRITE
}
UNLOCK TABLES

MySQL允许客户端会话明确地获取表锁，以便与其他会话合作访问表，或者在会话需要独占访问表的时期防止其他会话修改这些表。一个会话只能为自己获取或释放锁。一个会话不能为另一个会话获取锁，也不能释放另一个会话持有的锁。这确保了锁的独立性和互斥性，每个会话都有自己的锁管理。

锁可以用于模拟事务或在更新表时提高速度。这在《表锁定的限制和条件》中有更详细的解释。

LOCK TABLES明确为当前客户端会话获取表锁。表锁可以用于基本表或视图。您必须具有LOCK TABLES权限，并且对要锁定的每个对象都具有SELECT权限。

对于视图锁定，LOCK TABLES会将视图中使用的所有基本表添加到要锁定的表集中，并自动锁定它们。对于任何被锁定的视图底层表，LOCK TABLES会检查视图定义者（对于SQL SECURITY DEFINER视图）或调用者（对于所有视图）对表具有适当的权限。

如果您使用LOCK TABLES明确锁定表，触发器中使用的任何表也会隐式锁定，如LOCK TABLES和触发器中所述。

如果您使用LOCK TABLES明确锁定表，任何受外键约束关联的表都会隐式打开并锁定。对于外键检查，会在相关表上采取一个共享的只读锁（LOCK TABLES READ）。对于级联更新，会在参与操作的相关表上采取共享的写锁（LOCK TABLES WRITE）。

UNLOCK TABLES明确释放当前会话持有的任何表锁。LOCK TABLES在获取新锁之前会隐式释放当前会话持有的任何表锁。

UNLOCK TABLES的另一个用途是释放使用FLUSH TABLES WITH READ LOCK语句获取的全局读锁，该锁允许您锁定所有数据库中的所有表。有关详细信息，请参阅第13.7.8.3节，“FLUSH语句”。（如果您有像Veritas这样可以在某个时刻创建快照的文件系统，这是获得备份的非常方便的方式。）

表锁仅保护其他会话的不适当读取或写入。持有WRITE锁的会话可以执行诸如DROP TABLE或TRUNCATE TABLE之类的表级操作。对于持有READ锁的会话，不允许执行DROP TABLE和TRUNCATE TABLE操作。

以下讨论仅适用于非临时表。对于临时表，允许（但会被忽略）使用LOCK TABLES。该表可以在创建它的会话内自由访问，而不管其他锁定是否生效。不需要锁定，因为其他会话无法看到该表。

表锁获取
表锁释放
表锁与事务的交互
LOCK TABLES和触发器
表锁的限制和条件

表锁获取

要在当前会话中获取表锁，请使用LOCK TABLES语句，该语句会获取元数据锁（请参阅第8.11.4节，“元数据锁定”）。

下面是可用的锁类型：
1、READ [LOCAL] 锁：

持有锁的会话可以读取表（但不能写入）。
多个会话可以同时为表获取READ锁。
其他会话可以在不显式获取READ锁的情况下读取表。
使用LOCAL修饰符可以在持有锁的情况下允许其他会话执行非冲突的INSERT语句（并发插入）。然而，如果在持有锁的同时要使用服务器外部的进程操作数据库，则不能使用READ LOCAL（请参阅第8.11.3节，“并发插入”）。对于InnoDB表，READ LOCAL与READ相同。

2、[LOW_PRIORITY] WRITE 锁：

持有锁的会话可以读取和写入表。
只有持有锁的会话可以访问表，直到释放锁之前，其他会话无法访问它。
其他会话对表的锁请求在持有WRITE锁的情况下会被阻塞。
LOW_PRIORITY修饰符没有效果。在MySQL的早期版本中，它影响锁定行为，但现在不再如此。它现在已被弃用，并且使用它会产生警告。请改用不带LOW_PRIORITY的WRITE。

WRITE锁通常比READ锁具有更高的优先级，以确保尽快处理更新操作。这意味着如果一个会话获取了一个READ锁，然后另一个会话请求一个WRITE锁，后续的READ锁请求会等待，直到请求WRITE锁的会话获取并释放了锁。（对于max_write_lock_count系统变量的小值，这一策略可能会有异常情况，详见第8.11.4节，“元数据锁定”）。

如果由于其他会话在任何表上持有锁而导致LOCK TABLES语句必须等待，它将被阻塞，直到可以获取所有锁。

需要锁的会话必须在单个LOCK TABLES语句中获取所有需要的锁。在持有这些锁的情况下，会话只能访问被锁定的表。例如，在以下语句序列中，尝试访问t2时会发生错误，因为在LOCK TABLES语句中没有锁定t2：

mysql> LOCK TABLES t1 READ;
mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
mysql> SELECT COUNT(*) FROM t2;
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

INFORMATION_SCHEMA数据库中的表是一个例外。即使会话持有使用LOCK TABLES获取的表锁，也可以在不显式锁定的情况下访问它们。

您不能在单个查询中多次引用具有相同名称的锁定表。请改用别名，为表和每个别名获取单独的锁：

mysql> LOCK TABLE t WRITE, t AS t1 READ;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

错误会发生在第一个INSERT语句，因为对于被锁定的表有两个相同名称的引用。第二个INSERT成功，因为对表的引用使用了不同的名称。

如果您的语句通过别名引用表，那么必须使用相同的别名锁定表。不能在不指定别名的情况下锁定表：

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

反之，如果您使用别名锁定表，那么您在语句中必须使用该别名引用它：

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

表锁释放

当会话持有的表锁被释放时，它们都会同时被释放。会话可以显式释放其锁，或在某些条件下隐式释放锁。

会话可以使用UNLOCK TABLES来显式释放锁。
如果会话在已经持有锁的情况下发出LOCK TABLES语句以获取锁，那么它现有的锁会在授予新锁之前被隐式释放。
如果会话开始一个事务（例如，使用START TRANSACTION），则会执行隐式的UNLOCK TABLES操作，导致现有的锁被释放。（有关表锁定与事务之间交互的更多信息，请参见表锁定与事务的交互。）

如果客户会话的连接终止，无论是正常终止还是异常终止，服务器都会隐式释放该会话持有的所有表锁（事务性和非事务性）。如果客户重新连接，则锁不再有效。此外，如果客户端具有活动事务，则服务器会在断开连接时回滚事务，如果重新连接，则新会话将启用自动提交。

因此，客户端可能希望禁用自动重新连接。启用自动重新连接后，如果发生重新连接，客户端不会收到通知，但任何表锁或当前事务都会丢失。禁用自动重新连接后，如果连接中断，下一个发出的语句会引发错误。客户端可以检测到错误并采取适当的操作，例如重新获取锁或重做事务。请参阅自动重新连接控制。

提示：
如果您对已锁定的表使用ALTER TABLE，它可能会变得解锁。例如，如果您尝试进行第二次ALTER TABLE操作，结果可能会是一个错误：“表'tbl_name'未使用LOCK TABLES锁定”。要处理这种情况，在进行第二次更改之前再次锁定表。另请参阅第B.3.6.1节，“ALTER TABLE的问题”。

表锁与事务的交互

LOCK TABLES和UNLOCK TABLES与事务的使用交互如下：

LOCK TABLES不是事务安全的，在尝试锁定表之前会隐式提交任何活动事务。
UNLOCK TABLES会隐式提交任何活动事务，但只有在使用LOCK TABLES获取表锁的情况下才会生效。例如，在以下一组语句中，UNLOCK TABLES会释放全局读锁，但不会提交事务，因为没有表锁生效：
```
FLUSH TABLES WITH READ LOCK;
START TRANSACTION;
SELECT ... ;
UNLOCK TABLES;
```
开始一个事务（例如，使用START TRANSACTION）会隐式提交任何当前事务并释放现有的表锁。
使用FLUSH TABLES WITH READ LOCK会获取全局读锁，而不是表锁，因此在表锁定和隐式提交方面不受与LOCK TABLES和UNLOCK TABLES相同的行为影响。例如，START TRANSACTION不会释放全局读锁。详见第13.7.8.3节，“FLUSH语句”。
其他隐式导致事务提交的语句不会释放现有的表锁。有关这类语句的列表，请参阅第13.3.3节，“导致隐式提交的语句”。
在使用事务表（例如InnoDB表）时，正确的方式是首先使用SET autocommit = 0（而不是START TRANSACTION）开始一个事务，然后使用LOCK TABLES，并且不要在事务明确提交之前调用UNLOCK TABLES。例如，如果您需要写入表t1并从表t2读取，可以这样做：

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

当您调用LOCK TABLES时，InnoDB会在内部获取其自己的表锁，而MySQL会获取自己的表锁。InnoDB会在下一次提交时释放其内部表锁，但要让MySQL释放其表锁，您必须调用UNLOCK TABLES。您不应该将autocommit设置为1，因为在这种情况下，InnoDB会立即在调用LOCK TABLES后释放其内部表锁，这样会非常容易发生死锁。如果autocommit设置为1，InnoDB根本不会获取内部表锁，以帮助旧应用程序避免不必要的死锁。

ROLLBACK不会释放表锁。

`LOCK TABLES`和触发器

如果您使用LOCK TABLES明确锁定表，并且在触发器中使用了这些表，那么这些表将隐式地被锁定：

• 这些锁是在与使用LOCK TABLES语句明确获取的锁同时获取的。

• 在触发器中使用的表的锁取决于该表是否仅用于读取。如果是的话，那么只需要读锁。否则，将使用写锁。

• 如果表在使用LOCK TABLES明确进行读取锁定，但由于可能在触发器中进行修改，因此需要进行写锁定，那么将采用写锁而不是读锁。（也就是说，由于表在触发器中的出现需要隐式进行写锁定，因此对表的显式读锁请求将转换为写锁请求。）

假设您使用以下语句锁定了两个表，t1和t2：

LOCK TABLES t1 WRITE, t2 READ;

如果t1或t2中有任何触发器，那么在触发器中使用的表也会被锁定。假设t1有一个如下所示的触发器定义：

CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROW
BEGIN
 UPDATE t4 SET count = count+1
 WHERE id = NEW.id AND EXISTS (SELECT a FROM t3);
 INSERT INTO t2 VALUES(1, 2);
END;

LOCK TABLES语句的结果是t1和t2被锁定，因为它们出现在语句中，而t3和t4被锁定，因为它们在触发器中被使用：

t1被锁定为写入，根据写入锁请求。
t2被锁定为写入，尽管请求是读锁。这是因为在触发器内部插入了t2，所以读请求被转换为写请求。
t3被锁定为读取，因为仅在触发器内部进行读取。
t4被锁定为写入，因为在触发器内部可能会进行更新。

表锁的限制和条件

可以安全地使用KILL来终止等待表锁的会话。请参阅第13.7.8.4节，“KILL语句”。

不能在存储过程中使用LOCK TABLES和UNLOCK TABLES。

不能使用LOCK TABLES锁定erformance_schema数据库中的表，除了setup_xxx表之外。

由LOCK TABLES生成的锁的范围是单个MySQL服务器。它与NDB Cluster不兼容，因为NDB Cluster没有一种方式可以跨多个mysqld实例强制执行SQL级别的锁定。您可以在API应用程序中执行锁定。有关更多信息，请参阅第23.2.7.10节，“与多个NDB Cluster节点相关的限制”。

在LOCK TABLES语句生效时，禁止以下操作：CREATE TABLE，CREATE TABLE ... LIKE，CREATE VIEW，DROP VIEW以及对存储函数、存储过程和事件的DDL语句。

对于某些操作，必须访问mysql数据库中的系统表。例如，HELP语句需要服务器端帮助表的内容，CONVERT_TZ()可能需要读取时区表。服务器会根据需要隐式地对系统表进行读取锁定，因此您无需显式锁定它们。这些表的处理方式如上所述。

mysql.help_category
mysql.help_keyword
mysql.help_relation
mysql.help_topic
mysql.time_zone
mysql.time_zone_leap_second
mysql.time_zone_name
mysql.time_zone_transition
mysql.time_zone_transition_type

如果您想要使用LOCK TABLES语句显式地在这些表上放置写锁，那么这个表必须是唯一被锁定的表；不能使用同一个语句锁定其他表。

通常情况下，您不需要锁定表，因为所有的单个UPDATE语句都是原子的；没有其他会话可以干扰正在执行的任何其他SQL语句。然而，在一些情况下，锁定表可能会提供一些优势：

如果您要在一组MyISAM表上运行许多操作，锁定您要使用的表会更快。锁定MyISAM表会加速在这些表上的插入、更新或删除操作，因为在调用UNLOCK TABLES之前，MySQL不会为被锁定的表刷新键缓存。通常情况下，键缓存在每个SQL语句之后都会被刷新。

锁定表的缺点是，没有会话可以更新一个被读锁定的表（包括持有锁的会话），并且除了持有锁的表之外，没有会话可以访问被写锁定的表。
如果您正在使用非事务性存储引擎的表，如果您希望确保在SELECT和UPDATE之间没有其他会话修改表，那么您必须使用LOCK TABLES。下面的示例需要使用LOCK TABLES来安全执行：

LOCK TABLES trans READ, customer WRITE;
SELECT SUM(value) FROM trans WHERE customer_id=some_id;
UPDATE customer
 SET total_value=sum_from_previous_statement
 WHERE customer_id=some_id;
UNLOCK TABLES;

如果没有使用LOCK TABLES，在执行SELECT和UPDATE语句之间，另一个会话可能会插入一个新行到trans表中。

在许多情况下，您可以通过使用相对更新（例如，UPDATE customer SET value=value+new_value）或LAST_INSERT_ID()函数来避免使用LOCK TABLES。

此外，在某些情况下，您还可以通过使用用户级别的Advisory Lock函数GET_LOCK()和RELEASE_LOCK()来避免锁定表。这些锁保存在服务器中的哈希表中，并使用pthread_mutex_lock()和pthread_mutex_unlock()进行高速实现。详见第12.14节，“锁定函数”。

有关锁定策略的更多信息，请参阅第8.11.1节，“内部锁定方法”。

13.3.7 SET TRANSACTION Statement

设置事务语句。

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
}

这个语句用于指定事务的特性。它接受一个或多个特性值的列表，用逗号分隔。每个特性值设置事务的隔离级别或访问模式。隔离级别用于操作InnoDB表。访问模式指定事务是以读/写模式还是只读模式运行。

此外，SET TRANSACTION可以包括一个可选的GLOBAL或SESSION关键字，用于指示语句的范围。

事务隔离级别
事务访问模式
事务特性范围

事务隔离级别

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

默认的隔离级别是REPEATABLE READ。其他允许的值包括READ COMMITTED、READ UNCOMMITTED和SERIALIZABLE。有关这些隔离级别的详细信息，请参阅第15.7.2.1节，“事务隔离级别”。

事务访问模式

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

默认情况下，事务以读/写模式进行，允许在事务中使用的表进行读取和写入。可以使用带有访问模式READ WRITE的SET TRANSACTION明确指定此模式。

如果将事务访问模式设置为READ ONLY，则禁止对表进行更改。这可以使存储引擎在不允许写入时进行性能优化。在只读模式下，仍然可以使用DML语句更改使用TEMPORARY关键字创建的表。与永久表一样，不允许使用DDL语句进行更改。

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

事务特性范围

您可以全局设置事务特性，为当前会话设置，或仅适用于下一次事务：

使用GLOBAL关键字：
- 该语句全局应用于所有后续会话。
- 现有会话不受影响。
使用SESSION关键字：
- 该语句适用于当前会话中执行的所有后续事务。
- 该语句允许在事务内部使用，但不会影响当前正在进行的事务。
- 如果在事务之间执行，该语句将覆盖任何之前设置了指定特性的下一个事务值的语句。

没有任何SESSION或GLOBAL关键字：

该语句仅适用于会话中下一个单独的事务。
后续事务将恢复使用指定特性的会话值。
在事务内部不允许该语句：

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

对全局事务特性的更改需要`CONNECTION_ADMIN`特权（或已弃用的`SUPER`特权）。任何会话都可以自由更改其会话特性（甚至在事务中间），或更改其下一次事务的特性（在开始该事务之前）。

[mysqld]
transaction-isolation = REPEATABLE-READ
transaction-read-only = OFF

在运行时，可以间接地使用前面描述的`SET TRANSACTION`语句来设置全局、会话和下一次事务范围级别的特性。也可以直接使用SET语句来为`transaction_isolation`和`transaction_read_only`系统变量赋值：

`SET TRANSACTION`允许使用可选的`GLOBAL`和`SESSION`关键字来在不同范围级别上设置事务特性。
用于分配值给`transaction_isolation`和`transaction_read_only`系统变量的`SET`语句具有不同范围级别的语法。

以下表格显示了每个`SET TRANSACTION`和变量赋值语法设置的特性范围级别。

表13.9 `SET TRANSACTION` 事务特性的语法

表13.10 事务特性的`SET`语法

在运行时，可以检查全局和会话级别的事务特性的值：

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

13.3.8 XA Transactions

支持XA事务的是InnoDB存储引擎。MySQL的XA实现基于X/Open CAE文档《分布式事务处理：XA规范》。该文档由The Open Group发布，可在这个链接上找到http://www.opengroup.org/public/pubs/catalog/c193.htm。
当前XA实现的限制在第13.3.8.3节“关于XA事务的限制”中有描述。

在客户端方面，没有特殊要求。与MySQL服务器的XA接口由以XA关键字开头的SQL语句组成。MySQL客户端程序必须能够发送SQL语句并理解XA语句接口的语义。它们不需要链接到最新的客户端库。旧的客户端库也可以使用。

在MySQL Connectors中，MySQL Connector/J 5.0.0及更高版本直接支持XA，通过一个处理XA SQL语句接口的类接口来为您处理。

XA支持分布式事务，即允许多个独立的事务资源参与全局事务。事务资源通常是RDBMS，但也可以是其他类型的资源。

全局事务涉及多个本身是事务性的操作，但它们必须作为一个组要么都成功完成，要么都作为一个组回滚。本质上，这将ACID属性“提升”到一个更高的级别，以便多个ACID事务可以作为全局操作的组成部分协调执行，同时也具有ACID属性。（与非分布式事务一样，如果您的应用程序对读取现象敏感，可能更喜欢使用SERIALIZABLE。REPEATABLE READ可能不足以支持分布式事务。）

一些分布式事务的示例：

一个应用程序可以充当一个将消息服务与RDBMS结合的集成工具。该应用程序确保处理消息发送、检索和处理的事务，这些事务还涉及到事务性数据库，都在一个全局事务中发生。您可以将其视为“事务性电子邮件”。
一个应用程序执行涉及不同数据库服务器的操作，例如MySQL服务器和Oracle服务器（或多个MySQL服务器），其中涉及多个服务器的操作必须作为全局事务的一部分发生，而不是作为每个服务器本地事务的独立部分。
一家银行将账户信息存储在RDBMS中，并通过自动取款机（ATM）分发和接收资金。必须确保ATM操作在账户中正确反映，但这不能仅通过RDBMS完成。全局事务管理器集成了ATM和数据库资源，以确保财务交易的总体一致性。

使用全局事务的应用程序涉及一个或多个资源管理器和一个事务管理器：

资源管理器（RM）提供对事务性资源的访问。数据库服务器是一种资源管理器。必须可以提交或回滚由RM管理的事务。
事务管理器（TM）协调全局事务的一部分的事务。它与处理每个事务的RM进行通信。全局事务及其分支由稍后描述的命名方案识别。

MySQL事务 – Step1 – 基础理论和概念

一、引入

二、知识点总结

2.1 事务的基本概念

2.2 特性（ACID：原子性、一致性、隔离性、持久性）

2.3 基础命令（START TRANSACTION, COMMIT, ROLLBACK）

三、原文翻译

13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements

13.3.2 Statements That Cannot Be Rolled Back

13.3.3 Statements That Cause an Implicit Commit

13.3.4 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements

13.3.5 LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Statements

13.3.6 LOCK TABLES and UNLOCK TABLES Statements

表锁获取

表锁释放

表锁与事务的交互

`LOCK TABLES`和触发器

表锁的限制和条件

13.3.7 SET TRANSACTION Statement

事务隔离级别

事务访问模式

事务特性范围

13.3.8 XA Transactions

林清杨

1 Comment

Leave a Comment 取消回复

一、引入

二、知识点总结

2.1 事务的基本概念

2.2 特性（ACID：原子性、一致性、隔离性、持久性）

2.3 基础命令（START TRANSACTION, COMMIT, ROLLBACK）

三、原文翻译

13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements

13.3.2 Statements That Cannot Be Rolled Back

13.3.3 Statements That Cause an Implicit Commit

13.3.4 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements

13.3.5 LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Statements

13.3.6 LOCK TABLES and UNLOCK TABLES Statements

表锁获取

表锁释放

表锁与事务的交互

LOCK TABLES和触发器

表锁的限制和条件

13.3.7 SET TRANSACTION Statement

事务隔离级别

事务访问模式

事务特性范围

13.3.8 XA Transactions

林清杨

1 Comment

Leave a Comment 取消回复

`LOCK TABLES`和触发器