How to work with JDBC and XA Transactions

March 7 2012

7 comments 0

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.


We'd love to hear your opinion on this post

7 Responses to “How to work with JDBC and XA Transactions”

  1. Hi German,

    You should warn Mule users that Oracle UCP is currently with Tomcat Connection Pool the ONLY supported connection pools by Mule ESB in order to use XA Transactions and JDBC Connector.

    As a matter of fact, Mule ESB supports ONLY pooled datasources implementing XADatasource. Most of available connection pools (Apache common DBCP, XAPool, application server pools, …) do not provide this kinda datasource.

    Moreover If Mule is embedded in an webapp using the connection pool provided by the application server, (eg. JBoss AS xa-datasource), it will not work because these datasources don’t implement neither the interface expected by Mule ESB to hanle correctly XA Transactions.

    I have already reported this on Mule JIRA and forum (http://www.mulesoft.org/jira/browse/MULE-5989).

    Kind regards,

    Bertrand

    Agree(0)Disagree(0)Comment
  2. This is an interesting blog-post but I seem to be missing a certain use-case.

    Suppose you have a jms inbound-endpoint and you have to perform some complex business logic on the object. You decided to leverage the logic to a spring-component because it cannot be modelled using mule. The business logic makes changes in the database and must share the same transaction as used by the jms-endpoint.

    How would one go about registering the jdbc connection, injected by spring into the component, into the xa-transaction started by mule so they are considered as a whole?

    Agree(0)Disagree(0)Comment
  3. Nice post.
    The important part of this post is that you need to use UCP as your connection pool when using XA.
    This is the only pool that works correctly with XA transactions in mule. (We tried all the know opensource pools available)

    When you do not use UCP you will get an error saying:
    Bound resource ….. is neither a MuleXaObject nor XAResource

    Agree(0)Disagree(0)Comment
  4. @Tom
    I have the same requirements and I have already reported this issue on Mule JIRA and forum (http://www.mulesoft.org/jira/browse/MULE-5990) as well.

    @All Mule ESB users and blog followers
    Please vote for both issues !

    Kind regards,

    Bertrand

    Agree(0)Disagree(0)Comment
  5. […] new features the software we are using very day is developing, and in one of that readings, i found this article about XA transactions and jdbc transport. We are using XA transactions extensively in Mule, so it […]

    Agree(0)Disagree(0)Comment
  6. Just in case anyone is interested, we have successfully integrated tomcat’s pool to work with Mule. As Jeff says, Oracle’s and Tomcat’s are the only pools that work as expected.

    If you want some details about our experience, please see: http://tecnologia.2020mobile.es/tomcat-jdbc-pool-and-mule/

    Thank you in advance,

    Agree(0)Disagree(0)Comment
  7. Hi German Solis,

    I have this one issue when I tried to call 2 different datasource within a flow. I am using 2 different connectors. First one is Oracle connector which is fetching all the rows from a table. Then, I have a MSSQL connector which is calling a stored procedure. Now, from the flow, I have an inbound endpoint which is referencing the oracle connector and then i have a foreach loop (to iterate thorugh the result set) in which I have an outbound endpoint which is calling the MSSQL connector. Below is the error I am getting :
    Can you please look at it and let me know if you find any solution.

    Message : Endpoint is transactional but transaction does not support it
    Code : MULE_ERROR–2

    Exception stack is:
    1. Endpoint is transactional but transaction does not support it (org.mule.api.transaction.TransactionException)
    org.mule.transport.AbstractConnector:2015 (http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/api/transaction/TransactionException.html)
    ——————————————————————————–
    Root Exception stack trace:
    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)
    + 3 more (set debug level logging or ‘-Dmule.verbose.exceptions=true’ for everything)

    Agree(3)Disagree(1)Comment