max processes exceeded with Transactions and Oracle!

[jadams68]

We're using Spring with Oracle and having an issue with our Oracle9i database where the jdbc connection counts are going through the roof...like Spring is not handling the connection close.

The transactions are XA transactions with Oracle and our DB2 database. They work, but they are leaving an open thread on the oracle side.

TIA!!!

[Rod Johnson]

What connection pool do you use? I assume you're using Spring JDBC, rather than any manual connection handling?

[jadams68]

Yes, Spring JDBC.
Currently we are going down the path that you need to use the same Id/Pwd for both Oracle and DB2 with connect/create session privileges.

We're basically seeing open connections/sessions on the oracle side.

[Rod Johnson]

Please show your DataSource bean definition.

[jadams68]

It's a JNDI reference, but this is the xml...
I could go and get you any specific values from the datasource properties in Websphere if you like...

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans SYSTEM "qvc-spring-beans.dtd" >

<beans>
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSou rceTransactionManager">
<property name="dataSource">
<ref bean="dataSourceDB2"/>
</property>
</bean>
<bean id="opportunityTransactionManagerTarget"
class="com.qvc.customerservices.app.ess.dao.Opport unityTransactionManagerImpl">
<property name="scheduleOpportunityDAO">
<ref bean="scheduleOpportunityDAO"/>
</property>
<property name="eligibilityRuleDAO">
<ref bean="eligibilityRuleDAO"/>
</property>
<property name="scheduleOpportunityRequestDAO">
<ref bean="scheduleOpportunityRequestDAO" />
</property>
</bean>
<bean id="opportunityTransactionManager"
class="org.springframework.transaction.interceptor .TransactionProxyFactoryBean">
<property name="transactionManager">
<ref bean="transactionManagerJTA"/>
<!--<ref local="transactionManager"/>-->
</property>
<property name="target">
<ref local="opportunityTransactionManagerTarget" />
</property>
<property name="transactionAttributes">
<props>
<!--<prop key="zxa*">PROPAGATION_REQUIRED, ISOLATION_READ_COMMITTED</prop>-->
<prop key="update*">PROPAGATION_REQUIRES_NEW</prop>
<prop key="do*">PROPAGATION_REQUIRED</prop>
</props>
</property>
</bean>

</beans>

[Rod Johnson]

Where is the definition of dataSourceDB2?

[jadams68]

It's a JNDI reference within the Websphere Data Source in the server config.

An update tho... we've done two things:

a) Granted the user on Oracle with CREATE SESSION privileges per this article:

http://www.di.unipi.it/~ghelli/bdl/A...1/jdbctran.htm

b) The project team was using two separate ID's..one for Oracle, one for DB2 and besides a bad practice, we may have confused the transaction manager with regards to using the connection session pool on Oracle.

We've begun testing again and we're not seeing the problem..."so far"... you know how that goes...let's give it some time...

Thanks!

[Rod Johnson]

If you want to use global transactions, you should use JtaTransactionManager.

Spring JDBC will close connections, so I'd be very surprised to see it leaking.

[jadams68]

Yes, sorry... bad cut and paste on my part...

Add this to the config.xml to get the whole picture...

<beans>

<!-- WebSphere transaction manager -->
<bean id="webSphereTransactionManager" class="org.springframework.transaction.jta.WebSphe reTransactionManagerFactoryBean" />

<!-- Transaction manager that delegates to JTA (for a transactional JNDI DataSource) -->
<bean id="transactionManagerJTA" class="org.springframework.transaction.jta.JtaTran sactionManager">
<property name="transactionManager">
<ref local="webSphereTransactionManager" />
</property>
</bean>
</beans>

[e_water_boy]

Where is the definition of dataSourceDB2?
I think it is necessity.