Natural Born Coder

Local transaction already has 1 non-XA Resource

—

by

doozer
in

While developing a bit of functionality that transferred data from one database to another in my current JEE 6 web application I came upon the exception below. Surprisingly for exceptions from deep within system this one is actually quite informative if you understand what it’s saying: the key is knowing that XA means distributed transaction. Transactions can be one of two types either local or non-distributed or distributed. A local transaction is what you are normally doing, the transaction involves a single resource (a database connection for example) which is doing all the transactional work itself.

WARNING: RAR7132: Unable to enlist the resource in transaction. 
    Returned resource to pool. Pool name: [ MyPool ]
WARNING: RAR5117 : Failed to obtain/create connection from connection pool [ MyPool ]. 
    Reason : com.sun.appserv.connectors.internal.api.PoolingException: java.lang.IllegalStateException: 
    Local transaction already has 1 non-XA Resource: cannot add more resources. 
WARNING: RAR5114 : Error allocating connection : [Error in allocating a connection. 
    Cause: java.lang.IllegalStateException: Local transaction already has 1 non-XA Resource: 
    cannot add more resources.  ]

A distributed, or global, transaction utilizes the services of a transaction manager which works with more than one resource in order to coordinate a single “global” transaction across all resources involved. In my case those two resources are two database connections to different databases.

Why do we need distributed transactions?

When you make a change to data you often want that change to be applied in an atomic manner – either the change happens or it doesn’t. This desire for atomicity is still present when more than one data source is involved it’s just much harder to ensure that it actually happens. Imagine the situation where money was being transferred between two banks. One step debits money from your account the second credits it to a different account. You would want to be very sure that step one rolled back if step two failed!

Transaction Managers

Transaction managers are built into JEE containers like GlassFish and are started automatically when needed so code can insert / delete / etc from multiple resources and when a rollback is issued it rolls back all resources involved. The protocol that allows this to all work is called two phase commit (2PC) which essentially work by having all the resources involved in the transaction vote on whether the overall transaction should be committed. If anyone votes no the whole transaction is rolled back.

Generally a data source has to be XA aware for it to participate in an XA transaction, however, some transactions managers (the GlassFish default one being a good example) implement a last participant optimization which allows one non-XA resource to partake in an XA transaction.

Fixing the Problem

So, the way to fix the error message is to either make sure that no transaction has to run in a distributed manner or to make sure that all resources that will participate in the transaction are XA aware.

Changing database connection pools from local only to distributed connections is fairly simple through the GlassFish interface. Login to the administration interface and navigate to Resources > JDBC > JDBC Connection Pools and select the pool that you want to configure. On the general tab selection XADataSource as the Resource Type and then set the classname appropriately. In the case of MySQL this means switching the class from:

com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource

to

com.mysql.jdbc.jdbc2.optional.MysqlXADataSource

In my particular case though this doesn’t result in a happy resolution to the original exception. If I make this change I then get this exception message:

XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state

The reason for this second exception is down to what I’m trying to do in the code. I’m importing data from one database into newly created tables in the target database. Create table statements have an implicit commit (in MySQL at least) so the first table creation tries to end the transaction. The connection is, however, in the active state which doesn’t allow for implicit commits and causes the above exception to be thrown. The only solution open at this point is to reorganize the code such that there are no distributed transactions in this area.