Read Consistency across Statements

A recent blog posting on the SET TRANSACTION READ ONLY reminded me of a note
that I had written in 2004. [note the references to AUM because most of my databases
were still 8i]

Oracle uses Rollback/Undo segments to ensure Read Consistency. Thus, if your query started at time t0, and it takes 30 minutes to run [ie till t30], you expect to see the data as of time t0 throughout the duration of the query. If another user were to issue an update at time t5 you would continue to see the data as of time t4 [or t0] even if your query comes to the respective row at, say, time t25. Read-consistency is provided by Oracle by ensuring that the pre-update image of the row is available in a Rollback/Undo segment [If that other user had committed at time t15, the information may or may not be still available in the Rollback/Undo segment at t25 as undo information relating to a committed update can be overwritten by other transactions -- in which case your query may encounter the "ORA-01555 'snapshot too old or rollback segment too small'" error]. All of this is common knowledge.

However, we sometimes forget that Read Consistency is at the Statement level, NOT the Transaction level. Thus, if you have two different SELECTs reading the same data, one after the other, and a second user updates *and* commits his update after the end of the first SELECT but before the beginning of the second SELECT, your second SELECT would *NOT* see the same data as the first SELECT.


Here are a few examples :

In the Query session :
1* select * from t1
18:10:12 SQL> /
COL1 COL2
---------- -----
1 a
2 b

In the Update session :
18:10:10 SQL> update t1 set col1=3,col2='c' where col1=2
18:10:41 2 /
1 row updated.

Back in the Query session :
1* select * from t1
18:12:38 SQL> /
COL1 COL2
---------- -----
1 a
2 b

So far, so good. As the Update session hasn't yet committed, I can still see the data.
18:13:32 SQL> l
1* select * from t1
18:13:33 SQL> /
COL1 COL2
---------- -----
1 a
2 b
18:13:34 SQL>

Here, I commit my update :
18:14:00 SQL> commit;
Commit complete.
18:14:01 SQL>

The Query session, now re-running the same SQL, sees the new data :
18:15:08 SQL> l
1* select * from t1
18:15:09 SQL> /
COL1 COL2
---------- -----
1 a
3 c
18:15:09 SQL>

As expected !
But, wait .... What if my Query session was actually a long-running report, consisting of multiple SQLs? If, say the first SQL fetched a count of records in a table and then printed that at the beginning of the report, and the last SQL also fetched a count and printed the count again, we might see different counts at the beginning and ending of the report !!
Here's an example of how you could get inconsistent data across different fetches in the same PLSQL block :

In the Query session :
18:53:35 SQL> l
1 declare
2 i number;
3 c1 number;
4 c2 varchar2(10);
5 cursor cursor1 is select col1, col2 from t1;
6 begin
7 i :=1;
8 loop
9 exit when i > 5;
10 open cursor1;
11 loop
12 fetch cursor1 into c1,c2;
13 exit when cursor1%NOTFOUND;
14 dbms_output.put_line('Got the values ' c1' and 'c2' in run 'i
' at ' to_char(sysdate,'HH24:MI:SS'));
15 end loop;
16 close cursor1;
17 dbms_lock.sleep(3);
18 i := i+1;
19 end loop;
20* end;
18:53:36 SQL> /
Got the values 1 and a in run 1 at 18:53:37
Got the values 3 and c in run 1 at 18:53:37
Got the values 1 and a in run 2 at 18:53:40
Got the values 26 and z in run 2 at 18:53:40
Got the values 1 and a in run 3 at 18:53:44
Got the values 26 and z in run 3 at 18:53:44
Got the values 1 and a in run 4 at 18:53:47
Got the values 26 and z in run 4 at 18:53:47
Got the values 1 and a in run 5 at 18:53:50
Got the values 26 and z in run 5 at 18:53:50
PL/SQL procedure successfully completed.
18:53:53 SQL>

In the Update session :
18:53:32 SQL> l
1* update t1 set col1=26, col2='z' where col1=3
18:53:38 SQL> /
1 row updated.
18:53:39 SQL> commit;
Commit complete.
18:53:40 SQL>

As the update was committed at 18:53:39, the Query session started seeing the new data thereafter, even though it was still within one PLSQL Block, within one Loop -- because a new SELECT was executed each time.
So how do we ensure that we get Read Consistent data across statements ? We can use the SET TRANSACTION READ ONLY command.

In the Query session :
19:00:35 SQL> set transaction read only;
Transaction set.
19:00:48 SQL> l
1* set transaction read only
19:00:49 SQL> get afiedt.buf
1 declare
2 i number;
3 c1 number;
4 c2 varchar2(10);
5 cursor cursor1 is select col1, col2 from t1;
6 begin
7 i :=1;
8 loop
9 exit when i > 5;
10 open cursor1;
11 loop
12 fetch cursor1 into c1,c2;
13 exit when cursor1%NOTFOUND;
14 dbms_output.put_line('Got the values ' c1' and 'c2' in run 'i
' at ' to_char(sysdate,'HH24:MI:SS'));
15 end loop;
16 close cursor1;
17 dbms_lock.sleep(3);
18 i := i+1;
19 end loop;
20* end;
19:00:59 SQL> /
Got the values 1 and a in run 1 at 19:01:02
Got the values 26 and z in run 1 at 19:01:02
Got the values 1 and a in run 2 at 19:01:06
Got the values 26 and z in run 2 at 19:01:06
Got the values 1 and a in run 3 at 19:01:09
Got the values 26 and z in run 3 at 19:01:09
Got the values 1 and a in run 4 at 19:01:12
Got the values 26 and z in run 4 at 19:01:12
Got the values 1 and a in run 5 at 19:01:15
Got the values 26 and z in run 5 at 19:01:15
PL/SQL procedure successfully completed.
19:01:18 SQL> select * from t1;
COL1 COL2
---------- -----
1 a
26 z
19:01:28 SQL> commit;
Commit complete.
19:01:43 SQL> select * from t1;
COL1 COL2
---------- -----
1 a
10 j
19:01:48 SQL>

In the Update session :
19:00:01 SQL> update t1 set col1=10, col2='j' where col1=26
19:01:03 2 /
1 row updated.
19:01:04 SQL> commit;
Commit complete.
19:01:05 SQL>

Although I had updated and committed at 19:01:04, my Query session continued to see the "old" or "Consistent" data, even at 19:01:18. I ended my "Read Consistent Across Statements" view by ending my pseudo-transaction in the Query session {It can be ended with either the COMMIT or the ROLLBACK keyword -- as we have not really done any DML in this session it makes no difference whether we issue a COMMIT or a ROLLBACK}. Only after that, could I see the updated records. The SET TRANSACTION READ ONLY "pseudo-transaction" does NOT allow DML [e.g INSERT or UPDATE] statements. Such DML would get an ORA-01456 error :
" 01456, 00000, "may not perform insert/delete/update operation inside a READ ONLY
transaction"
// *Cause: A non-DDL insert/delete/update or select for update operation
// was attempted
// *Action: commit (or rollback) transaction, and re-execute"

The SET TRANSACTION READ ONLY is useful when you have a report consisting of multiple statements and you need to ensure that all executions see the same Read-Consistent image. What is the downside ? You are more likely to hit ORA-01555 errors as Oracle does not guarantee preservation of the pre-image of updated data in the Rollback segments once an updating tranaction has committed. Oracle 9i with Automatic Undo Management and UNDO_RETENTION helps alleviate this problem by making a "best" attempt to keep data available in the Undo Segment for the duration specified by UNDO_RETENTION. Non-AUM 9i and 8i and below do not guarantee any retention of pre-update images in the Rollback Segments. The DBA might create more and larger rollback segments to reduce the probability {but not guarantee} of pre-update information being overwritten in the Rollback Segments.