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;

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';

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

SQL> update emp set sal=sal+1;
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

Published by

Laurent Schneider

Oracle Certified Master

6 thoughts on “read uncommitted”

  1. Hi Laurent,

    Your post started a discussion in our team, before 11g’s DBMS_XA this functionality was there in OCI for years, and what we negotiated as is that Oracle still does not permit the uncommitted data read. :)

    The reason is, in your demonstration you are attaching the second session to a transaction, there is only one transaction here, and as always a transaction can see it own changes, doesn’t matter how many sessions deattach or attach to that transaction.

    What do you think?

    Best regards.

  2. Hey, what a pleasure to start a discussion in your team :D

    Your team is working in the week-end? What an enthusiasm :D

    In your own session it is quite trivial that YOU CAN read your own uncommitted data. This XA mechanism is simply allowing to move the transaction to another session.

    But still only one transaction and one session at the same time can read uncommitted data…

    Regards

  3. Hi Laurent,

    This question looks like a point I was wondering on OTN(http://forums.oracle.com/forums/thread.jspa?forumID=61&threadID=695879)

    The isolation level would give you acces to uncommited data for ALL transactions, not a particular one.

    So for “Is it possible in Oracle to read uncommitted data from another session?” the answer will be NO
    BUT I agree if the question is “Is it possible in Oracle to read uncommitted data from all of any other session?”, the isolation level could help and the answer will be YES.

    Thanks and go on this very interesting blog :)

    Leo.

  4. Pingback: Já falei!! | Databases Without Borders
  5. Actually the description is not correct. It depends if the XA transaction is tightly or loosely coupled. If the XA transaction is loosely coupled, the other session will not see any changes.

    In addition it is one transaction from the viewpoint of the database, which is uniquely identified by the GTRID. How many sessions there are is not really important, only one of sessions will have non-suspended transaction branch.

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>