Oracle8 Tuning Release 8.0 A58246-01 |
Library | Product | Contents | Index

# 11
Oracle8 Transaction Modes

This chapter describes the different modes in which read consistency is performed. Topics in this chapter include:

- Using Discrete Transactions
- Using Serializable Transactions

## Using Discrete Transactions

You can improve the performance of short, nondistributed transactions by using the BEGIN_DISCRETE_TRANSACTION procedure. This procedure streamlines transaction processing so short transactions can execute more rapidly. This section describes:

- Deciding When to Use Discrete Transactions
- How Discrete Transactions Work
- Errors During Discrete Transactions
- Usage Notes
- Example

### Deciding When to Use Discrete Transactions

Discrete transaction processing is useful for transactions that:

- modify only a few database blocks
- never change an individual database block more than once per transaction
- do not modify data likely to be requested by long-running queries
- do not need to see the new value of data after modifying the data
- do not modify tables containing any LONG values

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

- Can the transaction be designed to work within the constraints placed on discrete transactions, as described in "Usage Notes" on page 11-4?
- Does using discrete transactions result in a significant performance improvement under normal usage conditions?

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 be used only 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, because 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 Also: Oracle8 Reference for 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. This call to the 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, some 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 application for checking out library books is an example of a transaction type that uses the BEGIN_DISCRETE_TRANSACTION procedure. 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 DISCRETE_TRANSACTION_FAILED exception 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.

## Using Serializable Transactions

Oracle allows application developers to set the isolation level of transactions. The isolation level determines what changes the transaction and other transactions can see. The ISO/ANSI SQL3 specification details the following levels of transaction isolation.

SERIALIZABLE | Transactions lose no updates, provide repeatable reads, and do not experience phantoms. Changes made to a serializable transaction are visible only to the transaction itself.
READ COMMITTED | Transactions do not have repeatable reads and changes made in this transaction or other transactions are visible to all transactions. This is the default transaction isolation.

If you wish to set the transaction isolation level, you must do so before the transaction begins. Use the SET TRANSACTION ISOLATION LEVEL statement for a particular transaction, or the ALTER SESSION SET ISOLATION_LEVEL statement for all subsequent transactions in the session.

See Also: Oracle8 SQL Reference for more information on the syntax of SET TRANSACTION and ALTER SESSION.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index