Home » RDBMS Server » Server Administration » Uncommitted Trasactions
Uncommitted Trasactions [message #320093] Wed, 14 May 2008 01:30 Go to next message
hexmanju
Messages: 71
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
Re: Uncommitted Trasactions [message #320095 is a reply to message #320093] Wed, 14 May 2008 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Uncommitted Trasactions [message #320237 is a reply to message #320095] Wed, 14 May 2008 07:15 Go to previous messageGo to next message
hexmanju
Messages: 71
Registered: September 2005
Location: MUMBAI
Member
Thanks for the reply..At what stage does the Undo/rollback segments rollback the uncommitted transactions.

Thanks
Manjunath
Re: Uncommitted Trasactions [message #320244 is a reply to message #320093] Wed, 14 May 2008 07:23 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> uncommitted transactions
Above is an oxymoron.
You have either (committed) transactions or uncommitted changes.
Re: Uncommitted Trasactions [message #320245 is a reply to message #320237] Wed, 14 May 2008 07:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Last step of instance recovery.

Regards
Michel
Re: Uncommitted Trasactions [message #320320 is a reply to message #320093] Wed, 14 May 2008 12:29 Go to previous messageGo to next message
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.
Re: Uncommitted Trasactions [message #320328 is a reply to message #320320] Wed, 14 May 2008 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Uncommitted Trasactions [message #320329 is a reply to message #320093] Wed, 14 May 2008 13:19 Go to previous messageGo to next message
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.


Re: Uncommitted Trasactions [message #320332 is a reply to message #320329] Wed, 14 May 2008 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Uncommitted Trasactions [message #320410 is a reply to message #320093] Thu, 15 May 2008 01:15 Go to previous messageGo to next message
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
Re: Uncommitted Trasactions [message #320482 is a reply to message #320093] Thu, 15 May 2008 04:33 Go to previous messageGo to next message
hexmanju
Messages: 71
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?
Re: Uncommitted Trasactions [message #320486 is a reply to message #320482] Thu, 15 May 2008 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Uncommitted Trasactions [message #320491 is a reply to message #320093] Thu, 15 May 2008 04:54 Go to previous messageGo to next message
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.

Re: Uncommitted Trasactions [message #320511 is a reply to message #320093] Thu, 15 May 2008 05:46 Go to previous messageGo to next message
hexmanju
Messages: 71
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?
Re: Uncommitted Trasactions [message #320519 is a reply to message #320511] Thu, 15 May 2008 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Uncommitted Trasactions [message #320522 is a reply to message #320519] Thu, 15 May 2008 06:10 Go to previous messageGo to next message
hexmanju
Messages: 71
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?

Re: Uncommitted Trasactions [message #320527 is a reply to message #320522] Thu, 15 May 2008 06:38 Go to previous message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: upgrading from 10g to 11g
Next Topic: What does Auxillary Instance mean?
Goto Forum:
  


Current Time: Mon Dec 05 02:52:44 CST 2016

Total time taken to generate the page: 0.28365 seconds