Deadlock issue in DBCP deployed on Tomcat

I'm using DBCP data source (with default configuration) in Spring configuration to manage my connections to the database, and I'm running into a deadlock condition when the number of clients increase.

I found that there is a deadlock issue in DBCP 1.2.1 which I was using, which was supposed to be resolved in 1.4. So I upgraded to 1.4, but the issue still persists.

In the thread dump, there are many threads blocked with the following stack trace on top:

   java.lang.Thread.State: WAITING on org.apache.commons.pool.impl.GenericObjectPool$Latch@b6b09e
at java.lang.Object.wait(Native Method)
at java.lang.Object.wait(Object.java:485)
at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1104)
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:200)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:350)
at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:261)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:101)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:160)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:开发者_如何学运维171)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:631)

Any suggestions are welcome!

---

I switched to c3p0, few years back. You can try that. I believe you didn't have to change much, it's just a game of configuration.

Somewhat related thread, Connection pooling options with JDBC: DBCP vs C3P0. Well, actually I made it related.

[edited, 19/10/12]

Tomcat 7 has a decent connection pool, The Tomcat JDBC Connection Pool.

---

Did you make sure the commons-pool version matches the dbcp version?

Also, I'm not seeing a deadlock in the stacktrace, it simply looks like you have threads waiting for connections to free up.. How many threads do you have trying to connect at the same time? How many connections have you configured for the pool etc..?

In debugging this kind of cases it's also useful to look at what the threads that have gotten a connection are doing.

---

Incrasing load on application is increasing requirement to concurrent connections. As your threads are hanging on borrowConnection() - means you are not having sufficient ActiveConnections available.

Incrkease maxActive in your datasource properties and set WHEN_EXHAUSTED_BLOCK to some time like 600ms - 1000ms. You will get No element available exception only after elapsed of 600ms -1000 ms.

---

I think this is caused by not closing connections in your app code, so you just run out of connections in the pool. Maybe you should try to set the "removeAbandoned" property in DBCP. This is documented in http://commons.apache.org/dbcp/configuration.html as

Setting this to true can recover db connections from poorly written applications which fail to close a connection.

Best of luck!

---

I was facing similar issues and this was solved by following steps

1. Close all database resources in proper sequence

   resultSet.close();
   statement.close();
   connection.close();
   

Different drivers are implemented differently, some driver would still hand-on on the connection if underlying resultSet is not closed.

2. Apache DBCP defaults needs to be tuned

dataSource.setDefaultAutoCommit(true);
dataSource.setMaxActive(700); // make sure db server has it 800 dataSource.setRemoveAbandoned(true); dataSource.setTestOnBorrow(true); dataSource.setLogAbandoned(true); dataSource.setTestWhileIdle(true); dataSource.setTestOnReturn(true); dataSource.setRemoveAbandonedTimeout(60);

Make sure database server can allow atleast 50+ connecitions more than number specified in setMaxActive as the dbcp gives x new connections first and then try to clean up connections exceeding setMaxActive number. On clean-up dbcp shows which all connections were not closed on the server log/console.