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

Issue

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

Solution

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.

Answered By - informatik01
Answer Checked By - Cary Denson (JavaFixing Admin)