Fuse distributed tx manager doesn't release DB sessions

Question

We have an OracleXADataSource that is being wrapped by Apache Aries in Fuse Fabric (like in this article). If I keep sending a lot of request to the server, it starts throwing the following error:

Caused by: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

When I check the sessions using the following query, after every request in Oracle, it keeps showing an increased number under current utilization.

select resource_name, current_utilization, max_utilization, limit_value 
from v$resource_limit 
where resource_name in ('sessions', 'processes', 'transactions');

CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE processes 545 768 800 sessions 553 774 1222 transactions 0 0 UNLIMITED

Most of the recommendations for this issue says to increase the processes and session limits in Oracle, but this would solve the problem temporarily, until we reach a certain load I'm affraid.

I found/tried the followings so far:

• Perodically when the load increases (or certain amount of time spent) the session and processes get decreased with a bigger amount (100-200). (I guess Geronimo periodically releases the sessions). At the same time when a number of sessions are released, the active transactions column shows the same amount:

CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE processes 355 768 800 sessions 363 774 1222 transactions 122 122 UNLIMITED

• If I shut down Fuse, the processes values goes back to initial size immediately (so the issue is on client side)
• If I turn off the distributed transaction support, then everything is fine and processes doesn't increase at all

• I tried adding pooling to the OracleXADataSource, but nothing has changed (it's deprecated, but I assume it still works. We don't have the UCP jar unfortunately, so I couldn't test it with that)

  <property name="connectionCachingEnabled" value="true"/>
  <property name="connectionCacheProperties">
      <props merge="default">
          <prop key="InitialLimit">1</prop>
          <prop key="MinLimit">1</prop>
          <prop key="MaxLimit">1</prop>
      </props>
  </property>
  

Answer 1

I couldn't resolve this issue using Aries unfortunately. I consider it a bug. However I managed to make it properly work using Atomikos, which I strongly recommend. Much more straightforward than using Aries' built in auto-proxy behavior: you declare everything so you know what actually happens.

<bean id="transactionManager" class="com.atomikos.icatch.jta.UserTransactionManager" init-method="init" destroy-method="close">
    <property name="forceShutdown" value="false" />
</bean>

<bean id="userTransaction" class="com.atomikos.icatch.jta.UserTransactionImp"> 
   <property name="transactionTimeout" value="300" /> 
</bean> 

<bean id="jtaTransactionManager" class="org.springframework.transaction.jta.JtaTransactionManager"> 
   <property name="transactionManager" ref="transactionManager" /> 
   <property name="userTransaction" ref="userTransaction" /> 
</bean>

<bean id="dataSource" class="com.atomikos.jdbc.AtomikosDataSourceBean">
    <property name="uniqueResourceName" value="oracledb" />
    <property name="xaDataSource">
        <bean class="oracle.jdbc.xa.client.OracleXADataSource">
            <property name="URL" value="jdbc:oracle:thin:@${db.host}:${db.port}:${db.sid}"/>
            <property name="user" value="${db.schema}" />
            <property name="password" value="${db.password}" />
        </bean>
    </property>
</bean>