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.

XA Transaction support in the NuoDB JDBC driver is currently a preview feature.

Caution: NuoDB encourages you to use preview features in your development projects. However, the use of preview features in production is not supported.

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:

The NuoXADataSource implementation in the NuoDB driver, 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 Java documentation for XADataSource at https://docs.oracle.com/javase/7/docs/api/javax/sql/XADataSource.html.

The 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 Java documentation for XAConnection at https://docs.oracle.com/javase/7/docs/api/javax/sql/XAConnection.html.

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 Java documentation for XAResource at https://docs.oracle.com/javaee/5/api/javax/transaction/xa/XAResource.html.

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. At this time the schema can also be specified as noted in these examples. Setting the properties can be done in two ways:

NuoXADataSource nuodbDs = new NuoXADataSource();     
nuodbDs.setUrl("jdbc:com.nuodb://broker_host:port/my_database");      
nuodbDs.setSchema("db_Schema");  // Optional      
XAConnection nuodbXAconn = nuodbDs1.getXAConnection(DB_USERNAME, DB_PASSWORD");

Or:

NuoXADataSource nuodbDs = new NuoXADataSource();
nuodbDs.setUrl("jdbc:com.nuodb://broker_host:port/my_database");       
nuodbDs.setSchema("db_Schema");    //optional       
nuodbDs.setUsername(DB_USERNAME);   
nuodbDs.setPassword(DB_PASSWORD);   
XAConnection nuodbXAConn = nuodbDs.getXAConnection();

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

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 prepare state. These transactions must be cleaned up by running recovery. In the following example, all transactions found in the transaction prepare state are rolled back.

NuoXADataSource nuodbDs = new NuoXADataSource();
nuodbDs.setUrl("jdbc:com.nuodb://broker_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 prepare state that are not part of the failed global transaction. This means 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;
    }
}

XA Transaction Restrictions

The following restrictions apply to using NuoDB XA transactions:

Support for for javax.sql.StatementEventListener

When a statement created from a pooled connection is closed, this should result in statement event listener notification.

javax.sql.PooledConnection pooledConnection = dataSource.getPooledConnection();
pooledConnection.addStatementEventListener(new StatementEventListener() 
{@Override 
public void statementClosed(StatementEvent event)
{ // should be called }
@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();