17

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!

Adeel Ansari
  • 39,541
  • 12
  • 93
  • 133
Iravanchi
  • 5,139
  • 9
  • 40
  • 56
  • I'm facing a similar issue.. did you solve this problem.. could you please give some pointer if so.. – Sudheer Feb 25 '14 at 10:24
  • I switched to c3p0 too, was very pleased with the features and stability. I haven't used DBCP since, so I can't tell if the issue still exists or not. – Iravanchi Feb 28 '14 at 18:16

5 Answers5

8

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.

Community
  • 1
  • 1
Adeel Ansari
  • 39,541
  • 12
  • 93
  • 133
  • In the case of switching connection pools, how about jdbc-pool? DBCP shows better performance in benchmarks compared to c3p0, and jdbc-pool is faster than both. Any experience with that? – Iravanchi Apr 19 '11 at 11:02
  • No, I never used jdbc-pool. Hence, can't say anything about that. But after reading about that it sounds promising. As far as performance is concerned, this thread, http://people.apache.org/~fhanik/jdbc-pool/jdbc-pool.html, suggests that DBCP is poor in performance. – Adeel Ansari Apr 20 '11 at 03:27
  • DBCP is poor compared to jdbc-pool, but this benchmark shows c3p0 is slower than both (but probably more robust that DBCP as you said): http://www.tomcatexpert.com/blog/2010/03/22/understanding-jdbc-pool-performance-improvements – Iravanchi Apr 20 '11 at 06:45
  • 5
    In case anyone reading this question, I've switched to c3p0 as advised by @Adeel Ansari, and not having any issues in much higher loads since then (which is almost 1.5 years ago) – Iravanchi Oct 16 '12 at 07:29
1

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.

arviarya
  • 650
  • 9
  • 9
  • What you describe causes a slow-down. I had a clear deadlock (where available connections that are returned to the pool is no longer served) which was apparent from a thread dump. None of the threads were doing anything, all of them were blocked by the pool. You should read the question and answers more carefully before posting an answer. – Iravanchi Oct 16 '12 at 07:26
1

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.

Mikko Wilkman
  • 1,495
  • 10
  • 8
  • Both are latest versions, DBCP 1.4, POOL 1.5.5. There's also a bug in POOL 1.5.2 that shows exactly the same trace as mine does, and it should be fixed by 1.5.3. And I'm using 1.5.5. I haven't configured the pool options, so its using defaults. Number of threads are normally 30 to 50 in normal condition, but when the application deadlocks, Tomcat creates a thread for each request and all of them get blocked, so it reaches 150 or 200 threads. – Iravanchi Apr 19 '11 at 11:04
  • And ALL of the threads are waiting for a connection after the deadlock condition. There's no other threads actively doing anything. All of them eventually come to the point that they need a connection, and they get in a queue that never moves forward. – Iravanchi Apr 19 '11 at 11:06
  • Ok so are you sure you're actually returning the connections to the pool too? – Mikko Wilkman Apr 19 '11 at 11:35
  • Absolutely. I'm using Spring aspects in a simple configuration (you can see the effect in the stack trace). And this is not an issue in a low-concurrency setting (with 10 or 20 users). I have even written a debugging filter to count open/close in each thread and check if there is any leak. – Iravanchi Apr 19 '11 at 11:46
  • Strange, that blocking is really happening when the pool is exhausted and I wouldn't call it a deadlock in that situation.. How is the connection closing with spring aspects handled? – Mikko Wilkman Apr 19 '11 at 13:53
  • There is no thread busy with a connection, and all threads are waiting to get one. I guess the issue is not exhaustion of the pool. Spring TX aspect returns (closes) the connection when the method call underneath returns in any way (either returning or throwing). – Iravanchi Apr 20 '11 at 06:49
  • 1
    I'm still pretty sure the situation is simply caused by the connections not being returned to the pool. That doesn't require any thread to be actually using that connection anymore. Perhaps you could verify this by debug logging the actual pool status (active, idle etc.. connections) when the problem is live? If that shows that the pool is empty, I'd continue by debugging the Spring TX close logic and how it applies to the type of DataSource you're using. – Mikko Wilkman Apr 20 '11 at 07:28
0

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!

jcamillo
  • 31
  • 6
  • 1
    No, the issue is not that the pool is exhausted. I actually created wrappers and counted the open/close pairs. Plus, when there are connection leaks, c3p0 will block too, and pretty fast (I've tested it). The thread dump shows is clearly that the threads are deadlocked to each other. – Iravanchi Jul 24 '12 at 13:47
0

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.

  1. 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.

veer7
  • 20,074
  • 9
  • 46
  • 74