DB connections not closing in OSGi

I have an OSGi bundle which needs to persist data in a database. As described in a previous stackoverflow question I have found that in order for transactions to work as expected I need to use an XADataSource to connect to the database. When I do so however I see that the connections to the database that are opened by my application are never closed, which of course results in the database not being able to accept any more connections after a while.

My setup is the following:

I have a bundle which creates the datasource and which only includes a blueprint.xml file with the following content

<blueprint xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0">

  <bean id="dataSource" class="com.mysql.jdbc.jdbc2.optional.MysqlDataSource">
      <property name="url" value="jdbc:mysql://localhost:3306/myschema"/>
      <property name="user" value="user"/>
      <property name="password" value="pass"/>
  </bean>

  <service interface="javax.sql.XADataSource" ref="dataSource">
    <service-properties>
            <entry key="osgi.jndi.service.name" value="jdbc/mysqlds"/>
    </service-properties>
  </service>
</blueprint>

Then in the bundle that persists my data I have a persistence.xml

<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">

    <persistence-unit name="mypu" transaction-type="JTA">
        <jta-data-source>osgi:service/javax.sql.DataSource/(osgi.jndi.service.name=jdbc/mysqlds)
        </jta-data-source>
    </persistence-unit>

</persistence>

And I specify that my service methods should run in a transaction in my blueprint.xml

<bean id="MyServiceImpl"
          class="com.test.impl.MyServiceImpl">
    <jpa:context property="em" unitname="mypu" />
    <tx:transaction method="*" value="Required" />
</bean>
<service id="MyService" ref="MyServiceImpl" interface="com.test.api.MyService" /> 

I deploy by bundles in Karaf, using Aries and OpenJPA for persistence, while I have also deployed the Aries transaction wrapper bundle (org.apache.aries.transaction.wrappers) in order to enlist my XA resources with the transaction manager.

Any ideas what I am missing in my configuration?

Edit: After some more searching I found this DBCP issue which suggests that the problem I'm having is a bug of DBCP with MySQL. However I'm at a loss on how to replace DBCP with some other Connection Pool implementation OpenJPA can work with. Any suggestions are more than welcome.

I used commons-dbcp to have a connection pool that also enlists XA Connections with the following configuration:

<bean id="myXAEnabledConnectionPoolDataSource" class="org.apache.commons.dbcp.managed.BasicManagedDataSource" destroy-method="close">
    <property name="xaDataSourceInstance" ref="mysqlXADataSourceBean" />
    <property name="transactionManager" ref="transactionManager" />
</bean>

You can get the transaction manager as a reference based on the interface javax.transaction.TransactionManager.

In this way commons-dbcp will handle the lifecycle of the connections properly. Please note that the destroy method is there so when the blueprint container stops the connection pool will be closed.

Edit:

1-2 years ago I had the same problem but with PostgreSQL. I debugged aries.transaction.wrapper at that time a lot but I cannot remember exactly the cause why I left it out. I think the motivation was behind that commons-dbcp is a solution that worked for me in previous projects while I could not fix aries.transaction.wrapper even after analyzing it's code a lot.

Please note that MysqlDataSource is not a connection pool. It gives back a new connection always when you need one. It is also not XA enabled. MysqlXADatasource is XA enabled so you should probably instantiate an object from that class. However, an XADataSource is responsible only to give back XAConnections for you but not for enlisting them. That is where a ManagedConnectionPool can help. A managed connection pool does the followings:

• Wraps all provided Connection objects with a custom managed connection class
• In case close is called on a connection, it is not closed if there is an ongoing transaction. It is only closed (added back to the pool) when a transaction commit or rollback is done
• In case a connection is queried from the pool and there was a connection also provided in the same transaction, the same transaction will be returned (that was a difficult sentence :))

Sometimes JDBC drivers provide connection pools and even managed connection pools, however, it is better to use the JDBC driver only to get new connections and wrap it with a 3rdparty library that was tested in several projects and works for sure.