XA Transactions for JDBC

An XA transaction allows multiple data resources to work within a single global transaction. The global transaction uses two-phase commit (2PC) to ensure that the database participates correctly.

In the first phase, each resource acknowledges the transaction is prepared for commit.
In the second phase, each resource acknowledges the commit is complete.

In JDBC, each resource has its own XADataSource object. For NuoDB, the data source class is called com.nuodb.jdbc.NuoXADataSource.

The steps for participating in a global transaction are:

Create a NuoXADataSource object once
Create an XAConnection from NuoXADataSource for each global transaction.
Create Connection and XAResource objects using the XAConnection object during each global transaction.
Manage the resource transaction during the global transaction using the XAResource object.

NuoXADataSource

The NuoXADataSource implementation in the NuoDB driver JAR, supports the following methods from javax.sql.XADataSource:

public XAConnection getConnection() throws SQLException
public XAConnection getConnection(String username, String password) throws SQLException
public void setUrl(String url)
public void setUsername(String username)
public void setPassword(String password)
public void setSchema(String schema)

For more information, see the Javadoc for XADataSource.

XAConnection

NuoDB’s XAConnection implementation supports the following methods from javax.sql.XAConnection:

public Connection getConnection() throws SQLException
public XAResource getXAResource() throws SQLException

For more information, see the Javadoc for XAConnection.

XAResource

The XAResource implementation in the NuoDB driver, supports the following methods from javax.transaction.xa.XAResource:

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

For more information, see the Javadoc for XAResource.

JDBC XA Resource Manager Support

The X/Open XA specification defines the interactions between the Transaction Manager and the Resource Manager.

The Transaction Manager, also known as the XA Coordinator, manages the XA or global transactions.
The Resource Manager manipulates a specific resource such as a database or a JMS system with transaction state changes. The resource manager is the database driver. Another resource manager example would be a XA compliant JMS queue driver.

JDBC XA was tested (but not necessarily fully supported) with the following transaction managers:

Atomikos RM
jBoss

Configure XA enabled DataSource

To configure XA enabled DataSource, create the NuoXADataSource object with database connection properties. The URL, username, and password properties are mandatory. Specifying the schema is optional.

For example:

/* Create the XA DataSource */
NuoXADataSource nuodbDs = new NuoXADataSource();

/* Configure the database URL, schema, username, and password */
nuodbDs.setUrl("jdbc:com.nuodb://ap-host:port/my-database");
nuodbDs.setSchema("db_Schema");    //optional
nuodbDs.setUsername(DB_USERNAME);
nuodbDs.setPassword(DB_PASSWORD);

/* Create a connection that supports XA transactions */
XAConnection nuodbXAConn = nuodbDs.getXAConnection();

The AP port is only required if it is not the default (48004).

For more information about connection properties, see Specifying Properties on the Connection URL and Connection Properties.

Running Recovery

In a two-phase commit transaction, if one or more of the data sources fails due to a crash or connection loss after the first phase of two-phase commit, the failed data sources are left in the transaction prepared state. Since no participating resource has a full view of the distributed system and can not decide on it’s own whether the transaction branch should be committed or rolled back, these transactions must be cleaned up by running the recovery process. Review the documentation for your transaction manager for full details about recovery.

In the following example (which does not use a transaction manager), all transactions found in the transaction prepared state are rolled back.

NuoXADataSource nuodbDs = new NuoXADataSource();
nuodbDs.setUrl("jdbc:com.nuodb://ap-host:port/my-database");
XAConnection nuodbXAconn = nuodbDs.getConnection(DB_USERNAME, DB_PASSWORD);
XAResource nuodbRes = nuodbXAconn.getXAResource();

Xid xids[] = nuodbRes.recover(XAResource.TMNOFLAGS);
for (int i = 0; i < xids.length; i++) {
    nuodbRes.rollback(xids[i]);
}

In most cases, the above example is unrealistic because there could be other transactions in the prepared state that are not part of the failed global transaction. Therefore, the application must search the array of Xid objects and recover only the transactions associated with the failed global transaction.

The array of Xid objects returned from the recover() method contains NuoXid objects defined as:

public class NuoXid implements Xid { ... }

These NuoXid objects are needed by NuoDB recovery so the rollback or commit of the transaction will work correctly. When searching the array for a specific Xid, the NuoXid.equals() method should be used as in the following example:

XID xid = createXid();
...

/* Failure, rollback the transaction identified by the above xid. */
Xid xids[] = nuodbRes.recover(XAResource.TMNOFLAGS);

for (int i = 0; i < xids.length; i++) {
    NuoXid nuoxid = (NuoXid)xids[i];
    if (nuoxid.equals(xid) == true) {
        nuodbRes.rollback(xids[i]);
        break;
    }
}

NuoDB’s implementation of xa_recover() ignores all flags. All XIDs are returned with a single call.

XA Transaction Restrictions

The following restrictions apply to using NuoDB XA transactions:

Auto-commit is not allowed.
Savepoints are not allowed.
Prepared transactions mus cleaned up as soon as possible. Leaving an XA transaction in the prepared state can cause conflicts with other transactions resulting in these other transactions waiting on the cleanup of the XA transactions.

Multiple interleaved XA transactions is not allowed on a single connection.

In the following example, multiple transactions are done on the same connection.

public void insertIntoFoo(int value1, int value2) throws SQLException, XAException {
    Xid xid1 = getXid(0, 1, 1);
    Xid xid2 = getXid(0, 1, 1);
    NuoXADataSource nuodbDs = new NuoXADataSource();
    nuodbDs.setUrl(getUrl(DATABASE));
    XAConnection nuodbXAconn = nuodbDs.getXAConnection(DBA_USER, DBA_PASSWORD);
    Connection nuodbConn1 = nuodbXAconn.getConnection();
    XAResource nuodbRes = nuodbXAconn.getXAResource();
    Statement nuodbStmt1 = nuodbConn1.createStatement();

    try {
        /* Start XID1 on the connection C1 */
        nuodbRes.start(xid1, XAResource.TMNOFLAGS);
        nuodbStmt1.executeUpdate(String.format(
            "insert into foo.foo1 (f11, f12) VALUES (%d, %d);", value1, value2));
        nuodbRes.end(xid1, XAResource.TMSUCCESS);

        /* Start an interleaved transaction XID2 on the same connection C2. Raises the exception:       */
        /* com.nuodb.jdbc.NuoDBXAException: NuoDB does not support multiple Xids on a single connection */
        nuodbRes.start(xid2, XAResource.TMNOFLAGS);
    } finally {
        nuodbStmt1.close();
        nuodbConn1.close();
    }
}

This reports the following error:

com.nuodb.jdbc.NuoDBXAException: NuoDB does not support multiple Xids on a single connection
    at com.nuodb.jdbc.RemXAResource.start(RemXAResource.java:227)

Event Listener Support

Event listener allows applications or connection pools to be notified when important connection or statement events occur statement created from a pooled connection is closed. To respond to the event, implement javax.sql.StatementEventListener and add it to the pooled connection.

In the following example, statementClosed gets called when the statement is closed.

javax.sql.PooledConnection pooledConnection = dataSource.getPooledConnection();
pooledConnection.addStatementEventListener(new StatementEventListener() {
    @Override
    public void statementClosed(StatementEvent event) {
        /* This method runs when the statement is closed */
    }

    @Override
    public void statementErrorOccurred(StatementEvent event) {
        /* Not supported */
    }
});

/* This should result in statementClosed() called */
pooledConnection.getConnection()
                .prepareStatement("select * from system.tables")
                .close();

Example

The following example shows a two-phase commit using two NuoXADataSource data sources. This example uses NuoDB data sources but other data sources can be used along with NuoXADataSource.

import com.nuodb.jdbc.*;
...

void runGlobalTransactions() {
    NuoXADataSource nuodbDs1 = new NuoXADataSource();
    NuoXADataSource nuodbDs2 = new NuoXADataSource();
    boolean keepGoing = true;

    while (keepGoing) {
        int value1 = 0;
        int value2 = 0;
        waitForRequest(value1, value2);

        Connection nuodbConn1 = nuodbDs1.getXAConnection().getXAConnection();
        Connection nuodbConn2 = nuodbDs2.getXAConnection().getXAConnection();
        XAResource nuodbRes1 = nuodbDs1.getXAConnection().getXAResource();
        XAResource nuodbRes2 = nuodbDs1.getXAConnection().getXAResource();
        Statement nuodbStmt1 = nuodbConn1.getStatement();
        Statement nuodbStmt2 = nuodbConn2.getStatement();

        Xid xid1 = createXid();
        Xid xid2 = createXid();

        /* Start both resources. */
        nuodbRes1.start (xid1, XAResource.TMNOFLAGS);
        nuodbRes2.start (xid2, XAResource.TMNOFLAGS);

        /* Update the databases. */
        NuodbStmt1.executeUpdate("INSERT INTO FOO1 (F1, F2) VALUES (value1, value2);");
        NuodbStmt2.executeUpdate("DELETE FROM FOO2 where F1=value1;");

        /* End both resources. */
        nuodbRes1.end(xid1, XAResource.TMSUSPEND);
        nuodbRes2.end(xid2, XAResource.TMSUSPEND);

        /* Perform phase-1 of commit. */
        int rtn1 =  nuodbRes1.prepare(xid1);
        int rtn2 =  nuodbRes2.prepare(xid2);

        /* Perform phase-2 of commit. */
        if (rtn1 == XAResource.XA_OK && rtn2 == XAResource.XA_OK) {
            nuodbRes1.commit(xid1, false);
            nuodbRes2.commit(xid2, false);
        } else {
            nuodbRes1.rollback(xid1);
            nuodbRes2.rollback(xid2);
        }

        nuodbStmt1.close();
        nuodbConn1.close();
        nuodbStmt2.close();
        nuodbConn2.close();
    }
}

The Xid object in the above example is typically supplied by a global transaction manager (a transaction manager implements the javax.transaction.TransactionManager interface) or created by the user application. The call to createXid() above is a fictional method.