This chapter describes how to use the Oracle XA library. Typically, you use this library in applications that work with transaction monitors. The XA features are most useful in applications in which transactions interact with more than one database.
The chapter includes the following topics:
See Also:
|
The X/Open Distributed Transaction Processing (DTP) architecture defines a standard architecture or interface that enables multiple application programs (APs) to share resources provided by multiple, and possibly different, resource managers (RMs). It coordinates the work between APs and RMs into global transactions.
The Oracle XA library conforms to the X/Open software architecture's XA interface specification. The Oracle XA library is an external interface that enables a non-Oracle, client-side transaction manager (TM) to coordinate global transactions, thereby allowing inclusion of non-Oracle Database RMs in distributed transactions. For example, a client application can manage an Oracle Database transaction and a transaction in an NTFS file system as a single, global transaction.
Figure 15-1 illustrates a possible X/Open DTP model.
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:
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.
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.
Note: The naming conventions for the TX interface and associated subroutines are vendor-specific. For example, thetx_open call may be referred to as tp_open on your system. In some cases, the calls may be implicit, for example, at the entry to a transactional RPC. Refer to the documentation supplied with the transaction processing monitor for details.
|
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.
As a resource manager, Oracle Database is required to publish the information described in Table 15-1.
Table 15-1 Required XA Features Published by Oracle Database
XA Feature | Oracle Database Details |
---|---|
|
The Oracle Database |
|
The Oracle Database resource manager name within the |
Close string |
The close string used by |
Open string |
The format of the open string used by |
Libraries |
Libraries needed to link applications using Oracle XA have platform-specific names. The procedure is similar to linking an ordinary precompiler or OCI program except that you may have to link any TPM-specific libraries. If you are not using |
Requirements |
None. The functionality to support XA is part of both Standard Edition and Enterprise Edition. |
The Oracle XA library subroutines enable a TM to instruct Oracle Database how to process transactions. Generally, the TM must open the resource by using xa_open()
. Typically, the opening of the resource results from the AP's call to tx_open
. Some TMs may call xa_open()
implicitly when the application begins.
Similarly, there is a close (using xa_close()
) that occurs when the application is finished with the resource. The close may occur when the AP calls tx_close
or when the application terminates.
The TM instructs the RMs to perform several other tasks, which include the following:
Starting a new transaction and associating it with an ID
Rolling back a transaction
Preparing and committing a transaction
XA Library subroutines are described in Table 15-2.
Table 15-2 XA Library Subroutines
XA Subroutine | Description |
---|---|
|
Connects to the RM. |
|
Disconnects from the RM. |
|
Starts a new transaction and associates it with the given transaction ID (XID), or associates the process with an existing transaction. |
|
Disassociates the process from the given XID. |
|
Rolls back the transaction associated with the given XID. |
|
Prepares the transaction associated with the given XID. This is the first phase of the two-phase commit protocol. |
|
Commits the transaction associated with the given XID. This is the second phase of the two-phase commit protocol. |
|
Retrieves a list of prepared, heuristically committed, or heuristically rolled back transactions. |
|
Forgets the heuristically completed transaction associated with the given XID. |
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.
Oracle Database's XA interface includes some additional functions, which are described in Table 15-3.
Table 15-3 Additional Functions in the XA Interface for Oracle Database
Function | Description |
---|---|
Returns the OCI service handle for a given XA connection. The |
|
|
Returns the OCI environment handle for a given XA connection. The |
|
Converts an Oracle Database error code to an XA error code (only applicable to dynamic registration). The first parameter is the service handle used to execute the work in the database. The second parameter is the error code that was returned from Oracle Database. Use this function to determine if the error returned from an OCI command was caused because the |
This section discusses developing and installing Oracle XA applications:
The responsibilities of the DBA or system administrator are as follows:
Define the open string, with help from the application developer. This task is described in "Defining the xa_open() String".
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 thecatxpend .sql script
|
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.
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 |
Start the relevant database instances to bring Oracle XA applications on-line. You should perform this task before starting any TPM servers.
The responsibilities of the application developer are as follows:
Define the open string with help from the DBA or system administrator, as explained in "Defining the xa_open() String".
Develop the applications.
Observe special restrictions on transaction-oriented SQL statements for precompilers.
Link the application according to TPM vendor instructions.
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:
You can define an open string with the syntax shown in Example 15-1.
The following strings shows sample parameter settings:
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
The following sections describe valid parameters for the required_fields
and optional_fields
placeholders.
Note:
|
The required_fields
placeholder in Example 15-1 refers to any of the following name-value pairs described in Table 15-4.
Table 15-4 Required Fields for the xa_open() string
Syntax Element | Description |
---|---|
|
Indicates that no explicit user or password information is provided and that the operating system authentication form will be used. For more information refer to Oracle Database Administrator's Guide. |
|
Specifies the username and password for a valid Oracle Database account. For example, |
|
Specifies the maximum number of seconds allowed in a transaction between one service and the next, or between a service and the commit or rollback of the transaction, before the system aborts the transaction. For example, For example, if the TPM uses remote procedure calls between the client and the servers, then The value of |
The optional_fields
placeholder in Example 15-1 refers to any of the following name-value pairs described in Table 15-5.
Table 15-5 Optional Fields in the xa_open() String
Syntax Element | Description |
---|---|
|
Specifies whether local transactions are allowed. The default value is |
|
Indicates the name used by Oracle Database precompilers to identify the database. For example, Application programs that use only the default database for the Oracle Database precompiler (that is, they do not use the The |
|
Specifies the path name on the local machine where the Oracle XA library error and tracing information should be logged. The default is |
|
Specifies whether the application is initialized in object mode. The default value is false. If the application needs to use certain API calls that require object mode, such as |
|
Specifies the number of cursors to be allocated when the database is opened. It serves the same purpose as the precompiler option |
|
Specifies the Oracle Net database link to use to log on to the system. This string should be an entry in You can use the |
|
Specifies whether locks are shared. Oracle Database transaction branches within the same global transaction can be coupled tightly or loosely. If branches are loosely coupled, then they do not share locks. Set the value to |
|
Specifies the number of seconds Oracle Database waits for a transaction branch that is being used by another session before |
|
Specifies whether the application is multithreaded. The default value is |
This section describes how to use the Oracle XA library with precompilers and Oracle Call Interface (OCI). It contains the following subsections:
When used in an Oracle XA application, cursors are valid only for the duration of the transaction. Explicit cursors should be opened after the transaction begins, and closed before the commit or rollback.
You have the following options when interfacing with precompilers:
The following examples use the precompiler Pro*C/C++.
To interface to a precompiler with the default database, make certain that the DB
=db_name field used in the open string is not present. The absence of this field indicates the default connection. Only one default connection is allowed for each process.
The following is an example of an open string identifying a default Pro*C/C++ connection.
ORACLE_XA+SqlNet=maildb+ACC=P/scott/tiger +SesTM=10+LogDir=/usr/local/logs
Note that the DB
=db_name is absent, indicating an empty database ID string.
The syntax of a SQL statement would be:
EXEC SQL UPDATE Emp_tab SET Sal = Sal*1.5;
To interface to a precompiler with a named database, include the DB
=db_name field in the open string. Any database you refer to must reference the same db_name you specified in the corresponding open string.
An application may include the default database as well as one or more named databases.For example, suppose you want to update an employee's salary in one database, his department number (DEPTNO
) in another, and his manager in a third database. You would configure the following open strings in the transaction manager:
Example 15-2 Sample Open String Configuration
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
Note that there is no DB
=db_name field in the last open string in Example 15-2.
In the application server program, you would enter declarations such as:
EXEC SQL DECLARE PAYROLL DATABASE; EXEC SQL DECLARE MANAGERS DATABASE;
Again, the default connection (corresponding to the third open string that does not contain the DB
field) needs no declaration.
When doing the update, you would enter statements similar to the following:
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;
There is no AT
clause in the last statement because it is referring to the default database.
In Oracle Database precompilers release 1.5.3 or later, you can use a character host variable in the AT
clause, as the following example shows:
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...
Caution: Do not have XA applications create connections other than the ones created throughxa_open() . Any work performed on non-XA connections would be outside the global transaction and would have to be committed separately.
|
Oracle Call Interface applications that use the Oracle XA library should not call OCISessionBegin()
to log on to the resource manager. Rather, the logon should be done through the TPM. The applications can execute the function xaoSvcCtx()
to obtain the service context structure when they need to access the resource manager.
In applications that need to pass the environment handle to OCI functions, you can also call xaoEnv()
to find that handle.
Because an application server can have multiple concurrent open Oracle Database resource managers, it should call the function xaoSvcCtx()
with the correct arguments to obtain the correct service context.
See Also: Oracle Call Interface Programmer's Guide for more information about using theOCISvcCtx handle
|
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".
Table 15-6 TX Interface Functions
TX Function | Description |
---|---|
|
Logs into the resource manager(s) |
|
Logs out of the resource manager(s) |
|
Starts a new transaction |
|
Commits a transaction |
|
Rolls back the transaction |
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.
The following examples illustrate precompiler applications. Assume that the application server has already logged onto the RMs system, in a TPM-specific manner. Example 15-3 shows a transaction started by an application server.
Example 15-3 Transaction Started by an Application Server
/***** 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> }
Example 15-4 shows a transaction started by an application client.
Example 15-4 Transaction Started by an Application 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>
}
To migrate existing precompiler or OCI applications to a TPM application that uses the Oracle XA library, you must do the following:
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.
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()
.
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. |
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.
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.
Note: In Oracle Database, each thread that accesses the database must have its own connection. |
The xa_open()
string provides the clause Threads=
. You must specify this clause as true
to enable the use of threads by the TM. The default is false
. In most cases, the TM creates the threads; the application does not know when a new thread is created. Therefore, it is advisable to allocate a service context on the stack within each service that is written for a TM application. Before doing any Oracle Database-related calls in that service, you must call the xaoSvcCtx
function to retrieve the initialized OCI service context. You can then use this context for OCI calls within the service.
The following restrictions apply when using threads:
Any Pro* or OCI code that executes as part of the application server process on the transaction monitor cannot be threaded unless the transaction monitor is explicitly told when each new application thread is started. This is typically accomplished by using a special C compiler provided by the TM vendor.
The Pro* statements EXEC
SQL
ALLOCATE
and EXEC
SQL
USE
are not supported. Therefore, when threading is enabled, you cannot use embedded SQL statements across non-XA connections.
If one thread in a process connects to Oracle Database through XA, then all other threads in the process that connect to Oracle Database must also connect through XA. You cannot connect through EXEC
SQL
CONNECT
in one thread and through xa_open()
in another thread.
This section discusses how to find information in case of problems or system failure. It also discusses trace files and recovery of pending transactions. This section contains the following topics:
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.
Note: Multiple Oracle XA library resource managers with the sameDB field and LogDir field in their open strings log all trace information that occurs on the same day to the same trace file.
|
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.
Table 15-8 Sample Trace File Contents
String | Description |
---|---|
|
The time when the information is logged. |
|
The process ID (PID). |
|
The resource manager ID. |
|
The name of the module. |
|
The error returned as specified in the XA standard. |
|
The Oracle Database information that was returned. |
Normally, the XA trace file is opened only if an error is detected. The xa_open()
string DbgFl
provides a tracing facility to record additional detail about the XA library. By default, its value is zero. You can set it to any combination of the following values:
0x1
, which enables you to trace the entry and exit to each procedure in the XA interface. This value can be useful in seeing exactly which XA calls the TP Monitor is making and which transaction identifier it is generating.
0x2
, which enables you to trace the entry to and exit from other non-public XA library routines. This is generally of use only to Oracle Database developers.
0x4
, which enables you to trace various other "interesting" calls made by the XA library, such as specific calls to the OCI. This is generally of use only to Oracle Database developers.
Note: The flags are independent bits of anub4 , so to obtain printout from two or more flags, you must set a combined value of the flags.
|
The XA application determines a location for the trace file according to the following algorithm:
The LogDir
directory specified in the open string.
If you do not specify LogDir
in the open string, then the Oracle XA application attempts to create the trace file in the following directory (if the Oracle home is accessible):
%ORACLE_HOME%\rdbms\trace
on Windows
$ORACLE_HOME/rdbms/log
on UNIX
If the Oracle XA application cannot determine where the Oracle home is located, then the application creates the trace file in the current working directory.
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.
The following views under the Oracle Database SYS
account contain transactions generated by regular Oracle Database applications and Oracle XA applications:
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_name
xa.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.
This section contains the following topics:
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.
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.
This section contains the following topics:
Oracle Database permits different instances to operate on different transaction branches in RAC. For example, Node 1 can operate on branch A while Node 2 operates on branch B. If transaction branches are on different instances, then they are loosely coupled and do not share locks. In this case, Oracle Database treats different units of work in different application threads as separate entities that do not share resources.
A different case is when multiple instances operate on a single transaction branch. For example, assume that a single transaction lands on Node 1 and Node 2 as follows:
Node 1
xa_start()
SQL operations
xa_end()
(SUSPEND)
Node 2
xa_start()
(RESUME)
xa_prepare()
xa_commit()
xa_end()
In the previous sequence, Oracle Database returns an error because Node 2 should not resume a branch that is physically located on a different node (Node 1).
The way to achieve tight coupling in RAC is to use DTP services, that is, services whose cardinality (one) ensures that all tightly-coupled branches land on the same instance—whether or not load balancing is enabled. Mid-tier components address Oracle Database by means of a common logical database service name that maps to a single RAC instance at any point in time. An intermediate name resolver for the database service hides the physical characteristics of the database instance. DTP services enable all participants of a tightly-coupled global transaction to create branches on one instance.
For example, when you use a DTP service, the following sequence of actions occurs on the same instance:
xa_start()
SQL operations
xa_end()
(SUSPEND)
xa_start()
(RESUME)
SQL operations
xa_prepare()
xa_commit()
or xa_rollback()
Moreover, multiple tightly-coupled branches land on the same instance if each addresses the Oracle RM with the same DTP service.
To leverage all instances in the cluster, create multiple DTP services, with one or more on each node that hosts distributed transactions. All branches of a global distributed transaction exist on the same instance. Thus, you can leverage all instances and nodes of a RAC cluster to balance the load of many distributed XA transactions, thereby maximizing application throughput.
See Also: Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide to learn how to manage distributed transactions in a Real Application Clusters configuration. |
Prior to Oracle Database 10g Release 2 (10.2), the onus of detecting failure and triggering failover and failback in RAC was on the TM. In order to ensure information about in-doubt transactions is propagated to DBA_2PC_PENDING, TM was required to invoke xa_recover()
before TM can proceed to resolve the in-doubt transactions. If an instance failed, then the XA client library could not fail over to another instance until it had run the SYS.DBMS_SYSTEM.DIST_TXN_SYNC
procedure to ensure that the undo segments of the failed instance were recovered. In the current release there is no such requirement to invoke xa_recover()
in cases where the TM has enough information about in-flight transactions.
Note: In releases subsequent to Oracle Database 9i Release 2,xa_recover() is required to wait for distributed DML to complete on remote sites.
|
Using DTP services in RAC has the following benefits:
Automates instance failure detection.
Automates instance failover and failback. When an instance fails, the DTP service hosted on this instance fails over to another instance. The failover forces clients to reconnect; nevertheless, the logical names for the service remain the same. Failover is automatic and does not require an administrator intervention. The administrator can induce failback by a service relocate command, but all failback-related recovery is automatically handled within the database server.
Enables Oracle Database rather than the client to drive instance recovery. The database does not require mid-tier TM involvement to determine the state of transactions prepared by other instances.
See Also: Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide to learn how to manage instance recovery |
The TM must maintain the global uniqueness of transaction IDs (XIDs). According to the XA specification, the RM must accept XIDs from the TM. XA on RAC cannot determine whether a given XID is unique throughout the cluster.
For example, suppose that there is an XID Fmt(x).Tx(1).Br(1)
on RAC instance 1 and another XID Fmt(x).Tx(1).Br(1)
on RAC instance 2. Each of these can start a branch and execute SQL even though the XID is not unique across RAC instances.
This section describes restrictions concerning the following SQL operations:
Because the transaction manager is responsible for coordinating and monitoring the progress of the global transaction, the application should not contain any Oracle Database-specific statement that independently rolls back or commits a global transaction. However, you can use rollbacks and commits in a local transaction.
Do not use EXEC
SQL
ROLLBACK
WORK
for precompiler applications when you are in the middle of a global transaction. Similarly, an OCI application should not execute OCITransRollback()
, or the Version 7 equivalent orol()
. You can roll back a global transaction by calling tx_rollback()
.
Similarly, a precompiler application should not have the EXEC
SQL
COMMIT
WORK
statement in the middle of a global transaction. An OCI application should not execute OCITransCommit
() or the Version 7 equivalent ocom()
. For example, use tx_commit
() or tx_rollback
() to end a global transaction.
Because a DDL SQL statement, such as CREATE
TABLE
, implies an implicit commit, the Oracle XA application cannot execute any DDL SQL statements.
Oracle Database does not guarantee that session state will be valid between TPM services. For example, if a TPM service updates a session variable (such as a global package variable), then another TPM service that executes as part of the same global transaction may not see the change. Use savepoints only within a TPM service. The application must not refer to a savepoint that was created in another TPM service. Similarly, an application must not attempt to fetch from a cursor that was executed in another TPM service.
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.
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
).