About undo blocks & read consistency

From: Spendius <spendius_at_muchomail.com>
Date: 9 Jul 2003 04:51:13 -0700
Message-ID: <aba30b75.0307090351.17aaff81@posting.google.com>

(to simplify assume everything happens in memory -we're dealing with a well tuned DB-)

1/ OK let's say we have a user A who updates a record

about M. John Doe whose age was erroneously entered in the application (29 has been recorded, it's gotta be corrected to 31): Oracle updates the dbf block in the dbf buffers and copies an image of the block in the rbs buffers right ?
2/ If at the same time user B performs a SELECT on all

the male personnel who's less than 30 years old: Oracle's going to traverse the blocks responding to this request but it's going to sneak around the block in the dbf buffers where user A has put '31' and read the block in the rollback buffers OK ? 3/ Now user A commits, and for whatever reason user B

   reexecutes his query: now he's going to see
> Name Posit. G Age
> -------------- ------ - ---
> Didier Barber Clerk M 34
> John Doe DBA M 31
> Jim Stock Chief M 48
> ...

   instead of
> John Doe DBA M 29

   a few seconds before.

I have 2 questions:
o Are the data & rbs blocks mixed in the buffers or are there 2 distinct buffers (one for the data, the other one for the rollback), 2 zones clearly defined ? Was it relevant to write above "dbf buffers" and "rbs buffers" as I did it ?
o Once user A has commited his update, what's the very flag that tells Oracle to read the proper block, what's the mechanism that returns the good block from which user B must get his information from ? Where is this flag located ? 'Cause as far as I understood, the data block is immediately updated, but not read by another session while still not commited => once it is, it's the rbs block which is no more read, but the data block takes the relieve...

This whole mechanism is pretty darn complex and I'm not sure the explanations I read were really efficacious... In advance thanks !!

user A (instant T, but not commited yet)

  UPDATE person
  SET age=31
  WHERE id=92993;

__________ _________
(d. buffers) (rbs buff.)
+----------+

|+--------+|    +--------+ 
|| Bl. 1  ||    | Bl. 1  | 
|| =====  ||    | =====  | 
|| ...    || => |        | 
|| age=31 ||    | age=29 | 
|| ...    ||    |        | 
|+--------+|    +--------+ 
|+--------+|
|| Bl. 2  ||
|| =====  ||
|| ...    ||
|| ...    ||
|| ...    ||
|+--------+|

+----------+

user B (instant T+1, still not commited)

  SELECT *
  FROM person
  WHERE sex='m'
    AND age < 30;

                   |
		  \|/
 __________      _________

(d. buffers) (rbs buff.)
+----------+

|+--------+|    +--------+ 
||\Bl\/1 /||    | Bl. 1  | 
|| \=/\=/ ||    | =====  | 
|| ./. \  ||    |        | 
|| /g\/3\ ||    | age=29 | 
||/../\  \||    |        | 
|+--------+|    +--------+ 
|+--------+|        |
|| Bl. 2  ||        |
|| =====  ||        |
|| ...    || <------+
|| ...    ||
|| ...    ||
|+--------+|
+----------+
     |

    \|/
   [continues to traverse
    the dbf buffers] Received on Wed Jul 09 2003 - 06:51:13 CDT