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
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.
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
Thank you for the clarification Laurent.
Not working at all but emailing and preparing for the new week lets say. 🙂
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.
Pingback: Já falei!! | Databases Without Borders
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.
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
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 😉