目录
介绍
首先在会话里查看一下默认的隔离级别:
select @@transaction_isolation;
结果如下:
可以看到默认隔离级别是repeatable read.
事务并发遇到的一致性问题有:
- 脏写:一个事务修改了另一个事务修改过但是还没有提交的数据。这个脏写最严重了,必须不能存在。
- 脏读:一个事务读取另一个事务修改过但是还没有提交的数据。这个脏读也相当严重。
- 不可重复读:一个事务读取数据但是还没有提交,这时候另一个事务把这个数据修改了。
- 幻读:一个事务读取了一些符合搜索条件的记录但是还没有提交,这时候另一个事务写入了一下符合刚刚那个事务搜索条件的记录。
隔离级别有:
- read uncommitted
- read committed
- repeatable read
- 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语句还是执行成功了的。
刚刚除了“脏写”,现在再说说脏写。脏写也是罪大恶极,所以所有隔离级别直接报错,也是一点机会都不给。难怪很多地方都不提脏写。