Using an external connection pool with TransactionsEssentials®

It is not possible to use a non-XA aware connection pool, the main reason has to do with exclusive connections release. This topic tries to give an exhaustive explanation of the problem.

IMPORTANT: application-servers and other platform vendors can use their own, XA-capable pools with Atomikos. It suffices for the pools to be aware of XA transactions and the details explained on this page.

Transactions multiplexing

The JTA specification mandates that a connection must be able to work with multiple transactions simultaneously. This is required because the transaction is not controlled anymore by the database but by an external component, the transaction manager.

This capability is called transactions multiplexing or transactions interleaving.

To make things clearer, let's take an example. Consider this code in a non-XA world:

 1. DataSource ds = getDataSource();
 2. 
 3. Connection c1 = ds.getConnection();
 4.  // do some SQL
 5. c1.commit();
 6. c1.close();

Line 1. Get a reference to a non-XA connection pool that implements javax.sql.DataSource.
Line 3. Get a connection from the pool. The pool reserves the physical connection, it won't give it to another call to getConnection().
Line 5. The transaction running on connection c1 gets committed.
Line 6. The connection is released back to the pool. It can now be acquired again by another call to getConnection().

Now let's take the exact same code but using JTA to control the transaction:

 1. UserTransaction ut = getUserTransaction();
 2. DataSource ds = getDataSource();
 3. 
 4. ut.begin();
 5.
 6. Connection c1 = ds.getConnection();
 7.  // do some SQL
 8. c1.close();
 9.
10. ut.commit();

Line 1. Get a reference to the JTA transaction manager's UserTransaction.
Line 2. Get a reference to an XA connection pool that implements javax.sql.DataSource.
Line 4. Start the JTA transaction.
Line 6. Get a connection from the pool. The pool reserves the physical connection, it won't give it to another call to getConnection().
Line 8. The connection is released back to the pool. It can now be acquired again by another call to getConnection().
Line 10. The transaction running on connection c1 gets committed.

This is fine at first look but what happens if this code gets interrupted between line 8 and 10 by another thread that tries to get a connection from the exact same pool ? And what if that other thread gets connection c1 back ?

Now you understand why a connection must be able to support multiple transactions at a time. The problem is that most databases do not support this.

The need for exclusive connections

Exclusive connections are a workaround for a database's lack of transactions multiplexing support. The idea is pretty simple: do not release the connection to the pool until after the JTA transaction has finished.

Taking back our previous example:

 1. UserTransaction ut = getUserTransaction();
 2. DataSource ds = getDataSource();
 3. 
 4. ut.begin();
 5.
 6. Connection c1 = ds.getConnection();
 7.  // do some SQL
 8. c1.close();
 9.
10. ut.commit();

Line 1. Get a reference to the JTA transaction manager's UserTransaction.
Line 2. Get a reference to an XA connection pool that implements javax.sql.DataSource.
Line 4. Start the JTA transaction.
Line 6. Get a connection from the pool. The pool reserves the physical connection, it won't give it to another call to getConnection().
Line 8. The connection is not released to the pool but marked as closed.
Line 10. The transaction running on connection c1 gets committed. All connections marked as closed that were part of the transaction are now released.

You can now see that only one transaction can execute on a connection thanks to this trick. The problem is that the connection must be aware of the JTA transaction to be called back when it finishes. This is fortunately an internal implementation detail that the application developer does not need to be aware of.

External non-XA pool

It sounds possible to use an external non-XA connection pool around a non-pooling DataSource that would be provided by the transaction manager's implementation.

How would that work ? Say that you have a non-pooling javax.sql.DataSource implementation that wraps a javax.sql.XADataSource. You could just wrap it with another javax.sql.DataSource implementation that would get connections from the wrapped non-pooling datasource and pool them.

Unfortunately, if you take the exclusive connections release problem into account it quickly appears that it might not be such a good idea. How would the wrapper tool know that it has to wait until the transaction is terminated before handing the connection to another thread ? It can't since it is not XA-aware by definition.

This is why (at least) basic connection pool capabilities should be provided with a transaction manager. spacer

Copyright © 2014 Atomikos BVBA. Transaction Management for Extreme Transaction Processing and SOA Environments serving ISV, Commercial, OEM and Open Source Markets
Site map RSS ATOM