脏读(Drity Read):事务T1修改了一行数据,事务T2在事务T1提交之前读到了该行数据。
不可重复读(Non-repeatable read): 事务T1读取了一行数据。 事务T2接着修改或者删除了改行数据,当T1再次读取同一行数据的时候,读到的数据时修改之后的或者发现已经被删除。
幻读(Phantom Read): 事务T1读取了满足某条件的一个数据集,事务T2插入了一行或者多行数据满足了T1的选择条件,导致事务T1再次使用同样的选择条件读取的时候,得到了比第一次读取更多的数据集。
MySQL/INNODB支持ANSI SQL STANDARD规定的四种隔离级别(READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE).本篇文章重点关注一下MySQL REPEATABLE READ隔离级别与其他数据实现方式上的不同之处。
下面看一下MySQL在REPEATABLE READ 隔离级别下的工作方式:
开启两个session。
接下来看一下另外一个开源数据库PostgreSQL在REPEATABLE READ 隔离级别下的工作方式:
同样测试了SQL SERVER,得到的结果与PostgreSQL是一致的。
从上面的执行情况我们可以看到MySQL与PostgreSQL两者工作方式上有所不同。MySQL在执行UPDATE语句的时候对于session2的INSERT语句是可以看到的,也就是说发生了幻读。那么MySQL在隔离级别为REPEATABLE READ的情况下,表现出来的幻读现象是否属于一个BUG呢?曾经有人在2013年给官方提过一个关于该现象的BUG,请参考 从BUG页面的注释可以了解到,该现象是与MySQL对REPATABLE READ隔离级别的实现方式有关。而这种幻读现象对于REPATABLE READ隔离级别也是正确的方式。请看wikipedia上对于REPEATABLE READ的描述:
另外我们接着看一下ANSI SQL STANDARD对于各种隔离级别发生幻读的规定:
我们从wikipedia以及ANSI SQL STANDARD可以看到对于REPEATABLE READ隔离级别下是允许出现幻读现象的。
接下来我们看一下UPDATE的执行过程。对于UPDATE操作执行流程的简单描述如下:
根据WHERE条件扫描一条记录(row_search_for_mysql)
更新当前获取的记录(ha_innobase::update_row)
重新将更新后的记录写入InnoDB存储引擎(row_upd_step)
那么我们按照上面的这个流程看一下源码方面的执行过程:
从上面的代码我们可以看到,对于UPDATE操作更新的记录包含幻读读取到的已提交事务的最新记录。那么接下来看为什么UPDATE之后的SELECT语句对于UPDATE之后的所有语句都可见了? 原因是前面的UPDATE语句执行之后,会将当前记录上存储的事务信息更新为当前的事务,而当前事务所做的任何更新,对本事务所有SELECT查询都变的可见,因此最后输出的结果是UPDATE执行后更新的所有记录。