详细验证MYSQL里的隔离级别

目录

介绍

准备工作

read uncommitted

脏写

脏读

不可重复读

幻读

serializable

脏写

脏读

不可重复读

幻读

read uncommitted

脏写

脏读

不可重复读

幻读

repeatable read

脏写

脏读

不可重复读

幻读

总结

---

介绍

首先在会话里查看一下默认的隔离级别：

select @@transaction_isolation;

结果如下：

可以看到默认隔离级别是repeatable read.

事务并发遇到的一致性问题有：

1. 脏写：一个事务修改了另一个事务修改过但是还没有提交的数据。这个脏写最严重了，必须不能存在。
2. 脏读：一个事务读取另一个事务修改过但是还没有提交的数据。这个脏读也相当严重。
3. 不可重复读：一个事务读取数据但是还没有提交，这时候另一个事务把这个数据修改了。
4. 幻读：一个事务读取了一些符合搜索条件的记录但是还没有提交，这时候另一个事务写入了一下符合刚刚那个事务搜索条件的记录。

隔离级别有：

1. read uncommitted
2. read committed
3. repeatable read
4. serializable

下面来验证这四个隔离级别。

准备工作

在当前会话里建一个数据库：

create database test_transactionIsolation;

建表，简单点：

create table test_tr_i( id int, name varchar(10));

插入数据：

insert into test_tr_i values(1,'zhang san'),(2,'li si');

查看一下有没有错误：

select * from test_tr_i;

结果：

 

本文把修改隔离级别以及创建数据库和表的会话成为会话1，验证隔离级别的会话称为会话2、会话3、会话4……

read uncommitted

首先修改一下会话的隔离级别，这里我使用全局范围内的隔离级别：

set global transaction isolation level read uncommitted;

在会话1下查看一下隔离级别是不是改了：

结果和预料的不太一样啊。

原来是执行完修改隔离级别的语句后，当前会话的隔离级别是不变的，只有之后开启的会话的隔离级别才会改变。

那么，我再开一个会话2看看隔离级别是什么：

可以看到这个会话2的隔离级别改了。

再开一个会话3，隔离级别也是改了的。

脏写

在会话2里：

begin;
update test_tr_i set name='lao zhang' where id=1;

开启事务，修改数据，但是没有提交。

此时，打开会话3：

begin;
update test_tr_i set name='lao zhang1' where id=1;

试图再次修改会话2里的事务修改过的数据。但是：

可以看到，解决了脏写的问题。

脏读

数据还是恢复成：

注意：以后每次验证都恢复成这个样子。

在会话2里：

begin;
update test_tr_i set name='lao zhang' where id=1;

开启事务，修改数据，但是没有提交。

那么在会话3里：

begin;
select * from test_tr_i where id=1;

会话3的查询结果：

结果是会话2里改变的数据。

那么会话2执行下面操作：

rollback;

也就是会话2里的事务回滚了。

会话3再：

select * from test_tr_i where id=1;

会话3的结果：

又回到了最原始的数据。脏读还是出现了，也就是会话3里的事务之前读了一个不知道哪里冒出来的数据。

不可重复读

恢复数据。

会话2：

begin;
select * from test_tr_i where id=1;

会话2结果：

 会话3：

begin;
update test_tr_i set name='lao zhang' where id=1;
commit;

也就是会话2还没有提交事务，会话3里的事务改了会话2里的事务读取的数据。

此时，会话2：

select * from test_tr_i where id=1;

会话2的事务再读一次的结果：

和刚刚读的不一样了，不可重复读出现了。

幻读

恢复数据。

会话2：

begin;
select * from test_tr_i where name like 'zhang%';

结果：

会话3：

begin;
insert into test_tr_i values(3,'zhang fei');
commit;

会话3的事务再会话2的事务还没有提交的时候又加了一条数据，这条数据符合刚刚会话2里的事务读取的要求。

此时，会话2：

select * from test_tr_i where name like 'zhang%';

结果：

多了一条，幻读了哟。

综合，read uncommitted除了脏写，其他的都可能发生。

 

 

serializable

先再会话1设置隔离级别。

当然会话2和会话3的隔离级别不变啦。上面说到使用global关键字的话，已经存在的会话的隔离级别不变。

那么久新建会话4和会话5.

脏写

在会话4里：

begin;
update test_tr_i set name='lao zhang' where id=1;

开启事务，修改数据，但是没有提交。

此时，打开会话5：

begin;
update test_tr_i set name='lao zhang1' where id=1;

试图再次修改会话4里的事务修改过的数据。但是：

 可以看到，解决了脏写的问题。

脏读

在会话4里：

begin;
update test_tr_i set name='lao zhang' where id=1;

开启事务，修改数据，但是没有提交。

那么在会话5里：

begin;
select * from test_tr_i where id=1;

会话5的查询结果：

说明不能脏读。

不可重复读

恢复数据。

会话4：

begin;
select * from test_tr_i where id=1;

会话4结果：

 会话5：

begin;
update test_tr_i set name='lao zhang' where id=1;

也就是会话4还没有提交事务，会话5里的事务改了会话4里的事务读取的数据。

此时，会话5：

说明会话4里的事务读取了一条记录，会话5里的事务不能修改该记录，即避免了不可重复读。

幻读

恢复数据。

会话4：

begin;
select * from test_tr_i where name like 'zhang%';

结果：

会话5：

begin;
insert into test_tr_i values(3,'zhang fei');

会话5的事务再会话4的事务还没有提交的时候又加了一条数据，这条数据符合刚刚会话4里的事务读取的要求。

此时会话5显示的结果：

可以看到，无法插入，避免了幻读。

综合，serializable避免了脏写、脏读、不可重复读、幻读。

read uncommitted

会话1设置隔离级别

开启新会话，会话6和会话7.

脏写

在会话6里：

begin;
update test_tr_i set name='lao zhang' where id=1;

开启事务，修改数据，但是没有提交。

此时，打开会话7：

begin;
update test_tr_i set name='lao zhang1' where id=1;

试图再次修改会话6里的事务修改过的数据。但是：

可以看到，解决了脏写的问题。

脏读

恢复数据。

在会话6里：

begin;
update test_tr_i set name='lao zhang' where id=1;

开启事务，修改数据，但是没有提交。

那么在会话7里：

begin;
select * from test_tr_i where id=1;

会话7的查询结果：

结果是会话6里改变之前的数据，并没有读到会话6里改变后的数据。

说明避免了脏读。

不可重复读

恢复数据。

会话6：

begin;
select * from test_tr_i where id=1;

会话6结果：

 会话7：

begin;
update test_tr_i set name='lao zhang' where id=1;
commit;

也就是会话6还没有提交事务，会话7里的事务改了会话6里的事务读取的数据。

此时，会话6：

select * from test_tr_i where id=1;

会话6的事务再读一次的结果：

 和刚刚读的不一样了，不可重复读出现了。

幻读

恢复数据。

会话6：

begin;
select * from test_tr_i where name like 'zhang%';

结果：

会话7：

begin;
insert into test_tr_i values(3,'zhang fei');
commit;

会话7的事务再会话6的事务还没有提交的时候又加了一条数据，这条数据符合刚刚会话6里的事务读取的要求。

此时会话7显示的结果：

会话6：

select * from test_tr_i where name like 'zhang%';

结果：

记录多了一条。说明幻读出现了。

综合，read committed避免了脏写、脏读，但是会出现不可重复读、幻读。

repeatable read

会话1修改隔离级别。新建会话8、会话9。

脏写

在会话8里：

begin;
update test_tr_i set name='lao zhang' where id=1;

开启事务，修改数据，但是没有提交。

此时，打开会话9：

begin;
update test_tr_i set name='lao zhang1' where id=1;

试图再次修改会话8里的事务修改过的数据。但是：

 可以看到，解决了脏写的问题。

脏读

在会话8里：

begin;
update test_tr_i set name='lao zhang' where id=1;

开启事务，修改数据，但是没有提交。

那么在会话9里：

begin;
select * from test_tr_i where id=1;

会话9的查询结果：

结果是会话8里改变之前的数据，并没有读到会话8里改变后的数据。

说明避免了脏读。

不可重复读

恢复数据。

会话8：

begin;
select * from test_tr_i where id=1;

会话8结果：

 会话9：

begin;
update test_tr_i set name='lao zhang' where id=1;
commit;

也就是会话8还没有提交事务，会话9里的事务改了会话8里的事务读取的数据。

此时，会话8：

select * from test_tr_i where id=1;

会话8的事务再读一次的结果：

会话1执行和会话8里的事务查询条件一样的语句：

select * from test_tr_i where id=1;

会话1的结果：

会话1里的结果变了。但是会话8里的事务还没有提交，相同的查询语句的查询结果是一样的，说明不可重复读避免了。

幻读

恢复数据。

会话8：

begin;
select * from test_tr_i where name like 'zhang%';

结果：

会话9：

begin;
insert into test_tr_i values(3,'zhang fei');
commit;

会话9的事务再会话8的事务还没有提交的时候又加了一条数据，这条数据符合刚刚会话8里的事务读取的要求。

此时会话9显示的结果：

会话8：

select * from test_tr_i where name like 'zhang%';

结果：

记录还是只有一条。说明幻读没有出现。

问题出现了，不是都说repeatable read会出现幻读吗。

上面的结果说明在实际中，repeatable read隔离级别下可以很多时候避免幻读？

对的，就是这样，这和MVCC（多版本并发控制）有关。

综合，repeatable read可以帮忙脏写，脏读，不可重复读，大部分幻读。

总结

在各个隔离级别下，这四种现象发生的可能性。

隔离级别	脏写	脏读	不可重复读	幻读
read uncommitted	不可能	可能	可能	可能
read committed	不可能	不可能	可能	可能
repeatable read	不可能	不可能	不可能	不太可能
serializable	不可能	不可能	不可能	不可能

可以发现，serializable隔离级别下一个事务不管读还是写，若还没有提交。这时候若另一个事务想读或写，直接报错，一点机会都不给。真的是很严格的一个隔离级别的。其他隔离级别（除了脏写）虽然不能避免一些现象，但是SQL语句还是执行成功了的。

刚刚除了“脏写”，现在再说说脏写。脏写也是罪大恶极，所以所有隔离级别直接报错，也是一点机会都不给。难怪很多地方都不提脏写。