OraFAQ Forum: Server Administration » Uncommitted Trasactions

Home » RDBMS Server » Server Administration » Uncommitted Trasactions

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Uncommitted Trasactions [message #320093]

Wed, 14 May 2008 01:30

hexmanju
Messages: 78
Registered: September 2005
Location: MUMBAI

Member

Hi

Can anyone let me know that while a database is up and running is there chances that the dirty (modified) buffers which are uncommited gets flushed to the Data files and then how is this taken care of when the Instance is up and running.Everywhere i read this that next time instance recovery takes place it will rollback uncommitted trasaction but say if instance recovery happens after a while or so will the uncommitted transaction sit in the Data file till that time.

Kindly explain
Thanks
MM

Report message to a moderator

Re: Uncommitted Trasactions [message #320095 is a reply to message #320093]

Wed, 14 May 2008 01:38

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

(Actual) Instance recovery only happens when there is a failure.
Then dirty blocks that have been flushed are rolled back using the information in the undo/rollback segments.

Regards
Michel

Report message to a moderator

Re: Uncommitted Trasactions [message #320237 is a reply to message #320095]

Wed, 14 May 2008 07:15

hexmanju
Messages: 78
Registered: September 2005
Location: MUMBAI

Member

Thanks for the reply..At what stage does the Undo/rollback segments rollback the uncommitted transactions.

Thanks
Manjunath

Report message to a moderator

Re: Uncommitted Trasactions [message #320244 is a reply to message #320093]

Wed, 14 May 2008 07:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> uncommitted transactions
Above is an oxymoron.
You have either (committed) transactions or uncommitted changes.

Report message to a moderator

Re: Uncommitted Trasactions [message #320245 is a reply to message #320237]

Wed, 14 May 2008 07:23

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Last step of instance recovery.

Regards
Michel

Report message to a moderator

Re: Uncommitted Trasactions [message #320320 is a reply to message #320093]

Wed, 14 May 2008 12:29

varu123
Messages: 754
Registered: October 2007

Senior Member

Quote:

Last step of instance recovery.

The first and the last step of instance recovery is rolling forward.

Rolling backward is something that happens after the instance recovery is done.

Because recovery essentially means making database consistent by applying redo.

Once database gets opened,recovery is over but applying undo might continue.

Report message to a moderator

Re: Uncommitted Trasactions [message #320328 is a reply to message #320320]

Wed, 14 May 2008 13:12

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Rolling backward is something that happens after the instance recovery is done.

I don't agree with this, instance recovery ends when rolling back is over.

Quote:

Once database gets opened,recovery is over

No, recovery is still going on with undo apply.

Concepts
Section Overview of Instance and Crash Recovery

Quote:

Crash and instance recovery involve two distinct operations: rolling forward the current, online datafiles by applying both committed and uncommitted transactions contained in online redo records, and then rolling back changes made in uncommitted transactions to their original state.

Regards
Michel

Report message to a moderator

Re: Uncommitted Trasactions [message #320329 is a reply to message #320093]

Wed, 14 May 2008 13:19

varu123
Messages: 754
Registered: October 2007

Senior Member

Well,Thats what the documentaion says.
and this is what Tom Kyte says
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:817501300346876428

Quote:

crash recovery = rolling forward.

rolling back just sort of happens afterwards.

Report message to a moderator

Re: Uncommitted Trasactions [message #320332 is a reply to message #320329]

Wed, 14 May 2008 13:41

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Just a matter of definition.
Oracle one is instance/crash recovery = rolling forward + rolling back.
But this is not important, the important point is that rolling forward is sufficient to get a database in consistent state (that is any query will give an answer that is consistent with the data committed at the current point in time) and this is why the database can be opened.

Regards
Michel

Report message to a moderator

Re: Uncommitted Trasactions [message #320410 is a reply to message #320093]

Thu, 15 May 2008 01:15

varu123
Messages: 754
Registered: October 2007

Senior Member

Quote:

Oracle one is instance/crash recovery = rolling forward + rolling back.

Tom Kyte represents oracle.
If you see his articles,he refers "oracle" as "we".

So he contradicts with himself here Laughing

Laughing

Report message to a moderator

Re: Uncommitted Trasactions [message #320482 is a reply to message #320093]

Thu, 15 May 2008 04:33

hexmanju
Messages: 78
Registered: September 2005
Location: MUMBAI

Member

Thanks for all your inputs but what i am still unclear is i am telling you that my instance does not crash at all.Say if a user does not commit a huge transaction at all and due to lack of space in DB cache that change gets flushed into data files.what happens to that data change if my instance does not crash at all .Will Oracle allow it to stay in the data file .

I understand at Checkpoint all modified buffers gets flushed to data files and in that if we have uncommitted changes that also goes to the data file?AM i correct.WIll it stay there for ever and say if no crash happens its gonna sit there or what?

Report message to a moderator

Re: Uncommitted Trasactions [message #320486 is a reply to message #320482]

Thu, 15 May 2008 04:40

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If the instance never ends, then after sometime transaction is either committed and changes are permanent or rolled back and changes are undo.
If you clean stop the instance, then uncommitted changes are undo before the shutdown ends.

Regards
Michel

Report message to a moderator

Re: Uncommitted Trasactions [message #320491 is a reply to message #320093]

Thu, 15 May 2008 04:54

varu123
Messages: 754
Registered: October 2007

Senior Member

Quote:

Say if a user does not commit a huge transaction at all and due to lack of space in DB cache that change gets flushed into data files.what happens to that data change if my instance does not crash at all .Will Oracle allow it to stay in the data file .

Commit/Rollback is a must otherwise there would be indefinite row level locks preventing users to see updated data.So taht data change is not visible to other sessions.

Report message to a moderator

Re: Uncommitted Trasactions [message #320511 is a reply to message #320093]

Thu, 15 May 2008 05:46

hexmanju
Messages: 78
Registered: September 2005
Location: MUMBAI

Member

Thanks Varu..So if user does not commit at all,and as Michel Cadot said after some time Oracle will either commit it or roll it back.Am i correct?

Report message to a moderator

Re: Uncommitted Trasactions [message #320519 is a reply to message #320511]

Thu, 15 May 2008 05:59

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Michel Cadot said after some time Oracle will either commit it or roll it back

I didn't say this! sorry if my post was misleading.
Oracle does NEVER commit or roll back unless you ask it to do so (possibly when you ask it to terminate the instance).
What I wanted to say is that one day the client will either commit or roll back (or fail which is the same as rollback), or rollback will be forced by the end of the instance/database (or by the next startup if the end is abrupt).

Regards
Michel

Report message to a moderator

Re: Uncommitted Trasactions [message #320522 is a reply to message #320519]

Thu, 15 May 2008 06:10

hexmanju
Messages: 78
Registered: September 2005
Location: MUMBAI

Member

Thanks Michel Cardot for staying with my Post and patiently replying my queries..It has been good knowing u through this post.

Just a question->Do u think Oracle Database allows such uncommitted trasactions frequently or does it happen once a while .I mean to ask does it really Happen?

Report message to a moderator

Re: Uncommitted Trasactions [message #320527 is a reply to message #320522]

Thu, 15 May 2008 06:38

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

For Oracle (this is not true for other rdbms), you must commit or rollback only when your functional transaction is over. You do all the work your business needs require and only then you commit (or rollback).
That means that some transactions may last hours and Oracle is designed to handle them.

Regards
Michel

Report message to a moderator

Previous Topic:	upgrading from 10g to 11g
Next Topic:	What does Auxillary Instance mean?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 02:14:09 CST 2024