Re: Transactions: blocks/pages SCN and ordering

On Nov 30, 12:51 am, joel garry <joel-ga..._at_home.com> wrote:
> On Nov 29, 10:30 am, markpapadakis <markpapada..._at_gmail.com> wrote:
>
>
>
> > Greetings,
>
> > I was wondering if someone would shed some light to the following
> > question, described as follows:
> > Transaction 7 begins
> > time passes
> > A transaction with SCN 8 updates data block 64 and commits. Data block
> > 64 now is 'owned' by SCN = 8
> > time passes
> > Transaction A begins ( gets SCN = 10)
> > time passes
> > Transaction B begins ( gets SCN = 20)
> > time passes
> > Transaction B updates a row which requires updating the disk block 64
> > Transaction B commits and disk block 64 gets an 'ownership' with SCN =
> > 20
> > time passes
> > Transaction A updates block 64 (noone has locked it now)
> > Transaction A commits and block 64 gets an 'ownership' with SCN = 10
>
> > Can this happen?
> > Should Transaction A rollback because when it wishes to update it,
> > that data block is owned by
> > an SCN greater than the one assigned to it on initialization? If it
> > shouldn't, then when transaction with SCN wishes to read in the data
> > from that block in accordance to the revision implied by its SCN:
> > 1.Should read in the data block
> > 2. Data block is owned by SCN = 10, but we need latest revision before
> > SCN = 7
> > 3. Jumps to the undo block, but the SCN of the undo block is 20. It
> > should see then that 20 > 7, therefore it would consider the undo
> > block overwritten by a more recent transaction and would issue a
> > snapshot too old. Right?
>
> If I'm following your example correctly, no. I think your
> misconception is the single ownership. Oracle can have multiple
> copies of blocks (or rather, some of the information in them), and
> reconstructs the block as it should have looked at the SCN current
> when the transaction that is looking at it started. This has side-
> effects like no blocking of readers and being able to automatically
> recover the database after a crash, not to mention often being able to
> fulfil data requests without bothering the disk. It can sometimes
> have negative performance effects if code is written with wrong
> assumptions as to how it works.
>
> Look for discussions explaining ORA-1555 as well as commit on
> asktom.oracle.com.
>
>
>
> > I have very little experience with Oracle Database Server. I was just
> > wondering whether the revisions of pages should be in sequential
> > order, or not.
>
> Keep it up, you are learning. Between the Concepts manual, Tom Kyte's
> books and trying these things yourself the little light bulb will
> light up over your head. Just keep in mind different database engines
> by default handle the physical and logical aspects of transactions
> differently.
>
> jg
> --
> @home.com is bogus.
> "That baked 'em good!" - A Boy and His Doghttp://www.networkworld.com/community/node/22413

Thank you Joel,

However, I am still not certain as to wether the SCN ordering rule should apply.
* Isn't any given data block 'owned' on a datafile (i.e last updated by a transaction of SCN x )?
* Do revision data exist on undo blocks? So that if someone wishes to get the data based on a given revision (i.e based on the SCN ), it would read in the actual data (perhaps cached) from the data file for that block, and optionally follow undo blocks chains ( and apply their data to what was read thus far ) until it gets to the point where it reaches an undo block with SCN < desired SCN * Suppose a transaction wishes to read a row data based on SCN x whereas the current datablock is currently owned by SCN z ( whereas z
> x ). In that case, reconstruction would happen through undo files.
It gets that data and updates something. Then it commits to the datafile on disk. Do the undo 'instructions' recorded into the undo log represent whatever would be required to bring the actual datafile page back to its previous state?

Thank you very much. I will look further into that ORA-1555 message as well as check asktom.oracle.com.

Mark Papadakis Received on Fri Nov 30 2007 - 01:31:47 CST