MySQL 入门(二)——事务

在数据库中,事务处理用来维护数据库的完整性,它保证成批的 MySQL 操作要么完全执行,要么完全不执行。在事务中,误操作之后能够及时的恢复,并保证在不同的 session 间的一致性。
MySQL 中只有 InnoDB 引擎的表才支持事务。

事务控制语句

  • BEGIN 或者 START TRANSACTION:开始一个新事务;
  • COMMIT:提交当前事务,使其变成永久的修改;
  • ROLLBACK:回滚当前事务,取消所有修改;
  • SET autocommit = {0 | 1}:是否开启自动提交。默认情况下,MySQL 是开启了自动提交的。在事务内部,自动提交是关闭的;
  • SAVEPOINT identifier:在事务中创建一个保存点,一个事务可以有多个保存点;
  • RELEASE SAVEPOINT identifier:删除某个保存点;
  • ROLLBACK TO identifier:把事务回滚到某个保存点。

事务隔离级别

数据库允许多个并发事务同时进行读写和修改,使用事务隔离来保持数据的一致性。
InnoDB 引擎提供四种事务隔离级别:

  1. READ UNCOMMITTED:未提交读,俗称脏读。可以读取到其他会话未提交的数据;
  2. READ COMMITTED:提交读,也称不重复读。只能读取到其他会话已经提交的数据,Oracle 等多数数据库的默认级别;
  3. REPEATABLE READ:可重复读。在同一个事务内的查询永远保持一致,MySQL InnoDB 的默认级别;
  4. SERIALIZABLE:串行读。完全串行化的,读写操作都会阻塞。

通过命令 SELECT @@GLOBAL.transaction_isolationSELECT @@SESSION.transaction_isolation 来查看全局与当前会话的事务隔离级别。

以下用例子来具体说明四种隔离级别的区别。
现有一个表 test

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM test;
+----+---------+---------+---------+
| id | name | Chinese | English |
+----+---------+---------+---------+
| 1 | 张三 | 98 | 59 |
| 2 | 李四 | 88 | 96 |
| 3 | Sui Xin | 77 | 81 |
+----+---------+---------+---------+
3 rows in set (0.00 sec)

READ UNCOMMITTED

session A 在事务中插入一条数据但不提交:

1
2
BEGIN;
INSERT INTO test VALUES(0, "Shane", 90, 91);

session B 中设置隔离级别为 READ UNCOMMITTED

1
2
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM test;

此时已经可以看到 session A 刚才插入但没有提交的数据:

1
2
3
4
5
6
7
8
9
10
mysql> SELECT * FROM test;
+----+---------+---------+---------+
| id | name | Chinese | English |
+----+---------+---------+---------+
| 1 | 张三 | 98 | 59 |
| 2 | 李四 | 88 | 96 |
| 3 | Sui Xin | 77 | 81 |
| 4 | Shane | 90 | 91 |
+----+---------+---------+---------+
4 rows in set (0.00 sec)

READ COMMITTED

session A 在事务中插入一条数据但不提交:

1
2
BEGIN;
INSERT INTO test VALUES(0, "Shane", 90, 91);

session B 中设置隔离级别为 READ COMMITTED

1
2
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM test;

此时看不到 session A 中的修改:

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM test;
+----+---------+---------+---------+
| id | name | Chinese | English |
+----+---------+---------+---------+
| 1 | 张三 | 98 | 59 |
| 2 | 李四 | 88 | 96 |
| 3 | Sui Xin | 77 | 81 |
+----+---------+---------+---------+
3 rows in set (0.00 sec)

但当 session A 中将事务提交之后:

1
COMMIT;

session B 中就可以看到数据的修改:

1
2
3
4
5
6
7
8
9
10
mysql> SELECT * FROM test;
+----+---------+---------+---------+
| id | name | Chinese | English |
+----+---------+---------+---------+
| 1 | 张三 | 98 | 59 |
| 2 | 李四 | 88 | 96 |
| 3 | Sui Xin | 77 | 81 |
| 4 | Shane | 90 | 91 |
+----+---------+---------+---------+
4 rows in set (0.00 sec)

而这种隔离会造成一种现象,同一个会话里面读到的数据可能不一致,叫做不可重复读

REPEATABLE READ

session B 中设置事务隔离级别为 REPEATABLE READ

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

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

mysql> SELECT * FROM test;
+----+---------+---------+---------+
| id | name | Chinese | English |
+----+---------+---------+---------+
| 1 | 张三 | 98 | 59 |
| 2 | 李四 | 88 | 96 |
| 3 | Sui Xin | 77 | 81 |
+----+---------+---------+---------+
3 rows in set (0.00 sec)

session A 插入一条数据(没使用事务,即立即提交的):

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> INSERT INTO test VALUES(0, "Shane", 90, 91);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test;
+----+---------+---------+---------+
| id | name | Chinese | English |
+----+---------+---------+---------+
| 1 | 张三 | 98 | 59 |
| 2 | 李四 | 88 | 96 |
| 3 | Sui Xin | 77 | 81 |
| 4 | Shane | 90 | 91 |
+----+---------+---------+---------+
4 rows in set (0.00 sec)

回到 session B,发现数据没有改变,只有 3 条数据:

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM test;
+----+---------+---------+---------+
| id | name | Chinese | English |
+----+---------+---------+---------+
| 1 | 张三 | 98 | 59 |
| 2 | 李四 | 88 | 96 |
| 3 | Sui Xin | 77 | 81 |
+----+---------+---------+---------+
3 rows in set (0.00 sec)

但此时插入一条 id=4 的数据就会报错:

1
2
mysql> INSERT INTO test VALUES(4, "Smith", 94, 92);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

这就是幻读现象:可重复读要求在一个事务中,读取的结果必须保持一致,但是读取的结果并不一定是数据库真实的结果,因为有可能在前次读取之后数据库被别的 session 做了修改。

SERIALIZABLE

session B 中设置隔离级别为 SERIALIZABLE 并开启事务:

1
2
3
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT * FROM test;

此时在 session A 中插入数据:

1
INSERT INTO test VALUES(0, "Shane", 90, 91);

会发现,该语句会等待。若 session B 完成提交,就结束等待并执行;若超时(该时间可以进行配置),则抛出错误 Lock wait timeout exceeded
可以看到,SERIALIZABLE 是事务隔离级别中最严的,不会出现数据的不一致性,但是效率却大打折扣了。