Re: Transactions: blocks/pages SCN and ordering

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 sideeffects  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 Dog
http://www.networkworld.com/community/node/22413