29 June, 2007

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.

9 comments:

Anonymous said...
This comment has been removed by a blog administrator.
Nitin said...

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=25
The 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 block
I 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?

Regards!

Nitin.

Nitin said...

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=25
The 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 block
I 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?

Regards!

Nitin.

Hemant K Chitale said...

1. The SELECT will read
from UNDO if it needs to.
If the block hasn't been
modified and contains an
SCN older than the SELECT,
then, obviously, there is
no change to "undo".

2. In the second case, the
SELECT doing the SUM() will
obtain a read consistent image. Since it started
before the update, it will
attempt to read the pre-
update image from the
undo segment. The ITL
entry for that row in the
block header helps it
identify the undo segment
to read from.

Nitin said...

Thanks For the Reply.

Just little moe explanation on 2nd case please.

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...

Regards!

Hemant K Chitale said...

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]

Nitin said...

Thanks Hemant.
It has cleared my doubts.

Unknown said...

Hi Hemant,

I understood how read consistency works in case of one session using select and other session modifying data. But I have a doubt in below situation

session 1: started updating table t1 at scn 100
session 2: updated block 2 of table t1 at scn 120 and committed
session 1 now during its course of updating table t1 comes to block 2 to modify other row , will it create a read consistency copy of block 2 as of scn 100 and modify after? or will it modify the block 2 which was at scn 120.

If it creates a consistency copy as of scn 100 and modifies ,what happens to changes made by session2 at scn 120 . Which version of block is written to disk during next checkpoint?

Thank you
Rakesh

Hemant K Chitale said...

Rakesh,
session 1 has to restart the update,with a *fresh* read-consistent copy of data in all the blocks, not just the new version of data in block 2.