Oracle7 Server Tuning

Contents Index Home Previous Next

Discrete Transactions

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

Deciding When to Use Discrete Transactions

This streamlined transaction processing is useful for transactions that

In deciding to use discrete transactions, you should consider the following factors:

Discrete transactions can be used concurrently with standard transactions. Choosing whether to use discrete transactions should be a part of your normal tuning procedure. Although discrete transactions can only be used for a subset of all transactions, for sophisticated users with advanced application requirements, where speed is the most critical factor, the performance improvements can make working within the design constraints worthwhile.

How Discrete Transactions Work

During a discrete transaction, all changes made to any data are deferred until the transaction commits. Redo information is generated, but is stored in a separate location in memory.

When the transaction issues a commit request, the redo information is written to the redo log file (along with other group commits) and the changes to the database block are applied directly to the block. The block is written to the database file in the usual manner. Control is returned to the application once the commit completes. This eliminates the need to generate undo information since the block is not actually modified until the transaction is committed, and the redo information is stored in the redo log buffers.

As with other transactions, the uncommitted changes of a discrete transaction are not visible to concurrent transactions. For regular transactions, undo information is used to re-create old versions of data for queries that require a consistent view of the data. Because no undo information is generated for discrete transactions, a discrete transaction that starts and completes during a long query can cause the query to receive the "snapshot too old" error if the query requests data changed by the discrete transaction. For this reason, you might want to avoid performing queries that access a large subset of a table that is modified by frequent discrete transactions.

To use the BEGIN_DISCRETE_TRANSACTION procedure, the DISCRETE_TRANSACTIONS_ENABLED initialization parameter must be set to TRUE. If this parameter is set to FALSE, all calls to BEGIN_DISCRETE_TRANSACTION are ignored and transactions requesting this service are handled as standard transactions. See Oracle7 Server Reference for more information about setting initialization parameters.

Errors During Discrete Transactions

Any errors encountered during processing of a discrete transaction cause the predefined exception DISCRETE_TRANSACTION_FAILED to be raised. These errors include the failure of a discrete transaction to comply with the usage notes outlined below. (For example, calling BEGIN_DISCRETE_TRANSACTION after a transaction has begun, or attempting to modify the same database block more than once during a transaction, raises the exception.)

Usage Notes

The BEGIN_DISCRETE_TRANSACTION procedure must be called before the first statement in a transaction. The call to this procedure is effective only for the duration of the transaction (that is, once the transaction is committed or rolled back, the next transaction is processed as a standard transaction).

Transactions that use this procedure cannot participate in distributed transactions.

Although discrete transactions cannot see their own changes, you can obtain the old value and lock the row, using the FOR UPDATE clause of the SELECT statement, before updating the value.

Because discrete transactions cannot see their own changes, a discrete transaction cannot perform inserts or updates on both tables involved in a referential integrity constraint.

For example, assume the EMP table has a foreign key constraint on the DEPTNO column that refers to the DEPT table. A discrete transaction cannot attempt to add a department into the DEPT table and then add an employee belonging to that department because the department is not added to the table until the transaction commits and the integrity constraint requires that the department exist before an insert into the EMP table can occur. These two operations must be performed in separate discrete transactions.

Because discrete transactions can change each database block only once, certain combinations of data manipulation statements on the same table are better suited for discrete transactions than others. One INSERT statement and one UPDATE statement used together are the least likely to affect the same block. Multiple UPDATE statements are also unlikely to affect the same block, depending on the size of the affected tables. Multiple INSERT statements (or INSERT statements that use queries to specify values), however, are likely to affect the same database block. Multiple DML operations performed on separate tables do not affect the same database blocks, unless the tables are clustered.

Example

An example of a transaction type that uses the BEGIN_DISCRETE_TRANSACTION procedure is an application for checking out library books. The following procedure is called by the library application with the book number as the argument. This procedure checks to see if the book is reserved before allowing it to be checked out. If more copies of the book have been reserved than are available, the status RES is returned to the library application, which calls another procedure to reserve the book, if desired. Otherwise, the book is checked out and the inventory of books available is updated.

CREATE PROCEDURE checkout (bookno IN NUMBER (10)
                           status OUT VARCHAR(5)) 
AS
DECLARE 
   tot_books   NUMBER(3); 
   checked_out NUMBER(3); 
   res        NUMBER(3); 
BEGIN 
   dbms_transaction.begin_discrete_transaction; 
   FOR i IN 1 .. 2 LOOP 
      BEGIN 
         SELECT total, num_out, num_res 
           INTO tot_books, checked_out, res 
           FROM books 
           WHERE book_num = bookno 
           FOR UPDATE; 
      IF res >= (tot_books - checked_out) 
      THEN 
         status := 'RES'; 
      ELSE 
         UPDATE books SET num_out = checked_out + 1 
            WHERE book_num = bookno; 
         status := 'AVAIL' 
      ENDIF; 
      COMMIT; 
      EXIT; 
   EXCEPTION 
      WHEN dbms_transaction.discrete_transaction_failed THEN 
         ROLLBACK; 
      END; 
   END LOOP; 
END;

Note the above loop construct. If the exception DISCRETE_TRANSACTION_FAILED occurs during the transaction, the transaction is rolled back, and the loop executes the transaction again. The second iteration of the loop is not a discrete transaction because the ROLLBACK statement ended the transaction; the next transaction processes as a standard transaction. This loop construct ensures that the same transaction is attempted again in the event of a discrete transaction failure.

Contents Index Home Previous Next