数据库事务 ACID 之隔离性体验

介绍

数据库事务定义

在执行SQL语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分。例如，一个转账操作：

-- 从id=1的账户给id=2的账户转账100元
-- 第一步：将id=1的A账户余额减去100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 第二步：将id=2的B账户余额加上100
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

这两条SQL语句必须全部执行，或者，由于某些原因，如果第一条语句成功，第二条语句失败，就必须全部撤销。

这种把多条语句作为一个整体进行操作的功能，被称为数据库事务。

数据库事务特性

• A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
• C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
• I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
• D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

事务的隔离级别

多个事务同时操作同一数据的情况

• READ UNCOMMITTED：可以读取到对方在事务中的数据。（有脏读，不可重复读，幻读问题）
• READ COMMITTED：无法读取对方在事务中的数据，可以读取到事务 commit 后的数据。（解决脏读问题）
• REPEATABLE READ：无法读取到对方事务 commit 后的数据。（解决不可重复读问题）
• SERIALIZABLE：事务A和事务B串行执行。（解决幻读问题）

数据不一致性问题

脏读：比如在事务A中对num字段进行操作，将num从1改成了2后，事务B读取到了num为2，但此时事务A对事务进行了回滚，那么事务B读到的数据（num=2）就是不正确的，就是脏数据，无法使用。（ 读的数据是错的 ）

不可重复读：比如事务B读取到了 num 字段值为 1，此时事务A修改了 num 为2并进行提交。事务B读取到的 num 就变成了2，再也读不到1了。（ 事务中每次读到的数据不一样 ）

幻读：比如事务B通过 id=99 读取num字段值为null（id=99 还未插入数据），此时事务A 通过 insert 新增了 id=99的数据 num =1 并提交。事务B依然无法查询到 id=99 的 num 值，但是可以通过 UPSET修改 id=99 的 num 数据，再次查询 id=99 可以查询到修改后的 num 值。（ 读不到的数据却可以做修改操作 ）

---

数据一致性问题演示

首先，我们准备好students表的数据，该表仅一行记录：

mysql> select * from students;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+
1 row in set (0.00 sec)

然后，分别开启两个MySQL客户端连接，按顺序依次执行事务A和事务B：

脏读（READ UNCOMMITTED）：

时刻	事务A	事务B
1	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2	BEGIN;	BEGIN;
3	UPDATE students SET name = ‘Bob’ WHERE id = 1;
4		SELECT * FROM students WHERE id = 1;
5	ROLLBACK;
6		SELECT * FROM students WHERE id = 1;
7		COMMIT;

不可重复读（READ COMMITTED）：

时刻	事务A	事务B
1	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2	BEGIN;	BEGIN;
3		SELECT * FROM students WHERE id = 1;
4	UPDATE students SET name = ‘Bob’ WHERE id = 1;
5	COMMIT;
6		SELECT * FROM students WHERE id = 1;
7		COMMIT;

幻读（REPEATABLE READ）：

时刻	事务A	事务B
1	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2	BEGIN;	BEGIN;
3		SELECT * FROM students WHERE id = 99;
4	INSERT INTO students (id, name) VALUES (99, ‘Bob’);
5	COMMIT;
6		SELECT * FROM students WHERE id = 99;
7		UPDATE students SET name = ‘Alice’ WHERE id = 99;
8		SELECT * FROM students WHERE id = 99;
9		COMMIT;

串行（SERIALIZABLE）：

时刻	事务A	事务B
1	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2	BEGIN;	BEGIN;
3		SELECT * FROM students WHERE id = 99;
4	INSERT INTO students (id, name) VALUES (99, ‘Bob’);
5	COMMIT;