You are here: Client Development > Using the NuoDB Java JDBC Driver > Notes for Using Java JDBC Driver > JDBC and XA Transactions Using NuoDB

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:

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

JDBC XA Resource Manager Support

The X/Open XA specification defines the interactions between the Transaction Manager (TM) 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:

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();

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

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 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. See 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://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 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;
    }
}

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

XA Transaction Restrictions

Caution: The following restrictions apply to using NuoDB XA transactions:

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

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();