Release 8.0
A54638-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8(TM) Server Tuning Release 8.0 A54638-01 |
|
This chapter describes the different methods in which read consistency is performed. Topics in this chapter include:
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. This section describes:
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.
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 Also: Oracle8 Server Reference Manual for more information about setting initialization parameters.
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.)
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.
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.
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:
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 Server SQL Reference for more information on the syntax of SET TRANSACTION and ALTER SESSION.
