深入理解oracle的事务隔离性

转： http://www.bkjia.com/oracle/925189.html

深入理解oracle的事务隔离性

在Oracle关系数据库中，我们先来看下面这个问题：
A事务：select <cols> from T where id > 10 and id < 10000;
B事务：update T set id = 45000 where id = 4501
两个事务按下面的顺序执行：
A事务：|--------------------------------|commit
B事务： |-------------|commit
也就是A事务先开始执行，过一段时间B事务再开始执行，但是B事务先执行完并commit提交了，A事务又过了一段时间才完成。那么问题来了，在这种情况下，问A事务能不能取得正确的结果，两个事务之间会不会有干扰，怎么干扰？
这是一个典型的关系型数据库事务的隔离性问题，而且，针对不同的数据库（存储引擎），可能会有不同的表现。

根据上面的描述，以oracle为例，它的缺省数据库隔离级别是读已提交（read-committed），事务A持有一个读锁（瞬间共享读锁），B持有一个排它写锁。
Read Committed读已提交的官方定义是，通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。
按照读已提交的定义，似乎按上题的条件，A，B两个事务都能够正确完成并commit提交。

但关系数据库厂商，它们的产品往往不会完完全全的按照规范来实现，总会附加一些自己特有的东西在里面。那么我们接下来详细分析一下，oracle是怎样处理的，SQL语句执行的内部过程相当复杂，大概比较显式和通俗易懂的是，先运行执行计划，然后执行SQL优化等策略，接着可能根据关键字，进行加锁处理，上下文切换等操作，比如select语句就会加一个读锁。

在执行DML语句时，Oracle会给每一行增加一个sn序列号，比如select <cols> from T where id > 10 and id < 10000;这条语句，查询出将近1w条数据，在执行扫描的时候，发现符合条件的行就会加一个sn（实际操作时，可能是和内存中某个sn数值关联起来），这个sn序列号实际上被当做乐观锁使用。
那么可能出现下面的情况，事务A的select语句还没有执行完，当执行到2000条的时候，B开始了一个update T set id = 45000 where id = 4501的事务，由于在oracle中，写锁的级别高于读锁，因此这时候B事务的update语句取得写锁，成功执行完并commit，交出写锁。
当先开始的select语句执行到4501时，如果此时B事务已经commit，那么A事务会接着执行下去，成功commit，反之，当A事务执行到4501行时，B事务还未commit，那么二者的锁在4501这条数据发生冲突,这时整个A事务就会出错。

这里插一句，对于DML的select语句来说，只具有读一致性，所以失败了仅仅是报错放弃，不会回滚。

然而，上面的描述却有一个知识缺失点，就是所谓的MVCC(Multi-Version Concurrency Control)---基于多版本的并发控制协议 (注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。MVCC最大的好处是：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，现阶段几乎所有的RDBMS，都支持了MVCC。

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁(一种就是上面提及的sn序列号方式的乐观锁)，保证其他事务不会再并发修改这条记录。

在oracle里，undo就是所谓的快照。如果undo够大的话，A事务的select返回的是没有执行update的语句前的数据；如果undo不够大，A事务的select会直接报错没有返回值，因为是隐式提交，所以并不会rollback回滚。
这就是oracle的经典错误ORA-01555快照过旧。

再回到一开始的原题目中，当执行事务A的select语句时，并没有明确指出是快照读还是当前读。因此，为严密起见，我们最终的结果是：
1.如果A事务执行的是快照读，如果undo够大的话，A,B事务都能够正确commit提交，A事务的select返回的是没有执行update的语句前的数据；如果undo不够大，B事务能够正确commit，A事务的select会直接报错没有返回值，事实上数据库的读写事务，绝大多数都属于这种情况；
2.如果A事务执行的是当前读，那么当A事务的select读操作和B事务的update写操作没有冲突时（不会同时读写4501那一行），两个事务都能正确执行；反之，A事务是有可能出错的。并不是A事务只要先执行，两个事务就一定能成功commit提交。

快照读：select 

当前读：select * from xx for update; update ; delete;