How to work with JDBC and XA Transactions

Transactions are a fundamental piece of Mule, since they are essential to guaranteeing that, in the event of an exception or a problem, the consistency of the process will be maintained and that there will be no loss of information. When working with a unique resource (such as one JMS Broker, or one Database), the solution is pretty straight forward. By simply using the <transaction> element you can configure a nice transactional flow.

When dealing with several data sources in the same flow, however, this becomes a little bit more complex since it involves another kind of transaction, the XA Transaction. This also requires some additional configuration in order to make it work correctly. In this blog post, I’ll explain and show you a full working example of am XA Transactional Flow that you can use as starting point and will hopefully save you some time.

In this example I’ll create a simple flow that will consume records from a database table, process them and insert the results in a different database. Since we are dealing here with two different datasources we’ll need an XA Transaction to make sure that the entire process executes correctly.

The first step is to declare the transaction manager (in this case we’ll use the JBoss transaction manager), which is as simple as adding the following lines (element and namespace declaration):

Next, we’ll need to define the datasources. This is a very important step that is sometimes ignored: both the Datasource and Database Driver need to be XA Compliant!

In this example I’m using the UCP DataSource (and connecting to a Oracle XE database). You should pay particular attention to the class and connectionFactoryClassName attributes of the DataSource bean. I’m also using a second database that points to a MySQL Database and defining an XA Compilant class for the MySQL datasource (com.mysql.jdbc.jdbc2.optional.MysqlXADataSource) as I did with the Oracle DS.

Now we need to create the JDBC Connector itself. Since there is no specific XA configuration needed you will not see any particular difference between this and any other connector.

I’ve declared two queries, one in each Connector. The first consumes the records from the source (dbQuerySql), and the other invokes a stored procedure that will ultimately insert data in the destination Database.

That completes global configuration. All that’s left now is to configure the flow itself.

We are starting the transaction in the JDBC inbound endpoint, so we declare the XA transaction with the action ALWAYS_BEGIN:

For all other resources that want to join the transaction, we will only need to declare it with the action ALWAYS_JOIN, BEGIN_OR_JOIN or JOIN_IF_POSSIBLE. In this example the outbound endpoint will use the ALWAYS_JOIN action:

And that’s all you need! Pretty simple isn’t it?

Since the objective of this first post is to have the XA transactions up and running I’m not adding any complex logic in the flow so we can focus on the XA configuration itself. I do want, however, to introduce a very helpful development tool: the “Test” Component.
In this case, I’m using it to log the message details, but you can also use it simulate an exception (using the throwException and exceptionToThrow attributes ), simulate a wait time by using the “waitTime” attribute (very helpful when testing timeouts), and modify the payload (with the attribute “appendString”), among other things.

Of course there are plenty more options, and we have yet to cover the “exception” scenario in which you’ll need to handle a thrown exception. (For that you’ll need to stay tuned for a future post in which we’ll expand this example.)

Now to finish, here is the complete Mule configuration file

I hope you find this useful! Please feel free to post questions and comments here or in the forums.