Oracle7 Server Concepts

Oracle and Transaction Management

A transaction in Oracle begins when the first executable SQL statement is encountered. An executable SQL statement is a SQL statement that generates calls to an instance, including DML and DDL statements.

When a transaction begins, Oracle assigns the transaction to an available rollback segment to record the rollback entries for the new transaction. See "Transactions and Rollback Segments" for more information about this topic.

A transaction ends when any of the following occurs:

You issue a COMMIT or ROLLBACK (without a SAVEPOINT clause) statement.

You execute a DDL statement (such as CREATE, DROP, RENAME, ALTER). If the current transaction contains any DML statements, Oracle first commits the transaction, and then executes and commits the DDL statement as a new, single statement transaction.

A user disconnects from Oracle. (The current transaction is committed.)

A user process terminates abnormally. (The current transaction is rolled back.)

After one transaction ends, the next executable SQL statement automatically starts the following transaction.

Note: Applications should always explicitly commit or roll back transactions before program termination.

Committing Transactions

Committing a transaction means making permanent the changes performed by the SQL statements within the transaction.

Before a transaction that has modified data is committed, the following will have occurred:

Oracle has generated rollback segment records in rollback segment buffers of the SGA. The rollback information contains the old data values changed by the SQL statements of the transaction.

Oracle has generated redo log entries in the redo log buffers of the SGA. These changes may go to disk before a transaction is committed.

The changes have been made to the database buffers of the SGA. These changes may go to disk before a transaction actually is committed.

After a transaction is committed, the following occurs:

The internal transaction table for the associated rollback segment records that the transaction has committed, and the corresponding unique system change number (SCN) of the transaction is assigned and recorded in the table.

LGWR writes the redo log entries in the redo log buffers of the SGA to the online redo log file. LGWR also writes the transaction's SCN to the online redo log file. This is the atomic event that constitutes the commit of the transaction.

Oracle releases locks held on rows and tables (see "Locking Mechanisms" for a discussion of locks).

Oracle marks the transaction "complete".

Note: 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 DBWR background process. This action takes place when it is most efficient to do so. As mentioned above, this may happen before the transaction commits or, alternatively, it may happen some time after the transaction commits. See "Oracle Processes"

for more information about the LGWR and DBWR.

Rolling Back Transactions

Rolling back means undoing any changes to data that have been performed by SQL statements within an uncommitted transaction.

Oracle allows you to roll back an entire uncommitted transaction. Alternatively, you can roll back the trailing portion of an uncommitted transaction to a marker called a savepoint; see the following section, "Savepoints", for a complete explanation of savepoints.

In rolling back an entire transaction, without referencing any savepoints, the following occurs:

Oracle undoes all changes made by all the SQL statements in the transaction by using the corresponding rollback segments.

Oracle releases all the transaction's locks of data (see "Locking Mechanisms" for a discussion of locks).

The transaction ends.

In rolling back a transaction to a savepoint, the following occurs:

Oracle rolls back only the statements executed after the savepoint.

The specified savepoint is preserved, but all savepoints that were established after the specified one are lost.

Oracle releases all table and row locks acquired since that savepoint, but retains all data locks acquired previous to the savepoint (see "Locking Mechanisms" for a discussion of locks).

The transaction remains active and can be continued.

Savepoints

Intermediate markers or savepoints can be declared within the context of a transaction. You use savepoints to divide a long transaction into smaller parts.

Using savepoints, you can arbitrarily mark your work at any point within a long transaction. This allows you the option of later rolling back work performed before the current point in the transaction (the end of the transaction) but after a declared savepoint within the transaction. For example, you can use savepoints throughout a long complex series of updates so that if you make an error, you do not need to resubmit every statement.

Savepoints are also useful in application programs in a similar way. If a procedure contains several functions, you can create a savepoint before each function begins. Then, if a function fails, it is easy to return the data to its state before the function began and then to re-execute the function with revised parameters or perform a recovery action.

After a rollback to a savepoint, Oracle releases the data locks obtained by rolled back statements. Other transactions that were waiting for the previously locked resources can proceed. Other transactions that want to update previously locked rows can do so.