JDBC connection pool runs out of connections when Context reload="true" is enabled in Tomcat

Question

I am developing a Java EE web application in Eclipse Juno. I have configured Tomcat to use JDBC connection pool (org.apache.tomcat.jdbc.pool) along with PostgreSQL database. Here are the configurations in my project's META-INF/context.xml:

<?xml version="1.0" encoding="UTF-8"?>
<Context>
    <!-- Configuration for the Tomcat JDBC Connection Pool -->
    <Resource name="jdbc/someDB"
        type="javax.sql.DataSource"
        auth="Container"
        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
        driverClassName="org.postgresql.Driver"
        url="jdbc:postgresql://localhost:5432/somedb"
        username="postgres"
        password="12345"
        maxActive="100"
        minIdle="10"
        initialSize="10"
        validationQuery="SELECT 1"
        validationInterval="30000"
        removeAbandoned="true"
        removeAbandonedTimeout="60"
        abandonWhenPercentageFull="50" />
</Context>

My application is deployed to Tomcat using Eclipse, and in Tomcat's context.xml an attribute reloadable is set to "true" to automatically reload the web application if a change is detected:

<Context reloadable="true">

I have noticed that every time the above mentioned automatic reload is happening 10 more connections to PostgreSQL db is reserved (because in webapp's context.xml initialSize="10"). So after 10 changes a PSQLException is thrown:

org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
...

If I manually restart Tomcat - everything is fine and just 10 connections are reserved.

Does anybody know the way around this issue, so it could be possible to develop with reloadable set to "true" and not cause pooling more connections every time the context is reloaded?

Would appreciate any help.

P.S. Apache Tomcat Version 7.0.32

Most likely a duplicate of http://stackoverflow.com/questions/8435359/why-do-connections-persist-when-i-undeploy-a-webapp-using-the-tomcat-7-jdbc-conn — Isaac, Nov 28 '12 at 00:38
@Isaac "It has been corrected from Tomcat 7.0.11", but I have 7.0.32 and still the same result. So basically it's a bug? — informatik01, Nov 28 '12 at 00:45
Might be a regression. If you're absolutely sure that you are freeing up all connections, and the issue still happens, then I'd ask to reopen the bug report. — Isaac, Nov 28 '12 at 01:11
@Isaac Yup, closing all ResultSets, Statements/PreparedStatements, Connections in finally blocks. Anyways thanks for the help — informatik01, Nov 28 '12 at 09:14
There is a typo in the context.xml: validatonQuery="SELECT 1" validation misses an I. Noticed during copy/paste but no chance to edit the question. — Clerenz, Mar 03 '17 at 16:50

informatik01 · Accepted Answer · 2022-08-30T20:20:08.150

THE SOLUTION (tl;dr)

In order to solve this issue, add an attribute closeMethod (documented here) with the value "close" to the Resource element in the context.xml file.

Here's the correct content of my /META-INF/context.xml file:

<Context>
    <!-- Configuration for the Tomcat JDBC Connection Pool -->
    <Resource name="jdbc/someDB"
        type="javax.sql.DataSource"
        auth="Container"
        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
        driverClassName="org.postgresql.Driver"
        url="jdbc:postgresql://localhost:5432/somedb"
        username="postgres"
        password="12345"
        maxActive="100"
        minIdle="10"
        initialSize="10"
        validationQuery="SELECT 1"
        validationInterval="30000"
        removeAbandoned="true"
        removeAbandonedTimeout="60"
        abandonWhenPercentageFull="50"
        closeMethod="close" />
</Context>

Pay attention to the attribute closeMethod. I tested it and now the number of connections are kept STRICTLY as defined in the context.xml file!

NOTE
There is one moment (related to JNDI) that may be taken care of. See the UPDATE 3 for the complete description.

Long answer

OK, I found the above solution thanks to Apache Tomcat committor Konstantin Kolinko. I reported this issue as an Apache Tomcat bug on ASF Bugzilla and ~~it turned out it's not a bug~~ (see UPDATE 1).

=== UPDATE 1 (2012-12-03) a.k.a. "A New Hope" ===

Well, it still turned out to be a bug. Mark Thomas, the Apache Tomcat 7 release manager, confirmed that (quote):

"This is a memory leak bug in jdbc-pool. PoolCleaner instances are retaining references to the ConnectionPool preventing it from being GC'd.
...
This has been fixed in trunk and 7.0.x and will be included in 7.0.34 onwards."

So if you have an older Tomcat version (less than 7.0.34), use the above solution, otherwise, ~~starting with Apache Tomcat version 7.0.34, there should be no issues like the one I described.~~ (see UPDATE 2)

=== UPDATE 2 (2014-01-13) a.k.a. "The Issue Strikes Back" ===

It seems like the issue initially described in my bug report is still present even for the currently latest Apache Tomcat version 7.0.50 and I also reproduced it with Tomcat 7.0.47 (thanks to Miklos Krivan for pointing it out). Although now Tomcat sometimes manages to close additional connections after reloading, and sometimes the number of connections are increased after one reload and then kept steady, but eventually this behavior is still not reliable.

I still could reproduce the initially described issue (although again not that easy: it may be related to the frequency of successive reloads). Seems like it's just a matter of time, i.e. if Tomcat has enough time after reload, it manages the connection pool more or less as it should. As Mark Thomas mentioned in his comment (quote): "As per the docs for closeMethod, that method exists solely to speed up the freeing of resources that would otherwise be freed by GC." (end of quote), and it looks like the speed is the defining factor.

When using the solution presented by Konstantin Kolinko (to use closeMethod="close") everything WORKS just fine, and the number of connections reserved are kept STRICTLY as defined in the context.xml file. So it appears that using closeMethod="close" is the ONLY true way (at the moment) to avoid running out of connections after context reloading.

=== UPDATE 3 (2014-01-13) a.k.a. "Return of the Tomcat Release Manager" ===

The mystery behind the behavior described in the UPDATE 2 is solved. More details have been cleared now after I received a reply from Mark Thomas (Tomcat release manager). I hope this is the last update. So the bug was indeed fixed as was mentioned in the UPDATE 1. I am posting the essential part from Mark's reply here as a quote (emphasis mine):

The actual memory leak found while investigating this bug has been fixed in 7.0.34 onwards as per comments #4 to #6.

The issue of the connections not being closed on reload is a result of the J2EE specification for JNDI resources and this part of the bug report is therefore invalid. I am restoring the state of this bug to fixed to reflect that the memory leak that did exist has been fixed.

To expand on why the failure to immediately close connection after reload is invalid, the J2EE specification provides no mechanism for the container to tell the resource it is no longer required. Therefore all the container can do is clear references to the resource and wait for garbage collection (which will trigger the closure of the pool and the associated connections). Garbage collection occurs at times determined by the JVM so this is why it takes an indeterminate amount of time for connections to be closed after a context reload as a garbage collection may not occur for some time.

Tomcat has added the Tomcat specific JNDI attribute closeMethod which can be used to trigger the explicit close of a JNDI resource when a context is stopped. If waiting for GC to clean up resources is not acceptable then simply use this parameter. Tomcat does not use this by default as it may have unexpected and unwanted side-effects for some JNDI resources.

If you'd like to see a standard mechanism provided for telling JNDI resources that they are no longer required then you need to lobby the J2EE expert group.

Conclusion

Just use the solution presented in the beginning of this post (but, just in case, keep in mind the JNDI related issue that can theoretically arise from using it).

Alternative solution

Michael Osipov suggested using his CloseableResourceListener, which prevents memory leaks caused by left open resources during undeployment of web applications. So you may also give it a try.

DISCLAIMER
The aliases for the UPDATES were inspired by the Star Wars film series. All rights belong to their respective owners.

Unfortunately Tomcat 7.0.35 has the same trouble but the closeMethod="close" value solved my problem. — Miklos Krivan, Jan 11 '14 at 17:21
Tomcat 7.0.47 also has the same trouble but the closeMethod="close" works perfect. — Miklos Krivan, Jan 11 '14 at 17:43
@MiklosKrivan See the final description of this issue in the UPDATE 3. — informatik01, Jan 13 '14 at 14:19
What is the "unexpected and unwanted side-effects for some JNDI resources" and does it apply to postgres connections? — user3427419, Jan 18 '15 at 07:14
@user3427419 I am sorry, I don't know the exact answer for this question, this is the quote of the Apache Tomcat release manager [Mark Thomas](http://stackoverflow.com/users/1299005/mark-thomas). I'll post your question on the [bug report page](https://issues.apache.org/bugzilla/show_bug.cgi?id=54217#c13), we'll see what will be the answer. — informatik01, Jan 18 '15 at 16:00
The short answer is "it depends". The default for closeMethod is "close". The chances are that calling JndiResource.close() (if such a method exists) when the resource is no longer required will have the desired effect but, because "close()" is not part of any standard API, if it does exist we have no way of knowing what the behaviour will be. Therefore the result of calling this method may be "unexpected and unwanted". Regarding postgres, if you use Tomcat's built in connection pooling then "close()" will get called on the connection pool which is almost certainly what you do want. — Mark Thomas, Jan 18 '15 at 16:55
I am programatically creating the Hikari pool. How do I specify this close method? I am already calling pool.close() in contextDestroyed() but I sometimes get the message: ```The web application [xxxx] appears to have started a thread named [HikariPool-6 housekeeper] but has failed to stop it. This is very likely to create a memory leak.``` — gbenroscience, Jun 01 '19 at 11:22
@gbenroscience Too few details. 1) If you have configured HikariCP in a Spring application, the _standard practice_ is to annotate the method where you made configuration with `@Bean(destroyMethod = "close")`. 2) Read about ["**leakDetectionThreshold**"](https://github.com/brettwooldridge/HikariCP/blob/2d82b6f00a6deb774afbc561439ec584c3fd7dbb/README.md), maybe it will help. 3) It would be much better to post a new [question](https://stackoverflow.com/questions/ask) and describe your problem there. Thus you will draw much more attention to it and have more chances to get a proper help. — informatik01, Jun 01 '19 at 17:08
@informatik01 , I mean is there a way to specify the close method to be used while creating the pool programatically? Its a Tomcat web app — gbenroscience, Jun 01 '19 at 18:42
@gbenroscience How exactly and **where** is your data source configured? How do you make it to be **managed by Tomcat** programmatically? Or is it managed by _any_ IoC container at all? — informatik01, Jun 01 '19 at 23:09
@gbenroscience If you're not using any IoC container, but pure Servlets /JSP deployed on Tomcat, you could register _ServletContextListener_ and call the [close()](https://static.javadoc.io/com.zaxxer/HikariCP/2.6.3/com/zaxxer/hikari/HikariDataSource.html#close--) method on your configured _HikariDataSource_ inside the [_contextDestroyed()_](https://docs.oracle.com/javaee/7/api/javax/servlet/ServletContextListener.html#contextDestroyed-javax.servlet.ServletContextEvent-) method. Such questions **should be asked separately**, because they require quite a few additional details to be clarified. — informatik01, Jun 01 '19 at 23:10
@gbenroscience Form the HikariCP FAQ, ["How do I properly shutdown the HikariCP DataSource?"](https://github.com/brettwooldridge/HikariCP/wiki/FAQ#q-how-do-i-properly-shutdown-the-hikaricp-datasource): "_Shutting down the DataSource is especially important in web application containers where applications can be hot-deployed. Call the shutdown() or **close()** method on the HikariDataSource instance. **You can typically configure Spring or other IOC containers** to specify a "destroy" method._" — informatik01, Jun 01 '19 at 23:15
@informatik01 , You guessed right this is my exact situation. I deploy pure JSPs and Servlets on Tomcat. I create the pool programatically in the ServletContextListener's constructor and I close it in the contextDestroyed. The error sometimes happen when I dynamically redeploy the app using Netbeans deploy on save feature. — gbenroscience, Jun 01 '19 at 23:25
@gbenroscience So nobody calls the _close()_ method on HikariDataSource? Then try to do as I described above - do this in a ServletContextListener. Here is a _similar_ related [example](https://www.journaldev.com/1945/servletcontextlistener-servlet-listener-example). It's **night now** where I live, 2:37 AM. Good night. — informatik01, Jun 01 '19 at 23:37
The HikariDatasource is the pool. I mentioned that I am doing all this already in a ServletContextListener. Create the pool in the constructor and destroy it in the contextDestroyed as I said in the last comment. Thanks for assisting and good night...Its late night here, too. — gbenroscience, Jun 01 '19 at 23:43
@gbenroscience I haven't event noticed that you mentioned you use _contextDestroyed()_. It's because I was irritated by the fact you ignored that I **twice**asked you to make a separate post for your problem, I didn't even read you question carefully. I can ask in Russian, if English is not suitable )). — informatik01, Jun 02 '19 at 00:29
@gbenroscience This whole "NetBeans save on restart" thing for Tomcat apps - this could also cause problems, hard to tell. I have found [this discussion](https://github.com/brettwooldridge/HikariCP/issues/418#issuecomment-247287590) on the HikariCP project page (hint: nice place to ask HikariCP related questions straight to its author). Pay attention what the author of HikariCP Brett Wooldrige says. And for the **third** time: **PLEASE** create a separate question for your problem, describing the configuration details etc. Don't you see it's taking toooo much space in the comment section? — informatik01, Jun 02 '19 at 00:35
Forgive me @informatik01! I really dont know why I kept going. You have been most patient! Have a blessed night's rest — gbenroscience, Jun 02 '19 at 00:46
@gbenroscience Forgive me too for being irritated. God bless you! — informatik01, Jun 02 '19 at 00:57