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

An interesting point to note is that undo information, stored in undo tablespaces or undo segments, is protected
by redo as well. In other words, undo data is treated just like table data or index data—changes to undo generates
some redo, which is logged (to the log buffer and then the redo log file). Why this is so will become clear in a moment
when we discuss what happens when a system crashes. Undo data is added to the undo segment and is cached in the
buffer cache, just like any other piece of data would be.

Example INSERT-UPDATE-DELETE-COMMIT Scenario
For this example, assume we've created a table with an index as follows:

create table t(x int, y int);
create index ti on t(x);

And then we will investigate what might happen with a set of statements like this:

insert into t (x,y) values (1,1);
update t set x = x+1 where x = 1;
delete from t where x = 2;

We will follow this transaction down different paths and discover the answers to the following questions:

• What happens if the system fails at various points in the processing of these statements?

• What happens if the buffer cache fills up?

• What happens if we

ROLLBACK at any point?
What happens if we succeed and

•

COMMIT ?

The INSERT
The initial INSERT INTO T statement will generate both redo and undo. The undo generated will be enough
information to make the INSERT “go away.” The redo generated by the INSERT INTO T will be enough information to
make the INSERT “happen again.”
After the INSERT has occurred, we have the scenario illustrated in Figure 6-1 .