MySQL与Oracle中一致性读的相同点与差异

一、问题的提出

        一个操作执行select * from tb_trade_record where product_type = ‘BOOK’语句，假设表tb_trade_record初始时有1000000行数据，整个select过程持续10分钟。会话1在8：00发起这个select操作(这时，满足product_type = ‘BOOK’的记录有10000条)，8：02时，会话2向tb_trade_record表中插入了2条数据，且product_type = ‘BOOK’，并且已提交，会话1在8：10读取完毕。那么，会话1读出的记录是10000条，还是10002条？

 

二、什么是一致性读

       关于上述问题的解答如下：
             答案是：10000条，因为会话1是在8:00发起的，select操作应该从8：00那一刻的数据库快照中读取tb_trade_record表满足条件满足 product_type = ‘BOOK’的数据，而不论读取操作花多少时间，期间数据发生什么变化。因此不是读取结束8：10时的数据，更不是其他中间时刻的数据。所以，上述问题的答案是10000。
      读取操作发起的那一刻是8：00，整个读取操作应该都是从8：00那一刻的数据库快照中读取数据，这就是一致性读或者快照读。

三、Mysql与Oracle一致性读的实现

      1.Mysql一致性读的实现（InnoDB存储引擎）

         在每一行记录的后面保存两个隐藏的列实现一致性读。一列保存行的创建时间或者说是系统版本号，另外一列保存的是该行过期（删除）时的时间或者系统版本号。每次开启一个新事务时，系统版本号就会递增，事务开启时刻的系统版本号就是该事务的版本号。
       针对SELECT、INSERT、DELETE、UPDATE操作的具体实施如下：

       SELECT：
             InnoDB会根据以下条件检查每行记录：

       a.查找版本号小于等于当前事务的系统版本号，这样确保当前事务读取的行是在当前事务开启前已经存在，或者当前事务插入或者修改过的数据。

       b.行删除时的版本要么没有，要么大于当前事务的版本号（不能等于，等于表示是当前事务删除的），这样确保当前事务读取到的行在当前事务开始之前还没有被删除。
        INSERT：
              新插入的行将当前事务的系统版本号作为该行的版本号。
        DELETE：
              删除的行保存当前事务的系统版本号作为该行的版本号。
        UPDATE：
              update时插入一行新记录，保存当前事务的系统版本号作为该行的版本号，同时保存当前事务的系统版本号作为原来行的过期时的系统版本号。

        保存两个隐藏的列数据，使得大多数读操作都可以不用加锁，这样读操作简单，并发性能好，不用锁表，保证只会读取到符合要求的行。

          2.Oracle一致性读的实现

          a.块中记录SCN（system change number），scn是一个只增不减的数字，每行记录就存储在块中。当块的内容改变，scn就会增加。

          b.块提供ITL（interested transaction list）,如果更新了某条记录，该更新操作对应的事务就会被写进该记录所在块的itl中，如果事务没有提交或者回滚了，则块中就存在活动事务，数据库可识别出这种情况。

      Oracle基于scn和itl来实现一致性读。

四、相同点与差异

       1. 相同点：

        Mysql基于每行数据的创建时系统版本号和删除时系统版本号来实现一致性读；Oracle基于scn和itl来实现一致性读。虽然名称及实现方式不一样，但是它们都是实现MVCC（Multi-Version Concurrency Control多版本并发控制）。MVCC在很多情况下避免了加锁操作，实现非阻塞的读，提高并发操作的性能。它的实现是通过保存数据在某个时间点的快照来实现，不管读取操作执行多长时间，每个事务看到的数据都是一致的。

        a.都是将一行数据与另外一个或者两个数据关联起来，Mysql是在一行记录中增加两个隐藏列，一个是创建时版本号，一个是删除时版本号；Oracle通过在行记录所在的逻辑块（oracle的数据文件由若干个tablespace组成，一个tablespace由若干个segment组成，一个segment由若干个extent组成，一个extent由若干个block组成，数据表的每行记录就是存储在block里面）中，在这个块中除了保存数据记录外，还保存和这个记录相关的scn和itl，这样一行数据记录就和scn、itl关联起来。

        b.都是通过比较一个数字来进行是否需要读取该行数据的。Mysql通过比较创建时系统版本号来判断，Oracle通过比较scn来判断。如果读取期间发生了删除或者更新，Mysql使用删除时系统版本号找到之前的记录，oracle通过itl来在undo日志里面找到前记录。

        c.都需要额外的存储空间来存储额外的字段。

        2. 差异：

          a. 系统设计上的差异：Mysql是直接在一行数据内记录版本号，Oracle在数据内容外记录，然后再与内容关联起来。

         b.对于查找期间，删除或者更新记录的前记录的查找凡是不同。更新或者删除时，Mysql是新增一条记录，设置原纪录的删除版本号。Oracle删除时是在undo日志里面记录反向的update操作或者insert操作，因此查找更新或者删除的记录的方式不同

五、总结

        Mysql和Oracle都实现了基于MVCC的一致性读，实现方式不同，但目的相似，替代锁操作，提高了并发操作性能。
        Mysql（InnoDB存储引擎）实现MVCC，但只能在REPEATABLE READ（解决不可重复读） 和READ COMMITTED（解决脏读）两个隔离级别下工作。
         Oracle是在其逻辑体系结构（tablespace、segment、extent、block）的基础上实现的一致性读。

六、参考

      1. 《高性能MySQL》，电子工业出版社
       2. 《收货，不止Oracle》，电子工业出版社