When you use serializable, all queries give results consistent to the start of the transaction plus any changes made within the transaction itself. This applies to SQL in PL/SQL.
For example, create an empty table, a function to count its rows, load some data in - leave these UNCOMMITTED!
create table t (
c1 int
);
create or replace function f
return int as
retval int;
begin
dbms_session.sleep ( 10 );
select count(*) into retval from t;
return retval;
end f;
/
insert into t
with rws as (
select level x from dual
connect by level <= 1000
)
select * from rws;
(The sleep in the function is to give me time to switch between sessions & make it easier to view concurrency effects!)
In a different session, set the transaction level, insert a row, and call the function in a query. I've also set the query to count the table's rows to make it easier to see consistency effects.
While the query runs, commit the 1,000-row insert in the other session.
Using read committed the results are:
set transaction isolation level read committed;
insert into t values ( 0 );
select f, ( select count(*) from t ) from dual;
F (SELECTCOUNT(*)FROMT)
---------- ---------------------
1001 1So both queries see the insert in the session. The function sees the insert from the other session, but the plain SQL doesn't.
With serializable:
set transaction isolation level serializable;
insert into t values ( 0 );
select f, ( select count(*) from t ) from dual;
F (SELECTCOUNT(*)FROMT)
---------- ---------------------
1 1Both see the insert from the current session, but not the other session.
With read only:
set transaction read only;
insert into t values ( 0 );
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction
select f, ( select count(*) from t ) from dual;
F (SELECTCOUNT(*)FROMT)
---------- ---------------------
0 0The insert in the transaction fails (because it's read-only!) and neither query sees the data from the other session.
Once you complete the serializable & read only transactions (via commit or rollback), they can view the insert from the other session.