Connection pooling for XADataSource

[daniel.zweifel]

Hi all

I'm accessing a SQLServer database from FuseESB. To better the performance I want to use a connection pool. I have found a connection pool that can handle XAConnection, the Tomcat jdbc pool (successor of Apache dbcp). Following is my blueprint DataSource configuration.

Now my problem:

The XAConnection are retrieved from the connection pool as expected and the transactions work. But the the connections are never closed/released to the pool! With the above configuration I can open 10 connections, then the pool is exhausted and no new connections can be opened.

Am I missing some config to fully integrate the connection pool with the transaction manager?

Btw. it works without the connection pool, opening a new connection each time.

Thank you for any hints and ideas

Daniel

[davsclaus]

How do you access the database from Fuse ESB? Do you write some code yourself, do you use Camel, and if so what and how do you do?

[daniel.zweifel]

No Camel involved.

Just plain Java code, accessing the database by injecting the entity manager:

<jpa:context property="entityManager" unitname="pu" />

[davsclaus]

I would assume you need in your code to call commit / or some other way with the JPA API to tell the entity manager you are done.

Then the entity manager can close its resources, and among those the connection, which then ought to be returned back to the pool; ready for re-use.

[daniel.zweifel]

Isn't the transaction manager supposed to do this itself? At least the commit is done by the transaction manager. This is configured with

     <tx:transaction method="*" value="Required" />

After the outermost method with this configuration the transaction manager commits the transaction (this works).

I would also expect the transaction manager to then close the connection. The connection pool then could retrieve the connection back into the pool.

[davsclaus]

Yeah I would assume as well when you use declarative TX, so when you leave its scope it would call commit for you.

I suggest to build the example outside OSGi, eg as an unit test, and see if you can get it working.

I assume you are using Spring, so there should be plenty of JPA + Spring TX + Annotations examples out there.

[daniel.zweifel]

No, I'm using Blueprint and that's why I don't find any examples using pooled XADataSources.

As you mention examples outside OSGi and blueprint exist and they work. To me it seems that the problem lies somewhere in the interaction between the Aries transaction manager and the connection pool in this environment.

[paul.h]

I use tomcat jdbc pool too, but with spring JdbcTemplate and I have same symptoms.

I digg in sources of next libraries:

- org.apache.aries.transaction/org.apache.aries.transaction.jdbc/1.0.1.fuse-71-047

- org.apache.aries.transaction/org.apache.aries.transaction.manager/1.0.1.fuse-71-047

- spring-jdbc

- tomcat jdbc-pool

and found, that JdbcTemplate don't close connection if it's the transactional, and aries hasn't any option and code to release connection after global transaction commit or rollback.

I have next situation (my method test() annotated with @Transacted):

1) I execute update method on jdbcTemplate in my test() method. Spring container propagate global transaction context to it and after finishing update not release connection.

2) I execute next methods test1(), test2(), etc in same global transaction context

3) Transaction manager execute auto commit after all and also not realese used jdbc connections.

4) After next N executions getConnection() at tomcat jdbc pool datasource object I catch exception with message:

Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:50; busy:50; idle:0; lastwait:30000]

Pool consider that all connections is busy, but actually all 50 connections simply is not released!

It's very bad, all this is supported out-of-box by any application server.

Workaround for tomcat jdbc-pool: removeAbandoned = true, but it is crutches.

[paul.h]

Theme up!

Any comments? Any best practices or workarounds?