（2010-09-08）Oracle数据一致性之事务隔离级别

   题外话：你永远也别说自己懂oracle了，即使你真认为你懂了，你也不能说你懂了。oracle是一门博大精深的技术，得花一辈子的时间去学习。学习oracle得沉得住气，专心钻研。正如晶晶小妹说的：没有必胜的秘籍，没有方程式遵循，要赢，只有全身心的投入。其实人生中很多事都是这个道理。我想我应该静心点的。

 

自认为对 oracle Concept 已经有了比较不错的了解，陆续跟一些资深的 DBA 谈到底层的运作机制跟实现原理的时候，才发现基础依旧不够扎实，故准备花点时间从头过一遍 Concept，并且将理论通过实验来逐一验证，先从数据的一致性开始。

ANSI/ISO SQL 标准（SQL92）定义了四种事务隔离级别，分别为 Read uncommitted, Read committed, Repeatable read, Serializable，此四种隔离级别是针对多用户模式下出现的三种现象所定义，此三种现象分别为 dirty read, nonrepeatable read, phantom read

dirty read（脏读）是指一个事务读取了被其他事务写入但还未提交的数据。
nonrepeatable read（不可重复读）是指一个事务再次读取其之前曾经读取过的数据时，发现数据已被其他已提交的事务修改或删除。
phantom read（幻想读）是指事务按照之前的条件重新查询时，返回的结果集中包含其他已提交事务插入的满足条件的新数据。

oracle 支持其中两种 Read committed 和 Serializable，并且额外添加一种为 Read only。

Read committed 是 oracle 默认的事务隔离级别，做个测试来看下针对三种现象的处理方式

SQL> create table SONIC_TEST (C1 varchar(5));

Table created

SQL> insert into SONIC_TEST values ('AAAA');

1 row inserted

SQL> commit;

Commit complete

事务A执行查询

SQL> select * from SONIC_TEST;

C1
-----
AAAA
        
切换事务B执行update但是没有commit

SQL> update SONIC_TEST set C1='BBBB';

1 row updated

回到事务A再次执行查询

SQL> select * from SONIC_TEST;

C1
-----
AAAA
        
显然事务隔离级别 Read committed 阻止了 dirty read，继续

此时事务B执行commit，然后再回到事务A，进行同样的查询

SQL> select * from SONIC_TEST;

C1
-----
BBBB
        
这时候数据产生了变化，也就是说 Read committed 没有阻止 nonrepeatable read，继续

事务B中进行一个insert语句

SQL> insert into SONIC_TEST values ('CCCC');

1 row inserted

SQL> commit;

Commit complete

回到事务A再次用同样语句查询

SQL> select * from SONIC_TEST;

C1
-----
BBBB
CCCC

显然 Read committed 也没有阻止 phantom read

-----------------------------------------------------我是分割线----------------------------------

我们继续对事务隔离级别 Serializable 做同样的测试:

将数据恢复到原始状态，并且事务A的隔离级别调整为 Serializable

SQL> truncate table SONIC_TEST;

Table truncated

SQL> insert into SONIC_TEST values ('AAAA');

1 row inserted

SQL> commit;

Commit complete

SQL> alter session set isolation_level=serializable;

Session altered

SQL> select * from SONIC_TEST;

C1
-----
AAAA

切换到事务B,执行update操作但是不执行commit

SQL> update SONIC_TEST set C1='BBBB';

1 row updated

回到事务A，重新查询

SQL> select * from SONIC_TEST;

C1
-----
AAAA

显然事务隔离级别 Serializable 也阻止了 dirty read，继续

此时事务B执行commit，然后再回到事务A，进行同样的查询

SQL> select * from SONIC_TEST;

C1
-----
AAAA

这时候发现虽然事务B中对数据进行了更改，并且commit，但是事务A中读取到的依旧是做修改前的数据，这个表明事务隔离级别 Read committed 阻止了 nonrepeatable read，继续

事务B中进行insert

SQL> insert into SONIC_TEST values ('CCCC');

1 row inserted

SQL> commit;

Commit complete

返回事务A再次查询

SQL> select * from SONIC_TEST;

C1
-----
AAAA

数据依旧没有变化，表明事务隔离级别 Read committed 同样阻止了 phantom read。

结合测试，我们很容易得出结论
已提交读取（read committed）允许 dirty read，不允许 read uncommitted，不允许 phantom read
串行化（rerializable）不允许 dirty read，不允许 read uncommitted，不允许 phantom read

而如果在同时支持此四种事务隔离级别的 SQL Server 中对另外两种隔离级别也做测试，会得出最后的结论如下



这个时候表 SONIC_TEST 中的数据实际上已经被变更了，但是事务A因为运行在 rerializable 隔离级别下，所以查询出来的结果依旧是修改前的，那么如果这个时候事务A如果也对该表做 update 操作会发生什么情况呢？继续测试

事务A中对表进行update操作

SQL> update SONIC_TEST set C1='DDDD';

update SONIC_TEST set C1='DDDD'

ora-08177: can't serialize access for this transaction

出现 ora-08177 错误，更新失败，那么 oracle 具体是如何来控制这样的机制的呢？且听下回分解:)