stale Oracle processes

[compostellas]

Hello,

My application uses Oracle 9i, release 2 and is using the DataSourceTransactionManager. My DAO's are using a TransactionProxyFactoryBean with the following transaction attributes:

PROPAGATION_REQUIRED (for inserts, updates, & deletes)

and

PROPAGATION_REQUIRED, readOnly (for selects)

The problem is that these database connections are creating stale oracle processes with the following sql (from V$SQL):

"SET TRANSACTION READ WRITE"

and

"ALTER SESSION SET TIME_ZONE='-4:00'"

Should I be using different transaction attributes or additional configuration that I can change to eliminate the dreaded "ORA-00020 maximum number of processes (string) exceeded" error?

Thanks!!

[trisberg]

Could you post more of you configuration? What do you mean by "stale" processes? My guess is that your connections aren't released properly so your DataSource ends up creating a new connection for each call.

[compostellas]

Hi Thomas,

Thanks for the reply. When I say 'stale processes' I mean that Spring seems to create an oracle process and then lose it. Eventually, I get a "ORA-00020" error. I am running Oracle 9i on Solaris in dedicated server mode. Here is a snippet of my applicationContext.xml:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName"><value>oracle.jdbc.driver.O racleDriver</value></property>
<property name="url"><value>jdbc:oracle:thin:@localhost:1521 :database</value></property>
<property name="username"><value>username</value></property>
<property name="password"><value>password</value></property>
<property name="defaultAutoCommit"><value>false</value></property>

<property name="maxActive"><value>7</value></property>
<property name="logAbandoned"><value>true</value></property>
</bean>

<!-- NativeJdbcExtractor for the Commons DBCP connection pool above -->
<!-- (just needed for oracleLobHandler) -->
<bean id="nativeJdbcExtractor" class="org.springframework.jdbc.support.nativejdbc .CommonsDbcpNativeJdbcExtractor"
lazy-init="true"/>

<!-- LobHandler for Oracle JDBC drivers -->
<!-- (refers to the NativeJdbcExtractor above to get access to native OracleConnections) -->
<bean id="oracleLobHandler" class="org.springframework.jdbc.support.lob.Oracle LobHandler"
lazy-init="true">
<property name="nativeJdbcExtractor"><ref local="nativeJdbcExtractor"/></property>
</bean>

<!-- LobHandler for well-behaved JDBC drivers -->
<!-- (simply delegating to corresponding PreparedStatement and ResultSet methods) -->
<bean id="defaultLobHandler" class="org.springframework.jdbc.support.lob.Defaul tLobHandler"
lazy-init="true"/>

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSou rceTransactionManager">
<property name="dataSource"><ref local="dataSource"/></property>
</bean>

<!--
- Document Transactions
-->
<bean id="documentDao" class="com.foo.bar.DocumentDaoJdbc">
<property name="dataSource"><ref local="dataSource"/></property>
</bean>
<bean id="documentDaoTx" class="org.springframework.transaction.interceptor .TransactionProxyFactoryBean">
<property name="transactionManager"><ref bean="transactionManager"/></property>
<property name="target"><ref bean="documentDao"/></property>
<property name="transactionAttributes">
<props>
<prop key="update*">PROPAGATION_REQUIRED</prop>
<prop key="get*">PROPAGATION_REQUIRED,readOnly</prop>
</props>
</property>
</bean>

<!--
- Source Transactions
-->
<bean id="sourceDao" class="com.foo.bar.SourceDaoJdbc">
<property name="dataSource"><ref local="dataSource"/></property>
<property name="lobHandler"><ref local="oracleLobHandler"/></property>
</bean>
<bean id="sourceDaoTx" class="org.springframework.transaction.interceptor .TransactionProxyFactoryBean">
<property name="transactionManager"><ref bean="transactionManager"/></property>
<property name="target"><ref bean="sourceDao"/></property>
<property name="transactionAttributes">
<props>
<prop key="save*">PROPAGATION_REQUIRED</prop>
<prop key="get*">PROPAGATION_REQUIRED,readOnly</prop>
</props>
</property>
</bean>

When I load a document, I call both the "sourceDaoTx" and the "documentDaoTx" beans.

If I run the following dianostic query, I can watch the 'stale' processes grow, when the code to the transactions above are called. BTW, the dao calls are primarily 'selects'.

select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,9) box,
substr(b.username,1,10) username,
-- b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program,
substr(b.status,1,8) status,
b.paddr,
substr(to_char((sysdate-logon_time)*24),1,5) hrold
from
v$session b,
v$process a
where
b.paddr = a.addr
order by spid;

I have seen this behavior when doing standard Oracle and JDBC if a result set, statement, prepared statement, or connection is not closed properly. It was my understanding that the Spring JDBC API would handle this housekeeping for me. Is that accurate?

Thanks for your help!

-James

[trisberg]

Looks OK. What happens if you take out the readOnly setting? Could you also post a log at the DEBUG level?

[compostellas]

Ok, I did as you suggested and here are the log results (the number of 'stale' connections still grows).

2005-06-27 05:55:43,202 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [get*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:43,202 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [update*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:43,573 INFO org.springframework.jdbc.support.SQLErrorCodesFact ory - SQLErrorCodes loaded: [DB2, HSQL, MS-SQL, MySQL, Oracle, Informix, PostgreSQL, Sybase]
2005-06-27 05:55:43,583 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - Looking up default SQLErrorCodes for DataSource [org.apache.commons.dbcp.BasicDataSource@15f4a7f]
2005-06-27 05:55:43,593 DEBUG org.springframework.jdbc.datasource.DataSourceUtil s - Opening JDBC connection
2005-06-27 05:55:44,414 DEBUG org.springframework.jdbc.datasource.DataSourceUtil s - Closing JDBC connection
2005-06-27 05:55:44,414 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - Database product name cached for DataSource [org.apache.commons.dbcp.BasicDataSource@15f4a7f]: name is 'Oracle'
2005-06-27 05:55:44,414 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - SQL error codes for 'Oracle' found
2005-06-27 05:55:44,424 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [get*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:44,424 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [save*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:44,454 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [get*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:44,454 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [update*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:44,484 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - Looking up default SQLErrorCodes for DataSource [org.apache.commons.dbcp.BasicDataSource@15f4a7f]
2005-06-27 05:55:44,484 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - Database product name found in cache for DataSource [org.apache.commons.dbcp.BasicDataSource@15f4a7f]: name is 'Oracle'
2005-06-27 05:55:44,484 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - SQL error codes for 'Oracle' found
2005-06-27 05:55:44,484 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [get*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:44,484 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [update*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,020 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [get*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,020 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [update*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,030 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - Looking up default SQLErrorCodes for DataSource [org.apache.commons.dbcp.BasicDataSource@15863e4]
2005-06-27 05:55:56,030 DEBUG org.springframework.jdbc.datasource.DataSourceUtil s - Opening JDBC connection
2005-06-27 05:55:56,091 DEBUG org.springframework.jdbc.datasource.DataSourceUtil s - Closing JDBC connection
2005-06-27 05:55:56,091 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - Database product name cached for DataSource [org.apache.commons.dbcp.BasicDataSource@15863e4]: name is 'Oracle'
2005-06-27 05:55:56,091 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - SQL error codes for 'Oracle' found
2005-06-27 05:55:56,101 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [get*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,101 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [save*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,111 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [get*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,111 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [update*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,111 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - Looking up default SQLErrorCodes for DataSource [org.apache.commons.dbcp.BasicDataSource@15863e4]
2005-06-27 05:55:56,111 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - Database product name found in cache for DataSource [org.apache.commons.dbcp.BasicDataSource@15863e4]: name is 'Oracle'
2005-06-27 05:55:56,111 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - SQL error codes for 'Oracle' found
2005-06-27 05:55:56,121 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [get*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,121 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [update*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,161 DEBUG org.springframework.transaction.interceptor.Transa ctionInterceptor - Getting transaction for com.ngit.browser.DocumentDao.getDocument
2005-06-27 05:55:56,171 INFO org.springframework.jdbc.datasource.JdbcTransactio nObjectSupport - JDBC 3.0 Savepoint class is available
2005-06-27 05:55:56,171 DEBUG org.springframework.jdbc.datasource.DataSourceTran sactionManager - Using transaction object [org.springframework.jdbc.datasource.DataSourceTran sactionManager$DataSourceTransactionObject@101fa9e]
2005-06-27 05:55:56,171 DEBUG org.springframework.jdbc.datasource.DataSourceTran sactionManager - Creating new transaction
2005-06-27 05:55:56,171 DEBUG org.springframework.jdbc.datasource.DataSourceUtil s - Opening JDBC connection
2005-06-27 05:55:56,171 DEBUG org.springframework.jdbc.datasource.DataSourceTran sactionManager - Opened connection [org.apache.commons.dbcp.PoolableConnection@eccfe7] for JDBC transaction
2005-06-27 05:55:56,181 DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Bound value [org.springframework.jdbc.datasource.ConnectionHold er@9daa17] for key [org.apache.commons.dbcp.BasicDataSource@15863e4] to thread [TP-Processor3]
2005-06-27 05:55:56,181 DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Initializing transaction synchronization
2005-06-27 05:55:56,191 DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing SQL query [select LOCATION, CLASSLVL, RECEIVE_DATE_TIME, LOCK_VALUE from INDEXED_OBJECTS where DATA_ID=?]
2005-06-27 05:55:56,191 DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHold er@9daa17] for key [org.apache.commons.dbcp.BasicDataSource@15863e4] bound to thread [TP-Processor3]
2005-06-27 05:55:56,441 DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHold er@9daa17] for key [org.apache.commons.dbcp.BasicDataSource@15863e4] bound to thread [TP-Processor3]
2005-06-27 05:55:56,451 DEBUG org.springframework.jdbc.core.StatementCreatorUtil s - Setting SQL statement parameter value: columnIndex 1, parameter value [16658], valueClass [java.lang.Long], sqlType 2
2005-06-27 05:55:56,581 DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHold er@9daa17] for key [org.apache.commons.dbcp.BasicDataSource@15863e4] bound to thread [TP-Processor3]
2005-06-27 05:55:56,631 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [get*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,631 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [update*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,641 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - Looking up default SQLErrorCodes for DataSource [org.apache.commons.dbcp.BasicDataSource@2e6c66]
2005-06-27 05:55:56,641 DEBUG org.springframework.jdbc.datasource.DataSourceUtil s - Opening JDBC connection
2005-06-27 05:55:56,701 DEBUG org.springframework.jdbc.datasource.DataSourceUtil s - Registering transaction synchronization for JDBC connection
2005-06-27 05:55:56,701 DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Bound value [org.springframework.jdbc.datasource.ConnectionHold er@1ced821] for key [org.apache.commons.dbcp.BasicDataSource@2e6c66] to thread [TP-Processor3]
2005-06-27 05:55:56,701 DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHold er@1ced821] for key [org.apache.commons.dbcp.BasicDataSource@2e6c66] bound to thread [TP-Processor3]
2005-06-27 05:55:56,701 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - Database product name cached for DataSource [org.apache.commons.dbcp.BasicDataSource@2e6c66]: name is 'Oracle'
2005-06-27 05:55:56,701 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - SQL error codes for 'Oracle' found
2005-06-27 05:55:56,711 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [get*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,711 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [save*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,721 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [get*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,721 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [update*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,731 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - Looking up default SQLErrorCodes for DataSource [org.apache.commons.dbcp.BasicDataSource@2e6c66]
2005-06-27 05:55:56,731 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - Database product name found in cache for DataSource [org.apache.commons.dbcp.BasicDataSource@2e6c66]: name is 'Oracle'
2005-06-27 05:55:56,731 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - SQL error codes for 'Oracle' found
2005-06-27 05:55:56,731 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [get*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,731 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [update*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,812 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [get*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,812 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [update*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,822 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - Looking up default SQLErrorCodes for DataSource [org.apache.commons.dbcp.BasicDataSource@428527]
2005-06-27 05:55:56,822 DEBUG org.springframework.jdbc.datasource.DataSourceUtil s - Opening JDBC connection
2005-06-27 05:55:56,882 DEBUG org.springframework.jdbc.datasource.DataSourceUtil s - Registering transaction synchronization for JDBC connection
2005-06-27 05:55:56,882 DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Bound value [org.springframework.jdbc.datasource.ConnectionHold er@1c8f91e] for key [org.apache.commons.dbcp.BasicDataSource@428527] to thread [TP-Processor3]
2005-06-27 05:55:56,882 DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHold er@1c8f91e] for key [org.apache.commons.dbcp.BasicDataSource@428527] bound to thread [TP-Processor3]
2005-06-27 05:55:56,882 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - Database product name cached for DataSource [org.apache.commons.dbcp.BasicDataSource@428527]: name is 'Oracle'
2005-06-27 05:55:56,882 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - SQL error codes for 'Oracle' found
2005-06-27 05:55:56,892 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [get*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,892 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [save*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,902 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [get*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,902 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [update*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,902 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - Looking up default SQLErrorCodes for DataSource [org.apache.commons.dbcp.BasicDataSource@428527]
2005-06-27 05:55:56,902 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - Database product name found in cache for DataSource [org.apache.commons.dbcp.BasicDataSource@428527]: name is 'Oracle'
2005-06-27 05:55:56,902 DEBUG org.springframework.jdbc.support.SQLErrorCodesFact ory - SQL error codes for 'Oracle' found
2005-06-27 05:55:56,912 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [get*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:56,912 DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [update*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
2005-06-27 05:55:57,212 DEBUG org.springframework.transaction.interceptor.Transa ctionInterceptor - Invoking commit for transaction on com.ngit.browser.DocumentDao.getDocument
2005-06-27 05:55:57,212 DEBUG org.springframework.jdbc.datasource.DataSourceTran sactionManager - Triggering beforeCommit synchronization
2005-06-27 05:55:57,212 DEBUG org.springframework.jdbc.datasource.DataSourceTran sactionManager - Triggering beforeCompletion synchronization
2005-06-27 05:55:57,212 DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Removed value [org.springframework.jdbc.datasource.ConnectionHold er@1ced821] for key [org.apache.commons.dbcp.BasicDataSource@2e6c66] from thread [TP-Processor3]
2005-06-27 05:55:57,212 DEBUG org.springframework.jdbc.datasource.DataSourceUtil s - Closing JDBC connection
2005-06-27 05:55:57,212 DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Removed value [org.springframework.jdbc.datasource.ConnectionHold er@1c8f91e] for key [org.apache.commons.dbcp.BasicDataSource@428527] from thread [TP-Processor3]
2005-06-27 05:55:57,212 DEBUG org.springframework.jdbc.datasource.DataSourceUtil s - Closing JDBC connection
2005-06-27 05:55:57,212 DEBUG org.springframework.jdbc.datasource.DataSourceTran sactionManager - Initiating transaction commit
2005-06-27 05:55:57,212 DEBUG org.springframework.jdbc.datasource.DataSourceTran sactionManager - Committing JDBC transaction on connection [org.apache.commons.dbcp.PoolableConnection@eccfe7]
2005-06-27 05:55:57,212 DEBUG org.springframework.jdbc.datasource.DataSourceTran sactionManager - Triggering afterCompletion synchronization
2005-06-27 05:55:57,212 DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Clearing transaction synchronization
2005-06-27 05:55:57,212 DEBUG org.springframework.transaction.support.Transactio nSynchronizationManager - Removed value [org.springframework.jdbc.datasource.ConnectionHold er@9daa17] for key [org.apache.commons.dbcp.BasicDataSource@15863e4] from thread [TP-Processor3]
2005-06-27 05:55:57,212 DEBUG org.springframework.jdbc.datasource.DataSourceTran sactionManager - Closing JDBC connection [org.apache.commons.dbcp.PoolableConnection@eccfe7] after transaction
2005-06-27 05:55:57,212 DEBUG org.springframework.jdbc.datasource.DataSourceUtil s - Closing JDBC connection




It seems strange that the DataSourceTransactionManager is only closing connections and not opening them. Each "Opening JDBC connection" entry
has a corresponding "Closing JDBC connection" entry asside from the DataSourceTransactionManager closing activity. Is it possible that a prepared statement and/or a result set is not being closed?

[trisberg]

Could you describe the environment where you are running this code? also, what does your code do - I only saw a single query executed in the log.

[compostellas]

My env looks like this:
-Solaris 8
-Oracle 9i
-Apache 2
-Tomcat 5

My code takes a document id and retrieves it's file path from the database. The file is opened and read into the system and the runs through a processing pipeline (which also does database calls to determine keywords to highlight, etc.). At the end of the pipeline process the document is rendered to the user.

All of the database calls are pretty basic. That's why I'm at a loss as to why so many oracle processes would be created and stay in an inactive/stale state.

[trisberg]

Could you post some of your code? Is all data access done via JdbcTemplarte or other Spring JDBC classes?

Also there seems to be several DAOs created - I see a lot of

DEBUG org.springframework.transaction.interceptor.NameMa tchTransactionAttributeSource - Adding transactional method [get*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]