So in the last few days, I was working on adding distributed transaction support to WSO2 Data Services Server for its upcoming release. We use Apache DBCP for connection pooling, and its XA transaction support doesn’t seem to be well documented. So I thought of sharing some simple steps onto how to do this.

Creating the XADataSource

This is the first thing you will need. In order to retrieve XA 2-phase commit aware XAConnections, you will need to instantiate the respective XADataSource class. For example, in Oracle this is “oracle.jdbc.xa.client.OracleXADataSource”, in MySQL it’s “com.mysql.jdbc.jdbc2.optional.MysqlXADataSource”. These classes always contain a no parameter default constructor, and then you will have to set the respective properties for username/password, connection URL etc..

For example, using Oracle XE, this would be as follows,

OracleXADataSource ds = new oracle.jdbc.xa.client.OracleXADataSource();

These Java bean style properties are usually set by App servers in external configurations, in defining the data sources.

Getting the TransactionManager

The next step is to get yourself a transaction manager. There are many commercial and open source providers for this, one of the transaction managers that I tested which worked well is by Atomikos [1]. I also tried the Bitronix transaction manager, but when using it with DBCP, it threw an exception when it was trying to enlist the respective XAResource of a Connection. The Atomikos transaction manager can be created in the following way.

TransactionManager tm = new com.atomikos.icatch.jta.UserTransactionManager()

DBCP Configuration

Now with the XADataSource and the TransactionManager in hand, we can continue with the creation of DBCP XA connection factories. The key class for this is “org.apache.commons.dbcp.managed.DataSourceXAConnectionFactory”. The following code snippet demonstrate the usage.

DataSourceXAConnectionFactory connectionFactory = new DataSourceXAConnectionFactory(tm, ds);

Here, the connection factory takes in the TransactionManager and the XADataSource objects we created earlier. The following code creates rest of the connection pools and the final pooling DataSource object.

GenericObjectPool pool = new GenericObjectPool();
PoolableConnectionFactory factory = new PoolableConnectionFactory(connectionFactory, pool, null, null, false, true);
ManagedDataSource dataSource = new ManagedDataSource(pool, connectionFactory.getTransactionRegistry());

The final data source object is of type “ManagedDataSource”, this is an important class which derives from “PoolableDataSource”, and takes care of enlisting resources with the transaction manager.

So after all the above is set up, let see of a sample run of the usage of a distributed transaction.

Connection c = dataSource.getConnection();
PreparedStatement stmt = c.prepareStatement("insert into Customers values (customers_seq.nextval, 'XXX')");

So the above code actually just contain a single transaction, but for any connection that is created between begin() and commit() methods will be contained in a single global transaction. And you will notice here that, we simply close the connection we get, and does not run commit() ourselves. This is because, invoking close() doesn’t really close the connection as in the conventional non-XA connections, but this a pseudo-close, which in turn just signals the end of using this connection. The actual committing is done by the transaction manager, when it encounters its “commit()” call.

So what if we want to mix non-XA data sources in an environment where there are XA data sources and where a transaction manager is used to commit the operations. This can be done by using the “LocalXAConnectionFactory” class instead of “DataSourceXAConectionFactory”. By using this, non-XA connections also commits and rollbacks as signalled by the transaction manager. Some sample code on how to create the connection factory is show below.

ConnectionFactory connectionFactory = DriverManagerConnectionFactory(jdbcURL, dbcpProps);
connectionFactory = new LocalXAConnectionFactory(tm, connectionFactory);

In the above code, the dbcpProps are the usual properties you would use in creating a DBCP connection factory, which will include all properties such as, driver, url, username/password, connection pooling properties etc..

So I guess that covers it, have fun with DTP !! ..