When dealing with databases from a Java application, one of the issues that many people are faced with is performance. When many connections are required, connection pooling is almost mandatory. There are quite a few options out there, just to mention a few:

• BoneCp
• c3p0
• DBCP
• Tomcat JDBC connection pool
  

One thing we learned the hard way is that pooling of XA connections is not that easy. Most connection pools do not support XA connections. Some implementations in the documentation say they provide support, but either do not work, or there is some hidden fancy stuff which we never managed to get our head around. In some cases, you can do without pooling, but what if its really necessary? XA transactions are already heavy. Opening and closing connections each time makes them even worse.

So we need a connection pool that supports XA connections. Luckily there are a few, but I think the most interesting of all is XAPool: http://xapool.ow2.org. Don’t get too excited however, even though it sounds easy to get it to work, it isn’t.

All the tests mentioned in this blog were done using Mule 3.3.2 using JBoss TS as the transaction manager for XA. As a database we used H2 and Postgresql. For the purpose of keeping this blog simple, we only show configurations for the H2 database. We created a very simple Mule flow which starts a transaction on a Mule’s VM queue and continues over JDBC.

First let’s start by having a look at Tomcat JDBC Connection Pool. In the documentation, we can find that this library does provide support for XA transactions. However, we never managed to get it to work. We tried a lot of different configurations, but the end result was always the same, it did not really work. If anyone can suggest a solution, you’re very welcome.

Try number one, set the internal datasource using H2′s jdbcx datasource and configure Tomcat Pool XADataSource. Note that in the pool we are referring to the datasource, this is required to be able to create XA Connections:

<spring:bean id="internalDataSource" class="org.h2.jdbcx.JdbcDataSource" >
  	<spring:property name="URL" value="jdbc:h2:mem:test;DB_CLOSE_DELAY=-1" />
	<spring:property name="user" value="" />
	<spring:property name="password" value="" />
</spring:bean>
		
<spring:bean id="pooledDataSource" class="org.apache.tomcat.jdbc.pool.XADataSource" >
	<spring:property name="dataSource" ref="internalDataSource" />
</spring:bean>

The problem with this is that every time we tried to get an XA transaction going from Mule, we were faced with an “object already closed” exception:

Root Exception stack trace:
org.h2.jdbc.JdbcSQLException: The object is already closed [90007-172]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
	at org.h2.message.DbException.get(DbException.java:169)
	at org.h2.message.DbException.get(DbException.java:146)

To try to fix this, we tried to wrap the H2 datasource in an XAPool StandardXADatasource in case the connection was not being created properly for XA. This is the configuration:

<spring:bean id="internalDataSource" class="org.enhydra.jdbc.standard.StandardXADataSource" destroy-method="shutdown">
	<spring:property name="driverName" value="org.h2.Driver" />
	<spring:property name="url" value="jdbc:h2:mem:test;DB_CLOSE_DELAY=-1" />
	<spring:property name="user" value="" />
	<spring:property name="password" value="" />
</spring:bean>
		
<spring:bean id="pooledDataSource" class="org.apache.tomcat.jdbc.pool.XADataSource" >
	<spring:property name="dataSource" ref="internalDataSource" />
</spring:bean>

This did not help in any way, same issue encountered, just slightly different exception:

java.sql.SQLException: Connection is closed
	at org.enhydra.jdbc.standard.StandardConnectionHandle.preInvoke(StandardConnectionHandle.java:117)
	at org.enhydra.jdbc.core.CoreConnection.getAutoCommit(CoreConnection.java:104)
	at org.mule.transport.jdbc.xa.ConnectionWrapper.getAutoCommit(ConnectionWrapper.java:113)

Its important to note that both configurations work if we subtract the Tomcat JDBC Pool. So clearly, this pool does not really work for XA transactions, at least with Mule.

The final option is to try with XAPool directly. XAPool provides a class called StandardXAPoolDataSource, so this looked quite promising. On their website they even have an example: here. Wait sorry. Even though the example is called StandardXAPoolDataSource, it actually uses StandardXADataSource with no pooling! Nice!! (I’m being utterly sarcastic just in case you have not noticed.)

OK, so let’s dive in and try to configure StandardXAPoolDataSource:

<spring:bean id="internalDataSource" class="org.enhydra.jdbc.standard.StandardXADataSource" destroy-method="shutdown">
	<spring:property name="driverName" value="org.h2.Driver" />
	<spring:property name="url" value="jdbc:h2:mem:test;DB_CLOSE_DELAY=-1" />
	<spring:property name="user" value="" />
	<spring:property name="password" value="" />
</spring:bean>

<spring:bean id="pooledDataSource" class="org.enhydra.jdbc.pool.StandardXAPoolDataSource" destroy-method="shutdown">
	<spring:property name="user" value="" />
	<spring:property name="password" value="" />
	<spring:property name="dataSource" ref="internalDataSource" />
</spring:bean>

This looks promising. However there is a huge problem here. StandardXAPoolDataSource does not implement javax.sql.XADataSource. It implements only javax.sql.DataSource. This means when Mule tries to get an XA transaction, it will not manage, and throw the following exception:

org.mule.api.transaction.TransactionException: Endpoint is transactional but transaction does not support it
	at org.mule.transport.AbstractConnector.getTransactionalResource(AbstractConnector.java:2015)
	at org.mule.transport.jdbc.JdbcMessageDispatcher.doSend(JdbcMessageDispatcher.java:71)
	at org.mule.transport.AbstractMessageDispatcher.process(AbstractMessageDispatcher.java:81)

When taking a close look at the source, it looks like everything is in place. Only missing the implementation of javax.sql.XADataSource, and some of its methods. But these appear nowhere in the XAPool library.

Having established this, we implemented a class that extends StandardXAPoolDataSource and implements javax.sql.XADataSource. The implementation of the getXaConnection() method is just calling the super class getConnection() method which does all the magic pooling stuff.

package com.ricston.jdbc.xapool;

import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.logging.Logger;

import javax.sql.XAConnection;
import javax.sql.XADataSource;

import org.enhydra.jdbc.pool.StandardXAPoolDataSource;
import org.enhydra.jdbc.standard.StandardXAConnectionHandle;


public class RicstonStandardXAPoolDataSource extends StandardXAPoolDataSource implements XADataSource {

	/**
	 * 
	 */
	private static final long serialVersionUID = -6060990263159819182L;


	@Override
	public XAConnection getXAConnection() throws SQLException {
		return ((StandardXAConnectionHandle) this.getConnection()).xacon;
	}

	@Override
	public XAConnection getXAConnection(String user, String password)
			throws SQLException {
		return ((StandardXAConnectionHandle) this.getConnection(user, password)).xacon;
	}
	
	public Logger getParentLogger() throws SQLFeatureNotSupportedException{
		throw new SQLFeatureNotSupportedException();
	}

}

Now we only need to configure it in Spring and wire it up with our Mule’s JDBC connector, and we’re done.

<spring:bean id="internalDataSource" class="org.enhydra.jdbc.standard.StandardXADataSource" destroy-method="shutdown">
	<spring:property name="driverName" value="org.h2.Driver" />
	<spring:property name="url" value="jdbc:h2:mem:test;DB_CLOSE_DELAY=-1" />
	<spring:property name="user" value="" />
	<spring:property name="password" value="" />
</spring:bean>

<spring:bean id="pooledDataSource" class="com.ricston.jdbc.xapool.RicstonStandardXAPoolDataSource" destroy-method="shutdown">
	<spring:property name="user" value="" />
	<spring:property name="password" value="" />
	<spring:property name="dataSource" ref="internalDataSource" />
</spring:bean>

In a future post we intend to take a closer look at how to configure the pool size and other interesting attributes.

Enjoy.