事务的隔离级别(Isolation level)

对于事务的隔离级别, SQL92有以下几种模式:

1. READ UNCOMMITTED --Dirty reads, non-repeatable reads, and phantom reads are all allowed;

2. READ COMMITTED --Dirty reads are prevented; non-repeatable reads and phantom reads are allowed.;

3. REPEATABLE READ --Dirty reads and non-repeatable reads are prevented; phantom reads are allowed;

4. SERIALIZABLE --Dirty reads, non-repeatable reads, and phantom reads are all prevented;

对于事务的隔离级别, SQL92有以下几种模式:

1. READ UNCOMMITTED --Dirty reads, non-repeatable reads, and phantom reads are all allowed;

2. READ COMMITTED --Dirty reads are prevented; non-repeatable reads and phantom reads are allowed.;

3. REPEATABLE READ --Dirty reads and non-repeatable reads are prevented; phantom reads are allowed;

4. SERIALIZABLE --Dirty reads, non-repeatable reads, and phantom reads are all prevented;

目前,Oracle支持READ COMMITTED(Default) and SERIALIZABLE,SERIALIZABLE就是在事务一级保持数据的一致;

可以通过session parameter-isolation_level 来改变隔离级别;

Alter session set isolation_level = serializable || read committed; or
set transaction isolation level serializable || read committed;

create">test@tkyte>create table test(c1 int);

Table created.

insert">test@tkyte>insert into test values(1);

1 row created.

insert">test@tkyte>insert into test values(2);

1 row created.

commit">test@tkyte>commit;

Commit complete.

Session A:set">test@tkyte>set transaction isolation level read committed;

Transaction set.

select">test@tkyte>select * from test;

C1
----------
1
2
Session B:Alter">test@tkyte>Alter session set isolation_level = serializable;

Session altered.

select">test@tkyte>select * from test;

C1
----------
1
2
Session A:update">test@tkyte>update test set c1 = 11 where c1 = 1;

1 row updated.

select">test@tkyte>select * from test;

C1
----------
11
2

commit">test@tkyte>commit;

Commit complete.

Session B:update">test@tkyte>update test set c1 = 22 where c1 = 2;

1 row updated.

select">test@tkyte>select * from test;

C1
----------
1
22update">test@tkyte>update test set c1 = 33 where c1 = 1;
update test set c1 = 33 where c1 = 1
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

commit">test@tkyte>commit;

Commit complete.

select">test@tkyte>select * from test;

C1
----------
11
22

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/45259/viewspace-134861/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/45259/viewspace-134861/