带你用例子了解数据库事务的隔离级别

freddie-marriage-100212-unsplash.jpg

带你了解什么是脏读、不可重复读、幻读…

隔离性会发生什么?

如果不考虑事务的隔离性会发生什么错误,我想你一定听说过下面的几种错误:

脏读

脏读(Dirty Read)是某一事务读取了另一个事务未提交的脏数据。

不可重复读

不可重复读(NonRepeatable Read)则是读取了前一事务提交的数据。

幻读

幻读(Phantom Read)和不可重复读都是读取了另一条已经提交的事务(这点就脏读不同),所不同的是不可重复读查询的都是同一个数据项,而幻读针对的是一批数据整体(比如数据的个数)。

如果对着三种错误的概念朦胧没有关系!下面我们通过举例说明四种隔离性来让你充分了解这三种在实际例子中产生的结果。

在此之前,我们归纳一下四种级别分别会发生的哪种错误:

隔离级别 脏读 不可重复读 幻读
未提交读(Read uncommitted 可能 可能 可能
已提交读(Read committed 不可能 可能 可能
可重复读(Repeatable read 不可能 不可能 可能
可串行化(Serializable 不可能 不可能 不可能

1. 未提交读

未提交读(Read uncommitted)即允许当前会话事务读取到其他会话中未提交事务修改的数据,可能导致脏读。下面我们来举例说明,由于Innodb默认的隔离级别是Repeated Read,因此我们需要修改会话的隔离级别为read uncommitted

1
2
// 会话1、会话2
mysql> SET session transaction isolation level read uncommitted;

记录每个用户金额的账单表account数据如下:

1
2
3
4
5
6
7
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 100 |
| 2 | B | 100 |
+----+------+-------+

让我们来假设一个场景:A要向B转账100元,此时会话1开始事务并执行修改用户B金额的SQL语句:

1
2
3
4
5
// 会话1
mysql> begin; // 开始事务

// 更新用户B的金额
mysql> update account set money = money + 100 where id = 2;

如果此刻用户A通知用户B转账成功。并且用户B在会话2执行查询语句发现100元的确到账:

1
2
3
4
5
6
7
// 会话2
mysql> select * from account where id = 2;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 2 | B | 200 |
+----+------+-------+

但是因为某种原因,扣除A账户金额的SQL语句执行失败了,该事务回滚回复修改的数据:

1
2
// 会话1
mysql> rollback; // 回滚事务

这时候用户B再进行查询时,发现金额之前到账的金额又不见了?

1
2
3
4
5
6
7
// 会话2
mysql> select * from account where id = 2;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 2 | B | 100 |
+----+------+-------+

这就是发生了脏读,即会话1中的事务还未提交时(此时数据发生了改变),但是会话2却读取到了改变的数据,而之后事务发生回滚,数据恢复到事务开始之前的状态。因此会话2在此之前所读到的数据即为脏数据。

2. 已提交读

2.1 避免脏读

已提交读Read committed只能读取到已成功提交事务的数据,因此可以避免发生脏读。举例说明,同样我们把两个会话的隔离级别设置为已提交读:

1
mysql> SET session transaction isolation level read committed;

在隔离级别为 read committed 的情况下:如果会话1执行更新账户B的事务操作之后,此时会话2所读取到的金额值将不会发生改变:

1
2
3
4
5
6
7
// 会话2
mysql> select * from account where id = 2;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 2 | B | 100 |
+----+------+-------+

只有当会话1执行commit提交事务时,才会发生改变:

1
2
3
4
5
6
7
8
9
10
11
// 会话1
mysql> commit;


// 会话2
mysql> select * from account where id = 2;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 2 | B | 200 |
+----+------+-------+

2.2 不可重复读

但是,已提交读却会发生不可重复读的异常,可能在同一个事务内相同的查询是不同的结果,因为该数据被另一个会话事务提交修改。

例如会话1开始事务,并查询当前账户B此时的金额。但是就在此刻,用户C在会话3中提交了转账给账户B的事务:

1
2
3
4
5
6
7
8
9
10
11
12
// 会话1
mysql> select * from account where id = 2;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 2 | B | 100 |
+----+------+-------+

// 会话3
mysql> begin; // 开始事务
mysql> update account set money = money + 100 where id = 2;
mysql> commit; // 提交事务

随后,会话1再次查询账户B的金额时,发现金额已经发生了变化:

1
2
3
4
5
6
7
// 会话1
mysql> select * from account where id = 2;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 2 | B | 200 |
+----+------+-------+

这就是所谓的“不可重复读”——会话1可以在事务未结束期间查询到其他会话提交的事务所改变的数据。

3. 可重复读

可重复读(Repeatable readInnoDB 默认隔离级别。从名字上看,意味着它支持事务的可重复读,不会有“不可重复读”的错误情况出现。这是因为该隔离级别的情况下,在同一个事务内的查询都是事务开始时刻一致的。

3.2 避免不可重复读

同上边用户C转账给账户B例子来说,尽管会话3成功提交了修改账户B金额的事务,但是会话1读取到仍然是事务开始时刻的数据:

1
2
3
4
5
6
7
// 会话1
mysql> select * from account where id = 2;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 2 | B | 100 |
+----+------+-------+

从锁的机制来看,该隔离级别的情况下,在该sql第一次读取到数据后,就将这些数据加锁,其它事务无法修改这些数据,就可以实现可重复读了。

3.2 可能发生幻读

幻读是事务非独立执行时发生的一种现象,当事务A先前读取了数据,或者修改了全部数据,事务B还是可以insert数据提交,这时事务A就会发现莫名其妙多了一条之前没有的数据,这就是幻读,不能通过行锁来避免。

举例来说,公司财务处的小张接到通知给全体员工发年终奖,需要给老员工所有账户转账100元。于是在会话1中开始了事务,并查询了一下当前表中的数据:

1
2
3
4
5
6
7
8
9
10
// 会话1
mysql> begin;

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 100 |
| 2 | B | 100 |
+----+------+-------+

此时另一边人事部来了一位员工C,人事部小李在人员管理中向账单表添加了一条记录:

1
2
3
4
5
// 会话2
mysql> begin;
// 插入新员工C的记录
mysql> insert into account(id, name, money) values (3, 'C', '100');
mysql> commit;

小张在发现数据没错的情况下,执行了更新账户金额的操作,并发现操作成功;小张做事谨慎,在提交之前她又执行了查询的操作校正一下是否正确:

1
2
3
4
5
6
7
8
9
10
11
// 会话1
mysql> update account set money = money + 100;

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 200 |
| 2 | B | 200 |
| 3 | C | 200 |
+----+------+-------+

小张看到了感觉见鬼了一样,刚修改之前可没这条记录,而且金额也被修改了,吓得小张一直不敢提交事务,赶紧掏出手机联系了人事部…

这就是所谓的幻读,让小张感觉到第一次的查询是不是出现“幻觉”了?从锁的机制来看,尽管Repeatable read隔离级别对事务第一次读到的数据加锁,但是却无法锁住在事务期间其他会话insert的数据。

4. 可串行化

可串行化(Serializable)的隔离级别下,三种异常结果都不会发生。

这是因为,在该隔离级别情况下:读用读锁,写用写锁,读锁和写锁互斥,这么做可以有效的避免幻读、不可重复读、脏读等问题,但会极大的降低数据库的并发能力。

Pushy wechat
欢迎订阅我的微信公众号