|
READ UNCOMMITTED
Article by Rampant author Laurent Schneider
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.
Additional information on dirty reads is
available
here.
|
