Does Mule support database connection pooling with XA transaction ?

• User impact:

  Very High
• Configuration:

  Hide

  see MULE_HOME/src/mule-3.1.2-src.zip/org/mule/test/integration/transaction/XABridgeJmsJdbcTestCase.java

  Show

  see MULE_HOME/src/mule-3.1.2-src.zip/org/mule/test/integration/transaction/XABridgeJmsJdbcTestCase.java
• Similar Issues:

  None

Even if Mule seems to accept a pooled DataSource on a JDBC connector, none of them (xapool, DBCP, ...) implements the interface javax.sql.XADataSource. So the DataSource is never wrapped in an instance of org.mule.transport.jdbc.xa.DataSourceWrapper when the DataDource is set on a org.mule.transport.jdbc.JdbcConnector.

...
   public void setDataSource(DataSource dataSource)
   {
     if ((dataSource instanceof XADataSource))
     {
        this.dataSource = new DataSourceWrapper((XADataSource)dataSource);
     }
     else
     {
        this.dataSource = dataSource;
     }
   }
...

As you should know, a org.mule.transport.jdbc.xa.DataSourceWrapper will create org.mule.transport.jdbc.xa.ConnectionWrapper instances and in particular, org.mule.transaction.XaTransaction.MuleXaObject instances.

It's not really an issue until you use this connector on an endpoint with a XA transaction. As soon as you use this connector on a endpoint with a XA transaction, Mule will never enlist nor delist the corresponding resource in/from the Transaction because the resource doesn't not implement org.mule.transaction.XaTransaction.MuleXaObject nor javax.transaction.xa.XAResource. As a matter of fact, none of the pooled DataSources creates resource instances implementing directly theses interfaces (that's also what happens when you use Mule embedded in a web application and obtain a connection from JNDI through a pooled DataSource setup on the application server).

...
    public synchronized void bindResource(Object key, Object resource) throws TransactionException
    {
      if (this.resources.containsKey(key))
      {
        throw new IllegalTransactionStateException(CoreMessages.transactionResourceAlreadyListedForKey(key));
      }

      this.resources.put(key, resource);

      if (key == null)
      {
        this.logger.error("Key for bound resource " + resource + " is null");
      }

      if ((resource instanceof MuleXaObject))
      {
        MuleXaObject xaObject = (MuleXaObject)resource;
        xaObject.enlist();
      }
      else if ((resource instanceof XAResource))
      {
        enlistResource((XAResource)resource);
      }
      else
      {
        this.logger.error("Bound resource " + resource + " is neither a MuleXaObject nor XAResource");
      }
   }
...

As a consequence, only an error log message is written without throwing any exception (?!?!):
"Bound resource xxx is neither a MuleXaObject nor XAResource"

Moreover, the corresponding resource is never returned back to the pool because only resource implementing org.mule.transaction.XaTransaction.MuleXaObject are closed on a rollback or a commit on a XA transaction.

...
   private Map resources = new HashMap();

   protected void closeResources()
   {
     Iterator i = this.resources.entrySet().iterator();
     while (i.hasNext())
     {
       Map.Entry entry = (Map.Entry)i.next();
       Object value = entry.getValue();
       if ((value instanceof MuleXaObject))
       {
         MuleXaObject xaObject = (MuleXaObject)value;
         if (!xaObject.isReuseObject())
         {
           try
           {
             xaObject.close();
             i.remove();
           }
           catch (Exception e)
           {
             this.logger.error("Failed to close resource " + xaObject, e);
           }
         }
       }
     }
   }
...

As a consequence you will finally got an exception when you pool is full because idle connections are not properly returned back to the pool. And these resources are never removed from the Map as well.

For completeness... I've also found a unit test for this usecase in Mule distribution under MULE_HOME/src/mule-3.1.2-src.zip/org/mule/test/integration/transaction/XABridgeJmsJdbcTestCase.java. When you execute this test, you will have a successful result instead of a failure because no exception is thrown ! But if you look at the log messages, you will find that the connection has never been enlisted nor delisted in/from the transaction because the resource is neither a MuleXaObject nor XAResource !