Re: About undo blocks & read consistency

On 9 Jul 2003 04:51:13 -0700, spendius_at_muchomail.com (Spendius) wrote:

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

They are mixed.

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

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

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

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Jul 09 2003 - 12:51:09 CDT