关于ACID中的I即隔离性(Isolation),
配合姊妹篇事务的四大基本要素(即ACID原则)效果更佳

亲测体验关系型数据库事务的隔离级别,演示了mysql默认隔离级别可重复读情形下, 幻读的情况




这篇文章讲解十分清晰

例子很赞

从上到下,隔离级别逐渐增强



第一级隔离级别: 未提交读: Read uncommitted


另一个事务修改了数据,但尚未提交,而本事务中的SELECT会读到这些未被提交的数据(脏读)( 隔离级别最低,并发性能高 )

事务A用来修改某条记录,事务B包含查询该记录的操作;

(如A是修改余额,B是查询当前余额,进行判断,如果大于某个值,则再进行查询,将结果写入另外的一张日志表)

当事务A修改记录但还未执行,事务B执行读取到了事务A修改后的记录;

这就是

脏读(Dirty Read):

当前事务读到的数据, 是别的事务想要修改但没有修改成功的数据


亲测体验:

参考

查看全局的事务隔离级别:

show global variables like '%tx_isolation%';

(查看当前session的事务隔离级别:

show session variables like '%tx_isolation%';

在此不用 )


设置全局的事务隔离级别


set global isolation level 四种隔离级别之一;

即其全集为:

1
2
3
4
set global transaction isolation level READ UNCOMMITTED;
set global transaction isolation level READ COMMITTED;
set global transaction isolation level REPEATABLE READ;
set global transaction isolation level SERIALIZABLE;

在此将全局的隔离级别,由mysql默认的REPEATABLE READ修改为READ UNCOMMITTED

1
2
3
set global transaction isolation level READ COMMITTED;

show global variables like '%tx_isolation%';

注意: 执行命令后,并不会对当前session(窗口)生效。


当前表的内容为:


新开两个窗口,模拟两个事务:

1
2
3
4
5
6
7
8
9
set global transaction isolation level READ UNCOMMITTED;

show global variables like '%tx_isolation%';

set autocommit = 0;

start transaction;

insert into historical_figure (`sex`,`name`,`is_beauty`) values ("男","范蠡","0")

1
2
3
4
5
6
7
8
9
10
set global transaction isolation level READ UNCOMMITTED;

show global variables like '%tx_isolation%';


-- begin;

select * from historical_figure;

-- commit;


此时表为:(等同于事务2的select * 操作,读到了一条可能并没有成功提交的”脏数据”)


将事务1关闭,则变为:


( 如果事务1执行的是update操作

关闭事务1后,依然被未提交的事务1进行了修改..可以只以insert为例记忆即可 )




第二级隔离级别: 已提交读: Read committed

(绝大多数数据库,包括Sql Server/Oracle的默认隔离级别)


将上例中的全局隔离级别改为READ COMMITTED,重复之前操作,发现在已提交读隔离级别下,脏读现象被杜绝


1
2
3
4
5
6
7
8
9
10
11
set global transaction isolation level READ COMMITTED;

show global variables like '%tx_isolation%';

set autocommit = 0;

start transaction;

insert into historical_figure (`sex`,`name`,`is_beauty`) values ("女","赵飞燕","1");

-- commit
1
2
3
4
5
6
7
8
9
10
set global transaction isolation level READ COMMITTED;

show global variables like '%tx_isolation%';


-- begin;

select * from historical_figure;

-- commit;

但会出现不可重复读的问题:


在事务1里,前后两次相同的SELECT会读到不同的结果(即不重复读),因为有一个干扰事务2,在事务2提交前和提交后,事务1得到的两次查询结果是不同的。

事务B要等事务A提交后才能读取数据,这样就能解决上面的脏读问题,但引入了新的问题:

事务B执行,查询出该条记录(第一次),进行判断,符合条件;这时事务A执行,修改(扣减)余额,这个过程中B想再次查询该记录(第二次),必须等A执行完才能再读;等A执行结束B再去读取时,发现查出来的数据与第一次竟然不同

“这就是已提交读,若有事务对数据进行更新(UPDATE)操作时,读操作事务要等待这个更新操作事务提交后才能读取数据,这样可以解决脏读问题;

但却可能出现”一个事务范围内“的两次相同的查询却返回了不同数据,这带来了新问题,也就是

不可重复读(NonRepeatable Read):


考虑如下情况:

此时查到的数据为6条.

但当insert的事务 commit后,查到了7条数据!

即同一个事务,两次查询得到的结果不一致.




第三级隔离级别: 可重复读: Repeatable read

(MySql的默认隔离级别)


将上例中的全局隔离级别改为REPEATABLE READ;,重复之前操作,发现在可重复读隔离级别下,不可重复读现象被杜绝



提交有insert的事务后,发现查询事务得到的结果,和insert事务提交之前结果一致.

在”查询事务”未commit前,不管”insert事务”是否提交,得到的内容都是一致的,不管执行几次查询,结果都是最开始查询的结果.

当”查询事务”提交后,再次查询,才得到insert之后最新的数据..


但会出现幻读的问题:

在同一个事务里,SELECT的结果是事务开始时时间点的状态,因此,同样的SELECT操作读到的结果会是一致的。但是,会有幻读现象。

可重复读,即在开始读取数据(事务开启)时,不再允许修改(update)操作;

即事务B开启后,不允许其他事务(此处即A)的UPDATE操作;

可重复读可以解决不可重复读问题;

“不可重复读对应的是修改,即update操作。但是可能还会有幻读问题。

因为幻读问题对应的是插入insert操作(或删除操作),而非update操作。

**解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表**

幻读(Phantom Read):


事务A第一次查询取到的数据,比后来读取到数据条目少或者增加,仅针对数据的删与增



如事务1先查询id=5的数据有没有,如果没有,进行insert操作,但是没有commit; 这时有一个(干扰)事务2,insert了id=5的数据,且进行了提交; 而后,事务1进行提交,却发现报错,说id=5的key重复了…事务1就以为出现了”幻觉”:不对啊,我明明查询过,查询id=5返回的是空啊..


该隔离级别下,会有幻读现象的发生,参见亲测体验关系型数据库事务的隔离级别,有详尽实例及解述




第四级隔离级别: 串行化: Serializable


读操作会隐式获取共享锁,可以保证不同事务间的互斥(锁表)

在开始读取数据(事务开启)时,不再允许修改(update)操作,但还可以进行insert和delete操作;

假设事务C需要扫描全表,进行判断,将其中符合条件的写入另外一张表;

开启事务,第一次查询时,扫到全表有100条记录;这时恰巧有一个insert操作,新插入了一条数据,该次事务C执行到第二次查询时,发现成了101条记录,似乎出现了幻觉,这就是幻读;

Serializable 是最高的事务隔离级别,在该级别下,事务串行化顺序执行,可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下,比较耗数据库性能,一般不使用。


参考亲测体验关系型数据库事务的隔离级别该文中构造的幻读实例,当将隔离级别改为SERIALIZABLE时,将杜绝幻读现象的存在,但一般为效率起见,很少用此隔离级别,在此不做演示