DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Building Scalable Real-Time Apps with AstraDB and Vaadin
Register Now

Trending

  • Auto-Scaling Kinesis Data Streams Applications on Kubernetes
  • A Data-Driven Approach to Application Modernization
  • What Is React? A Complete Guide
  • Introduction To Git

Trending

  • Auto-Scaling Kinesis Data Streams Applications on Kubernetes
  • A Data-Driven Approach to Application Modernization
  • What Is React? A Complete Guide
  • Introduction To Git

XA Connection Pooling

Alan Cassar user avatar by
Alan Cassar
·
Oct. 03, 13 · Interview
Like (0)
Save
Tweet
Share
13.06K Views

Join the DZone community and get the full member experience.

Join For Free

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.



Connection (dance)

Published at DZone with permission of Alan Cassar, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Auto-Scaling Kinesis Data Streams Applications on Kubernetes
  • A Data-Driven Approach to Application Modernization
  • What Is React? A Complete Guide
  • Introduction To Git

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: