不完全归纳设置isolation level为transaction level的两点影响

不完全归纳设置isolation level为transaction level的两点影响：

1. 在设置了isolation level为transaction level的session执行两次同一个query的中间，

即使有在其他session更新并提交了该query相关表的数据，

这两次query所查出的结果仍然相同，

直到当前session发出commit或rollback完成一个transaction后，

在其他session提交了的修改才会在当前session中反映出来。

2. 如果session A更新某表的一行且未提交，

设置为transaction level的session B更新同一行时会先等待，

当session A提交后，session B的更新会失败，报ORA-08177错误：

SQL> update dept set loc='CCC' where deptno=50;

update dept set loc='CCC' where deptno=50
       *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

更改当前session的isolation_level：

alter session set isolation_level=serializable;

查看当前session id：select distinct sid from v$mystat;

REF:

1. 

<<Expert Oracle Database 11g Administration>> Page 345, Transaction- and Statement-Level Consistency

2.

ISOLATION_LEVEL

Syntax:

ISOLATION_LEVEL = {SERIALIZABLE | READ COMMITTED} 

The ISOLATION_LEVEL parameter specifies how transactions containing database modifications are handled. ISOLATION_LEVEL is a session parameter only, not an initialization parameter.

• SERIALIZABLE indicates that transactions in the session use the serializable transaction isolation mode as specified in SQL92. That is, if a serializable transaction attempts to execute a DML statement that updates rows currently being updated by another uncommitted transaction at the start of the serializable transaction, then the DML statement fails. A serializable transaction can see its own updates.

• READ COMMITTED indicates that transactions in the session will use the default Oracle Database transaction behavior. That is, if the transaction contains DML that requires row locks held by another transaction, then the DML statement will wait until the row locks are released.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_2012.htm