Skip to Main Content
  • Questions

  • When does uncommited data rollback from data file?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ravi.

Asked: August 09, 2016 - 11:24 am UTC

Last updated: August 11, 2016 - 12:44 pm UTC

Version: 11.2

Viewed 1000+ times

You Asked

Dear Team,

DBWR write flush data from db buffer cache at the time
• Checkpoint occurs
• Dirty buffers reach threshold
• There are no free buffers
• Timeout occurs
• Tablespace OFFLINE
• Tablespace READ ONLY
• Table DROP or TRUNCATE
• Tablespace BEGIN

Logwriter write data to redo log files at the time
--Every three seconds

--When the redo log buffer is one-third full

--When a DBWn process writes modified buffers to disk, if necessary


Means both datafiles and redolog files have uncommited data. Uncommited data get rollback
from datafile only at the time instance recovery by SMON and this happens during instance startup. If my production
database not restart for a long time means its has too much uncommited data.

My question is When does the uncommited data get rollback from datafile apart from instance recovery by smon?

Thanks & Regards
P.B.Ravi Kumar


and Connor said...

Notice how you wrote:

"DBWR write flush data from db buffer cache at the time
• Dirty buffers reach threshold"

When you "undo" a change to a block, you are in effect, just making a "new" change to the block, eg

update T set x = 2 where x = 1;

the block originally had a row: x=1
you changed it to have: x=2 (the block is dirty)

that block gets flushed out (or does not...it doesn't really matter).

Now you do: "rollback"

the (clean) block currently has: x=2
you change it to have: x=1 (the block is now dirty)

and hence DBWR will eventually get around to flushing it out again

A "rollback" is a fresh change to the block.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ravi Kumar, August 10, 2016 - 11:33 am UTC

Hi Mc Donald,
Thanks for your response, please let me know what will happen for the following scenario :
1.User1 updates a row in table x.

2.User2 updates a row in table y.

3.The database writes the before-change data in the undo tablespace.

4.User 2 has given commit.

5.Logwriter is called and writes both the commited entry by user2 and uncommited entry by user 1 from redolog buffer
to the online redolog files.

6.the database assigns a SCN for a commited transaction in the redolog file, i.e., for the transaction generated by user2.

7.Due to checkpoint,Flushing the contents of the redo log buffers to the redo log files will be done and
Flushing the contents of the database buffer cache to disk.

8.So at step 7, online redolog file and Data File will contain uncommited data generated by user1. When this uncomitted
data will be removed from the respective files, if there is no user initiated Rollback? How long will the uncommited data be retained in both the files?
Connor McDonald
August 11, 2016 - 12:44 pm UTC

The database (by design) is really really lazy. There's a good reason for that - why do work if you can avoid it ? The less work you do, the faster performance you have.

So there are many scenarios where we simply leave blocks in an "untidy" state on disk - it is not a big drama. For example, if you change LOTS of rows, then when you commit, we dont go back to all those blocks and wipe out the lock information on the rows...it's not worth the effort.

Now, then *next* person (session) who comes along and reads those rows...well, they are going to see all that mess. How do *they* know what's really committed, what's really uncommitted etc ? It's not too difficult - the details of the seemingly active transaction on that block can be looked for in the undo area. If its marked as committed, or is not there (ie, was committed so long ago that we dont care), then that messy block can be tidied up by that reading session.

Hope this helps.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.