3. XA Interface#

This chapter explains the general concept of distributed transactions, introduces the XA standard, and describes the XA interface.

It explains how to use a global transaction manager to access Altibase via ODBC, JDBC and APRE, and specifies the support for the XA within Altibase. It also describes the limitations of the XA distributed transaction processing model and how to deal with problems that can arise in applications.

XA Interface Overview#

XA is a standard interface that is used for processing distributed transactions (also known as "global transactions"). It was proposed by The Open Group (formerly X/Open).

A distributed transaction (also known as a "global transaction") is a transaction that spans two or more nodes connected via a network. The database systems provide the resources for the transaction, while a TM (Transaction Manager) creates and manages the global transaction, which oversees all operations performed on these resources. The XA standard thus enables distributed applications to share resources provided by multiple database servers, and makes global transactions possible.

XA is a useful construct for applications that process transactions in one or more databases.

Application (AP)
An application defines the necessary transactions and the operations that constitute a transaction. An application can be written using, for example, the precompiler or the ODBC CLI.
Global Transaction
This refers to multiple transactions that are managed as a single transaction by a TM. It is essentially a distributed transaction.
Heuristic Completion
In some situations, when an RM does not receive an expected command pertaining to an in-doubt transaction, such as a COMMIT command or the like, the RM proceeds to commit or roll back the transaction of its own accord. Completion of transactions in this way is referred to as "Heuristic Commit" or "Heuristic Rollback", or collectively as "Heuristic Completion". Typical causes are network failure and transaction timeouts.
In-doubt Transaction
An "in-doubt transaction" is a transaction branch that has been prepared on an RM (i.e. DBMS) and for which a commit or rollback message has not yet been received. It is also known as a "pending transaction".
Resource Manager (RM)
A Resource Manager (RM) controls a resource that is accessed by an XA transaction. It must be possible to restore the resource to its original state in the event of a failure. An RM can be, for example, a relational database, a transactional queue, or a file system.
Transaction Branch
A transaction branch is essentially a sub-transaction that is part of the global transaction. It is executed on one of the Resource Managers (see above) participating in the global transaction. There is a one-to-one relationship between a transaction branch and a so-called "XID" (i.e. a Transaction ID in XA parlance).
Transaction Manager (TM)
A transaction manager provides an API that defines a transaction. It is responsible for committing and rolling back transactions and performing recovery. The TM has a two-phase commit engine to ensure that all of the RMs are consistent with each other.
Transaction Processing Monitor (TPM)
A Transaction Processing Monitor (TPM) coordinates the flow of transaction requests from one or more APs (see above) for resources managed by one or more RMs (see above). The RMs can be heterogeneous, and can be distributed across a network.
The TPM completes distributed transactions by coordinating commit and rollback operations. The TM portion of the TPM is responsible for determining the timing of distributed commit and rollback operations, that is, the TPM is responsible for controlling two-phase commit.
Because the TM manages distributed commit and rollback operations, it must be aware of, and able to communicate directly with, all RMs. The TM uses the XA interface for this. In the case of Altibase, the TM uses XA library functions of Altibase to control transaction processing by Altibase.
TX Interface
An AP (see above) controls a transaction through the TM using the TX interface. An AP does not use the XA interface directly. APs are not aware of the operations of individual transaction branches, and application threads do not participate directly in transaction branch tasks. The branches of a global transaction are managed on behalf of APs by the TM. APs merely request the TM to commit or roll back entire global transactions.

XA Structure#

As shown in the following diagram, the entities involved in a distributed transaction include one or more APs (Applications), the TM (Transaction Manager), and one or more RMs (Resource Managers).

[Figure 3-1] XA Structure

If an AP announces the start of a distributed transaction to the TM using TX interface, the TM determines which RMs (databases) are involved in the distributed transaction. The TM internally generates XIDs to identify the transaction branches that are to be executed in respective RMs, and then calls XA interface with the XIDs to the RMs.

Each RM (DB node) starts processing the transaction branch corresponding to the transmitted XID. In addition, until the request for termination of the transaction is received from the TM, the operation requested from the AP is recognized as the operation in the global transaction corresponding to the XID, and the operation transaction corresponding to the XID, and the operation is performed in the transaction branch.

To terminate the transaction, the AP calls the TM via the TX interface. The TM then uses the XA interface to instruct the RMs on which the branches of the distributed transaction are running to either commit or roll back their respective transaction branches.

XA and 2PC (Two-Phase Commit)#

The XA Interface of Altibase supports 2PC (Two-Phase Commit) transaction processing. Two-phase commit consists of separate prepare and commit steps.

In the prepare step, which is the first step of 2PC, the TM queries all database nodes (RMs) participating in a distributed transaction to determine whether it is possible to commit the transaction. If an individual RM is able to commit the transaction branch that has been assigned to it, it sends a message to the TM indicating that it is in a "prepare" state. If, however, an RM is not able to commit its transaction branch, it sends a corresponding message to the TM so that the transaction can be rolled back.

In the commit step, which is the second step of 2PC, the TM waits until it has received "prepare" acknowledgements from all RMs. If it receives such acknowledgements from all RMs, it sends an instruction to all RMs to commit the transaction. However, if there is even one RM that has not sent a "prepare" acknowledgement, the TM sends an instruction to all RMs to roll back the transaction.

xa_switch_t Structure#

Every RM has a switch that contains various information about the RM, including its entry points. This information is used by the TM. The structure of an RM's switch is known as xa_switch_t.

In Altibase, the name of the xa_switch_t is altibase_xa_switch. Its structure is as follows:

struct xa_switch_t {
    char name[RMNAMESZ];        /* name of resource manager */
    long flags;                 /* resource manager specific options */
    long version;               

    int (*xa_open_entry)(/*_ char *, int, long _*/);            /* xa_open fn pointer */
    int  (*xa_close_entry)(/*_ char *, int, long _*/);          /* xa_close fn pointer */
    int  (*xa_start_entry)(/*_ XID *, int, long _*/);           /* xa_start fn pointer */
    int  (*xa_end_entry)(/*_ XID *, int, long _*/);             /* xa_end fn pointer */
    int  (*xa_rollback_entry)(/*_ XID *, int, long _*/);        /* xa_rollback fn pointer */
    int  (*xa_prepare_entry)(/*_ XID *, int, long _*/);         /* xa_prepare fn pointer */
    int  (*xa_commit_entry)(/*_ XID *, int, long _*/);          /* xa_commit fn pointer */
    int  (*xa_recover_entry)(/*_ XID *, long, int, long _*/);   /* xa_recover fn pointer */
    int  (*xa_forget_entry)(/*_ XID *, int, long _*/);          /* xa_forget fn pointer */
    int  (*xa_complete_entry)(/*_ int *, int *, int, long _*/); /* xa_complete fn pointer */
};

The XA Library#

No additional library is required in order for applications to connect to Altibase using the Altibase XA. The required functionality is included in the odbccli library. All that is needed in order to use the XA-related functionality with Altibase is to link the XA-dependent applications with the libodbccli.a library file.

The XA Interface#

The XA Interface is the two-way interface that sits between the TM and the RMs. This interface consists of xa_ routines, which the TM uses to control RMs so that it can execute global transactions, and ax_ routines, which allow the RMs to make requests to the TM.

Note: Because Altibase does not support dynamic registration, each RM (Altibase database) must be called with xa_start before the start of a transaction.

XA Functions#

In Altibase, the XA-related functions are provided in altibase_xa_switch, which is Altibase's implementation of xa_switch_t

XA Interface	Description
xa_open	This is used to connect to an RM.
xa_close	This is used to close a connection with an RM.
xa_start	This is used to start a new transaction branch or restart an existing one, and to link the branch to a given XID.
xa_end	This is used to end an association with a transaction branch.
xa_rollback	This is used to roll back a transaction branch corresponding to a given XID.
xa_prepare	This is used to prepare a transaction branch to be committed.
xa_commit	This is used to commit a transaction branch.
xa_recover	This is used to show a list of XIDs corresponding to transactions that have been prepared, heuristically committed, or heuristically rolled back.
xa_forget	This is used to instruct an RM to discard information about a heuristically completed transaction branch.

[Table 3‑1] XA Interface

xa_open#

This is used to connect to an RM.

int xa_open(char *xa_info, int rmid, long flags);

xa_info is a null-terminated character string that contains information about the server. Its maximum length is 256 bytes. It has the same format as the parameters to the SQLDriverConnect function, and has the additional parameters XA_NAME and XA_LOG_DIR. For detailed information about the other parameters, please refer to the description of the SQLDriverConnect function in the CLI User's Manual.

NAME=value;NAME=value;NAME=value;…

Ex) DSN=127.0.0.1;UID=SYS;PWD=MANAGER ;XA_NAME=conn1

XA Parameter	Description
XA_NAME	Name used as the identifier for a connection in Altibase Embedded SQL programs. If you omit this value when writing the application with Altibase Embedded SQL, the default connection is used. If a name is specified in the XA_NAME attribute, this name can be used in the AT clause when executing the SQL statement.
XA_LOG_DIR	This is used to specify the directory in which information about Altibase XA library errors is logged. If the $ALTIBASE_HOME environment variable has been set, then the default value of XA_LOG_DIR is $ALTIBASE_HOME/trc. If $ALTIBASE_HOME has not been set, the default is the current directory.

[Table 3-2] Additional XA Interface Parameters

rmid is used to specify an identifier for the server to be accessed. This can be set to any arbitrary value.

If flags is not set to any other value, it must be set to the following value:

TMNOFLAGS

xa_close#

This terminate the connection with the specified RM.

int xa_close(char *xa_info, int rmid, long flags);

xa_info is a null-terminated character string that contains information about the server. Its maximum length is 256 bytes.

Note: XA_OK is returned even if xa_close is executed on a connection that is already closed. flags has no specific purpose in this function, and must be set to the following value:

TMNOFLAGS

xa_start#

This is used to start the execution of a transaction branch. XID is the identifier of a global transaction.

int xa_start(XID *xid, int rmid, long flags);

flags can be set to one or more of the following values.

TMRESUME
This is used to resume execution of a previously suspended transaction branch.
TMNOWAIT
If the execution of xa_start is blocked, this specifies that XA_RETRY is to be returned without waiting.
TMASYNC
This specifies that the transaction branch is to be executed in asynchronous mode (not supported in Altibase).
TMNOFLAGS
If flags is not set to any other value, it must be set to this value.
TMJOIN
This specifies that the transaction branch is to be connected to an existing transaction branch.

xa_end#

This is used to terminate the execution of a transaction branch.

int xa_end(XID *xid, int rmid, long flags);

flags can be set to one or more of the following values:

TMSUSPEND
This specifies that execution of the transaction branch is to be merely suspended, rather than permanently terminated. Execution of this transaction branch can be resumed later using xa_start with the TMRESUME flag.
TMSUCCESS
This is used to specify successful termination of a transaction branch. It can't be used together with TMSUSPEND or TMFAIL.
TMFAIL
This is used to specify abnormal termination of a transaction branch. The status of the transaction branch becomes "rollback only". It can't be used together with TMSUSPEND or TMSUCCESS.

xa_rollback#

This is used to roll back the operations performed by the transaction branch.

int xa_rollback(XID *xid, int rmid, long flags);

flags can be set to one of the following values:

TMASYNC
This specifies that the transaction branch is to be rolled back in asynchronous mode (not supported in Altibase).
TMNOFLAGS
If flags is not set to TMASYNC, it must be set to this value.

xa_prepare#

When using the two-phase commit protocol, this is executed before committing or rolling back a transaction.

int xa_prepare(XID *xid, int rmid, long flags);

flags can be set to one of the following values:

TMASYNC
(not supported in Altibase)
TMNOFLAGS
If flags is not set to TMASYNC, it must be set to this value.

xa_prepare can return the following values:

XA_RDONLY
This is returned when the transaction doesn't change any of the data on the RM (i.e. DBMS). The transaction does not need to be committed or rolled back.
XA_OK
This is returned when the prepare task is performed normally.

xa_commit#

This is used to commit a particular transaction branch.

int xa_commit(XID *xid, int rmid, long flags);

flags can be set to either of the following values:

TMONEPHASE
This is set to specify one-phase commit.
TMNOFLAGS
If flags is not set to any other value, it must be set to this value.

xa_recover#

This obtains a list of the XIDs corresponding to branch transactions that are in a prepared state on an Altibase server.

int xa_recover(XID *xids, long count, int rmid, long flags);

The return value indicates the number of XIDs that were recovered. The count parameter is used to set the maximum number of XIDs that fit into the xids array.

Flags can be set to one or more of the following values:

TMSTARTRSCAN
For more information, please refer to the XA Specification documentation.
TMENDRSCAN
For more information, please refer to the XA Specification documentation.
TMNOFLAGS
XIDs are returned starting at the current cursor position.

xa_forget#

This instructs the Altibase server (i.e. the RM) to stop managing a heuristically completed transaction branch.

int xa_forget(XID * xid, int rmid, long flags);

Flags can be set to the following value:

TMNOFLAGS
Always set to specify this value.

xa_complete#

When operating in asynchronous mode, this is used to determine whether to keep waiting for an operation to terminate. This is not supported in Altibase, and thus an error will always be returned.

Using XA#

This section describes the basic procedures for using ODBC, APRE and JDBC in an XA environment.

ODBC/XA execution order#

xa_open
Connect to the specified server.
SQLAllocHandle
Create a connection and environment handle to connect to ODBC.
SQLSetConnectAttr
Connect the XA connection to the connection handle.
SQLConnect
Since the actual connection was made with xa_open, this call does not make a new connection. However, SQLConnect changes the internal state of the connection in ODBC. If this step is omitted, DML operations cannot be performed.
xa_start
Start a transaction branch that corresponds to a specific XID.
executing SQL statements
Performs operations such as SQLPrepare and SQLExecute. If the commit statement is executed here, the server returns an error message.
xa_end
Terminate the transaction branch.
xa_prepare
Prepare to commit.
xa_commit
Commit the transaction.
SQLDisconnect
In ODBC, change the internal state of the connection to unconnected. However, the connection created by the actual XA is retained.
xa_close
Terminate xa connection.

SQLSetConnectAttr#

Calling SQLSetConnectAttr enables an XA connection to use an ODBC connection, so that an application can access a distributed transaction via ODBC.

The following parameters are provided to enable an XA connection to be configured using SQLSetConnectAttr:

SQLRETURN SQLSetConnectAttr (SQLHDBC hdbc,  
SQLINTEGER fAttr,  
SQLPOINTER vParam,  
SQLINTEGER sLen);

fAttr = ALTIBASE_XA_RMID
Setting the fAttr parameter to ALTIBASE_XA_RMID enables the connection specified using the hdbc parameter to use a specified XA connection. Detailed information about the XA connection is set by specifying a pointer for the vParam parameter, which is described below.
vParam
This must be set to the rmid value that was specified when a connection was established.
fAttr = SQL_ATTR_ENLIST_IN_XA
Make the specified hdbc connection the last XA connection.

Embedded SQL#

How to Author an Application depending on the Setting of XA_NAME in xa_open#

n XA applications, a cursor is valid only for a single transaction. This means that a cursor must be opened after the start of execution of a transaction, and must be closed before the transaction is completed (i.e. committed or rolled back).

How to Author an Application when Using the Default Connection#

If it is desired to use the default connection, the XA_NAME keyword must not be present in xa_info, which is the character string parameter of xa_open that contains the connection information. An example of xa_info without XA_NAME is shown below:

DSN=127.0.0.1;UID=SYS;PWD=MANAGER

It is therefore not possible to use the AT clause when executing SQL queries. The following query is acceptable because it does not contain an AT clause:

EXEC SQL UPDATE emp SET empno = 5;

How to Author an Application when Using XA_NAME to Specify One or More Connections#

If it is desired to specify a connection when using APRE to author an application, the XA_NAME keyword and a corresponding value must be present in the xa_info connection character string parameter of xa_open.

It is possible to write an application that uses a default connection and one or more additional connections specified using XA_NAME. This is accomplished as shown below.

If, for example, the names of the connections specified using XA_NAME are conn1 and conn2, the value of open_string in the TM (Transaction Manager) environment settings would be as follows:

DSN=127.0.0.1;UID=SYS;PWD=MANAGER;XA_NAME=conn1
DSN=127.0.0.1;UID=SYS;PWD=MANAGER;XA_NAME=conn2
DSN=127.0.0.1;UID=SYS;PWD=MANAGER

This permits the application to execute SQL statements that contain the AT clause, thereby accessing multiple servers, as shown below:

EXEC SQL AT conn1 UPDATE emp SET empno = 5;
EXEC SQL AT conn2 UPDATE emp SET empno = 5;
EXEC SQL UPDATE emp SET empno = 5;

Executing JDBC/XA#

The XA classes that are defined by the jdbc driver of Altibase are as shown below:

Altibase.jdbc.driver.AltibaseXADataSource
Altibase.jdbc.driver.AltibaseXAResource
Altibase.jdbc.driver.AltibaseXID

The ABXADataSource class is the only one that the user accesses directly. The user does not need to directly access the other classes, as they are implemented in the JTA interface class.

Create an ABXADataSource Object

AltibaseXADataSource xaDataSource = new AltibaseXADataSource();
xaDataSource.setUrl(args[0]);
xaDataSource.setUser("SYS");
xaDataSource.setPassword("MANAGER");

Create an XAConnection Object
Create an XAConnection object by calling the getXAConnection method in the XADataSource class.
```
XAConnection xaConnection = xaDataSource.getXAConnection("SYS", "MANAGER:");
```
Create an XAResource Object
Create an XAResource object by calling the getXAResource method in the XAConnection class.
```
XAResource xaResource = xaConnection.getXaResource();
```
Create a Connection Object
Create a connection object to use for executing SQL statements by calling the getConnection method in the XAConnection class.
```
Connection conn1 = xaConnection.getConnection();
```
Use the XAResource Object to Execute XA Functions
XA functions such as xa_start and xa_end can be executed using the methods in the XAResource class.
```
xaResource.start(AltibaseXID, XAResource.TMNOFLAGS);
```

Execute SQL Statements using the Connection Object

Statement stmt = conn.createStatement();
int cnt = stmt.executeUpdate("insert into t1 values (4321)");

XA Transaction Control#

This section describes how to control transactions in an Altibase XA environment.

When using the XA library, the SQL COMMIT and ROLLBACK statements are not used to commit and roll back transactions. Instead, the users must use the TX interface that is provided by the TM in application programs, as shown below.

The TM typically controls a transaction using the XA interface.

TX Interface	Description
tx_open	This logs on to an RM.
tx_close	This logs off from an RM.
tx_begin	This starts execution of a new transaction.
tx_commit	This commits a transaction.
tx_rollback	This rolls back a transaction.

[Table 3-3] The TX Interface

The process of calling the TX and XA interfaces is as shown in the following diagram:

[Figure 3-2] The Process of Calling TX and XA Interfaces

A TPM (Transaction Processing Monitor) application has a client/server structure in which a client requests a service provided by an application server. Service is divided into logical work units. When Altibase is used as the RM, a logical work unit typically consists of a set of SQL statements.

Example#

In the following example, it is assumed that the application server has already logged on to the TPM system.

Starting a Transaction on an Application Server#

The following example shows the start of a transaction on an application server.

Client:
tpm_service("SERVICE1");

Server:
SERVICE1()
{
<get service specific data>
tx_begin();
EXEC SQL UPDATE....;
tpm_service("SERVICE2");
tx_commit();
<return service status back to the client>
}

Starting a Transaction on a Client#

The following example shows the start of a transaction on a client.

Client:
tx_begin();
tpm_service("SERVICE1");
tmp_service("SERVICE2");
tx_commit();

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 the client>
}

Changing an Existing Application into a TPM Application#

To change an existing application (Precompiler or ODBCCLI application) into a TPM (Transaction Processing Monitor) application that uses the XA library of Altibase, follow the procedure outlined below:

Convert the application into one that incorporates a "service" framework. Here, the term "framework" means one in which a client requests a service from an application server. In some TPMs, the tx_open and tx_close functions must be explicitly called, while in other TPMs, the logging on and off takes place implicitly.
General connection statements must be changed into a TPM-compatible form. For example, when working with APRE, replace the EXEC SQL CONNECT statement with a call to tx_open, and when working with ODBCCLI, replace the SQLDriverConnect statement with calls to both tx_open and SQLConnect. The ODBCCLI SQLDriverConnect statement must be replaced by both tx_open and SQLConnect. Although the actual connection is achieved using tx_open, the SQLConnect task is necessary in order for it to be possible to make a connection internally within ODBC. For more detailed information, please refer to Executing ODBC/XA, which outlines the required tasks in sequence.
Disconnection statements must also be changed into a TPM-compatible form. Replace the EXEC SQL DISCONNECT statement (when working with APRE) or the SQLDisconnect statement (when working with ODBCCLI) with a call to tx_close ().
Commit and rollback statements must also be changed into a TPM-compatible form. When working with APRE, replace the EXEC SQL COMMIT statement with a call to tx_commit and the EXEC SQL ROLLBACK statement with a call to tx_rollback. When working with ODBCCLI, replace the SQLEndTran statement with a call to either tx_commit or tx_rollback, as appropriate. Use tx_begin to initiate the execution of a transaction.

Before terminating a transaction, the application must exit the state in which it is ready to fetch records. That is, after fetching data using a cursor and before ending the transaction, the CLOSE RELEASE statement must be used to close the cursor and free all associated resources.

Altibase Statement	TPM Functions
CONNECT	tx_open
Implicit commencement of transaciton	tx_begin
SQL	Service that executes the SQL statements
COMMIT	tx_commit
ROLLBACK	tx_rollback
DISCONNECT	tx_close
SET TRANSACTION READ ONLY	Not allowed

Limitations when using XA#

The use of XA is limited in the following ways:

Limitations when using XA
Limitations related to Transaction Branches
No Support for Association Migration
No Support for Asynchronous Calls
No Support for Dynamic Registration
Server shutdown

Limitations on Use of SQL#

Rollback and Commit#

Because global transactions are managed by the TM, the Altibase transaction control statements COMMIT and ROLLBACK must not be used within an XA application to control global transactions.

Instead, tx_commit and tx_rollback must be used to complete global transactions. This means that the EXEC SQL ROLLBACK and EXEC SQL COMMIT statements can't be used within applications authored using APRE. Similarly, SQLEndTran can't be used within an ODBCCLI application to commit or roll back a transaction.

DDL Statements#

Because DDL SQL statements are implicitly committed, they can't be used within XA applications of Altibase.

AUTOCOMMIT Session Property#

Because global transactions execute in non-autocommit mode, the AUTOCOMMIT property can't be changed using the ALTER SESSION SET AUTOCOMMIT = TRUE statement.

SET TRANSACTION#

The Altibase SET TRANSACTION { READ ONLY | READ WRITE | ISOLATION LEVEL ... } data control statement can't be used within an XA application of Altibase.

Connection or Disconnection with EXEC SQL Statements#

The EXEC SQL CONNECT and EXEC SQL DISCONNECT statements can't be used to establish or terminate connections in applications authored using APRE.

Multiple application threads participate in the execution of a single global transaction. These threads have either tightly-coupled or loosely-coupled relationship between them.

Threads that have a tightly-coupled relationship share a common resource. In addition, an RM handles a pair of coupled threads as a single entity. The RM must ensure that tightly-coupled threads do not reach a resource deadlock in a transaction branch. However, there is no need to provide this guarantee for loosely-coupled threads. The RM handles loosely-coupled transaction branches as though they were different global transactions.

Relationship between XID and Thread#

If the TM assigns a new XID (branch qualifier) to a thread, this thread has a loosely-coupled relationship with the other threads in the same branch. The RM handles this thread as though it were a separate global transaction.

In contrast, if the TM joins a branch with an XID, that is, assigns an existing XID (branch qualifier) to a thread, the thread has a tightly-coupled relationship with the other threads sharing this branch. The RM regards tightly-coupled threads as one object, and must guarantee that a resource deadlock does not occur between tightly-coupled threads.

No Support for Association Migration#

Association migration (in which the TM associates a suspended branch with another branch and resumes its execution) is not supported in Altibase.

No Support for Asynchronous Calls#

Asynchronous XA calls are not supported in XA applications of Altibase.

No Support for Dynamic Registration#

The Altibase server does not support dynamic registration. Only static registration is supported. In so-called "dynamic registration", an RM registers a global transaction with the TM before it starts executing a transaction branch.

In static registration, it is necessary to use xa_start to tell an RM that a transaction has commenced.

Server Shutdown#

Suppose that the server terminates abnormally or that the shutdown abort command is executed on the server, and that there are one or more transactions that are in a prepared state at that time. When the server is subsequently restarted, recovery tasks will be performed, after which it will be possible to execute these transactions using the xa_recover statement.

If the server is shut down normally using the shutdown immediate or shutdown normal command while there are one or more transactions in a prepared state, Altibase aborts these transactions in order to shut down. Recovery tasks are then performed when the server is subsequently restarted, and these prepared transactions are restored to their previous state.

In contrast, if there are no prepared transactions when the server is shut down normally, recovery tasks will not be performed when the server is restarted.

JDBC Distributed Transactions#

Altibase JDBC can be used to implement distributed transactions, as it complies with the OpenXA standards related to connection pooling and distributed transaction processing, as set forth in the JDBC 2.0 extension API.

The jdbc driver package of Altibase includes classes for realizing all of the distributed transaction processing functionality in accordance with the XA standard.

JTA (Java Transaction API) and Application Server#

The method by which an application processes a distributed transaction through an application server is shown in the following figure:

[Figure 3-3] Distributed Transaction Processing

The application server supports the use of XAConnections that make it possible to connect to respective resources.

An application connects to an application server, establishes a connection, and executes queries. The application server manages the transaction using the TM (Transaction Manager). The TM accesses required resources using a Resource Adapter provided by the DBMS vendor.

When the resource to which a connection is to be established using the Resource Adapter is a DBMS, the JDBC driver package can be used as the Resource Adapter. A Resource Adapter has 4 classes, namely the ResourceFactory, Transactional Resource (XAConnection), Connection, and XAResource classes.

The ResourceFactory class is used to create an XAConnection object. In the case of JDBC, the factory that is used to create XAConnection objects is XADataSource. The application server obtains an XAConnection object (for connecting to a DBMS) from the XADataSource factory. The application server then obtains an instance of a connection object (java.sql.Connection), to be used by the application, and an instance of a XAResource object, to be used by the TM, from the XAConnection object.

XA Components#

In this section, the standard XA interfaces provided in the JDBC 2.0 Optional Package are explained, along with the Altibase classes in which they are implemented.

XADataSource Interface#

The javax.sql.XADataSource interface is a factory for creating XAConnection objects. This interface's getXAConnection method returns an XAConnection object.

public interface XADataSource
    {
       XAConnection getXAConnection() throws SQLException;
       XAConnection getXAConnection(String user, String password)
          throws SQLException;
       ...
}

Altibase.jdbc.driver.ABXADataSource is the class in which the XADataSource interface is implemented, and is included in the JDBC driver provided by Altibase. It is derived from the Altibase.jdbc.driver.ABConnectionPoolDataSource class. The ABConnectionPoolDataSource class is in turn derived from the Altibase.jdbc.driver.DataSource class.

Therefore, the ABXADataSource class includes all the connection properties that the DataSource and ABConnectionPoolDataSource classes have.

[Figure 3-4] ABXADataSource Class

The getXAConnection method of the ABXADataSource class returns an instance of the XAConnection type. Because this is actually an instance of the ABPooledConnection class, the ABPooledConnection class is the implementation of the XAConnection interface.

An XA data source can be registered in the Java Naming and Directory Interface (JNDI) and used.

XAConnection Interface#

The XAConnection interface is a child interface of the PooledConnection interface. It includes the getConnection, close, addConnectionEventListener and removeConnectionEventListener methods.

public interface XAConnection extends PooledConnection
    {
   javax.jta.xa.XAResource getXAResource() throws SQLException;
   …
    }

An XAConnection instance establishes a physical connection to a database. It is used to manage a distributed transaction, and to obtain an XAResource object that plays a role in managing the distributed transaction.

Altibase.jdbc.driver.ABPooledConnection class is the actual instance of the XAConnection type.

The getXAResource method of the ABPooledConnection class returns an instance of the ABXAResource object. The getConnection method returns an instance of the ABConnection object.

[Figure 3-5] ABPooledConnection Class

The ABConnection instance returned by the getConnection method acts as a temporary handle for the physical database connection. It acts like a normal connection until the transaction branch starts participating in the global transaction. At the moment that the transaction branch participates in the global transaction, AUTOCOMMIT becomes false. After the global transaction terminates, AUTOCOMMIT is restored to its original state, that is, its state prior to the start of the global transaction.

Each time an XAConnection instance's getConnection method is called, it returns a new instance of a Connection object. At this time, if any previous connection instance that was created by the same XAConnection instance still exists, it is closed. It is nevertheless advisable to explicitly close a previous Connection instance before opening a new one. Calling the close method of an XAConnection instance closes the physical connection to the database.

XAResource Interface#

The TM uses instances of the ABXAResource object to coordinate all of the transaction branches. An instance of the Altibse.jdbc.driver.ABXAResource type is an instance of the class in which the javax.transaction.xa.XAResource interface is implemented.

[Figure 3-6] ABXAResource Class

Whenever the getXAResource method of the ABPooledConnection class is called, the JDBC driver of Altibase creates and returns an instance of the ABXAResource class, and associates the ABXAResource instance with a Connection instance. This is the Connection object that is used by the transaction branch.

The ABXAResource class has several methods for controlling a transaction branch of a distributed transaction.

A TM receives an instance of the ABXAResource class from a middle-tier component such as an application server. The ABXAResource class exposes the following methods:

void start(Xid xid, int flags)
void end(Xid xid, int flags)
int prepare(Xid xid)
void commit(Xid xid, boolean onePhase)
void rollback(Xid xid)
public void forget(Xid xid)
public Xid[] recover(int flag)

For more detailed information, please refer to the description of the javax.transaction.xa.XAResource class in the Java API Specifications.

Xid interface#

The TM creates instances of the Xid interface and uses them to coordinate the branches of a distributed transaction. Each transaction branch is assigned a unique transaction ID, which includes the following information:

Format identifier

Global transaction identifier

Branch qualifier

In Altibase, the javax.transaction.xa.Xid interface is implemented as the XID class in the Altibase.jdbc.driver package.

Note: Altibase.jdbc.driver.XID does not need to be used to make ABXAResource calls. Any class in which the javax.transaction.xa.Xid interface is implemented can be used for this.

Error Handling#

When errors occur, XA-related methods throw the ABXAException class. The ABXAException class is a subclass of the javax.transaction.xa.XAException class.

Making XA Settings in Application Servers#

Making XA Settings in WebLogic#

In the WebLogic console, expand Services -> JDBC -> Connection Pools -> Configure a new JDBC Connection Pool, and then enter the required JDBC connection information.

	NON-XA	XA
URL	jdbc:Altibase://[ip]:[port]/dbname	jdbc:Altibase://[ip]:[port]/ dbname
Driver Classname	Altibase.jdbc.driver.AltibaseDriver	Altibase.jdbc.driver. AltibaseXADataSource
Properties	User=[username]	User= [username]

[Table 3-4] Connection Information for Non-XA and XA Environments

[Figure 3-7] Entering JDBC Connection Information

Create a DataSource using the newly created Connection Pool. Expand Services->JDBC->Data Sources and choose Configure a new JDBC Data Source. Enter the Name and JNDI Name and check "Honor Global Transactions". For "Pool Name", enter the name of the pool created in the first step in the window shown in [Figure 6-7].

Note: In versions of WebLogic prior to version 8.1, a new DataSource is created by expanding Services->JDBC->XA Data Sources.

[Figure 3-8] Creating a Data Source

Weblogic Application Example#

// step 1. JNDI Lookup and get UserTransaction Object
Context ctx = null;
Hashtable env = new Hashtable();

// Parameter for weblogic
env.put(Context.INITIAL_CONTEXT_FACTORY, "weblogic.jndi.WLInitialContextFactory");
env.put(Context.PROVIDER_URL,"t3://localhost:7001");
env.put(Context.SECURITY_PRINCIPAL,"weblogic");
env.put(Context.SECURITY_CREDENTIALS,"weblogic");

ctx = new InitialContext(env);
System.out.println("Context Created :"+ctx);

// step 2. get User Transaction Object
UserTransaction tx = (UserTransaction)ctx.lookup("javax.transaction.UserTransaction");

// step 3 start Transaction
System.out.println("Start Transaction :"+tx);
tx.begin();

try{
// step 4. doing query
// step 4-1. get Datasource
DataSource xads1 = (DataSource)ctx.lookup("altiTXDS");

Making XA Settings in JEUS#

Here is how to make the basic settings to create a JDBC data source in JEUS.

Under "JEUS Manager Resource(s)", choose "JDBC" and then select "Create New JDBC Data Source".
Enter the following information in the basic setup window that appears.
- DBMS : Other
- Other DataSource:
- Data Source Class Name: Altibase.jdbc.driver.AltibaseXADataSource
- Data Source Type : XADataSource
Enter appropriate values for Database Name, Port Number, Server Name, User and Password

[Figure 3-9] Setting a Data Source in JEUS

JEUS Application Example#

// step 1. JNDI Lookup and get UserTransaction Object
Context ctx = null;
Hashtable env = new Hashtable();

// Parameter for weblogic
env.put(Context.INITIAL_CONTEXT_FACTORY, "jeus.jndi.JNSContextFactory");
env.put(Context.URL_PKG_PREFIXES, "jeus.jndi.jns.url");    
env.put(Context.PROVIDER_URL, "127.0.0.1");
env.put(Context.SECURITY_PRINCIPAL,"jeus");
env.put(Context.SECURITY_CREDENTIALS,"jeus");

ctx = new InitialContext(env);
System.out.println("Context Created :"+ctx);

// step 2. get User Transaction Object
UserTransaction tx = (UserTransaction)ctx.lookup("java:comp/UserTransaction");

// step 3 start Transaction
System.out.println("Start Transaction :"+tx);
tx.begin();

try{
// step 4. doing query
// step 4-1. get Datasource
DataSource xads1 = (DataSource)ctx.lookup("altiTXDS");

Example#

The following example illustrates how to implement distributed transactions using Altibase XA.

In this example, the operations are executed in the following order:

Start transaction branch #1.
Start transaction branch #2.
Execute DML operations on branch #1.
Execute DML operations on branch #2.
End transaction branch #1.
End transaction branch #2.
Prepare branch #1.
Prepare branch #2.
Commit branch #1.
Commit branch #2.

import java.sql.*;
import javax.sql.*;
import Altibase.jdbc.driver.*;
import javax.transaction.xa.*;

class XA4
{
  public static void main (String args [])
       throws SQLException
  {

    try
    {
        String URL1 = "jdbc:Altibase://localhost:25226/mydb";
        // You can put a database name after the @ sign in the connection URL.
        String URL2 = "jdbc:Altibase://localhost:25226/mydb";
        // Create first DataSource and get connection
        Altibase.jdbc.driver.DataSource ads1 = new Altibase.jdbc.driver.DataSource();
        ads1.setUrl(URL1);
        ads1.setUser("SYS");
        ads1.setPassword("MANAGER");
        Connection conna = ads1.getConnection();

        // Create second DataSource and get connection
        Altibase.jdbc.driver.DataSource ads2 = new Altibase.jdbc.driver.DataSource();
        ads2.setUrl(URL2);
        ads2.setUser("SYS");
        ads2.setPassword("MANAGER");
        Connection connb = ads2.getConnection();

        // Prepare a statement to create the table
        Statement stmta = conna.createStatement ();

        // Prepare a statement to create the table
        Statement stmtb = connb.createStatement ();

        try
        {
          // Drop the test table
          stmta.execute ("drop table my_table");
        }
        catch (SQLException e)
        {
          // Ignore an error here
        }

        try
        {
          // Create a test table
          stmta.execute ("create table my_table (col1 int)");
        }
        catch (SQLException e)
        {
          // Ignore an error here too
        }

        try
        {
          // Drop the test table
          stmtb.execute ("drop table my_tab");
        }
        catch (SQLException e)
        {
          // Ignore an error here
        }

        try
        {
          // Create a test table
          stmtb.execute ("create table my_tab (col1 char(30))");
        }
        catch (SQLException e)
        {
          // Ignore an error here too
        }

        // Create XADataSource instances and set properties.
        AltibaseXADataSource axds1 = new AltibaseXADataSource();
        axds1.setUrl("jdbc:Altibase://localhost:25226/mydb");
        axds1.setUser("SYS");
        axds1.setPassword("MANAGER");

        AltibaseXADataSource axds2 = new AltibaseXADataSource();

        axds2.setUrl("jdbc:Altibase://localhost:25226/mydb");
        axds2.setUser("SYS");
        axds2.setPassword("MANAGER");

        // Get XA connections to the underlying data sources
        XAConnection pc1  = axds1.getXAConnection();
        XAConnection pc2  = axds2.getXAConnection();

        // Get the physical connections
        Connection conn1 = pc1.getConnection();
        Connection conn2 = pc2.getConnection();

        // Get the XA resources
        XAResource axar1 = pc1.getXAResource();
        XAResource axar2 = pc2.getXAResource();

        // Create the Xids With the Same Global Ids
        Xid xid1 = createXid(1);
        Xid xid2 = createXid(2);

        // Start the Resources
        axar1.start (xid1, XAResource.TMNOFLAGS);
        axar2.start (xid2, XAResource.TMNOFLAGS);

        // Execute SQL operations with conn1 and conn2
        doSomeWork1 (conn1);
        doSomeWork2 (conn2);

        // END both the branches -- IMPORTANT
        axar1.end(xid1, XAResource.TMSUCCESS);
        axar2.end(xid2, XAResource.TMSUCCESS);

        // Prepare the RMs
        int prp1 =  axar1.prepare (xid1);
        int prp2 =  axar2.prepare (xid2);

        System.out.println("Return value of prepare 1 is " + prp1);
        System.out.println("Return value of prepare 2 is " + prp2);

        boolean do_commit = true;

        if (!((prp1 == XAResource.XA_OK) || (prp1 == XAResource.XA_RDONLY)))
           do_commit = false;

        if (!((prp2 == XAResource.XA_OK) || (prp2 == XAResource.XA_RDONLY)))
           do_commit = false;

        System.out.println("do_commit is " + do_commit);
        System.out.println("Is axar1 same as axar2 ? " + axar1.isSameRM(axar2));

        if (prp1 == XAResource.XA_OK)
          if (do_commit)
             axar1.commit (xid1, false);
          else
             axar1.rollback (xid1);

        if (prp2 == XAResource.XA_OK)
          if (do_commit)
             axar2.commit (xid2, false);
          else
             axar2.rollback (xid2);

         // Close connections
        conn1.close();
        conn1 = null;
        conn2.close();
        conn2 = null;

        pc1.close();
        pc1 = null;
        pc2.close();
        pc2 = null;

        ResultSet rset = stmta.executeQuery ("select col1 from my_table");
        while (rset.next())
          System.out.println("Col1 is " + rset.getInt(1));

        rset.close();
        rset = null;

        rset = stmtb.executeQuery ("select col1 from my_tab");
        while (rset.next())
          System.out.println("Col1 is " + rset.getString(1));

        rset.close();
        rset = null;

        stmta.close();
        stmta = null;
        stmtb.close();
        stmtb = null;

        conna.close();
        conna = null;
        connb.close();
        connb = null;

    } catch (SQLException sqe)
    {
      sqe.printStackTrace();
    } catch (XAException xae)
    {
      System.out.println("XA Error is " + xae.getMessage());
    }
  }

  static Xid createXid(int bids)
    throws XAException
  {
      byte[] gid = new byte[1]; gid[0] = (byte)9;
      byte[] bid = new byte[1]; bid[0] = (byte)bids;
      byte[] gtrid = new byte[4];
      byte[] bqual = new byte[4];

      System.arraycopy(gid,0,gtrid,0,1);
      System.arraycopy(bid,0,bqual,0,1);
      Xid xid = new XID(0x1234,gtrid,bqual);
      return xid;
  }

  private static void doSomeWork1 (Connection conn)
   throws SQLException
  {
      String sql ;
      Statement stmt = conn.createStatement();
      sql = "insert into my_table values(1)";
      stmt.executeUpdate(sql);
      stmt.close();
  }

  private static void doSomeWork2 (Connection conn)
   throws SQLException
  {
      String sql ;
      Statement stmt = conn.createStatement();
      sql = "insert into my_tab values('test')";
      stmt.executeUpdate(sql);
      stmt.close();
  }
}

How to Solve Application Problems Using XA#

This section explains how to determine the cause of any XA-related errors that may occur.

Checking XA Tracking Information#

The XA library of Altibase records information that is useful for tracing errors in a trace file. If you open this file, you can check information such as error codes and messages.

For example, if xa_open fails, you can use the trace information to find out whether the open string is wrong, whether the Altibase server was not found, or whether the logon failed.

XA Trace File Name and Location#

altibase_xa_[XA_NAME][YYYYMMDD].log

XA_NAME : This is the connection name specified in the xa_info character string field XA_NAME=value. If XA_NAME is not specified in the xa_info character string, it will be NULL
YYYYMMDD : This is the date specified in the trace file (YYYYMMDD).

If the $ALTIBASE_HOME environment variable has been set, this trace file will be created in $ALTIBASE_HOME/trc. If the $ALTIBASE_HOME environment variable has not been set, the trace file will be created in the current directory.

Example#

104744.19381.1:
ulxXaOpen : XAER_RMERR : [ERR-4102E] Invalid password

"104744" is the time the log was recorded (HHMISS), "19381" is the Process ID (PID), and "1" is the Resource Manager ID.

ulxXaOpen is the module name, XAER_RMERR is the XA error code, [ERR-4102E] is the error code returned by the Altibase server, and "invalid password" is the error message returned by the Altibase server.

Processing In-doubt Transaction#

The TM is responsible for providing functionality for detecting problems that give rise to in-doubt and pending transactions and automatically completing in-doubt transactions. The RM in which the in-doubt or pending transaction is taking place maintains a lock on all resources associated with the prepared transaction until the transaction has been completed and it receives an instruction to commit the transaction.

However, if another transaction requires the data locked by an in-doubt transaction, or if a transaction remains in an in-doubt or pending status for an excessive amount of time, it will be necessary for the DBA to manually handle the transaction.

Altibase provides the V$DBA_2PC_PENDING performance view, which displays information about the state of in-doubt transactions so that they can be dealt with. For more information about this and other performance views, please refer to the Altibase General Reference.

To manually process such transactions, the DBA can forcefully commit or rollback transaction as shown below:

COMMIT FORCE 'global_tx_id';
ROLLBACK FORCE 'global_tx_id';

Example#

This example shows how to check the state of in-doubt transactions and manually commit a transaction as desired.

iSQL> select * From v$dba_2pc_pending; 
LOCAL_TRAN_ID         GLOBAL_TX_ID                    
------------------------------------------------------
9280                  69.FAEDFAED.00000001        
21315                 69.FAEDFAED.00000002    
2 rows selected. 
iSQL> commit force '69.FAEDFAED.00000002'; 
Commit force success.

Checking Heuristically Completed Transactions#

It is possible to check information about heuristically completed transactions. A so-called "heuristically completed transaction" is a transaction that is either committed or rolled back at the discretion of the RM after the RM fails to receive a transaction completion instruction (such as commit or rollback) from the TM for whatever reason.

If an in-doubt transaction is forcibly committed or rolled back, it is said to be a heuristically committed or heuristically rolled back transaction. Information about this transaction will be visible in the SYS_XA_HEURISTIC_TRANS_ meta table.

To delete this information, call xa_forget after the execution of xa_recover, or execute remove_xid().

Example#

After the DBA commits an in-doubt transaction, information about the transaction is visible in the SYS_XA_HEURISTIC_TRANS_ meta table.

iSQL> select * From v$dba_2pc_pending; 
V$DBA_2PC_PENDING.LOCAL_TRAN_ID 
V$DBA_2PC_PENDING.GLOBAL_TX_ID         
------------------------------------------ 
100421               
69.FAEDFAED.00000001  
1 row selected. 

iSQL> commit force '69.FAEDFAED.00000001'; 
Commit force success. 

iSQL> select * from system_.sys_xa_heuristic_trans_; 
SYS_XA_HEURISTIC_TRANS_.FORMAT_ID 
SYS_XA_HEURISTIC_TRANS_.GLOBAL_TX_ID      
SYS_XA_HEURISTIC_TRANS_.BRANCH_QUALIFIER   
SYS_XA_HEURISTIC_TRANS_.STATUS 
SYS_XA_HEURISTIC_TRANS_.OCCUR_TIME 
--------------------------------------
69                   
FAEDFAED    
00000001    
1     
2008/08/29 10:09:53  
1 row selected.

3. XA Interface#

XA Interface Overview#

XA Related Glossary#

XA Structure#

XA and 2PC (Two-Phase Commit)#

xa_switch_t Structure#

The XA Library#

The XA Interface#

XA Functions#

xa_open#

xa_close#

xa_start#

xa_end#

xa_rollback#

xa_prepare#

xa_commit#

xa_recover#

xa_forget#

xa_complete#

Using XA#

ODBC/XA execution order#

SQLSetConnectAttr#

Embedded SQL#

How to Author an Application depending on the Setting of XA_NAME in xa_open#

How to Author an Application when Using the Default Connection#

How to Author an Application when Using XA_NAME to Specify One or More Connections#

Executing JDBC/XA#

XA Transaction Control#

Example#

Starting a Transaction on an Application Server#

Starting a Transaction on a Client#

Changing an Existing Application into a TPM Application#

Limitations when using XA#

Limitations on Use of SQL#

Rollback and Commit#

DDL Statements#

AUTOCOMMIT Session Property#

SET TRANSACTION#

Connection or Disconnection with EXEC SQL Statements#

Limitations related to Transaction Branches#

Relationship between XID and Thread#

No Support for Association Migration#

No Support for Asynchronous Calls#

No Support for Dynamic Registration#

Server Shutdown#

JDBC Distributed Transactions#

JTA (Java Transaction API) and Application Server#

XA Components#

XADataSource Interface#

XAConnection Interface#

XAResource Interface#

Xid interface#

Error Handling#

Making XA Settings in Application Servers#

Making XA Settings in WebLogic#

Weblogic Application Example#

Making XA Settings in JEUS#

JEUS Application Example#

Example#

How to Solve Application Problems Using XA#

Checking XA Tracking Information#

XA Trace File Name and Location#

Example#

Processing In-doubt Transaction#

Example#

Checking Heuristically Completed Transactions#

Example#