Redo and Undo - Oracle Database Transactions and Locking Revealed - page 123

• A buffered redo log for the preceding two items has been generated in the SGA.

• Depending on the size of the preceding three items and the amount of time spent, some

combination of the previous data may be flushed onto disk already.

• All locks have been acquired.

When we ROLLBACK ,
We undo all of the changes made. This is accomplished by reading the data back from the •

undo segment and, in effect, reversing our operation and then marking the undo entry as
applied. If we inserted a row, a ROLLBACK will delete it. If we updated a row, a rollback will
reverse the update. If we deleted a row, a rollback will reinsert it again.

• All locks held by our session are released, and everyone who was enqueued waiting on locks

we held will be released.

A COMMIT , on the other hand, just flushes any remaining data in the redo log buffers. It does very little work
compared to a ROLLBACK . The point here is that you don't want to roll back unless you have to. It is expensive since you
spend a lot of time doing the work, and you'll also spend a lot of time undoing the work. Don't do work unless you're
sure you are going to want to COMMIT it. This sounds like common sense—of course I wouldn't do all of the work unless
I wanted to COMMIT it. However, I've often seen a developer use a “real” table as a temporary table, fill it up with data,
report on it, and then roll back to get rid of the temporary data. Later we'll talk about true temporary tables and how to
avoid this issue.

Summary
In this chapter, we explored redo and undo and took a look at what they mean to the developer. The key point to take
away from this chapter is the importance of redo and undo, and the fact that they are not overhead—they are integral
components of the database and are necessary and mandatory. Once you have a good understanding of how they
work and what they do, you'll be able to make better use of them. Understanding that you are not “saving” anything
by committing more frequently than you should (you are actually wasting resources, as it takes more CPU, more disk,
and more programming) is probably the most important point of all. Be aware of what the database needs to do, and
then let the database do it.