再谈mysql repeatable事务级别适用场景与SQL Server的snapshot隔离级别



我们先看看业界标准

ANSI/ISO SQL标准定义了4中事务隔离级别：未提交读（read uncommitted），提交读（read committed），重复读（repeatable read），串行读（serializable）。

对于不同的事务，采用不同的隔离级别分别有不同的结果。不同的隔离级别有不同的现象。主要有下面3种现在：

1、脏读（dirty read）：一个事务可以读取另一个尚未提交事务的修改数据。

2、非重复读（nonrepeatable read）：在同一个事务中，同一个查询在T1时间读取某一行，在T2时间重新读取这一行时候，这一行的数据已经发生修改，可能被更新了（update），也可能被删除了（delete）。

3、幻像读（phantom read）：在同一事务中，同一查询多次进行时候，由于其他插入操作（insert）的事务提交，导致每次返回不同的结果集。

不同的隔离级别有不同的现象，并有不同的锁定/并发机制，隔离级别越高，数据库的并发性就越差，4种事务隔离级别分别表现的现象如下表：

隔离级别脏读非重复读幻像读read uncommitted允许允许允许read committed 允许允许repeatable read  允许serializable  

标准的repeatable read是允许幻读的,因为这一级别只在读取过的纪录上加共享锁,防止别其它事务修改,但是不禁止其它人插入新数据.

比如在SQL Server RR事务中:

select * from t where id in (1,2)

如果两条纪录都存在,那么他们都会被锁定,保证后续不会被他人修改,但是如果某条纪录不存在,RR是不会禁止其它人插入对应id纪录的.

而且在其它人插入新数据后,SQL Server随后再执行select也可以获得新插入的纪录.RR保证读到的都不会被修改,但是没读到的不做任何保证.

而mysql的RR级别,

All consistent reads within the same transaction read the snapshot established by the first read

当第一次读的时候,他们就被固化(快照)了,自己再也无法发现他们的任何变化,但是却不禁止其它人修改删除插入.

其它人修改删除插入也都不可见.可以说mysql的RR直接连幻读也避免了.

这种级别适合做什么呢? 其实不太适合一般的联机交易类似,而更适合统计查询报表类需求.

比如,我们需要根据一些明细,统计数据,并将结果保存到另外几张表中,也就是无法通过一个SQL完成,使用mysql RR就比较合适

begn work

insert into tj1 select * from t where ....

insert into tj2 select * from t where ....

commit

如果使用read commit级别,tj1插入后,t可能被修改新增删除,那么随后的tj2结果就可能与tj1的基础数据不一致.

但是使用RR级别就不存在这个问题.因此第一次读就被固化了.即便t随后被清空,也不影响tj2的结果.

如果没有这种机制,比如要在readcommit级别下完成这样的任务,则只能通过临时表,第一次先把所有需要统计的数据保存到临时表,

随后统计全部在临时表进行.这显然比较麻烦.

但是mysql RR面对报表需要还是有缺陷设想:

begin work

insert into tj1 select * from t where ....

insert into tj2 select * from t where ....

insert into tj2 select * from t2 where ...

commit

也就是还要统计另外一张表t2,假如t2与t1有关联,那么读t和读t2现在不是在同一个时刻,这两次读可能数据已经不一致了.

为此SQL Server还有一种隔离级别 SNAPSHOT.

这一级别看名字就知道也是用了快照,但与mysql RR不同的是,SQL Server的快照是在事务开始时进行,而非第一次读的时候,

上述例子中begin work一执行,我们就可假想,此时SQL Server就为这个事务对整个库做了快照,所以后续无论读什么,

都是事务开始时刻一致的数据,因此统计再不会出现数据不一致的问题了.

显然实现此功能是需要代价的,必须作出额外配置:

必须将 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON

本来SQLServer事务开始后,都可随意再调整事务隔离级别,但是显然其他级别是无法切换到snapshot级别的,

因为快照根本没有建立.

这样的需求,即便是mysql和SQL Server的serializable级别也无法做到,因为两者串行级别是类似的,

将所有select涉及到的范围键全部加共享锁,也就是阻止新纪录的插入,这样彻底避免了幻读,但是先后两次读的不同数据未必是一致的.

因为两者都无法阻止其他人修改删除它们还未读的数据.

但是oracle的serializable级别又很特殊,因为他没有RR级别,serializable级别也很常用,

串行化隔离的事务只能看到事务执行前就已经提交的数据

这实际类似SQLServer的snapshot级别!此时mysql的确显得有些弱了.

总结一下,对于大部分联机交易型应用,几种数据库最适合的隔离级别都是readcommit,不光提供了最大并发可能,也避免很多潜在歧义.

其它隔离级别也不都是摆摆样子的,也有他们适合的场景.但是很多时候我们可以通过在readcommit级别上自己控制锁类型来调整隔离度.

所以,建议mysql将默认隔离级别修改为readcommit.