spring - I am getting pool error Timeout waiting for idle object

Question

  <Resource name="jdbc/name" auth="Container" type="javax.sql.DataSource"
               maxActive="100" maxIdle="30" maxWait="10000"
               username="root" password="root" driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql:url?autoReconnect=true"/> 

I created connection pool using the above statement in Tomcat server.

Some android app is connecting with my application using web service.Through web service i am sending and receiving some data. I am getting the error

SQLState: null
[2013-12-05 14:13:06,156]ERROR069688[http-8080-10] - org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:78) -
 Cannot get a connection, pool error Timeout waiting for idle object

I saw Connection Pool Exception: Cannot get a connection, pool error Timeout waiting for idle object .

But no clue for me.

What are the reasons for this.

Thanks in advance...

Answer 1

This exception is stating that the pool manager cannot produce a viable connection to a waiting requester and the maxWait has passed therefore triggering a timeout. There are several potential causes, but they usually fall into two broad categories:

1. The DB is down or unreachable. This could be because you forgot to start it, or it crashed, or the network between you and the DB has stopped working. But basically, the pool has been unable to provide a valid new connection and so the requester has sat waiting for a new connection for longer than the timeout and has given up. This is usually the less likely case cause you usually see other errors when this happens.

2. The connection pool (which is set to 100 max active) is out of connections. This could be due to high demand volume, or it could be an indication of a connection leak where connections are never returned to the pool and eventually you exceed the max connections limit. This is the more likely cause... usually a connection leak due to not closing db resources when you're done with them.

Sometimes 1 and 2 to create this scenario if say the DB encounters an error where queries stop being executed. When that happens, its like a connection leak because new connections go out and block on a query against the DB and are never released. Eventually all the connections are active and the next thread to request a connection will be put in the wait queue because there are no more connections to give out. Since the DB's hosed, you won't see any other exceptions until the first requester who times out in the wait queue. We used to see this occasionally using Oracle UCP when the Oracle DB backend crashed.

I'd recommend using JConsole to monitor the DB pool sizes when this happens and determine which of the two above categories of causes this error is triggered by. And then, you can try to fix the connection or manipulate the pool size/timeout parameters to accommodate the real demand (you can also manipulate connector parameters in Tomcat to reduce the overall demand on the application). If you're running in Tomcat, you can usually just run JConsole (which is in the JDK bin directory) and attach to the tomcat process and then just look through the JMX console to find where the pool size is found... that usually depends on the data source type (dbcp vs oracle ucp vs etc). Then, you can double click on the field values to plot them so you can track them over time.

Also, you may need to enable JMX in Tomcat for JConsole JMX monitoring to work.