MySQL 事务
最后修改于 2023 年 1 月 10 日
在 MySQL 教程的这一部分，我们将介绍事务。
事务的定义
事务是针对一个或多个数据库中的数据执行的数据库操作的原子单元。事务中所有 SQL 语句的效果可以要么全部提交到数据库，要么全部回滚。
MySQL 支持多种存储引擎。InnoDB 完全符合 ACID 标准。ACID 代表原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。可靠的事务必须支持这四个属性。
事务中的操作必须是原子性的。这意味着所有操作要么都成功，要么都失败。这就是全有或全无的规则。一致性属性确保数据库在事务完成后处于一致状态。数据有效，没有未完成的记录。例如，没有客户没有付款记录，或者没有没有客户的付款记录。隔离性是其他操作无法访问在尚未完成的事务期间修改的数据的要求。隔离问题发生在并发事务的情况下。如果没有隔离，数据可能会最终处于不一致的状态。持久性是数据库系统从任何类型的系统故障中恢复已提交的事务更新的能力。
隔离级别
在高并发环境中，高度隔离的事务可能导致死锁。死锁是一种情况，其中事务争夺资源，并有效地阻止彼此访问资源。此外，隔离级别和数据库性能之间存在权衡。因此，数据库系统为事务提供了几个隔离级别。
MySQL 提供了四个事务隔离级别
- 可串行化
- 可重复读
- 读已提交
- 读未提交
在可串行化隔离级别下，所有事务以完全隔离的方式发生。所有事务都是一个接一个执行的。在可重复读隔离级别下，语句无法读取已被其他事务修改但尚未提交的数据。在当前事务完成之前，其他事务无法修改当前事务已读取的数据。这是 InnoDB 的默认隔离级别。在读已提交隔离级别下，语句无法读取已被其他事务修改但尚未提交的数据。语句会等待其他事务写入锁定的数据行被解锁，然后再获取自己的锁。这可以防止它们读取脏数据。在读未提交隔离级别下，语句可以读取已被其他事务修改但尚未提交的行。
幻读、不可重复读和脏读是在事务未完全隔离时可能遇到的问题。当事务重新执行查询并返回一组满足搜索条件的行，但发现由于另一个最近提交的事务，满足条件的行集已更改时，会发生幻读。当事务重新读取它先前读取的数据并发现该数据已被另一个事务修改时，会发生不可重复读，自从初始读取以来已提交。当事务从已被另一个事务修改但尚未提交的行读取数据时，会发生脏读。
下表显示了所有隔离级别以及可能遇到的问题。
| 隔离级别 | 幻读 | 不可重复读 | 脏读 |
|---|---|---|---|
| 可串行化 | 不可能 | 不可能 | 不可能 |
| 可重复读 | 可能 | 不可能 | 不可能 |
| 读已提交 | 可能 | 可能 | 不可能 |
| 读未提交 | 可能 | 可能 | 可能 |
MySQL 的默认事务隔离级别是可重复读。
mysql> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+
当前的隔离级别存储在 tx_isolation 服务器变量中。
mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; mysql> SELECT @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | SERIALIZABLE | +----------------+
我们可以使用 SET TRANSACTION ISOLATION LEVEL 语句更改隔离级别。
自动提交
MySQL 还自动提交不属于事务的语句。任何没有以 START 开头的 UPDATE 或 INSERT 语句的结果将立即对所有连接可见。
mysql> SELECT @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+
默认情况下，autocommit 变量已设置。
mysql> SET autocommit=0; mysql> SELECT @@autocommit; +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+
可以关闭 autocommit。
现在我们将演示 autocommint 变量。
mysql> SELECT @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ CREATE TABLE Test(Num INTEGER NOT NULL) engine=InnoDB;
autocommit 已设置。我们使用 InnoDB 存储引擎创建一个简单的 Test 表，该引擎支持事务。
mysql> INSERT INTO Test VALUES (1), (2), (3); mysql> SELECT * FROM Test; +-----+ | Num | +-----+ | 1 | | 2 | | 3 | +-----+
我们在表的列中插入三行。这些值将立即提交。
mysql> SET autocommit=0; mysql> INSERT INTO Test VALUES (4), (5); mysql> SELECT * FROM Test; +-----+ | Num | +-----+ | 1 | | 2 | | 3 | | 4 | | 5 | +-----+
现在我们将 autocommit 变量设置为 false。我们插入两个值并从表中选择所有数据。现在我们在表中有了 5 行。
mysql> ROLLBACK; mysql> SELECT * FROM Test; +-----+ | Num | +-----+ | 1 | | 2 | | 3 | +-----+
但是，数据不会永久写入表中。使用 ROLLBACK 语句，我们将它们取回。
mysql> INSERT INTO Test VALUES (4), (5); mysql> COMMIT; mysql> ROLLBACK; mysql> SELECT * FROM Test; +-----+ | Num | +-----+ | 1 | | 2 | | 3 | | 4 | | 5 | +-----+
现在我们再次插入值 4、5。这次，这些行使用 COMMIT 语句提交。后续的回滚语句无效。
开始事务
启用 autocommit 后，每个单独的 SQL 语句都会自动包装在其自己的事务中。要启动我们自己的事务，我们发出 START TRANSACTION 语句。该事务稍后将使用 COMMIT 或 ROLLBACK 语句完成。可以在事务的主体中发出多个语句。所有这些语句都作为一个单元提交或回滚。
mysql> TRUNCATE Test; Query OK, 0 rows affected (0.02 sec) mysql> SELECT * FROM Test; Empty set (0.00 sec)
我们将使用相同的 Test 表。我们截断表中的数据。
mysql> START TRANSACTION; mysql> INSERT INTO Test VALUES (1), (2); mysql> INSERT INTO Test VALUES (3), (4); mysql> SELECT * FROM Test; +-----+ | Num | +-----+ | 1 | | 2 | | 3 | | 4 | +-----+
在上面的代码中，我们启动一个事务并将四行插入表中。这些值尚未提交。从当前连接，这些行是可见的。
$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 65 Server version: 5.1.41-3ubuntu12.9 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT * FROM mydb.Test; Empty set (0.00 sec)
但是，从不同的连接，Test 表是空的。我们启动一个 mysql 客户端程序的新实例。这是与 MySQL 数据库的不同连接。从此连接，这些值尚不可见。
mysql> COMMIT;
最后，COMMIT 语句将数据提交到表中。这些行从两个连接都可见。
我们启动另一个事务。这次数据将被回滚。
mysql> START TRANSACTION; mysql> INSERT INTO Test VALUES (5), (6); mysql> INSERT INTO Test VALUES (7), (8); mysql> ROLLBACK; mysql> SELECT * FROM Test; +-----+ | Num | +-----+ | 1 | | 2 | | 3 | | 4 | +-----+
在上面的 SQL 代码中，我们启动一个新事务。我们将四个值插入 Test 表中。我们使用 ROLLBACK 语句回滚更改。后续从该表中选择内容显示数据尚未提交到表中。
在 MySQL 教程的这一部分，我们使用了事务。