事务

1. 实验介绍

1.1 实验内容

在本节内容中，我们将介绍 MySQL 的事务处理。

# 使用 默认环境的同学 可以重新导入 数据
wget http://labfile.oss.aliyuncs.com/courses/980/files/week7/exec_sql.txt

1.2 实验知识点

2. 事务

事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元。

而在 MySQL 中，事务由将要执行的一条或多条 SQL 语句组成，它们是一个整体，要么全部被执行，要么彻底不执行，不会出现执行一半的情况。

即整个执行单元，如果全部执行成功，最后就会提交（COMMIT）所有的修改操作到实际的数据库中，而在这些 SQL 语句中，如果有一条语句不能完成，我们就可以通过撤销操作（又称回滚，ROLLBACK）撤销整个事务中前面所做的修改，回到事务开始执行之前的状态。

2.1 ACID

关系数据库最重要的特性是满足 ACID 性质：

• A atomicity 代表原子性；
• C consistency 代表一致性；
• I isolation 代表隔离性；
• D durability 代表持久性；

ACID 能够保证事务的可靠性，这是什么意思呢？就是说能够保证一系列数据库的操作组成一个完整逻辑过程，要么全部被执行，要么彻底不执行，不会出现执行一半的情况。例如银行转帐，从原账户扣除金额，到向目标账户添加金额，这两个数据库操作的总和，构成一个完整的逻辑过程，不可拆分。

为了满足 ACID 性质，MySQL 支持各种约束，比如插入一条数据时，需要检查外键是否存在，这些操作虽然能确保数据的一致性，但是很多时候却降低了并发操作的能力，所以在如今的互联网网站中，如果有高并发的需求往往不再使用关系数据库的 ACID 性质，更有的直接使用非关系数据库。

有兴趣的同学可以阅读文章： MySQL 中事务的实现

2.2 自动提交

在我们使用客户端连接到 MySQL 服务器时，即开始一个会话，这时数据库的自动提交功能是开启的，每执行完一条 SQL 语句就会进行提交操作。

对于自动提交功能的开启在 MySQL 中可以通过一个系统变量 autocommit 来设置。如下所示，我们通过之前学习的查看系统变量的方式来查看 autocommit 的值：

mysql> show variables like "autoco%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

这里显示的是 ON ，即对应的布尔值为 1，我们设置其为 0 :

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "autoco%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+

此时，自动提交功能已经关闭了，如下所示，我们演示回滚和提交功能：

# 首先，查询 student 表的数据
mysql> SELECT * FROM student;
+------+---------------+-------+-------+
| s_id | s_name        | s_sex | s_age |
+------+---------------+-------+-------+
| 1001 | shiyanlou1001 | man   |    10 |
| 1002 | shiyanlou1002 | woman |    20 |
| 1003 | shiyanlou1003 | man   |    18 |
| 1004 | shiyanlou1004 | woman |    40 |
| 1005 | shiyanlou1005 | man   |    17 |
+------+---------------+-------+-------+

# 插入一条数据，在设置关闭自动提交时，当前会话就已经处于一个事务之中了
mysql> INSERT INTO student VALUES(1006, "shiyanlou1006", "man", 20);
Query OK, 1 row affected (0.00 sec)

# 再次查询 student 表的数据，可以看到 s_id 为 1006 的数据
mysql> SELECT * FROM student;
+------+---------------+-------+-------+
| s_id | s_name        | s_sex | s_age |
+------+---------------+-------+-------+
| 1001 | shiyanlou1001 | man   |    10 |
| 1002 | shiyanlou1002 | woman |    20 |
| 1003 | shiyanlou1003 | man   |    18 |
| 1004 | shiyanlou1004 | woman |    40 |
| 1005 | shiyanlou1005 | man   |    17 |
| 1006 | shiyanlou1006 | man   |    20 |
+------+---------------+-------+-------+
6 rows in set (0.00 sec)

# 回滚，接着使用回滚，撤销上一次的插入操作
mysql> ROLLBACK;
Query OK, 0 rows affected (0.04 sec)

# 再次查询，因为刚刚的回滚操作，可以看到 s_id 为 1006 的数据并未出现在查询结果之中了
mysql> SELECT * FROM student;
+------+---------------+-------+-------+
| s_id | s_name        | s_sex | s_age |
+------+---------------+-------+-------+
| 1001 | shiyanlou1001 | man   |    10 |
| 1002 | shiyanlou1002 | woman |    20 |
| 1003 | shiyanlou1003 | man   |    18 |
| 1004 | shiyanlou1004 | woman |    40 |
| 1005 | shiyanlou1005 | man   |    17 |
+------+---------------+-------+-------+
5 rows in set (0.00 sec)

# 再次执行插入操作
mysql> INSERT INTO student VALUES(1006, "shiyanlou1006", "man", 20);
Query OK, 1 row affected (0.00 sec)

# 再次查询
mysql> SELECT * FROM student;
+------+---------------+-------+-------+
| s_id | s_name        | s_sex | s_age |
+------+---------------+-------+-------+
| 1001 | shiyanlou1001 | man   |    10 |
| 1002 | shiyanlou1002 | woman |    20 |
| 1003 | shiyanlou1003 | man   |    18 |
| 1004 | shiyanlou1004 | woman |    40 |
| 1005 | shiyanlou1005 | man   |    17 |
| 1006 | shiyanlou1006 | man   |    20 |
+------+---------------+-------+-------+
6 rows in set (0.00 sec)

# 提交，使用提交(COMMIT)，将数据的修改持久化，并结束当前事务，开始一个新的事务
mysql> COMMIT;
Query OK, 0 rows affected (0.02 sec)

# 回滚，这时再次使用回滚语句，但是因为已经提交，插入的操作并不会被撤销
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

# 使用查询，验证已经提交的修改并不会被撤销
mysql> SELECT * FROM student;
+------+---------------+-------+-------+
| s_id | s_name        | s_sex | s_age |
+------+---------------+-------+-------+
| 1001 | shiyanlou1001 | man   |    10 |
| 1002 | shiyanlou1002 | woman |    20 |
| 1003 | shiyanlou1003 | man   |    18 |
| 1004 | shiyanlou1004 | woman |    40 |
| 1005 | shiyanlou1005 | man   |    17 |
| 1006 | shiyanlou1006 | man   |    20 |
+------+---------------+-------+-------+

# 再次插入一条新的数据，
mysql> INSERT INTO student VALUES(1007, "shiyanlou1007", "man", 30);
Query OK, 1 row affected (0.00 sec)

# 退出当前会话
mysql> QUIT;
Bye

# 此时再次连接数据库，开始一个会话，进行查询操作，可以看见 s_id 为 1007 的数据并未存在于 student 表中。因为我们直接退出会话，并未执行提交操作。
...

2.3 语法使用

在上面的内容中，我们给大家演示了回滚和提交的一些原理和基本操作。并且使用 SET 临时修改了当前会话的系统变量 autocommit。而在关闭当前连接，开始一个新的会话后，已经关闭的自动提交功能会重新打开，因为 SET autocommit 的方式属于临时修改。服务默认的配置项依然是开启自动提交。

虽然我们可以通过修改配置文件的方式一直设置关闭自动提交。但是这样做的后果就是，如果我们对数据库做出了修改，而忘记了提交修改，在关闭当前会话后，修改并未被持久化。

因此很多时候我们并不需要关闭自动提交功能，在需要使用事务的时候，还可以使用 MySQL 相关的语法来启动一个事务：

开始事务

START TRANSACTION | BEGIN [WORK]

这里我们可以使用 START TRANSACTION 或者 BEGIN [WORK] 来启动一个事务。

结束事务

COMMIT WORK CHAIN] [[NO] RELEASE]

[AND CHAIN] 子句在当前事务结束时立即开始新事务，默认值为 [NO CHAIN]。 RELEASE 代表终止事务后断开当前会话。

回滚事务 回滚整个事务。

ROLLBACK WORK CHAIN] [[NO] RELEASE]

隐式提交 不是所有的 SQL 语句都是可以回滚的。一般来说，创建，修改以及删除数据库，数据表，视图等操作都是不可以回滚的。

并且它们会造成隐式的提交。除此之外，一些语句在执行后也会造成隐式提交，如开始一个新的事务，锁定语句以及数据加载语句等。甚至还包括对 mysql 数据库的修改等操作也会造成隐式提交。

当开始一个事务，并创建一个数据表，这时创建表的操作是不能被撤销回滚的，因为该操作已经被提交，即使我们并没有显式地使用 COMMIT 去提交事务，这就是隐式提交。

保存点 而对于事务中的回滚操作，有时候我们不需要回滚整个事务，只需要回滚到事务的某个执行语句之前，我们就可以通过创建保存点，然后在使用回滚操作的时候，回滚到指定的保存点即可。

相关的语法使用格式如下：

SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

SAVEPOINT 用来设置保存点，identifier 为保存点的唯一名称标识。

RELEASE 用来删除指定的保存点。

如下的简单示例：

# 这里我们以 student 表为例，首先，查看相关的内容

# 自动提交为默认开启
mysql> SHOW VARIABLES LIKE "autoco%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

# 查看此时 student 表的数据
mysql> SELECT * FROM student;
+------+---------------+-------+-------+
| s_id | s_name        | s_sex | s_age |
+------+---------------+-------+-------+
| 1001 | shiyanlou1001 | man   |    10 |
| 1002 | shiyanlou1002 | woman |    20 |
| 1003 | shiyanlou1003 | man   |    18 |
| 1004 | shiyanlou1004 | woman |    40 |
| 1005 | shiyanlou1005 | man   |    17 |
| 1006 | shiyanlou1006 | man   |    20 |
+------+---------------+-------+-------+
6 rows in set (0.00 sec)

# 开启事务
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

# 插入一条数据
mysql> INSERT INTO student VALUES(1007, "shiyanlou1007", "man", 30);
Query OK, 1 row affected (0.00 sec)

# 设置保存点，
mysql> SAVEPOINT save_insert;
Query OK, 0 rows affected (0.00 sec)

# 删除一条数据 1006
mysql> DELETE FROM student where s_id=1006;
Query OK, 1 row affected (0.26 sec)

# 查询数据，此时 1007 被插入，1006 被删除
mysql> SELECT * FROM student;
+------+---------------+-------+-------+
| s_id | s_name        | s_sex | s_age |
+------+---------------+-------+-------+
| 1001 | shiyanlou1001 | man   |    10 |
| 1002 | shiyanlou1002 | woman |    20 |
| 1003 | shiyanlou1003 | man   |    18 |
| 1004 | shiyanlou1004 | woman |    40 |
| 1005 | shiyanlou1005 | man   |    17 |
| 1007 | shiyanlou1007 | man   |    30 |
+------+---------------+-------+-------+

# 回退到设置的保存点，即插入 1007 之后，删除 1006 之前
mysql> ROLLBACK TO save_insert;
Query OK, 0 rows affected (0.00 sec)

# 此时 1006 还未删除，并且 1007 已经插入。
mysql> SELECT * FROM student;
+------+---------------+-------+-------+
| s_id | s_name        | s_sex | s_age |
+------+---------------+-------+-------+
| 1001 | shiyanlou1001 | man   |    10 |
| 1002 | shiyanlou1002 | woman |    20 |
| 1003 | shiyanlou1003 | man   |    18 |
| 1004 | shiyanlou1004 | woman |    40 |
| 1005 | shiyanlou1005 | man   |    17 |
| 1006 | shiyanlou1006 | man   |    20 |
| 1007 | shiyanlou1007 | man   |    30 |
+------+---------------+-------+-------+

# 删除保存点，删除 1006 ,1007 数据
mysql> RELEASE SAVEPOINT save_insert;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM student where s_id=1006;
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM student where s_id=1007;
Query OK, 1 row affected (0.00 sec)

# 提交后再次查询
mysql> COMMIT;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM student;
+------+---------------+-------+-------+
| s_id | s_name        | s_sex | s_age |
+------+---------------+-------+-------+
| 1001 | shiyanlou1001 | man   |    10 |
| 1002 | shiyanlou1002 | woman |    20 |
| 1003 | shiyanlou1003 | man   |    18 |
| 1004 | shiyanlou1004 | woman |    40 |
| 1005 | shiyanlou1005 | man   |    17 |
+------+---------------+-------+-------+

如上所示，我们演示了使用语法显示的开启一个事务，并通过创建保存点，回滚等操作。希望大家能熟悉事务的工作流程。

MySQL 事务处理操作视频：

3. 总结

通过本节实验，可以认识到事务在数据库中意义和它的操作方式等。