Inno' Blog HomeArchivesTagsCategoriesGalleryAbout
重学MySQL之事务
|MySQL

重新学习 MySQL 的基础知识，本章内容为事务的介绍

什么是事务

数据库事务(Database Transaction) ，事务是一系列作为一个逻辑单元来执行的操作集合。

事务具有四大特征（ACID）：

  • 原子性（Atomicity）：事务是最小的工作单位，不可以再分割，事务中的操作要么全部执行，要么全都不执行，不能只完成部分操作。原子性在数据库系统中，由恢复机制来实现。
  • 一致性（Consistency）：同一事务中的 SQL 语句，必须保证同时成功或这失败，事务开始前和结束后的状态应该保持一致性。数据库一致性的定义是由用户负责的。
  • 隔离性（Isolation）：系统必须保证事务不受其他并发执行事务的影响，即当多个事务同时运行时，各事务之间相互隔离，不可互相干扰。事务查看数据时所处的状态，要么是另一个并发事务修改它之前的状态，要么是另一个并发事务修改它之后的状态，事务不会查看中间状态的数据。隔离性通过系统的并发控制机制实现。
  • *持久性（Durability）：事务一旦结束 ( COMMIT ) ，就不可以再返回了 ( ROLLBACK ) 。

事务的 ACID 原则保证了一个事务或者成功提交，或者失败回滚，二者必居其一。

为什么要使用事务

举例来说，银行转账的时候，用户 A 向用户 B 转账了 100 元，此时在数据库中，用户 A 的金额应当 -100，而用户 B 的金额应当 +100。倘若只有一条 SQL 语句执行成功，而另一条执行失败，那么就会出现数据前后不一致的情况。

因此在执行多条有关联的 SQL 语句时，事务要求这些 SQL 语句要么同时执行成功，要么都失败。

事务的流程

在 MySQL 中，要使用事务，首先就需要先开启事务，然后执行 SQL 语句，所有 SQL 语句都执行完确认无误后就可以提交；如果在提交之前及时发现了错误，可以使用回滚来撤销刚才执行的 SQL 语句，使其回到最后一次提交数据时的状态。

注意，一旦 SQL 执行结果提交成功，回滚操作就无法撤销刚才的操作。因此，提交（COMMIT）与回滚（ROLLBACK）应当是一对并列操作而非递进操作。

在 MySQL 中，事务是默认自动提交的。所以，为了保证一致性，以便在 SQL 语句执行错误后及时回滚，我们可以选择手动关闭自动提交

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 查询事务的自动提交状态
mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

-- 关闭自动提交
mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.00 sec)

-- 查询自动提交状态
mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

此后，倘若我们需要提交数据，就需要手动提交，即在 @@AUTOCOMMIT = 0 时，使用 COMMIT 命令提交事务。

如果需要回滚，则在 @@AUTOCOMMIT = 0 时，使用 ROLLBACK 命令回滚事务。

但是，关闭自动提交后，就会使得数据库的操作更加繁琐，因为每次执行语句后都需要手动输入 COMMIT。

因此，我们可以选择使用手动开启事务的方式

开启事务

在 MySQL 中，可以使用 BEGIN 或者 START TRANSACTION 来手动开启事务

以上面银行转账为例，创建一张 user 表，并插入两条记录

1
2
3
4
5
6
7
8
9
10
11
12
mysql> CREATE TABLE user(
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL,
    -> money INT DEFAULT 0
    -> );
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO user(name, money) VALUES('Mike', 1000);
Query OK, 1 row affected (0.36 sec)

mysql> INSERT INTO user(name, money) VALUES('Amy', 2000);
Query OK, 1 row affected (0.08 sec)

开启事务，完成 Mike 向 Amy 转账 100 元的操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE user SET money=money-100 WHERE name='Mike';
Query OK, 1 row affected (0.31 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE user SET money=money+100 WHERE name='Amy';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | Mike |   900 |
|  2 | Amy  |  2100 |
+----+------+-------+
2 rows in set (0.00 sec)

虽然我们还没有提交修改，但会发现数据表中的数据已经更改了，但是不要慌，此时发生变化的数据只是暂存在一张临时表中，要完成最终数据的修改，仍然需要手动提交。

提交事务

提交事务，意味着将事务开始以来所执行的所有数据修改成为数据库的永久部分，因此也标志着一个事务的结束。一旦执行了该命令，将不能回滚事务。只有在所有修改都准备好提交给数据库时，才执行这一操作。

1
2
3
4
5
6
7
8
9
10
11
mysql> COMMIT;
Query OK, 0 rows affected (0.09 sec)

mysql> SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | Mike |   900 |
|  2 | Amy  |  2100 |
+----+------+-------+
2 rows in set (0.00 sec)

此时，对数据的修改才正式生效。

撤销事务

正如前面所说，如果修改已经提交，那么回滚是无效的

1
2
3
4
5
6
7
8
9
10
11
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | Mike |   900 |
|  2 | Amy  |  2100 |
+----+------+-------+
2 rows in set (0.00 sec)

开启事务，尝试执行 Amy 向 Mike 转账 200 元的操作，并在最后进行回滚

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE user SET money=money-200 WHERE name='Amy';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE user SET money=money+200 WHERE name='Mike';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | Mike |  1100 |
|  2 | Amy  |  1900 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.07 sec)

mysql> SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | Mike |   900 |
|  2 | Amy  |  2100 |
+----+------+-------+
2 rows in set (0.00 sec)

回滚成功。

不过有时候，我们并不需要回退所有操作，可以让事务回滚到指定位置，此时，需要在事务中设定保存点（SAVEPOINT）。

保存点的创建通过 SAVEPOINT <保存点名称> 语句来实现，再执行 ROLLBACK TO <保存点名称> 语句回滚到该保存点。

开启事务，完成以下操作：

  1. Mike 向 Amy 转账 200 元
  2. Amy 向 Mike 转账 100 元
  3. 回滚到第二步，并将最终修改提交

最终的结果是，Mike 仅仅只向 Amy 转账了 200 元。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE user SET money=money-200 WHERE name='Mike';
Query OK, 1 row affected (0.32 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE user SET money=money+200 WHERE name='Amy';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | Mike |   700 |
|  2 | Amy  |  2300 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> SAVEPOINT mike2amy;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE user SET money=money-100 WHERE name='Amy';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE user SET money=money+100 WHERE name='Mike';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | Mike |   800 |
|  2 | Amy  |  2200 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> ROLLBACK TO mike2amy;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | Mike |   700 |
|  2 | Amy  |  2300 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | Mike |   700 |
|  2 | Amy  |  2300 |
+----+------+-------+
2 rows in set (0.00 sec)

事务的隔离性

之前的例子中提到了原子性、一致性和持久性，但未提到隔离性，因此这里单独讲解

事务的隔离性分为四种（性能从高到低，隔离级别从低到高）：

1. READ UNCOMMITTED ( 读取未提交 )

如果有多个事务，那么任意事务都可以看见其他事务的未提交数据。

设置系统隔离级别，LEVEL 后面表示要设置的隔离级别。

1
2
3
4
5
6
7
8
9
10
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.TX_ISOLATION;
+-----------------------+
| @@GLOBAL.TX_ISOLATION |
+-----------------------+
| READ-UNCOMMITTED      |
+-----------------------+
1 row in set (0.00 sec)

在查询隔离级别时，SELECT @@GLOBAL.TX_ISOLATION; 表示系统的隔离级别，而不加 GLOBAL 的语句 SELECT @@TX_ISOLATION; 则表示会话的隔离级别，设置隔离级别的时候也可以选择是否是 GLOBAL 的。

注意，以上是查询隔离级别的语法是 MySQL 5.x 的，如果是 MySQL 8.x 则需要略加修改

1
2
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
SELECT @@TRANSACTION_ISOLATION;

该级别的性能是最优的，但是隔离性是最差的。当一个事务可以读取另一个事务未提交的数据时，就会出现脏读（Dirty Read） 的情况，这在实际开发中是不允许出现的。

2. READ COMMITTED ( 读取已提交 )

只能读取到其他事务已经提交的数据。

需要注意的是，在有新的事务连接进来时，它们可以查询到已经提交过的事务数据。但是对于当前事务来说，它们看到的还是未提交之前的数据。

1
2
3
4
5
6
7
8
9
10
11
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.TX_ISOLATION;
+-----------------------+
| @@GLOBAL.TX_ISOLATION |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+
1 row in set (0.00 sec)

虽然 READ COMMITTED 让我们只能读取到其他事务已经提交的数据，但还是会出现问题，就是在读取同一个表的数据时，可能会发生前后不一致的情况。这被称为不可重复读现象 ( READ COMMITTED ) 。

3. REPEATABLE READ ( 可被重复读 )

MySQL 默认事务隔离级别

如果有多个连接都开启了事务，那么事务之间只能共享之前已提交的记录，比如事务 A 在读到一条数据之后，此时事务 B 对该数据进行了修改并提交，那么事务 A 再读该数据，读到的还是原来的内容。

1
2
3
4
5
6
7
8
9
10
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.TX_ISOLATION;
+-----------------------+
| @@GLOBAL.TX_ISOLATION |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

不过，可重复读会导致 幻读（Phantom Read） 问题，它指的是事务 A 在读取某一范围的数据时，另一个事务在该范围内修改了新的数据，当事务 A 再读取该范围的数据时，会发现出现了新的数据，这导致了数据前后的不一致。而解决幻读的方式就是使用更高级别的隔离——串行化（SERIALIZABLE）。

4. SERIALIZABLE ( 串行化 )

假设把所有的事务都放在一个串行的队列中，那么所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作 ( 这意味着队列中同时只能执行一个事务的写入操作 ) 。

1
2
3
4
5
6
7
8
9
10
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.TX_ISOLATION;
+-----------------------+
| @@GLOBAL.TX_ISOLATION |
+-----------------------+
| SERIALIZABLE          |
+-----------------------+
1 row in set (0.00 sec)

如果事务 A 开启后执行了 SQL 语句但未提交，此时事务 B 开启后也打算执行 SQL 语句，因为隔离级别设置为串行化，所以在事务 A 未手动提交之前，事务 B 的操作都将进入阻塞状态，或者出现等待超时。

Author: Inno Fang
Link: http://innofang.github.io/2020/03/29/%E9%87%8D%E5%AD%A6MySQL%E4%B9%8B%E4%BA%8B%E5%8A%A1/
Copyright Notice: All articles in this blog are licensed under CC BY-NC-ND 4.0 unless stating additionally.
MySQL