JDBC and XA Transactions Using NuoDB

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 to ensure all resources are in agreement on the commit.

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 basic steps for participating in a global transaction are:

A NuoXADataSource object is created once and used to create an XAConnection for each global transaction.
The XAConnection object is used to create Connection and XAResource objects during each global transaction.
The XAResource object manages the resource transaction during the global transaction.

The Classes

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 further 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 further 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 further 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 these transaction managers:

Atomikos RM
jBoss

Example

The following example shows a two-phase commit using two NuoXADataSource data sources. This example only 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.

Setting Connection Properties

When a new NuodbXADataSource object is created, the URL, username and password need setting. The schema can also be specified if desired.

Setting the properties can be done in two ways:

/* Set the username and password as properties */
NuoXADataSource nuodbDs = new NuoXADataSource();
nuodbDs.setUrl("jdbc:com.nuodb://ap-host:port/my-database");
nuodbDs.setSchema("db_Schema");    //optional
nuodbDs.setUsername(DB_USERNAME);
nuodbDs.setPassword(DB_PASSWORD);
XAConnection nuodbXAConn = nuodbDs.getXAConnection();

Or:

/* Specify the username and password when creating the connection */
NuoXADataSource nuodbDs = new NuoXADataSource();
nuodbDs.setUrl("jdbc:com.nuodb://ap-host:port/my-database");
nuodbDs.setSchema("db_Schema");       // Optional
XAConnection nuodbXAconn = nuodbDs1.getXAConnection(DB_USERNAME, DB_PASSWORD);

In all examples shown, the AP port is not required unless it is changed from the default 48004.

For more information about connection properties, see:

Running Recovery

If after the first phase of two-phase commit, one or more of the data sources fails due to a crash or connection loss, 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 recovery. Review the documentation for your transaction manager for full details about how it does 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 concurrent transactions in the prepared state that are not part of the failed global transaction. This means that the application must search the array of Xid objects and only recover 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.
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. Care should be taken to ensure prepared transactions are cleaned up as soon as possible.

NuoDB does not support multiple interleaved XA transactions on one 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();
    }
}

The following error is raised when you try to start a new global XID.

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

When a statement created from a pooled connection is closed, this should result in statement event listener notification. To respond to the event, implement javax.sql.StatementEventListener and add it to the pooled connection.

This example does so via an anonymous inner class:

javax.sql.PooledConnection pooledConnection = dataSource.getPooledConnection();
pooledConnection.addStatementEventListener(new StatementEventListener() {
    @Override
    public void statementClosed(StatementEvent event) {
        /* Should be called, add code to respond to the event */
    }

    @Override
    public void statementErrorOccurred(StatementEvent event) {
        /* We don’t support invalidation of prepared statements at all now ... */
    }
});

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