0678-0680

Previous Table of Contents Next

Page 678

By changing the read-consistency from statement level to transaction level, you force the current transaction to ignore any changes made to the database during this transaction and view the data as it existed immediately before the transaction started. This mode is helpful if you are executing long running reports against tables that might change during the duration of the report.

When you are creating a read-only transaction, two major changes take effect. First, the number of commands available to the read-only transaction is limited. Second, because the process is literally read-only, it does not require additional locks against tables and does not acquire a rollback segment or redo log. This is helpful because it limits the processing overhead from the database associated with normal transactions.

Please refer to the command reference at the end of this chapter for a list of commands to which read-only transactions are limited.

Remote Transactions

Remote transactions are transactions containing single or multiple statement(s) to be executed against a non-local database. These statements all reference the same node. If they do not, they are considered separate remote transactions, and the instance will split them up. One of the major differences between remote and normal transactions is that redo and rollback information against a remote transaction is stored on the remote database. None of this information is transferred to your local database to be used for recovery.

Read-Consistency

Read-consistency is not a difficult concept to grasp. In short, read-consistency guarantees that the data you are viewing while executing a transaction does not change during that transaction. With read-consistency, if two users are updating the same table at the same time, user1 will not see the changes made by the other user during their transaction. Likewise, user2 cannot see any changes committed by user1 until both transactions are complete. If they happen to be working on the same row in the table, this becomes a locking issue instead of read- consistency.

Read-consistency is the major building block that enables multiple users to update, select, and delete from the same tables without having to keep a private copy for each user. When combined with locking techniques, read-consistency provides the foundation for a multiuser database in which users can do similar or identical operations without difficulty.

Take a look at an example of the way read-consistency works in a theoretical telemarketing department. User1 is entering an order for a customer, while user2 is changing customer information. Both users have concurrent transactions (they are executing at the same time), but user1 began his transaction first. Suppose that user2 makes a mistake and changes the phone number for the same customer whose order is being entered. Because user1 began his transaction first, he will always be looking at the "before picture" of the customer data and will see the

Page 679

customer's previous phone number when querying the user's data. This is true even if user2 commits his changes. Why? Because it is possible that user1's transaction is solely dependent on the data that existed when his transaction began. Imagine the confusion that would result if data could be changed while an already executing query were making changes based on that data! It would be nearly impossible to guarantee the coordination and functioning of all processing within the application.

Read-consistency is also a secondary function of rollback segments. Aside from making it possible to undo changes from a transaction, they also provide other users with a "before picture" of the data being modified by any process. If a transaction must review data that has been modified by a concurrent uncommitted transaction, it must look in the rollback segments to find that data.

Steps to Processing a Transaction

Understanding the steps followed during the execution of a transaction can be quite helpful in planning and implementing custom applications. It is also important for the database administrator to know these steps because they can help in understanding and tuning the database parameters and processes. This discussion covers normal transactions. Other transactions ”such as distributed, remote, and discreet ”are treated a bit differently because these transactions are short in nature, and those differences are documented throughout this chapter. The processing steps follow:

1. DML/DDL statement is entered.
2. Rollback segment is assigned or requested .
3. Statement is optimized.
4. Optimizer generates an execution plan.
5. The execution plan is followed to manipulate/return data.
6. Transaction loops through steps 1_5 until commit, rollback, or session termination.

The following sections examine each step individually.

Entering DML/DDL Statements

The issuing of DML or DDL statements can take place through a number of ways, including SQL*Forms, SQL*Plus, and custom C programs. The rules governing the start and end of transactions are the same no matter which way a SQL statement is issued.

Assigning Rollback Segments

Rollback segments are assigned randomly by Oracle at the beginning of each transaction (not session) when the first DML statement is issued, and they are used to roll back the transaction to a specified savepoint or to the beginning of a transaction. The selection of a rollback segment is based on which rollback segments are currently available and how busy each segment

Page 680

is. By default, DDL statements are not issued rollback segments due to the nature of DDL commands. They are, however, issued redo entries so that the modifications can be reapplied if the database must be recovered.

TIP

In an Oracle8 database, you can examine the number of times a particular rollback segment was requested and was waited for. Check the data dictionary view STATS$ROLL for the column TRANS_TBL_WAITS and TRANS_TBL_GETS. If the ratio of GETS to WAITS is high, you may need to increase the number of rollback segments in your database.

Two types of transactions do not acquire rollback segments. These are read-only transactions and remote transactions. Read-only transactions, by their nature, do not modify data, so they do not require rollback segments. Remote transactions actually do acquire rollback segments, but these rollback segments are allocated on the remote database that the transaction is executed on. Distributed transactions are really a form of remote transactions and follow the same rule.

There is no limit on the number of rollback segments a user can access throughout a given session, but only one rollback segment will be used at any given time for any transaction. In other words, a transaction will acquire one and only one rollback segment to be used for the duration of the transaction. Once the transaction is complete, the rollback segment is released. Space used in that rollback segment is dictated by the amount of data that is modified.

Long-Running Transactions and Rollback Segment Allocation

Transactions that modify large amounts of data require larger rollback segments. By using the SET TRANSACTION command, you can specify a specific rollback segment to be used by a given transaction. See the section "The SET TRANSACTION Command Reference" for further explanation of how to do this. It is important to note, however, that a SET TRANSACTION command must be the very first command issued in a transaction. If it is not, an error message will be returned.

Once a transaction is completed, the rollback segment is released. This does not mean that the segment's data is overwritten immediately, though. Sometimes other transactions that started before this transaction finished need access to the unmodified data for read-consistency. In this case, the rollback segment containing the "before picture" will be used. Unfortunately, Oracle does not lock this data into the rollback segment to prevent the data blocks from being reused if needed. If your rollback segments are too small, you may encounter the error rollback
segment too old. If this error occurs, the transaction that received the error is forced to rollback. This error implies two things:

• A rollback segment's extent was reused for a new transaction.
• A transaction that started before the other transaction ended required access to the previously unmodified data for read-consistency.

Previous Table of Contents Next