Hi,

In the following example on "Read Only transaction" testing on Oracle 8.1.7 (Windows NT 4.0 platform)

The table "testread" in this example
has a column teststring char(25)

SQLPLUS Session 1
=================

commit;
insert into testread values('testdata1')
commit;

SQLPLUS Session 2
=================

commit;
set transaction read only;
select * from testread;

Results are :

teststring
-----------

testdata1


SQLPLUS Session 1 continued
===========================
insert into testread values('testdata2');
commit;

SQLPLUS Session 2 continued
===========================

select * from testread;

Results are : (again it displays the same results since we have not committed yet for Session 2 to end the Read only transaction)

teststring
-----------

testdata1

SQLPLUS Session 3 (A brand new session to the same database)
=================
commit;
set transaction read only;
select * from testread;

Results are :

teststring
-----------

testdata1
testdata2

From this example, one can learn how Read Only isolation is neatly performed by Oracle.

The question to you is how does Oracle accomplish "Read Consistency" in the above scenarios.
i.e. Readonly transaction in Session 2 is different from Session 3. The row testdata2 for the second insert was seen only by Session 3
and not by Session 2, since it was started just after the first insert of row testdata1 (i.e. after the first insert was committed)

How does Oracle maintain consistency of the data for these 2 sessions in a consistent fashion until you end the "read only transaction" by committing it. i.e. Any number of selects in Session 1 before commit always returns "testdata1" and does not include "testdata2".

Is it from the REDO logs ?

What are the sideeffects of using this. Does the inserts into the datablocks take longer than usual, since there are multiple "read only transactions" that are reading them.


Please explain a little bit on the internals if you can

Thanks a lot

Sukumar Alagappan