SYS用户设置serializable事务和read only事务无效

Oracle官方文档：http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10005.htm

SET TRANSACTION

READ ONLY

The READ ONLY clause establishes the current transaction as a read-only transaction. This clause established transaction-level read consistency.

All subsequent queries in that transaction see only changes that were committed before the transaction began. Read-only transactions are useful for reports that run multiple queries against one or more tables while other users update these same tables.

This clause is not supported for the user SYS. Queries by SYS will return changes made during the transaction even if SYS has set the transaction to be READ ONLY.

 

------------------------------------------------------

reference：http://fxz-2008.javaeye.com/blog/836406

 

Oracle允许在事务开始时使用语句来设定事务的级别，以下三个语句分别设定oracle的三种事务隔离级别：

set transaction isolation level read committed;

set transaction isolation level serializable;

set transaction read only;

Plsql事务处理的语句有下列一些：

Commit, rollback, savepoint, rollback to savepoint, set transaction ,lock table等。

例如：

Set transaction read only

这个语句建立一个事务级的读一致性，事务内所有的查询所见的都是事务开始之前的改变，只读事务对于表有修改的许多查询报表来说比较有用。这个语句不适用于SYS用户，意味着，即使SYS用户设置事务为只读，查询也会返回事务期间的更改。

测试：

Scott用户下：

Session1：

SQL> set transaction read only;

Transaction set

SQL> select sum(sal) from scott.emp ;

 SUM(SAL)

----------

  28124.2

 

Session2：

SQL> update scott.emp a set a.sal = a.sal + 1.1;

14 rows updated

SQL> commit;

Commit complete

Session1：

SQL> select sum(sal) from scott.emp ;

 SUM(SAL)

----------

  28124.2

SQL> commit;

Commit complete

SQL> select sum(sal) from scott.emp ;

 SUM(SAL)

----------

  28139.6

很容易看出set transaction read only的效果，如果实在sys下面，情况则是这样的：

Session1：

SQL> set transaction read only;

Transaction set

SQL> select sum(sal) from scott.emp ;

 SUM(SAL)

----------

  28139.6

Session2：

SQL> update scott.emp a set a.sal = a.sal + 1.1;

14 rows updated

SQL> commit;

Commit complete

SQL>

Session1：

SQL> select sum(sal) from scott.emp ;

 SUM(SAL)

----------

    28155