Mysql案例刨析事务隔离级别_Mysql

很多小伙伴对 mysql 的隔离级别一直心存疑惑，其实这个问题一点都不难，关键看怎么讲！单纯的看理论，绝对让你晕头转向，但是，如果我们通过几个实际的 sql 来演示一些，大家就会发现这玩意原来这么简单！

今天松哥想通过几个简单的案例，来和大家演示一下 mysql 中的事务隔离级别问题。

1. 理论

mysql 中事务的隔离级别一共分为四种，分别如下：

序列化（serializable）
可重复读（repeatable read）
提交读（read committed）
未提交读（read uncommitted）

四种不同的隔离级别含义分别如下：

serializable

如果隔离级别为序列化，则用户之间通过一个接一个顺序地执行当前的事务，这种隔离级别提供了事务之间最大限度的隔离。

repeatable read

在可重复读在这一隔离级别上，事务不会被看成是一个序列。不过，当前正在执行事务的变化仍然不能被外部看到，也就是说，如果用户在另外一个事务中执行同条 select 语句数次，结果总是相同的。（因为正在执行的事务所产生的数据变化不能被外部看到）。

read committed

read committed 隔离级别的安全性比 repeatable read 隔离级别的安全性要差。处于 read committed 级别的事务可以看到其他事务对数据的修改。也就是说，在事务处理期间，如果其他事务修改了相应的表，那么同一个事务的多个 select 语句可能返回不同的结果。

read uncommitted

read uncommitted 提供了事务之间最小限度的隔离。除了容易产生虚幻的读操作和不能重复的读操作外，处于这个隔离级的事务可以读到其他事务还没有提交的数据，如果这个事务使用其他事务不提交的变化作为计算的基础，然后那些未提交的变化被它们的父事务撤销，这就导致了大量的数据变化。

在 mysql 数据库种，默认的事务隔离级别是 repeatable read

2. sql 实践

接下来通过几条简单的 sql 向读者验证上面的理论。

2.1 查看隔离级别

通过如下 sql 可以查看数据库实例默认的全局隔离级别和当前 session 的隔离级别：

mysql8 之前使用如下命令查看 mysql 隔离级别：

				?

									select @@global.tx_isolation, @@tx_isolation;

查询结果如图：

Mysql案例刨析事务隔离级别

可以看到，默认的隔离级别为 repeatable-read，全局隔离级别和当前会话隔离级别皆是如此。

mysql8 开始，通过如下命令查看 mysql 默认隔离级别：

				?

									select @@global.transaction_isolation, @@transaction_isolation;

就是关键字变了，其他都一样。

通过如下命令可以修改隔离级别（建议开发者在修改时修改当前 session 隔离级别即可，不用修改全局的隔离级别）：

				?

									set session transaction isolation level read uncommitted

上面这条 sql 表示将当前 session 的数据库隔离级别设置为 read uncommitted，设置成功后，再次查询隔离级别，发现当前 session 的隔离级别已经变了，如图1-2：

Mysql案例刨析事务隔离级别

注意，如果只是修改了当前 session 的隔离级别，则换一个 session 之后，隔离级别又会恢复到默认的隔离级别，所以我们测试时，修改当前 session 的隔离级别即可。

2.2 read uncommitted

2.2.1 准备测试数据

read uncommitted 是最低隔离级别，这种隔离级别中存在脏读、不可重复读以及幻象读问题，所以这里我们先来看这个隔离级别，借此大家可以搞懂这三个问题到底是怎么回事。

下面分别予以介绍。

首先创建一个简单的表，预设两条数据，如下：

Mysql案例刨析事务隔离级别

表的数据很简单，有 javaboy 和 itboyhub 两个用户，两个人的账户各有 1000 人民币。现在模拟这两个用户之间的一个转账操作。

注意，如果读者使用的是 navicat 的话，不同的查询窗口就对应了不同的 session，如果读者使用了 sqlyog 的话，不同查询窗口对应同一个 session，因此如果使用 sqlyog，需要读者再开启一个新的连接，在新的连接中进行查询操作。

2.2.2 脏读

一个事务读到另外一个事务还没有提交的数据，称之为脏读。具体操作如下：

首先打开两个sql操作窗口，假设分别为 a 和 b，在 a 窗口中输入如下几条 sql （输入完成后不用执行）：

				?

									start transaction;

									update account set balance=balance+100 where name='javaboy';

									update account set balance=balance-100 where name='itboyhub';

									commit;

在 b 窗口执行如下 sql，修改默认的事务隔离级别为 read uncommitted，如下：

				?

									set session transaction isolation level read uncommitted

接下来在 b 窗口中输入如下 sql，输入完成后，首先执行第一行开启事务（注意只需要执行一行即可）：

				?

									start transaction;

									select * from account;

									commit;

接下来执行 a 窗口中的前两条 sql，即开启事务，给 javaboy 这个账户添加 100 元。

进入到 b 窗口，执行 b 窗口的第二条查询 sql（select * from user;），结果如下：

Mysql案例刨析事务隔离级别

可以看到，a 窗口中的事务，虽然还未提交，但是 b 窗口中已经可以查询到数据的相关变化了。

这就是脏读问题。

2.2.3 不可重复读

不可重复读是指一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。具体操作步骤如下（操作之前先将两个账户的钱都恢复为1000）：

首先打开两个查询窗口 a 和 b ，并且将 b 的数据库事务隔离级别设置为 read uncommitted。具体 sql 参考上文，这里不赘述。
在 b 窗口中输入如下 sql，然后只执行前两条 sql 开启事务并查询 javaboy 的账户：

				?

									start transaction;

									select * from account where name='javaboy';

									commit;

前两条 sql 执行结果如下：

Mysql案例刨析事务隔离级别

在 a 窗口中执行如下 sql，给 javaboy 这个账户添加 100 块钱，如下：

				?

									start transaction;

									update account set balance=balance+100 where name='javaboy';

									commit;

4.再次回到 b 窗口，执行 b 窗口的第二条 sql 查看 javaboy 的账户，结果如下：

Mysql案例刨析事务隔离级别

javaboy 的账户已经发生了变化，即前后两次查看 javaboy 账户，结果不一致，这就是不可重复读。

和脏读的区别在于，脏读是看到了其他事务未提交的数据，而不可重复读是看到了其他事务已经提交的数据（由于当前 sql 也是在事务中，因此有可能并不想看到其他事务已经提交的数据）。

2.2.4 幻象读

幻象读和不可重复读非常像，看名字就是产生幻觉了。

我举一个简单例子。

在 a 窗口中输入如下 sql：

				?

									start transaction;

									insert into account(name,balance) values('zhangsan',1000);

									commit;

然后在 b 窗口输入如下 sql：

				?

									start transaction;

									select * from account;

									delete from account where name='zhangsan';

									commit;

我们执行步骤如下：

首先执行 b 窗口的前两行，开启一个事务，同时查询数据库中的数据，此时查询到的数据只有 javaboy 和 itboyhub。
执行 a 窗口的前两行，向数据库中添加一个名为 zhangsan 的用户，注意不用提交事务。
执行 b 窗口的第二行，由于脏读问题，此时可以查询到 zhangsan 这个用户。
执行 b 窗口的第三行，去删除 name 为 zhangsan 的记录，这个时候删除就会出问题，虽然在 b 窗口中可以查询到 zhangsan，但是这条记录还没有提交，是因为脏读的原因才看到了，所以是没法删除的。此时就产生了幻觉，明明有个 zhangsan，却无法删除。

这就是幻读。

看了上面的案例，大家应该明白了脏读、不可重复读以及幻读各自是什么含义了。

2.3 read committed

和 read uncommitted 相比，read committed 主要解决了脏读的问题，对于不可重复读和幻象读则未解决。

将事务的隔离级别改为 read committed 之后，重复上面关于脏读案例的测试，发现已经不存在脏读问题了；重复上面关于不可重复读案例的测试，发现不可重复读问题依然存在。

上面那个案例不适用于幻读的测试，我们换一个幻读的测试案例。

还是两个窗口 a 和 b，将 b 窗口的隔离级别改为 read committed，

然后在 a 窗口输入如下测试 sql：

				?

									start transaction;

									insert into account(name,balance) values('zhangsan',1000);

									commit;

在 b 窗口输入如下测试 sql：

				?

									start transaction;

									select * from account;

									insert into account(name,balance) values('zhangsan',1000);

									commit;

测试方式如下：

首先执行 b 窗口的前两行 sql，开启事务并查询数据，此时查到的只有 javaboy 和 itboyhub 两个用户。
执行 a 窗口的前两行 sql，插入一条记录，但是并不提交事务。
执行 b 窗口的第二行 sql，由于现在已经没有了脏读问题，所以此时查不到 a 窗口中添加的数据。
执行 b 窗口的第三行 sql，由于 name 字段唯一，因此这里会无法插入。此时就产生幻觉了，明明没有 zhangsan 这个用户，却无法插入 zhangsan。