Oracle7 Server Concepts Manual Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index

Go to previous file in sequence Go to next file in sequence

Transaction Management

The pigs did not actually work, but directed and supervised the others.

George Orwell: Animal Farm

This chapter defines a transaction and describes how you can manage your work using transactions. It includes:

Introduction to Transactions

A transaction is a logical unit of work that contains one or more SQL statements. A transaction is an atomic unit; the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).

A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly (with a COMMIT or ROLLBACK statement) or implicitly (when a DDL statement is issued).

To illustrate the concept of a transaction, consider a banking database. When a bank customer transfers money from a savings account to a checking account, the transaction might consist of three separate operations: decrement the savings account, increment the checking account, and record the transaction in the transaction journal.

Oracle must allow for two situations. If all three SQL statements can be performed to maintain the accounts in proper balance, the effects of the transaction can be applied to the database. However, if something (such as insufficient funds, invalid account number, or a hardware failure) prevents one or two of the statements in the transaction from completing, the entire transaction must be rolled back so that the balance of all accounts is correct.

Figure 12 - 1 illustrates the banking transaction example.

Figure 12 - 1. A Banking Transaction

Statement Execution and Transaction Control

A SQL statement that "executes successfully" is different from a "committed" transaction.

Executing successfully means that a single statement was parsed and found to be a valid SQL construction, and that the entire statement executed without error as an atomic unit (for example, all rows of a multi-row update are changed). However, until the transaction that contains the statement is committed, the transaction can be rolled back, and all of the changes of the statement can be undone. A statement, rather than a transaction, executes successfully.

Committing means that a user has said either explicitly or implicitly "make the changes in this transaction permanent". The changes made by the SQL statement(s) of your transaction only become permanent and visible to other users after your transaction has been committed. Only other users' transactions that started after yours will see the committed changes.

Statement-Level Rollback

If at any time during execution a SQL statement causes an error, all effects of the statement are rolled back. The effect of the rollback is as if that statement were never executed.

Errors that cause statement rollbacks are errors discovered during the execution stage of SQL statement processing (such as attempting to insert a duplicate value in a primary key or an invalid number into a numeric column), not the parsing stage (such as syntax errors in a SQL statement). Single SQL statements can also be rolled back to resolve deadlocks (competition for the same data); see "Deadlock Detection" [*].

Therefore, a SQL statement that fails causes the loss only of any work it would have performed itself; it does not cause the loss of any work that preceded it in the current transaction. If the statement is a DDL statement, the implicit commit that immediately preceded it is not undone. This is a statement-level rollback.

Note: Users cannot directly refer to implicit savepoints in rollback statements.

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:

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:

After a transaction is committed, the following occurs:

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:

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


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.

Discrete Transaction Management

Application developers can improve the performance of short, non-distributed transactions by using the procedure BEGIN_DISCRETE_TRANSACTION. This procedure streamlines transaction processing so short transactions can execute more rapidly.

During a discrete transaction, all changes made to any data are deferred until the transaction commits. Of course, other concurrent transactions are unable to see the uncommitted changes of a transaction whether the transaction is discreet or not.

Oracle generates redo information, but stores it in a separate location in memory. When the transaction issues a commit request, Oracle writes the redo information to the redo log file (along with other group commits), and applies the changes to the database block directly to the block. Oracle returns control to the application once the commit completes. This eliminates the need to generate undo information, since the block actually is not modified until the transaction is committed, and the redo information is stored in the redo log buffers.

For more information on discrete transactions, see Oracle7 Server Tuning.

Go to previous file in sequence Go to next file in sequence
Prev Next
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index