管理事务

摘要：本文学习了如何使用DCL语句管理事务。

1 简介

1.1 定义

事务是一组逻辑处理单位，可以是执行一条SQL语句，也可以是执行几个SQL语句。

事务用来保证数据由一种存储情况变为另一种存储情况，组成事务的各个单元要么都执行成功，要么都执行失败。

1.2 作用

如果只是简单的一条SQL语句的执行，那么是不需要事务的，但在一些复杂的情况下，一个操作会涉及到多条SQL语句的执行，这种情况下就有必要保证所有的操作全部成功或者全部失败。

比如小明给小红转账的操作，就会涉及到从小明账户扣钱和给小红账户充钱的两个操作：

• 只有两个操作都成功执行了整个操作才算成功，这时就可以提交整个事务，状态由转账前变为转账后。
• 如果任何一个操作执行失败的话整个操作都要算做失败，这时就需要恢复事务，保证两个账户上的金额和转账前是一样的，状态恢复到转账前。

事务保证了一组操作的完整性和安全性。

1.3 存储引擎

默认使用InnoDB引擎，支持事务。

使用命令查看数据库支持的存储引擎，以及存储引擎是否支持事务：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

mysql> show engines;
+--------------------+---------+---------+--------------+------+------------+
| Engine             | Support | Comment | Transactions | XA   | Savepoints |
+--------------------+---------+---------+--------------+------+------------+
| InnoDB             | DEFAULT | ...     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | ...     | NO           | NO   | NO         |
| MEMORY             | YES     | ...     | NO           | NO   | NO         |
| BLACKHOLE          | YES     | ...     | NO           | NO   | NO         |
| MyISAM             | YES     | ...     | NO           | NO   | NO         |
| CSV                | YES     | ...     | NO           | NO   | NO         |
| ARCHIVE            | YES     | ...     | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | ...     | NO           | NO   | NO         |
| FEDERATED          | NO      | ...     | NULL         | NULL | NULL       |
+--------------------+---------+---------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql>

在Transactions列中显示YES表示支持事务。

1.4 基本术语

保存点（Savepoint）指在事务执行前或者事务执行后，数据在数据库里的存储情况，有时也称为状态。

回退（Rollback）指撤销事务的操作，事务期间执行的操作都将失效，事务恢复到上个状态。

提交（Commit）指提交事务的操作，事务期间执行的操作全部生效，事务进入新的状态。

1.5 事务特性

原子性（Atomicity）指事务包含的所有操作要么全部成功提交，要么全部失败回滚。

一致性（Consistency）指事务必须使数据库的数据和资源从一个一致性状态变换到另一个一致性状态。

隔离性（Isolation）指当多个用户并发访问数据库并且操作同一张表时，数据库为每一个用户开启事务，不会被其他事务的操作干扰，多个并发事务之间相互隔离。

持久性（Durability）指一个事务一旦被提交了，对数据的改变就是永久性的，哪怕数据库遇到故障也不会丢失提交的改动。

1.6 并发问题

对事务的操作分为两类：一种是读取事务（查），另一种是修改事务（增删改）。

单个事务的情况下，不会产生并发问题。如果多个事务在同一时刻操作同一数据可能会影响最终期望的结果，产生并发问题。

常见的并发问题：

• 脏写：更新时更新。事务一更新数据未提交，事务二更新相同数据并提交，事务一回滚，事务二提交的更新数据无效。
• 脏读：更新时读取。事务一更新数据未提交，事务二读取数据，事务一回滚，事务二读取的数据无效。
• 不可重复读：读取时更新或者删除。事务一读取数据，事务二修改数据或者删除数据，在事务二提交，事务一读取到不同的数据。
• 幻读：读取时插入。事务一读取数据，事务二插入数据，在事务二提交，事务一读取到插入的数据。

更新丢失：

• 第一类更新丢失：更新时更新。事务一更新数据并提交，事务二更新相同数据并回滚，事务一提交的更新数据无效。
• 第二类更新丢失：更新时更新。事务一更新数据并提交，事务二更新相同数据并提交，事务一提交的更新数据无效。

数据库不允许第一类更新丢失问题，第二类更新丢失问题无法通过隔离级别解决，一般使用锁解决。

1.7 隔离级别

为了解决不同程度的并发问题，SQL标准定义了隔离级别，每个级别都有各自的具体规则。

主要的隔离级别有四种：

• 读未提交（RU，Read Uncommitted）：最低的隔离级别，可以看到未提交事务的执行结果，同时更新相同数据会阻塞。可避免脏写。
• 读已提交（RU，Read Committed）：大多数系统的默认隔离级别，但不是MySQL默认隔离级别，只能看到已提交事务的执行结果。可避免脏写、脏读。
• 可重复读（RR，Repeatable Read）：MySQL默认隔离级别，在事务执行期间，读取的都是相同的数据。可避免脏写、脏读、不可重复读。
• 串行化（S，Serializable）：最高的隔离级别，通过强制事务排序解决并发问题。在每个读操作的数据行增加共享锁，可能导致大量超时和竞争。可避免脏写、脏读、不可重复读、幻读。

MySQL的默认隔离级别是RR级别，InnoDB存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了幻读的问题。

2 操作

2.1 自动提交

查询事务自动提交：

1

select @@autocommit;

开启自动提交：

1

set autocommit = 1;

关闭自动提交：

1

set autocommit = 0;

2.2 开启事务

语法：

1

start transaction;

也可以使用begin;或者begin work;开启事务。

2.3 提交事务

语法：

1

commit;

也可以使用commit work;提交事务。

示例：

1
2
3
4
5
6
7
8
9
10
11

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

mysql> update student set sex = '女' where id = 904;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql>

2.4 回滚事务

语法：

1

rollback;

也可以使用rollback work;回滚事务。

示例：

1
2
3
4
5
6
7
8
9
10
11

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

mysql> update student set sex = '女' where id = 904;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql>

2.5 设置还原点

还原点必须要在事务内才能使用，否则会产生错误。

创建还原点：

1

savepoint 名称;

回滚还原点：

1

rollback to 名称;

删除还原点：

1

release savepoint 名称;

2.6 设置隔离级别

查看当前事务的隔离级别：

1

select @@tx_isolation;

设置隔离级别：

1

set tx_isolation = "隔离级别名称";

隔离级别名称：

• Read-Uncommitted：读未提交。
• Read-Committed：读已提交。
• Repeatable-Read：可重复读。
• Serializable：串行化。