| How commit and rollback behave [message #479179] |
Thu, 14 October 2010 23:14  |
Aju
Messages: 94
Registered: October 2004
|
Member |
|
|
Its a general question and should be an easy for experts.
On a huge transaction the buffer cache gets fulled and on that case oracle automatically writes to the data files. And as such when we issue commit statement it does not take more time. It only writes the dirty buffers to the dirty buffers to the datafiles.
When we rollback does the data gets erased from the datafile. What is the mechanism of rolling back ? It should be clearing the dirty buffers for sure.
Thanks
|
|
|
|
|
|
| Re: How commit and rollback behave [message #479190 is a reply to message #479180] |
Fri, 15 October 2010 01:24  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
On COMMIT, nothing is written to the data files. On COMMIT, the log buffer is written to the online log files.
Actually, there are several mistakes in your understanding of how DML is executed, you probably need to read some of the introductory dovumentation (begin with the coincepts guide).
|
|
|
|
|
|
| Re: How commit and rollback behave [message #479201 is a reply to message #479194] |
Fri, 15 October 2010 02:44 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
@everyone: is this a comprehensible and correct description of rollback:
There is no ROLLBACK command in Oracle. When a user issues the SQL command ROLLBACK, the session's server process constructs statements using data in the undo segment that will reverse the effect of the user's statements in the transaction so far, executes them, and then commits the entire set of statements (the user's SQL and the generated SQL) as one transaction. So a rolled back transaction is in fact a committed transaction that has no nett effect on the data.
|
|
|
|
| Re: How commit and rollback behave [message #479207 is a reply to message #479179] |
Fri, 15 October 2010 04:55 |
Aju
Messages: 94
Registered: October 2004
|
Member |
|
|
Thank you all. I clarified few of the doubts, but need to read more. To my understanding Only at the checkpoints the data is written to the datafiles and the redolog file.
And on the rollback, the redo is rolled back with the data from undo.
Can anyone please refer me a link to the understanding of data storing in index blocks and the search mechanism with respect to searching the block address
|
|
|
|
|
|
| Re: How commit and rollback behave [message #479822 is a reply to message #479228] |
Tue, 19 October 2010 10:35 |
Aju
Messages: 94
Registered: October 2004
|
Member |
|
|
As I read more some fresh doubts crop-up.
Extract from the online document ==>
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/transact.htm#sthref639
The data changes for a committed transaction, stored in the database buffers of the SGA, are not necessarily written immediately to the datafiles by the database writer (DBWn) background process. This writing takes place when it is most efficient for the database to do so. It can happen before the transaction commits or, alternatively, it can happen some time after the transaction commits.
1. So what happens when there is rollback when the data is already written to the datafiles ? What exactly is meant by undo is (re)applied ? The online dovumentation seems to be not pretty clearer to me.
Here is the inference after a long search
http://pavandba.files.wordpress.com/2009/11/undo_redo1.pdf
On ROLLBACK scenarios the data from undo segment is copied to the buffer cache and flushes the dirty buffers. This data from the buffer cache will be latter flushed to the data files with the original values(at checkpoints). So that meant, the datafile was initially updated/inserted even before a commit. And this data is again removed from the datafile and is overwritten.
Please correct me if i am wrong again.
2. Now my other doubt is "Is undo created while we have an insert statement".
I am sorry if it really nags... But I am not clear.
|
|
|
|
| Re: How commit and rollback behave [message #479829 is a reply to message #479822] |
Tue, 19 October 2010 11:06 |
 |
Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
1. Undo apply means that undo information is executed to return back the data as previously: delete -> insert, update -> update in the opposite, insert -> delete. This happens in the same way as your own data modifications.
2. Yes. Oracle must know which row has been inserted to be able to remove it on rollback.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
