ORACLE 中实现 READ UNCOMMITTED

The default isolation level is READ COMMITTED. It means, a session read the committed data.

Session 1: 

SQL> set transaction isolation level read committed; 

Transaction set.

Session 2: 

SQL> update emp set sal=4000 where ename='SCOTT'; 

1 row updated.

Session 1: 

SQL> select sal from emp where ename='SCOTT'; 

       SAL
----------
      3000

Session 2: 

SQL> commit;
Commit complete.

Session 1: 

SQL> select sal from emp where ename='SCOTT'; 

       SAL
----------
      4000
SQL> update emp set sal=3000 where ename='SCOTT'; 

1 row updated. 

SQL> commit;
Commit complete.

When the session 1 reads the salary of Scott, it gets the value that is committed in the database.

Another isolation level is SERIALIZABLE.

Session 1: 

SQL> set transaction isolation level serializable; --开启一个serializale的事务,这句必须是事务开始的第一个语句.Sys用户不能执行这句.它是改变当前事务的隔离级别.

--事务从这里就已经开始了，同时已经绑定了SCN号，以后的查询都是基于这个SCN号读数据的，如果需要undo,也是用这个SCN号去还原数据.SERIALIZABLE级别算是实习了事务级的读一致性，

--因为所有读都是基于同一个SCN号的.

Transaction set.

Session 2: 

SQL> update emp set sal=5000 where ename='SCOTT'; 

1 row updated. 

SQL> commit; 

Commit complete.

Session 1: 

SQL> select sal from emp where ename='SCOTT'; --看不到别的事务在自己开启后commit的更新.

       SAL
----------
      3000 

SQL> update emp set sal=sal+1;--要更新的行被session2修改过了，这个行已经有了新的SCN号了,虽然我们可以读到事务开始前的SCN号对应的数据，但是无法读到最新的SCN号对应的数据.

--在这种情况下参试更新，oracle会报错ORA-08177. Oracle报错也是无奈之举，这个事务看不到新数据，因为如果基于事务开始时的旧SCN号对应的数据来进行更新，将无意中覆盖掉别的事--务的提交成果。造成更新丢失.
update emp set sal=sal+1
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
SQL> roll
Rollback complete.
SQL> select sal from emp where ename='SCOTT'; 

       SAL
----------
      5000 

SQL> update emp set sal=3000 where ename='SCOTT'; 

1 row updated. 

SQL> commit; 

Commit complete.

In session 1, the isolation level of the transaction is set to SERIALIZABLE.

Session 2 update the salary of Scott to 5000 and commits.

The session 1 therefore does not read committed data and any tentative to change the committed data will fail. Roll[back;] ends the transaction.

The session 1 can then read committed data and update the salary to 3000.

Ok, let’s imagine you have to interview an OCM and you want to ask him a very difficult question:

- Is it possible in Oracle to read uncommitted data from another session?

Let’s try

Session 1:

SQL> var rc number
SQL> set autop on
SQL> select sal from emp where ename='SCOTT'; 

       SAL
----------
      3000 

SQL> exec :rc:=DBMS_XA.XA_START(DBMS_XA_XID(1),
  DBMS_XA.TMNOFLAGS) 

PL/SQL procedure successfully completed. 

        RC
----------
         0 

SQL>
SQL> UPDATE emp SET sal=6000 WHERE ename='SCOTT'; 

1 row updated. 

SQL> exec :rc:=DBMS_XA.XA_END(DBMS_XA_XID(1),
  DBMS_XA.TMSUSPEND)

 

PL/SQL procedure successfully completed. 

        RC
----------
         0

Session 2: 

SQL> select sal from emp where ename='SCOTT'; 

       SAL
----------
      3000 

SQL> var sal number
SQL> var rc number
SQL> set autop on
SQL> begin
  :rc:=DBMS_XA.XA_START(DBMS_XA_XID(1),DBMS_XA.TMRESUME);
  SELECT SAL INTO :SAL FROM EMP WHERE ENAME='SCOTT';
  :rc:=DBMS_XA.XA_END(DBMS_XA_XID(1), DBMS_XA.TMSUCCESS);
  :rc:=DBMS_XA.XA_ROLLBACK(DBMS_XA_XID(1));
end;
/

PL/SQL procedure successfully completed. 

       SAL
----------
      6000

         RC
----------
         0 

SQL> select sal from emp where ename='SCOTT'; 

       SAL
----------
      3000

So yes, you can read uncommitted data in a global transaction from another session.

But no, the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is not supported in Oracle.

Additional information on dirty reads is available here.

--这里其实是把session1里suspend的的事务移交到了session2，查询读取数据的时候，是transaction去读数据。所以这个事务能读到自己没有处理完毕的数据是理所应当的。

--这部分数据就保存在事务的工作域里。