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

8 thoughts on “read uncommitted

  1. TongucY

    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. Laurent Schneider Post author

    Hey, what a pleasure to start a discussion in your team ๐Ÿ˜€

    Your team is working in the week-end? What an enthusiasm ๐Ÿ˜€

    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. TongucY

    Thank you for the clarification Laurent.

    Not working at all but emailing and preparing for the new week lets say. ๐Ÿ™‚

  4. Leo Anderson

    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.

  5. Pingback: Jรก falei!! | Databases Without Borders

  6. steve

    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.

  7. Jeff Fischer

    Step 1 – Redefine what a “dirty read” is. ๐Ÿ™‚
    Step 2 – Claim your product doesn’t do dirty reads.

    Here’s from the Oracle docs “Changes of other transactions that occur during a query’s execution are not observed, guaranteeing that consistent data is returned for each query.”

    If it’s looking past uncommitted transactions, that is, by definition, a dirty read.

    This is serious moving the goal posts.

    https://docs.oracle.com/cd/B13789_01/server.101/b10743/consist.htm

  8. Laurent Schneider Post author

    In Oracle read-uncommited is not possible… unless you read from your transaction. To suspend the (uncommited) transaction and resume it in another session is of course more of a joke than a really dirty read ๐Ÿ˜‰

Comments are closed.