Oracle7 Server Application Developer's Guide

Controlling Transactions

In general, only application designers using the programming interfaces to Oracle are concerned with which types of actions should be grouped together as one transaction. Transactions must be defined properly so work is accomplished in logical units and data is kept consistent. A transaction should consist of all of the necessary parts for one logical unit of work, no more and no less. Data in all referenced tables should be in a consistent state before the transaction begins and after it ends. Transactions should consist of only the SQL statements or PL/SQL blocks that comprise one consistent change to the data.

A transfer of funds between two accounts (the transaction or logical unit of work), for example, should include the debit to one account (one SQL statement) and the credit to another account (one SQL statement). Both actions should either fail or succeed together as a unit of work; the credit should not be committed without the debit. Other non-related actions, such as a new deposit to one account, should not be included in the transfer of funds transaction.

Improving Performance

In addition to determining which types of actions form a transaction, when you design an application you must also determine if you can take any additional measures to improve performance. You should consider the following performance enhancements when designing and writing your application. Unless otherwise noted, each of these features is described in the Oracle7 Server Tuning manual.

Use the BEGIN_DISCRETE_TRANSACTION procedure to improve the performance of short, non-distributed transactions.

Use the SET TRANSACTION command with the USE ROLLBACK SEGMENT parameter to explicitly assign a transaction to an appropriate rollback segment. This can eliminate the need to dynamically allocate additional extents, which can reduce overall system performance.

Use the SET TRANSACTION command with the ISOLATION LEVEL set to SERIALIZABLE to get ANSI/ISO serializable transactions. See in this chapter, and also Chapter 10 in the Oracle7 Server Concepts manual.

Establish standards for writing SQL statements so that you can take advantage of shared SQL areas. Oracle recognizes identical SQL statements and allows them to share memory areas. This reduces memory storage usage on the database server, thereby increasing system throughput.

Use the ANALYZE command to collect statistics that can be used by Oracle to implement a cost-based approach to SQL statement optimization. You can supply additional "hints" to the optimizer as needed.

Call the DBMS_APPLICATION_INFO.SET_ACTION procedure before beginning a transaction to register and name a transaction for later use when measuring performance across an application. You should specify what type of activity a transaction performs so that the system tuners can later see which transactions are taking up the most system resources.

Increase user productivity and query efficiency by including user-written PL/SQL functions in SQL expressions as described .

Create explicit cursors when writing a PL/SQL application.

When writing precompiler programs, increasing the number of cursors using MAX_OPEN_CURSORS can often reduce the frequency of parsing and improve performance. The use of cursors is described of this Guide.

Committing a Transaction

To commit a transaction, use the COMMIT command. The following two statements are equivalent and commit the current transaction:

COMMIT WORK;

COMMIT;

The COMMIT command allows you to include the COMMENT parameter along with a comment (less than 50 characters) that provides information about the transaction being committed. This option is useful for including information about the origin of the transaction when you commit distributed transactions:

COMMIT COMMENT 'Dallas/Accts_pay/Trans_type 10B';

For additional information about committing in-doubt distributed transactions, see Oracle7 Server Distributed Systems, Volume I.

Rolling Back a Transaction

To roll back an entire transaction or a part of a transaction (that is, to a savepoint), use the ROLLBACK command. For example, either of the following statements rolls back the entire current transaction:

ROLLBACK WORK;

ROLLBACK;

The WORK option of the ROLLBACK command has no function.

To roll back to a savepoint defined in the current transaction, the TO option of the ROLLBACK command must be used. For example, either of the following statements rolls back the current transaction to the savepoint named POINT1:

ROLLBACK TO SAVEPOINT point1;

ROLLBACK TO point1;

For additional information about rolling back in-doubt distributed transactions see Oracle7 Server Distributed Systems, Volume I.

Defining a Transaction Savepoint

To define a savepoint in a transaction, use the SAVEPOINT command. The following statement creates the savepoint named ADD_EMP1 in the current transaction:

SAVEPOINT add_emp1;

If you create a second savepoint with the same identifier as an earlier savepoint, the earlier savepoint is erased. After a savepoint has been created, you can roll back to the savepoint.

There is no limit on the number of active savepoints per session. An active savepoint is one that has been specified since the last commit or rollback.

An Example of COMMIT, SAVEPOINT, and ROLLBACK

The following series of SQL statements illustrates the use of COMMIT, SAVEPOINT, and ROLLBACK statements within a transaction:

SQL Statement Results
SAVEPOINT A; First savepoint of this transaction.
DELETE . . . ; First DML statement of this transaction.
SAVEPOINT b; Second savepoint of this transaction.
INSERT INTO . . . ; Second DML statement of this transaction.
SAVEPOINT c; Third savepoint of this transaction.
UPDATE . . . ; Third DML statement of this transaction.
ROLLBACK TO c; UPDATE statement is rolled back, savepoint C remains defined.
ROLLBACK TO b; INSERT statement is rolled back, savepoint C is lost, savepoint B remains defined.
ROLLBACK TO c; ORA-01086 error; savepoint C no longer defined.
INSERT INTO . . . ; New DML statement in this transaction.
COMMIT; Commits all actions performed by the first DML statement (the DELETE statement) and the last DML statement (the second INSERT statement). All other statements (the second and the third statements) of the transaction had been rolled back before the COMMIT. The savepoint A is no longer active.

Privileges Required for Transaction Management

No privileges are required to control your own transactions; any user can issue a COMMIT, ROLLBACK, or SAVEPOINT statement within a transaction.

*SQL Statement*	*Results*
SAVEPOINT A;	First savepoint of this transaction.
DELETE . . . ;	First DML statement of this transaction.
SAVEPOINT b;	Second savepoint of this transaction.
INSERT INTO . . . ;	Second DML statement of this transaction.
SAVEPOINT c;	Third savepoint of this transaction.
UPDATE . . . ;	Third DML statement of this transaction.
ROLLBACK TO c;	UPDATE statement is rolled back, savepoint C remains defined.
ROLLBACK TO b;	INSERT statement is rolled back, savepoint C is lost, savepoint B remains defined.
ROLLBACK TO c;	ORA-01086 error; savepoint C no longer defined.
INSERT INTO . . . ;	New DML statement in this transaction.
COMMIT;	Commits all actions performed by the first DML statement (the DELETE statement) and the last DML statement (the second INSERT statement). All other statements (the second and the third statements) of the transaction had been rolled back before the COMMIT. The savepoint A is no longer active.