（2010-06-15）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 具体是如何来控制这样的机制的呢？且听下回分解:)

Oracle检索数据一致性与事务恢复：

Oracle为了保证用户检索数据的一致性， 通过UNDO记录，当用户检索数据库数据时，Oracle总是使用户只能看到被提交过的数据或特定时间点的数据(select语句时间点)，UNDO记录会被存放到回滚段中，假如该数据未提交，用户检索数据时，都是从UNDO记录中取得的。(如下图：)
1. ORACLE检索数据一致性
先打开一个SecureCRT.(第一个session)
先建一个表
SQL> create table c(a int);
Table created.
SQL> alter table c add b number;
Table altered.
SQL> desc c
Name Null? Type
----------------------------------------- -------- --------------------------------------------
A NUMBER(38)
B NUMBER
表中插入数据并提交
SQL> insert into c values(1,2);
1 row created.
SQL> insert into c values(3,4);
1 row created.
SQL> select * from c;
A B
---------- -----------------------------
1 2
3 4
SQL> commit;
Commit complete.
再打开一个SecureCRT.(第二个session)
查询
SQL> select * from c;
A B
---------- --------------------------
1 2
3 4
第一个session更改表中的数据但不提交
SQL> update c set b=10 where a=1;
1 row updated.
第二个session查询(修改但没有提交检索的是UNDO中的数据)
SQL> select * from c;
A B
---------- --------------------------
1 2
3 4
第一个session提交
SQL> commit;
Commit complete.
第二个会话查询(可见只有提交后才能检索到数据段的数据)
SQL> select * from c;
A B
---------- -------------------------
110
3 4
结论：如果用户修改数据但没有提交，其它用户检索的都是UNDO段的数据，这样就保证了数据的一致性
2.回滚数据(事务恢复)
1.当用户updata数据但还没有提交
SQL> select * from c;
A B
---------- -----------------------------
1 10
3 4
SQL> update c set b=2 where a=1;
SQL> select * from c;
A B
---------- -----------------------------
1 2
3 4
这时用户突然后悔了，想恢复到原来的状态
SQL> rollback;
Rollback complete.
SQL> commit;
SQL> select * from c;
A B
---------- -----------------------
110
3 4
可见当用户用命今rollback还能回滚到初始状态。
2.当用户updata数据且已提交
当用户updata数据且已提交后，可以根据SCN记录把数据还源。
先查看原始数据
SQL> select * from c;
A B
---------- ----------
110
3 4
找到SCN
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
693636
现在删除表中的数据并提交
SQL> delete from c;
2 rows deleted.
SQL> commit;
Commit complete.
查询(现在表中已没有数据了)
SQL> select * from c;
no rows selected
检索特定SCN的数据
SQL> select * from c as of scn 693636;
A B
---------- ----------
110
3 4
恢复数据
SQL> insert into c select * from c as of scn 693636;
2 rows created.
SQL> commit;
Commit complete.
现在再查询
SQL> select * from c;
A B
---------- ----------------------
110
3 4
可见可以根据SCN恢复到某一检查点的数据，如果把SCN转换成时间，，就可以把数据恢复到某一时间点。