15 Developing Applications with Oracle XA

DTP Terminology

This section introduces you to key terminology in distributed transaction processing.

Resource Manager (RM)

A resource manager controls a shared, recoverable resource that can be returned to a consistent state after a failure. Examples are relational databases, transactional queues, and transactional file systems. Oracle Database is an RM and uses its online redo log and undo segments to return to a consistent state after a failure.

Distributed Transaction

A distributed transaction, also called a global transaction, is a client transaction that involves updates to multiple distributed resources and requires an "all-or-none" semantics across distributed RMs.

Branch

A branch is a unit of work contained within one RM. Multiple branches make up one global transaction. In the case of Oracle Database, each branch maps to a local transaction inside the database server.

Transaction Manager (TM)

A transaction manager provides an API for specifying the boundaries of the transaction and manages commit and recovery. The TM implements a two-phase commit engine to provide an "all-or-none" semantics across distributed RMs.

An external TM is a middle-tier component that resides outside of Oracle Database. Normally, the database is its own internal TM. Using a standards-based TM enables Oracle Database to cooperate with other heterogeneous RMs in a single transaction.

Transaction Processing Monitor (TPM)

A TM is usually provided by a transaction processing monitor (TPM) vendor. A TPM coordinates the flow of transaction requests between the client processes that issue requests and the back-end servers that process them. Basically, a TPM coordinates transactions that require the services of several different types of back-end processes, such as application servers and RMs distributed over a network.

The TPM synchronizes any commits or rollbacks required to complete a distributed transaction. The TM portion of the TPM is responsible for controlling when distributed commits and rollbacks take place. Thus, if a distributed application program takes advantage of a TPM, then the TM portion of the TPM is responsible for controlling the two-phase commit protocol. The RMs enable the TMs to perform this task.

Because the TM controls distributed commits or rollbacks, it must communicate directly with Oracle Database (or any other RM) through the XA interface. It uses Oracle XA library subroutines, which are described in "XA Library Subroutines", to tell Oracle Database how to process the transaction, based on its knowledge of all RMs in the transaction.

Two-Phase Commit Protocol

The Oracle XA library interface follows the two-phase commit protocol. The sequence of events is as follows:

1. In the prepare phase, the TM asks each RM to guarantee that it can commit any part of the transaction. If this is possible, then the RM records its prepared state and replies affirmatively to the TM. If it is not possible, then the RM may roll back any work, reply negatively to the TM, and forget about the transaction. The protocol allows the application, or any RM, to roll back the transaction unilaterally until the prepare phase completes.

2. In phase two, the TM records the commit decision and issues a commit or rollback to all RMs participating in the transaction. TM can issue a commit for an RM only if all RMs have replied affirmatively to phase one.

Application Program (AP)

An application program defines transaction boundaries and specifies actions that constitute a transaction. For example, an AP can be a precompiler or OCI program. The AP operates on the RM's resource through its native interface, for example, SQL.

TX Interface

An application program starts and completes all transaction control operations through the TM by means of an interface called TX. The AP does not directly use the XA interface. APs are not aware of branches that fork in the middle-tier: application threads do not explicitly join, leave, suspend, and resume branch work, instead the TM portion of the transaction processing monitor manages the branches of a global transaction for APs. Ultimately, APs call the TM to commit all-or-none.

Tight and Loose Coupling

Application threads are tightly coupled if the RM considers them as a single entity for all isolation semantic purposes. Tightly coupled branches must see changes in each other. Furthermore, an external client must either see all changes of a tightly coupled set or none of the changes. If application threads are not tightly coupled, then they are loosely coupled.

Dynamic and Static Registration

Oracle Database supports both dynamic and static registration. In dynamic registration, the RM executes an application callback before starting any work. In static registration, you must call xa_start() for each RM before starting any work, even if some RMs are not involved.

Required Public Information

As a resource manager, Oracle Database is required to publish the information described in Table 15-1.

XA Library Subroutines

XA Library subroutines are described in Table 15-2.

In general, the AP does not need to worry about the subroutines in Table 15-2 except to understand the role played by the xa_open() string.

Extensions to the XA Interface

Oracle Database's XA interface includes some additional functions, which are described in Table 15-3.

Responsibilities of the DBA or System Administrator

The responsibilities of the DBA or system administrator are as follows:

1. Define the open string, with help from the application developer. This task is described in "Defining the xa_open() String".

2. Make sure the DBA_PENDING_TRANSACTIONS view exists and grant the SELECT privilege to the view for all Oracle users specified in the xa_open() string.

   In Oracle Database version 7 client applications, all Oracle Database accounts used by Oracle XA library must have the SELECT privilege on the V$XATRANS$ view. This view should have been created during the XA library installation. If necessary, you can manually create the view by running the SQL script xaview.sql as Oracle Database user SYS.

   See Also:

   Your Oracle Database platform-specific documentation for the location of the catxpend.sql script

3. Using the open string information, install the RM into the TPM configuration. Follow the TPM vendor instructions.

   The DBA or system administrator should be aware that a TPM system starts the process that connects to Oracle Database. Refer to your TPM documentation to determine what environment exists for the process and what user ID it will have. Be sure that correct values are set for $ORACLE_HOME and $ORACLE_SID in this environment.

4. Grant the user ID write permission to the directory in which the system will write the XA trace file.

   See Also:

   "Defining the xa_open() String" for information on how to specify an Oracle System Identifier (SID) or a trace directory that is different from the defaults

5. Start the relevant database instances to bring Oracle XA applications on-line. You should perform this task before starting any TPM servers.

Responsibilities of the Application Developer

The responsibilities of the application developer are as follows:

1. Define the open string with help from the DBA or system administrator, as explained in "Defining the xa_open() String".

2. Develop the applications.

   Observe special restrictions on transaction-oriented SQL statements for precompilers.

   See Also:

   "Interfacing XA with Precompilers and OCI"

3. Link the application according to TPM vendor instructions.

Defining the xa_open() String

The open string is used by the transaction monitor to open the database. The maximum number of characters in an open string is 256.

This section covers:

• Syntax of the xa_open() String

• Required Fields for the xa_open() String

• Optional Fields for the xa_open() String

ORACLE_XA{+required_fields...} [+optional_fields...]

ORACLE_XA+DB=MANAGERS+SqlNet=SID1+ACC=P/scott/tiger
  +SesTM=10+LogDir=/usr/local/xalog
ORACLE_XA+DB=PAYROLL+SqlNet=SID2+ACC=P/scott/tiger
  +SesTM=10+LogDir=/usr/local/xalog
ORACLE_XA+SqlNet=SID3+ACC=P/scott/tiger
  +SesTM=10+LogDir=/usr/local/xalog

Interfacing XA with Precompilers and OCI

This section describes how to use the Oracle XA library with precompilers and Oracle Call Interface (OCI). It contains the following subsections:

• Using Precompilers with the Oracle XA Library

• Using OCI with the Oracle XA Library

ORACLE_XA+SqlNet=maildb+ACC=P/scott/tiger
  +SesTM=10+LogDir=/usr/local/logs

EXEC SQL UPDATE Emp_tab SET Sal = Sal*1.5;

ORACLE_XA+DB=MANAGERS+SqlNet=SID1+ACC=P/scott/tiger
  +SesTM=10+LogDir=/usr/local/xalog
ORACLE_XA+DB=PAYROLL+SqlNet=SID2+ACC=P/scott/tiger
  +SesTM=10+LogDir=/usr/local/xalog
ORACLE_XA+SqlNet=SID3+ACC=P/scott/tiger
  +SesTM=10+LogDir=/usr/local/xalog

EXEC SQL DECLARE PAYROLL DATABASE;
EXEC SQL DECLARE MANAGERS DATABASE;

EXEC SQL AT PAYROLL UPDATE Emp_Tab SET Sal=4500 WHERE Empno=7788;
EXEC SQL AT MANAGERS UPDATE Emp_Tab SET Mgr=7566 WHERE Empno=7788;
EXEC SQL UPDATE Emp_Tab SET Deptno=30 WHERE Empno=7788;

EXEC SQL BEGIN DECLARE SECTION;
  DB_NAME1 CHARACTER(10);
  DB_NAME2 CHARACTER(10);
EXEC SQL END DECLARE SECTION;
    ...
SET DB_NAME1 = 'PAYROLL'
SET DB_NAME2 = 'MANAGERS'
    ...
EXEC SQL AT :DB_NAME1 UPDATE...
EXEC SQL AT :DB_NAME2 UPDATE...

Managing Transaction Control with XA

When you use the XA library, transactions are not controlled by the SQL statements that commit or roll back transactions. Rather, they are controlled by an API accepted by the TM that starts and stops transactions. You call the API that is provided by the transaction manager, including the TX interface listed in Table 15-6, but not the XA Library Subroutines listed in Table 15-2.

The TMs typically control the transactions through the XA interface. This interface includes the functions described in Table 15-2, "XA Library Subroutines".

Most TPM applications use a client/server architecture in which an application client requests services and an application server provides them. The examples shown in "Examples of Precompiler Applications" use such a client/server model. A service is a logical unit of work, which in the case of Oracle Database as the resource manager, comprises a set of SQL statements that perform a related unit of work.

For example, when a service named "credit" receives an account number and the amount to be credited, it executes SQL statements to update information in certain tables in the database. In addition, a service might request other services. For example, a "transfer fund" service might request services from a "credit" and "debit" service.

Typically, application clients request services from the application servers to perform tasks within a transaction. For some TPM systems, however, the application client itself can offer its own local services. As shown in "Examples of Precompiler Applications", you can encode transaction control statements within either the client or the server.

To have more than one process participating in the same transaction, the TPM provides a communication API that enables transaction information to flow between the participating processes. Examples of communications APIs include RPC, pseudo-RPC functions, and send/receive functions.

Because the leading vendors support different communication functions, the examples that follow use the communication pseudo-function tpm_service to generalize the communications API.

X/Open includes several alternative methods for providing communication functions in their preliminary specification. At least one of these alternatives is supported by each of the leading TPM vendors.

/***** Client: *****/
tpm_service("ServiceName");              /*Request Service*/

/***** Server: *****/
ServiceName()
{
  <get service specific data>
  tx_begin();                             /* Begin transaction boundary */
  EXEC SQL UPDATE ....;

  /* This application server temporarily becomes */
  /* a client and requests another service. */

  tpm_service("AnotherService");
  tx_commit();                             /* Commit the transaction */
  <return service status back to the client>
}

/***** Client: *****/
tx_begin();                            /* Begin transaction boundary */
tpm_service("Service1");
tpm_service("Service2");
tx_commit();                           /* Commit the transaction */

/***** Server: *****/
Service1()
{
  <get service specific data>
  EXEC SQL UPDATE ....;
  <return service status back to the client>
}
Service2()
{
  <get service specific data>
  EXEC SQL UPDATE ....;
  ...
  <return service status back to client>
}

Migrating Precompiler or OCI Applications to TPM Applications

To migrate existing precompiler or OCI applications to a TPM application that uses the Oracle XA library, you must do the following:

1. Reorganize the application into a framework of "services" so that application clients request services from application servers. Some TPMs require the application to use the tx_open and tx_close functions, whereas other TPMs do the logon and logoff implicitly.

   If you do not specify the SqlNet parameter in your open string, then the application uses the default Oracle Net driver. Thus, be sure that the application server is brought up with the ORACLE_HOME and ORACLE_SID environment variables properly defined. This is accomplished in a TPM-specific fashion. Refer to your TPM vendor documentation for instructions on how to accomplish this.

2. Ensure that the application replaces the regular connect and disconnect statements. For example, replace the connect statements EXEC SQL CONNECT (for precompilers) or OCISessionBegin(), OCIServerAttach(), and OCIEnvCreate() (for OCI) with tx_open(). Replace the disconnect statements EXEC SQL COMMIT/ROLLBACK WORK RELEASE (for precompilers) or OCISessionEnd()/OCIServerDetach (for OCI) with tx_close().

3. Ensure that the application replaces the regular commit or rollback statements for any global transactions and begins the transaction explicitly.

   For example, replace the COMMIT/ROLLBACK statements EXEC SQL COMMIT/ROLLBACK WORK (for precompilers), or OCITransCommit()/OCITransRollback() (for OCI) with tx_commit()/tx_rollback() and start the transaction by calling tx_begin().

   Note:

   The preceding is only true for global rather than local transactions. You should commit or roll back local transactions with the Oracle API.

4. Ensure that the application resets the fetch state before ending a transaction. In general, you should use release_cursor=no. Use release_cursor=yes only when you are certain that a statement will be executed only once.

Table 15-7 lists the TPM functions that replace regular Oracle Database commands when migrating precompiler or OCI applications to TPM applications.

Managing XA Library Thread Safety

If you use a transaction monitor that supports threads, then the Oracle XA library enables you to write applications that are thread safe. Nevertheless, you should keep certain issues in mind.

A thread of control (or thread) refers to the set of connections to resource managers. In an nonthreaded system, each process could be considered a thread of control because each process has its own set of connections to RMs and maintains its own independent resource manager table. In a threaded system, each thread has an autonomous set of connections to RMs and each thread maintains a private RM table. This private table must be allocated for each new thread and de-allocated when the thread terminates, even if the termination is abnormal.

Accessing XA Trace Files

The Oracle XA library logs any error and tracing information to its trace file. This information is useful in supplementing the XA error codes. For example, it can indicate whether an xa_open() failure is caused by an incorrect open string, failure to find the Oracle Database instance, or a logon authorization failure.

The name of the trace file is xa_db_namedate.trc, where db_name is the database name specified in the open string field DB=db_name, and date is the date when the information is logged to the trace file. If you do not specify DB=db_name in the open string, then it automatically defaults to the name NULL.

For example, xa_NULL06022005.trc indicates a trace file that was created on June 2, 2005. Its DB field was not specified in the open string when the resource manager was opened. The filename xa_Finance12152004.trc indicates a trace file was created on December 15, 2004. Its DB field was specified as "Finance" in the open string when the resource manager was opened.

Suppose that a trace file contains the following contents:

1032.12345.2:  ORA-01017:  invalid username/password;  logon denied
1032.12345.2:  xaolgn:  XAER_INVAL;  logon denied

Table 15-8 explains the meaning of each element.

Managing In-Doubt or Pending Transactions

In-doubt or pending transactions are transactions that have been prepared but not yet committed to the database. In general, the TM provided by the TPM system should resolve any failure and recovery of in-doubt or pending transactions. The DBA may have to override an in-doubt transaction if the following situations occur:

• It is locking data that is required by other transactions.

• It is not resolved in a reasonable amount of time.

Refer to the TPM documentation for more information about overriding in-doubt transactions in such circumstances and about how to decide whether the in-doubt transaction should be committed or rolled back.

Using SYS Account Tables to Monitor XA Transactions

The following views under the Oracle Database SYS account contain transactions generated by regular Oracle Database applications and Oracle XA applications:

• DBA_PENDING_TRANSACTIONS

• V$GLOBAL_TRANSACTION

• DBA_2PC_PENDING

• DBA_2PC_NEIGHBORS

For transactions generated by Oracle XA applications, the following column information applies specifically to the DBA_2PC_NEIGHBORS table:

• The DBID column is always xa_orcl

• The DBUSER_OWNER column is always db_namexa.oracle.com

Remember that the db_name is always specified as DB=db_name in the open string. If you do not specify this field in the open string, then the value of this column is NULLxa.oracle.com for transactions generated by Oracle XA applications.

For example, you could use the following SQL statement to obtain more information about in-doubt transactions generated by Oracle XA applications.

SELECT * 
FROM DBA_2PC_PENDING p, DBA_2PC_NEIGHBORS n
WHERE p.LOCAL_TRAN_ID = n.LOCAL_TRAN_ID
AND n.DBID = 'xa_orcl';

Alternatively, if you know the format ID used by the transaction processing monitor, then you can use DBA_PENDING_TRANSACTIONS or V$GLOBAL_TRANSACTION. Whereas DBA_PENDING_TRANSACTIONS gives a list of prepared transactions, V$GLOBAL_TRANSACTION provides a list of all active global transactions.

Using Database Links in XA Applications

Oracle XA applications can access other Oracle Database instances through database links with the following restrictions:

• They must use the shared server configuration.

  The transaction processing monitors (TPMs) use shared servers to open the connection to an Oracle Database A. Then the operating system network connection required for the database link is opened by the dispatcher instead of a dedicated server process. This allows different services or threads to operate on the transaction.

  If this restriction is not satisfied, then when you use database links within an XA transaction, it creates an operating system network connection between the dedicated server process and the other Oracle Database B. Because this network connection cannot be moved from one dedicated server process to another, you cannot detach from this dedicated server process of database A. Then when you access the database B through a database link, you receive an ORA-24777 error.

• The other database being accessed should be another Oracle Database.

Assuming that these restrictions are satisfied, Oracle Database allows such links and propagates the transaction protocol (prepare, rollback, and commit) to the other Oracle Database instances.

If using the shared server configuration is not possible, then access the remote database through the Pro*C/C++ application by using EXEC SQL AT syntax.

The init.ora parameter OPEN_LINKS_PER_INSTANCE specifies the number of open database link connections that can be migrated. These dblink connections are used by XA transactions so that the connections are cached after a transaction is committed. Another transaction is free to use the database link connection provided the user that created the connection is the same as the user who created the transaction. This parameter is different from the init.ora parameter OPEN_LINKS, which specifies the maximum number of concurrent open connections (including database links) to remote databases in one session. The OPEN_LINKS parameter is not applicable to XA applications.

Managing Transaction Branches in XA Applications

Oracle Database transaction branches within the same global transaction can be coupled tightly or loosely. If the transaction branches are tightly coupled, then they share locks. Consequently, pre-COMMIT updates in one transaction branch are visible in other branches that belong to the same global transaction. In loosely coupled transaction branches, the branches do not share locks and do not see updates in other branches.

In a tightly coupled branch, Oracle Database obtains the DX lock before executing any statement. Because the system does not obtain a lock before executing the statement, loosely coupled transaction branches result in greater concurrency. The disadvantage is that all transaction branches must go through the two phases of commit, that is, the system cannot use XA one-phase optimization.

Table 15-9 summarizes the trade-offs between tightly coupled branches and loosely coupled branches.

Using XA with Oracle Real Application Clusters

This section contains the following topics:

• Managing Transaction Branches on Oracle Real Application Clusters (RAC)

• Managing Instance Recovery in Real Application Clusters

• Global Uniqueness of XIDs in Real Application Clusters

SQL-Based XA Restrictions

This section describes restrictions concerning the following SQL operations:

• Rollbacks and Commits

• DDL Statements

• Session State

• EXEC SQL

Miscellaneous Restrictions

Note the following restrictions:

• Oracle Database does not support association migration (a means whereby a transaction manager may resume a suspended branch association in another branch).

• The optional XA feature asynchronous XA calls is not supported.

• Set the TRANSACTIONS initialization parameter to the expected number of concurrent global transactions. The initialization parameter OPEN_LINKS_PER_INSTANCE specifies the number of open database link connections that can be migrated. These database link connections are used by XA transactions so that the connections are cached after a transaction is committed.

  See Also:

  "Using Database Links in XA Applications"3

• The maximum number of xa_open() calls for each thread is 32.

• When building an XA application based on TP-monitor, ensure that the TP-monitors libraries (that define the symbols ax_reg and ax_unreg) are placed in the link line before Oracle Database's client shared library. If your platform does not support shared libraries or if your linker is not sensitive to ordering of libraries in the link line, use Oracle Database's non-shared client library. These link restrictions are applicable only when using XA's dynamic registration (Oracle XA switch xaoswd).