|Oracle8i Designing and Tuning for Performance
Release 2 (8.1.6)
Part Number A76992-01
This chapter describes the different modes in which read consistency is performed.
This chapter contains the following sections:
You can improve the performance of short, nondistributed transactions by using the
BEGIN_DISCRETE_TRANSACTION procedure. This procedure streamlines transaction processing so that short transactions can execute faster.
This section describes:
Discrete 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. Discrete transactions can be used only for a subset of all transactions, for sophisticated users with advanced application requirements. However, where speed is the most critical factor, the performance improvements can justify the design constraints.
During a discrete transaction, all changes made to any data are deferred until the transaction commits. Redo information is generated, but it 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 after the commit completes. Oracle does not 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 any 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 avoid performing queries that access a large subset of a table that is modified by frequent 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.)
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, after 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
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 that the
emp table has a
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 only affect the same database blocks if the tables are clustered.
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, then 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;
For the above loop construct, if the
DISCRETE_TRANSACTION_FAILED exception occurs during the transaction, then 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.
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.
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.
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 want to set the transaction isolation level, then you must do so before the transaction begins. Use the
LEVEL statement for a particular transaction, or use the
ISOLATION_LEVEL statement for all subsequent transactions in the session.
Oracle8i SQL Reference for more information on the syntax of