Purpose of Undo Data

Undo data serves several purposes in an Oracle database, and none of these purposes is trivial. All are important to the efficient and effective management of your database. It is therefore important to understand the purposes the undo data serves so that we can maintain it effectively.

Transaction Rollback

Whenever a transaction has modified a row in a table, the old image of these modified columns, the columns' undo data, is saved in the undo segment. If the transaction is rolled back for any reason, Oracle restores the original value or values by writing those values back to the row. This operation, and the command to achieve the purpose, is known as rollback (hence the old term for undo).

Transaction Recovery

If an instance fails and transactions are still in progress, Oracle needs to undo any of the uncommitted changes made when the database was open last. This is still rolling back but is now being done not by the users' choice, but as a part of transaction recovery. Recovery of these changes is possible only because all changes made to the undo segments are also protected by the information in the online redo log files.

Read Consistency

While transactions are actively in progress, other users are usually in the database. These users should not be able to see any uncommitted changes made to the data by other transactions. Even if a query starts before another transaction commits, if that query is actively in process when that update transaction commits, the query should not be able to access the newly committed data. This concept is called read consistency, and the old values of the data, the undo data values that are still in the undo segments, are used to provide the users with a consistent image for any given statement.

Oracle guarantees that every statement will see its data from a consistent time, even if any or all of that data is modified, or in the process of modification, by other transactions.

Whenever the Oracle server begins to execute a user's SELECT statement, it determines the system's current System Change Number (SCN) and ensures that any changes committed before this SCN are processed by the SELECT. Any changes that have not been committed before this SCN are not accessible to the SELECT statement.

For example, you have a long-running query that you are executing. You start this query at 11:00 a.m., when several other users are on the system, each with his own transactions, some updating, some inserting new data, some deleting, others running queries. Several row changes have already been committed when you start your SELECT statement. Others have not yet either been committed or rolled back. If a row has changes to it that were not committed at the beginning of your query, there is no way to determine whether they will be committed or rolled back. If a row that has uncommitted changes is a member of the resultset of your query, Oracle constructs a read-consistent image of that entire row by retrieving the before image of all changes from the undo segments and applying those changes to a copy of the row in memory.

Transaction Read Consistency

Read consistency is always provided by Oracle for the duration of any given SQL statement; however, you can request read consistency for the duration of an entire read-only transaction (or series of SQL statements) by issuing the following command:

 SET TRANSACTION READ ONLY; 

If you have a transaction that will be performing Data Manipulation Language (DML) operations, you can also request read consistency be maintained for the duration of that entire transaction. This is accomplished by issuing the following at the beginning of the transaction:

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 

In both cases, Oracle provides the data for the entire transaction, start to finish, and a read-consistent image of all of the data involved in the transaction.

It is important to remember that using SERIALIZABLE can have negative impacts on performance.