Oracle Performance and Engineering

Answering the two questions from yesterday, i.e., -

(a) can a transaction NOT read committed data that's committed by itself during the transaction? If so, then
(b) can a query - by some means - read the data modified by itself?

Well, let's try answering (a) with a test case.

An 'outer' transaction can have an 'autonomous transaction' inside that can commit within its scope - without commiting any the main / calling transaction. Such 'mini' self-scoped transactions within an ongoing transaction is called "Autonomous Transaction". They could be an excellent choice to record say, logging records. Even if your main transaction fails and thus, have to roll itself back - you may still want to commit the history or log. We can then define an transaction-let by defining it as "autonomous". Remember, the scope of autonomous transaction is bounded by either a COMMIT or ROLLBACK, and you *have* to do one of these to continue onto the main transaction.

So, a transaction could have an 'autonomous transaction' (that commits!) inside and thus can read the committed data from it.

An easy test case -

SQL> create table t1 (c1 number);

SQL> create table t2 (c2 number);


SQL> create or replace procedure f2
as
PRAGMA AUTONOMOUS_TRANSACTION;
-- MUST declare to specify it's Autonomous Txn%
begin
update t1 set c1 = 2;
insert into t2 values (1);
commit;
-- COMMIT is also a MUST
end;
/


SQL> create or replace function f1
return number
as
v_c t1.c1%type;
begin
f2;
select c1 into v_c from t1;
return v_c;
end f1;
/

We start a transaction (say, outer transaction) -

SQL> insert into t1 values (1);

SQL> select f1() from dual;

-- f1 calls f2 which starts and ends an 'autonomous transaction' by
-- (a) updating t1 and (b) inserting into t2

F1()
----------
1

SQL> select * from t2;

C2
----------
1

This shows that though the 'outer' transaction (that started earlier and is uncommitted) can show the committed insert made by the inner 'autonomous' transaction - BUT the inner one cannot see the uncommitted data in t1 -- and thus does NOT update ANY row in t1 to "c1 =2 ". That's what is expected. Because (by default) no transaction should read uncommitted data by another transaction in Oracle (or in any self-respecting database for that matter)!

This is called "isolation level" - which is READ COMMITTED (self explanatory) by default.

*But*, here's the deal - if we set it to SERIALIZABLE -- changes made by the inner transaction (autonomous) are NOT visible to the outer one!

Example -

SQL> truncate table t1;

SQL> truncate table t2;

SQL> set transaction isolation level serializable;

SQL> insert into t1 values (1);
-- outer transaction begins

SQL> select f1() from dual;
-- inserts one row in t2. updates t1 if finds data
-- COMMITs

F1()
----------
1

SQL> select * from t2;

no rows selected

Just as advertised! The outer transaction cannot see the committed data by the autonomous transaction that committed within it.

Here's what the Oracle Documentation says about this --

"Transaction Visibility

Changes made by an autonomous transaction become visible to other transactions
when the autonomous transaction commits. These changes become visible to the
main transaction when it resumes, if its isolation level is set to READ
COMMITTED (the default).

If you set the isolation level of the main transaction to SERIALIZABLE, changes
made by its autonomous transactions are not visible to the main transaction
when it resumes:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;"