45. SQL--事务（非常详细）

1. 前言

在数据库中，我们将一条 sql 语句称为一次基本的操作。将若干条 sql 语句“打包”在一起，共同执行一个完整的任务，这就是事务。

事务（ transaction）由一次或者多次基本操作构成，或者说，事务由一条或者多条 sql 语句构成。

事务有一个最显著的特征，就是它包含的所有 sql 语句作为一个整体向数据库提交，只有所有的 sql 语句都执行完成，整个事务才算成功，一旦某个 sql 语句执行失败，整个事务就失败了。事务失败后需要回滚所有的 sql 语句。

事务中的所有 sql 语句是一个整体，共同进退，不可分割，要么全部执行成功，要么全部执行失败。

事务有很多实用的场景。例如对于电商网站，通常将用户订单存储在一张表中，将商品库存情况存储在另一张表中，当有用户下单时，需要执行两条 sql 语句，一条负责更新订单表，一条负责更新库存表，这两条 sql 语句必须同时执行成功。如果只有一条语句执行成功，另一条语句执行失败，将导致数据库出错，这种后果是无法接受的。

为了避免出现意外，可以将以上两条语句放到一个事务中，其中一条语句执行失败时，数据库将回滚到原来的状态。对于买家来说，数据库回滚会导致下单失败，但这很容易处理，让买家再次下单即可。数据库的正确性永远是最重要的。

其实我们平时使用数据库时，就已经在使用事务了，只不过这种事务只包含一条 sql 语句，并且由数据库引擎自动封装和提交。这意味着，对于任何一条 sql 语句，要么执行成功，要么执行失败，不能成功一部分，失败一部分。

2. 事务的属性

一般来说，事务具有四个标准属性，分别是原子性（atomicity，或称不可分割性）、一致性（consistency）、隔离性（isolation，又称独立性）、持久性（durability），简称 acid。具体说明如下：
1) 原子性
一个事务中的所有 sql 语句，要么全部执行成功，要么全部执行失败，不会结束在中间的某个环节。事务在执行过程中发生错误，会被回滚（rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
2) 一致性
在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的数据必须完全符合所有的预设规则，其中包含数据的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
3) 隔离性
数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable）。
4) 持久性
事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

3. 事务执行流程

各种数据库对事务的支持细节不尽相同，本教程以 mysql 数据库为例进行讲解，mysql 使用标准 sql 来支持事务。

与事务控制有关的 sql 命令包括：

• begin 或者 start transaction：开始事务；
• commit：提交事务；
• rollback：回滚事务；
• savepoint：在事务内部设置回滚标记点；
• release savepoint：删除回滚标记点；
• rollback to：将事务回滚到标记点（rollback 命令的一种变形写法）。

一个事务要么提交（commit），要么回滚（rollback），提交意味着成功，回滚意味着失败。编写事务代码时，以 begin 命令开头，后跟一条或者多条 sql 语句，最后书写 commit 或者 rollback 命令；commit 和 rollback 对应事务的两种状态，只能出现一个。

事务控制命令仅能与 dml 类别的 sql 命令一起使用，包括 insert、update、delete 和 select，在创建或者删除表时不能使用事务，因为这些操作在数据库中是自动提交的。

1) 开始事务

开始事务有以下两种命令，选择其一即可：

commit;

或者

start transaction;

该命令用来标记一个事务的起始点。

2) 提交事务

提交事务使用如下命令：

commit;

提交事务意味着真正执行事务包含的 sql 语句，并把对数据库的修改写入到磁盘上的物理数据库中。commit 意味着事务结束，并且执行成功。

例如，有包含如下记录的 customers 表：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
+----+----------+-----+-----------+----------+

打开一个 MySQL 命令行窗口（我们称它为 A 窗口），使用事务向表中插入两条数据：

mysql> begin;
mysql> insert into customers (name, age, address, salary) values ('chaitali', 25, 'mumbai', 6500.00 );
mysql> insert into customers (name, age, address, salary) values ('hardik', 27, 'bhopal', 8500.00 );

再打开另外一个 mysql 命令行窗口（我们称它为 b 窗口），使用 select 命令查看 customers 表的内容：

mysql>  SELECT * FROM CUSTOMERS;
+----+---------+-----+-----------+--------+
| id | name    | age | address   | salary |
+----+---------+-----+-----------+--------+
|  1 | Ramesh  |  32 | Ahmedabad |   2000 |
|  2 | Khilan  |  25 | Delhi     |   1500 |
|  3 | Kaushik |  23 | Kota      |   2000 |
+----+---------+-----+-----------+--------+

您看，A 窗口对表的修改并没有影响到 B 窗口，B 窗口只能查看到修改之前的内容，这说明 A 窗口的修改动作并没有立即更新到物理数据库，所以在其它会话窗口中无法看到。

在 A 窗口中提交事务：

mysql> commit;

在 b 窗口中再次查看 customers 表的内容：

mysql>  SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary |
+----+----------+-----+-----------+--------+
|  1 | Ramesh   |  32 | Ahmedabad |   2000 |
|  2 | Khilan   |  25 | Delhi     |   1500 |
|  3 | Kaushik  |  23 | Kota      |   2000 |
|  4 | Chaitali |  25 | Mumbai    |   6500 |
|  5 | Hardik   |  27 | Bhopal    |   8500 |
+----+----------+-----+-----------+--------+

您看，在 A 窗口提交事务以后，对表所做的修改才真正更新到物理数据库中，所以才能在其它会话窗口中查看到 A 窗口中插入的数据。

3) 回滚事务

回滚意味着撤销尚未保存到物理数据库中的操作，具体语法格式如下：

rollback;

事务执行过程中如果发生了某种故障，事务不能继续执行，就可以撤销事务，此时对数据库的修改并不会保存到物理数据库中。撤销意味着事务结束，并且执行失败。

例如，有包含如下记录的 CUSTOMERS 表：

+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary |
+----+----------+-----+-----------+--------+
|  1 | Ramesh   |  32 | Ahmedabad |   2000 |
|  2 | Khilan   |  25 | Delhi     |   1500 |
|  3 | Kaushik  |  23 | Kota      |   2000 |
|  4 | Chaitali |  25 | Mumbai    |   6500 |
|  5 | Hardik   |  27 | Bhopal    |   8500 |
+----+----------+-----+-----------+--------+

使用事务删除最后两个用户，并回滚：

mysql> BEGIN;
mysql> DELETE FROM CUSTOMERS WHERE ID=4;
mysql> DELETE FROM CUSTOMERS WHERE ID=5;
mysql> ROLLBACK;

mysql> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary |
+----+----------+-----+-----------+--------+
|  1 | Ramesh   |  32 | Ahmedabad |   2000 |
|  2 | Khilan   |  25 | Delhi     |   1500 |
|  3 | Kaushik  |  23 | Kota      |   2000 |
|  4 | Chaitali |  25 | Mumbai    |   6500 |
|  5 | Hardik   |  27 | Bhopal    |   8500 |
+----+----------+-----+-----------+--------+

您看，回滚事务以后，物理数据库中的数据并没有发生改变，表中的内容和事务执行之前的内容一致。

4) 回滚标记点

ROLLBACK 命令默认回滚整个事务，也即事务中的所有修改操作都无效。但是 SQL 允许回滚事务的一部分，此时需要在事务中设置一个标记点，在该标记点之后的 SQL 语句将被回滚，之前的 SQL 语句将被成功执行。

设置标记点使用 SAVEPOINT 命令，具体语法如下：

SAVEPOINT point_name;

point_name 为标记点名字。

回滚到标记点使用 ROLLBACK TO 命令，具体语法如下：

ROLLBACK TO point_name;

例如，有包含如下记录的 CUSTOMERS 表：

+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary |
+----+----------+-----+-----------+--------+
|  1 | Ramesh   |  32 | Ahmedabad |   2000 |
|  2 | Khilan   |  25 | Delhi     |   1500 |
|  3 | Kaushik  |  23 | Kota      |   2000 |
|  4 | Chaitali |  25 | Mumbai    |   6500 |
|  5 | Hardik   |  27 | Bhopal    |   8500 |
+----+----------+-----+-----------+--------+

使用事务删除最后两个用户，并回滚到标记点：

mysql> BEGIN;
mysql> DELETE FROM CUSTOMERS WHERE ID=4;
mysql> SAVEPOINT sp;
mysql> DELETE FROM CUSTOMERS WHERE ID=5;
mysql> ROLLBACK TO sp;

mysql> SELECT * FROM CUSTOMERS;
+----+---------+-----+-----------+--------+
| id | name    | age | address   | salary |
+----+---------+-----+-----------+--------+
|  1 | Ramesh  |  32 | Ahmedabad |   2000 |
|  2 | Khilan  |  25 | Delhi     |   1500 |
|  3 | Kaushik |  23 | Kota      |   2000 |
|  5 | Hardik  |  27 | Bhopal    |   8500 |
+----+---------+-----+-----------+--------+

您看，我们回滚到标记点 sp，只有 ID 为 4 的用户被删除，ID 为 5 的用户依然留在数据库中。