MySQL 中的事务|极客教程

本章我们将介绍MySQL 中的事务。

事务的定义

事务是针对一个或多个数据库中数据的数据库操作的基本单位。事务中所有 SQL 语句的影响可以全部提交给数据库，也可以全部回滚。

MySQL 支持多种存储引擎。 InnoDB 完全符合 ACID。 ACID 代表原子性，一致性，隔离性和耐久性。可靠的事务必须支持所有这四个属性。

事务内的操作必须是原子。这意味着所有操作都将成功或失败。这是全有或全无的规则。一致性属性可确保事务完成后数据库处于一致状态。数据有效，没有半完成的记录。例如，没有没有付款记录的客户，或者没有客户的付款记录。隔离是其他操作无法访问在尚未完成的事务期间修改的数据的要求。在并发事务的情况下会出现隔离问题。如果没有隔离，则数据可能最终处于不一致状态。持久性是数据库系统针对任何类型的系统故障恢复已提交事务更新的能力。

隔离等级

在高度并发的环境中，高度隔离的事务可能导致死锁。僵局是一种情况，其中事务争用资源并有效阻止彼此访问资源。此外，在隔离级别和数据库性能之间需要权衡。因此，数据库系统为事务提供了几个隔离级别。

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 还自动提交不属于事务的语句。任何UPDATE或INSERT语句之前没有START的结果将立即对所有连接可见。

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

默认情况下设置自动提交变量。

mysql> SET autocommit=0;

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+

自动提交可以关闭。

现在，我们将演示 autocommint 变量。

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

CREATE TABLE Test(Num INTEGER NOT NULL) engine=InnoDB;

自动提交已设置。我们使用支持事务的 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语句提交行。后续回滚语句无效。

启用事务

启用自动提交功能后，每个单个 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)

但是，从另一个连接，测试表为空。我们启动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 代码中，我们开始一个新事务。我们在测试表中插入四个值。我们使用ROLLBACK语句回滚更改。从表中进行的后续选择显示数据未提交到表。