0687-0689

Previous Table of Contents Next

Page 687

The following steps illustrate what must take place if a transaction is rolled back:

  1. All changes are rolled back to the previous savepoint and the savepoint is preserved (or the beginning of the transaction if no savepoints have been specified).
  2. If the savepoint is not the last specified savepoint, all savepoints after this one are erased.
  3. Acquired locks are released.
  4. The transaction continues (if no savepoints were specified, the transaction is ended).
  5. If transaction is ended, the rollback segments are released as well, although no SCN is recorded.

Processing a Remote or Distributed Transaction

The steps required to process remote and distributed transactions are nearly identical to the way normal transactions are processed . The biggest difference is where the statement is parsed, and the instance whose resources are used for processing. The following steps add the logic required for remote and distributed transaction processing:

  1. The DML/DDL statement is entered.
  2. If the transaction modifies the data in the current instance, a rollback segment is assigned.
  3. The statement is broken down into separate statements by references to remote database(s).
  4. Any statements that modify local data are optimized.
  5. Statements referencing remote databases are forwarded to the remote database to be optimized.
  6. On the remote databases, statements that require rollback segments or redo information to be stored acquire those resources from themselves .
NOTE
In order for a remote transaction to work, you must have a networking package, such as SQL*Net Version 1 or 2, installed on all systems holding a database that you want to retrieve or update information on. If all databases reside on the same physical system, that system must have SQL*Net installed.

Distributed transactions require SQL*Net as well as Oracle's distributed option installed and working.

Once all of these requirements are met, standard SQL connection strings can be used to access the remote database(s). Refer to Chapter 13 for further information regarding SQL*Net connection strings.

Page 688

  1. The optimizer on each remote database generates an execution plan for received statements.
  2. The execution plans are followed to return data to the database that originally started the remote or distributed transaction.
  3. The local database collects all data returned and sorts through data and finishes processing like joins.
  4. Transaction loops through steps 1_9 until commit, rollback, or session termination.

Once again, look at each step individually in the following sections.

Entering DDL/DML Statements

All statements for remote and distributed transactions are entered on a local database or a database where local data resides. It is not necessary to log in to a database where data will be manipulated in order to issue queries against that database because that is essentially what a remote or distributed transaction is.

Assigning Rollback Segments

Just as in a normal transaction, if any part of the transaction's statements modify data on the local database, a rollback segment is assigned to track any changes made to the data.

Breaking Down Statements

Oracle must break down all statements that query or modify remote data in order to send them as a group to the remote database(s). Once they are grouped according to remote database, the statements are sent, via SQL*Net, to their intended destination.

Optimizing Local Statements

Just as in a normal transaction, the local statements are optimized, based on either the database parameter OPTIMIZER_MODE or the transaction-level parameter OPTIMIZER_GOAL. Once the desired explain plan is created and executed, data is returned and held until all data from remote transactions has been received.

Forwarding Remote Commands

All remote commands are forwarded to the intended database before they are optimized or parsed. Once the remote database has received the information, it acts just as it would on a local database: The statement is parsed, the shared SQL area is searched in order to find an identical parsed representation of the statement, the statement is optimized if necessary, and then the statement is executed.

At this point, all data is returned to the local database user or application. If data is to be compared with other data from the local or another remote database, that action takes place on the local database. The local database is responsible for correlating all returned data to provide the final desired output.

Page 689

NOTE
DDL statements (such as DROP TABLE or ALTER TABLE) are not allowed on remote database. To execute this family of commands, you must log in to the database as a local connection or issue a CONNECT command to access the remote database.

Assigning Remote Rollback Segments and Writing
Redo Logs

All statements that are sent to remote databases to update/manipulate data are assigned a rollback segment on the remote database as they would if manipulating data. The remote database is then responsible for all recovery operations should the database fail or should the transaction require a rollback. Remote transactions function like normal transactions when a commit or rollback statement is issued.

Optimizing Remote Statements

Statements that are sent to remote databases are not parsed by the local database. This is so that the remote database's shared SQL area can be searched for identical statements. Once the statement is parsed, it is either optimized or the optimized execution plan for the identical statement is used. Data is then returned to the local database.

Returning Data to the Local Database

As stated earlier, it is the responsibility of the local database, or the database from which the transaction was initiated, to receive data from all remote databases, correlate it, and return only the data that the original statement requested . This can include joins, WHERE and IN clauses, and GROUP BY statements.

Summarizing Remote and Distributed Transactions

Despite the differences in where the bulk of the transaction processing resides, the steps are much the same. If you're working in a distributed environment, though, you need to take into account quite a few other steps when dealing with complex updates and error handling. Should a transaction be abnormally terminated or an instance in the distributed environment go down, there are quite a few extra steps needed to help decide whether a distributed transaction should be committed or rolled back. It is better to refer to more in-depth documentation to learn more about two-phase commits and exactly how Oracle deals with the problems resulting from a downed instance or terminated session from within a distributed environment.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net