Page 687
The following steps illustrate what must take place if a transaction is rolled back:
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:
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. |
Page 688
Once again, look at each step individually in the following sections.
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.
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.
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.
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.
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. |
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.
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.
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.
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.