0

I am using Spring 2.5; a SimpleJdbcTemplate using apache-commons-dbcp connection pooling. There is also a thread pool which spawns a thread when a query is run (several queries are performed and their results processed once all are complete, so the threads are spawned to run queries in parallel).

Eventually, all the threads are waiting on getConnection() in a TIMED_WAIT. No thread seems to get a Connection. I check the DB and the connections are all idle.

What is causing this? I have 10 connections as the max Pool size and 50-100 threads. How should I be configuring this? The DBA says that should be enough connections (and I agree since they are all idle).

The same thing happens with BoneCP as the datasource for the SimpleJdbcTemplate as well.

dontocsata
  • 3,021
  • 3
  • 20
  • 19
  • Guess, you don't close connection explicitly. – Victor Sorokin Dec 06 '11 at 22:00
  • 1
    I don't think the OP is not closing the connections since he's using `SimpleJdbcTemplate` that takes care of it properly. However obviously this would be the first thing to try. But are you sure these connections are idle? You have hundred threads and only 10 connections... If the SQL query takes majority of the thread time, 90 threads are guaranteed to wait for connection while remaining 10 are actually running the query (probably waiting for a result on `socketRead`). Are you sure **all** threads are waiting? – Tomasz Nurkiewicz Dec 06 '11 at 22:03
  • SimpleJdbcTemplate should be closing them. Looking through JConsole at the threads, yes, ALL of them are waiting. The queries take less than 500 ms on average to complete. I have left it for over 15 minutes to ensure it wasn't slow queries, with no results. – dontocsata Dec 06 '11 at 22:08
  • Do you have a validation query set on the pool? Could the database be timing out these connections and the pool not knowing about it? Some sort of `select 1` type of query is appropriate here. – Gray Dec 06 '11 at 22:28
  • There isn't a validation query, all of the connections are valid and working. The DBA confirms that whenever the issue arises. On the DB, we see all the connections being made, they are just idle. – dontocsata Dec 07 '11 at 16:36

1 Answers1

2

Turns out it was because in the middle of looping through a ResultSet, another query was started, so with enough first queries, the second ones cannot get a connection and everything deadlocks.

dontocsata
  • 3,021
  • 3
  • 20
  • 19