Sui Xin's Blog

MySQL 入门(二)——事务

Posted on In Disqus:

在数据库中，事务处理用来维护数据库的完整性，它保证成批的 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_isolation 与 SELECT @@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 是事务隔离级别中最严的，不会出现数据的不一致性，但是效率却大打折扣了。

Related Posts