I am an Oracle Database Specialist in Singapore.
get an rss feed of this blog at http://hemantoracledba.blogspot.com/feeds/posts/default?alt=rss
follow me on twitter : @HemantKChitale

Search My Oracle Blog

Custom Search

29 June, 2007

Read Consistency across Statements

A recent blog posting on the SET TRANSACTION READ ONLY reminded me of a notethat I had written in 2004. [note the references to AUM because most of my databaseswere 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.

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> l1 declare2 i number;3 c1 number;4 c2 varchar2(10);5 cursor cursor1 is select col1, col2 from t1;6 begin7 i :=1;8 loop9 exit when i > 5;10 open cursor1;11 loop12 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:37Got the values 3 and c in run 1 at 18:53:37Got the values 1 and a in run 2 at 18:53:40Got the values 26 and z in run 2 at 18:53:40Got the values 1 and a in run 3 at 18:53:44Got the values 26 and z in run 3 at 18:53:44Got the values 1 and a in run 4 at 18:53:47Got the values 26 and z in run 4 at 18:53:47Got the values 1 and a in run 5 at 18:53:50Got the values 26 and z in run 5 at 18:53:50PL/SQL procedure successfully completed.18:53:53 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> l1* set transaction read only19:00:49 SQL> get afiedt.buf1 declare2 i number;3 c1 number;4 c2 varchar2(10);5 cursor cursor1 is select col1, col2 from t1;6 begin7 i :=1;8 loop9 exit when i > 5;10 open cursor1;11 loop12 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:02Got the values 26 and z in run 1 at 19:01:02Got the values 1 and a in run 2 at 19:01:06Got the values 26 and z in run 2 at 19:01:06Got the values 1 and a in run 3 at 19:01:09Got the values 26 and z in run 3 at 19:01:09Got the values 1 and a in run 4 at 19:01:12Got the values 26 and z in run 4 at 19:01:12Got the values 1 and a in run 5 at 19:01:15Got the values 26 and z in run 5 at 19:01:15PL/SQL procedure successfully completed.19:01:18 SQL> select * from t1;COL1 COL2---------- -----1 a26 z19:01:28 SQL> commit;Commit complete.19:01:43 SQL> select * from t1;COL1 COL2---------- -----1 a10 j19:01:48 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 ONLYtransaction"// *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.

7 comments:

Hi Hemant,Nice Explanation.I've some doubt about read consistency.You might have gone thorugh this thread.http://forums.oracle.com/forums/thread.jspa?threadID=651218&tstart=75&start=25The above threads has confused me.I'll be greatful to you if you could clear my doubts.

1.Do select statement Use UNDO Segments for read consistency ? if the block is not modified or no transaction is going on that blockI think Select doesn't use UNDO.

2.lets say my SELECT starts at time t0 with some SUM() function.and another transaction (UPDATE) starts at time t2 and updates the data at time t3 & Commits. But my SELECT is reached at time t10 now and it has already recorder the before immage of data before UPDATE, so here i'm having fuzzy data at the end of SELECT query. How oracle handles this situation?

Hi Hemant,Nice Explanation.I've some doubt about read consistency.You might have gone thorugh this thread.http://forums.oracle.com/forums/thread.jspa?threadID=651218&tstart=75&start=25The above threads has confused me.I'll be greatful to you if you could clear my doubts.

1.Do select statement Use UNDO Segments for read consistency ? if the block is not modified or no transaction is going on that blockI think Select doesn't use UNDO.

2.lets say my SELECT starts at time t0 with some SUM() function.and another transaction (UPDATE) starts at time t2 and updates the data at time t3 & Commits. But my SELECT is reached at time t10 now and it has already recorder the before immage of data before UPDATE, so here i'm having fuzzy data at the end of SELECT query. How oracle handles this situation?

1. The SELECT will readfrom UNDO if it needs to.If the block hasn't beenmodified and contains anSCN older than the SELECT,then, obviously, there is no change to "undo".

2. In the second case, theSELECT doing the SUM() willobtain a read consistent image. Since it startedbefore the update, it willattempt to read the pre-update image from the undo segment. The ITL entry for that row in theblock header helps it identify the undo segmentto read from.

Actually i want to say, my first query has reached time t10 and crossed that block as well ,which has updated.so how oracle gets to know that?Because it has passed that block already.so how oracle gets to know that it has to read a consistent image of that block as the block has changed...

In a busy database blocks are always being updated -- by possibly hundreds of concurrent transactions. Think about it. Would Oracle go back and re-read a block just because it got updated since it was last read by the query ? You'd have hundreds of querying sessions repeatedly re-reading blocks (and possibly going into endless loops).

Why should Oracle have to re-read a block ? [the real case would be if it was doing a [ eg nested loop] lookup via an index and had, say, 1 minute ago, read one row fetched by rowid and now needs to read another row because it is a different rowid from the index]