About undo blocks & read consistency

(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

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

user B (instant T+1, still not commited)
~~~~~~
SELECT *
FROM person
WHERE sex='m'
AND age < 30;

__________ _________
(d. buffers) (rbs buff.)
+----------+
[continues to traverse
the dbf buffers]

On 9 Jul 2003 04:51:13 -0700, spendius (AT) muchomail (DOT) com (Spendius) wrote:

They are mixed.


If the transaction is committed the SCN will be removed from the data
block.


Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

I typed my message a little to quickly here:
old" to have a consistant example...

Thanks for any enlightenment...

"Spendius" <spendius (AT) muchomail (DOT) com> wrote


Simplistic explanation, but it might help:

Oracle will always write the undo (and the redo for the undo) *first*.

So it will always give you a read consistent image (unless it can't, because
of 1555, in which case you are still safe, because it will refuse to give
you an image at all).

Don't worry about what is happening with the data block. It will take care
of itself in its own good time.

Regards,
Paul

Sybrand Bakker <gooiditweg (AT) sybrandb (DOT) demon.nl> wrote

Is it really "If the transaction is committed the SCN will be removed
from the data block"? Or am I misreading? Hope you can elaborate.

Yong Huang