Hanging indefinitely with Spring and org.apache.commons.dbcp.BasicDataSource name, class, property, status

amy

mit(status);Both the txManager and the jdbcTemplate are using an org.apache.commons.dbcp.BasicDataSource, configured thusly:
lt;bean id=quot;dataSourcequot; class=quot;org.apache.commons.dbcp.BasicDataSourcequot; destroy-method=quot;closequot;gt;       lt;property name=quot;usernamequot; value=quot;@db.username@quot;/gt;       lt;property name=quot;passwordquot; value=quot;@db.password@quot;/gt;       lt;property name=quot;uclquot; value=quot;@db.ucl@quot;/gt;       lt;property name=quot;driverClassNamequot; value=quot;@db.driver@quot;/gt;       lt;property name=quot;initialSizequot; value=quot;0quot;/gt;       lt;property name=quot;maxActivequot; value=quot;20quot;/gt;       lt;property name=quot;minIdlequot; value=quot;0quot;/gt;       lt;property name=quot;poolPreparedStatementsquot; value=quot;falsequot;/gt;       lt;property name=quot;defaultAutoCommitquot; value=quot;falsequot;/gt;       lt;property name=quot;defaultTransactionIsolationquot; value=quot;1quot;/gt;       lt;property name=quot;testOnBorrowquot; value=quot;truequot;/gt;       lt;property name=quot;testOnReturnquot; value=quot;truequot;/gt;       lt;property name=quot;testWhileIdlequot; value=quot;truequot;/gt;       lt;property name=quot;timeBetweenEvictionRunsMillisquot; value=quot;10000quot;/gt;       lt;property name=quot;validationQueryquot; value=quot;select 1 from dualquot;/gt;
lt;/beangt;There's the setup. Here's the problem: Earlier tonight I was running into a problem where all of my threads would hang indefinitely. Starting a remote debugging session revealed that they were all blocking on getTransaction, which was in turn blocking on the connection pooling object, because the pool had no idle connections left.

This struck me as really strange, because I assumed that once I called commit, the txManager would return the connection to the pool, so I should never flat-out run out of connections; the worst case, I assumed, would be a slowdown if the pool couldn't keep up with demand.

So I kept digging around the debugger and the Spring and Commons documentation, until I toggled something that seems to have fixed the problem -- I set nestedTransactionAllowed to false in the txManager bean. No more hangs!

So, does this make sense to you Spring experts out there? I'm a little puzzled. The documentation says that DefaultTransactionDefinition sets PROPAGATION_REQUIRED -- why would the system ever try to create a nested transaction?

Thanks in advance for any help you can offer. --Lawrence

you know i have the exact same problem.

But that didnt help.

Btw isnt the nestedTransactionAllowed set to false by default anyway?

Here's something to consider -- I was using an old MySQL JDBC driver, one that was for MySQL 4.1, when I'd upgraded the DB to 5.0. I'm using version 3.1.13 now.

I have a similar experience. I was declaring hibernate interceptors AND hibernate transaction managers on the SAME bean (my Facade).  i fould that even after commit, the connection was not released to the pool.  

The connection stays bound to the thread.  So other threads get starved.  But when a different request hits the same thread to which the connection was bound, things work ok, but only for that thread.  Somehow the effect is that the connection gets bound to a thread forever and never released to the pool!!

When I removed Hibernate Interceptors out of the picture, and keep only the Hib Tx Manager, the connections are being released to the pool and everything seems fine.

From what I understand from the documentation, Hib Interceptors and Hib Tx Managers should have coexisted harmoniously, even if declared on the same bean.  

Puzzled!

Did you try changing:

lt;property name=quot;defaultAutoCommitquot; value=quot;falsequot;/gt;

to true?

pearl8: Thanks for the suggestion, because I did leave those declarative tx beans in my config even though I wasn't using them. However, the problem's still happening.

dejanp: Wouldn't that break transactions?

No, Spring will set autocommit off when you start the transaction.

Does anyone know the reason that problem occurs?  I'm getting it too, and cant' understand why: I'm sure our Oracle / Tomcat / apache commons dbcp BasicDataSource combination should be very common.


Originally Posted by stanrosDoes anyone know the reason that problem occurs?  I'm getting it too, and cant' understand why: I'm sure our Oracle / Tomcat / apache commons dbcp BasicDataSource combination should be very common.

It's hard to tell what's common and what's not if you don't paste your datasource config here.

mons.dbcp.BasicDataSourcequot; destroy-method=quot;closequot;gt;       lt;property name=quot;driverClassNamequot; value=quot;oracle.jdbc.driver.OracleDriverquot; /gt;       lt;property name=quot;uclquot; value=quot;jdbcracle:thinhost:port:dbquot; /gt;       lt;property name=quot;usernamequot; value=quot;usernamequot; /gt;       lt;property name=quot;passwordquot; value=quot;passwordquot; /gt;       lt;property name=quot;defaultAutoCommitquot; value=quot;truequot;/gt;       lt;property name=quot;defaultTransactionIsolationquot; value=quot;2quot;/gt;   lt;/beangt;
Thanks for your interest in this.