Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Transactions: blocks/pages SCN and ordering

Re: Transactions: blocks/pages SCN and ordering

From: joel garry <joel-garry_at_home.com>
Date: Fri, 30 Nov 2007 14:27:05 -0800 (PST)
Message-ID: <5fba086c-b224-4adb-b5df-afa8c32eee28@s19g2000prg.googlegroups.com>


On Nov 29, 11:31 pm, markpapadakis <markpapada..._at_gmail.com> wrote:
> 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.

Well, you should read the Concepts manual about guaranteed consistency: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#sthref1944

> * Isn't any given data block 'owned' on a datafile (i.e last updated
> by a transaction of SCN x )?

Yes. Owned is perhaps a misleading word. Stamped might be closer.

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

Well, it just has to find the appropriate old information, not necessarily read everything between. Jonathan Lewis' Practical Oracle 8i book probably has the clearest explanation of what actually happens when you change data, The more modern undo adds a layer on top of that, and the even more modern stuff changes some details. Some search terms you can use to find information online about this are (add the term oracle, of course):
transaction table
segment header blocks
interested transaction list
optimistic commit
delayed block cleanout

You may find things like this: http://www.quest-pipelines.com/newsletter/Transaction%20Management.htm http://www.ixora.com.au/tips/admin/ora-1555.htm

One thing to remember: Oracle puts a lot of effort into writing as little as possible to disk as a bottlenecked operation.

Tom Kyte's books can help you figure it out learning by doing.

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

Yes, that is why they used to be called "rollback segments." Be sure to keep redo log and rollback/undo straight in your head, they are both used (for example, changes, including rollback, have to be recorded in the redo log before they are changed in the data block - that's why redo is so critical to Oracle, and perhaps its Achilles heel).

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

jg

--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20071130/news_1b30qualcomm.html
Received on Fri Nov 30 2007 - 16:27:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US