I have a jdbc pool in tomcat which is used to establish database connections.I am not explicitly closing the connection object after use.My 'maxActive' parameter is set to 100.The application runs smoothly for some time but then it fails to do database queries.It waits for infinite time to query to database.
Why is the 'removeAbandoned' parameter not returning unused connection objects back to the pool?
Resource name="jdbc/Dhaval/Application"
auth="Container" type="javax.sql.DataSource" driverClassName="oracle.jdbc.driver.OracleDriver" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
url="jdbc:oracle:thin:@XXXXXXX:orcl" username="XXXXXX" password="XXXXXX"
maxActive="5" maxIdle="2" maxWait="-1" logAbandoned="true" removeAbandoned="true" removeAbandonedTimeout="10"
You always need to explicitly call close() on a Connection, even if it is handed out by a connection pool. The call to close() is a signal to the connection pool that the Connection is to be returned to the pool.
If you don't call close() the connection pool doesn't know the connection is available again and your pool gets exhausted (sometimes the pool has advanced features to reclaim connections, but these might take considerable time to kick in and/or depend on garbage collection happening).
The Connection handed out by a connection pool is a logical connection (usually some kind of wrapper or proxy), and the close() method doesn't actually close the connection, but performs some cleanup like closing ResultSet, Statement etc created from the logical connection, invalidating the logical connection so it can no longer be used and then signalling the connection pool.
The connection pool will then decide on based on its configuration and internal logic to either put the connection back in the pool, or to close the physical connection (eg because there already are too many idle connections in the pool).