How to shutdown connection pool on context unload?

Question

after developing several webapps that all had a similar setup with spring, hibernate and c3p0 as connectionpool I wanted to investigate a problem that i noticed everytime: Connectionpool keeps the connections until you shutdown tomcat (or your application server).

Today i create the most basic project I could with these four dependencies:

org.springframework:spring-web
org.springframework:spring-orm
org.hibernate:hibernate-core
c3p0:c3p0

(plus the specific JDBC driver).

My web.xml only creates a ContextLoaderListener that sets up the application context.

<context-param>
    <param-name>contextConfigLocation</param-name>
    <param-value>
        /WEB-INF/applicationContext.xml
    </param-value>
</context-param>

<listener>
    <listener-class>
        org.springframework.web.context.ContextLoaderListener
    </listener-class>
</listener>

application context consists of two beans - datasource and session factory:

<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
</bean>

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass">
        <value>org.postgresql.Driver</value>
    </property>
    <property name="jdbcUrl">
        <value>jdbc:postgresql://localhost/mydb</value>
    </property>
    <property name="user">
        <value>usr</value>
    </property>
    <property name="password">
        <value>pwd</value>
    </property>
</bean>

When i start the webapp and either look into jconsole's MBeans or check my DBMS for open connections i notice the three initial connections made by c3p0.

PROBLEM: When i tell tomcat to stop the webapp, they still remain!

I created another ServletContextListener that only has the contextDestroyed method implemented and programatically shuts down the sessionFactory (and also deregisters the JDBC drivers which is also not done automatically). Code is:

@Override
public void contextDestroyed(ServletContextEvent sce) {

    ...
    sessionFactory().close();

    // deregister sql driver(s)
    Enumeration<Driver> drivers = DriverManager.getDrivers();
    while (drivers.hasMoreElements()) {
        Driver driver = drivers.nextElement();
        try {
            DriverManager.deregisterDriver(driver);
            log.info("deregistering jdbc driver: " + driver);
        } catch (SQLException e) {
            log.error("error deregistering jdbc driver: " + driver, e);
        }
    }
}

But is that really it? Is there no built-in mechanism that I am not aware of?

score 16 · Accepted Answer · answered Nov 23 '12 at 15:58

16

Have you tried to specify the destroy-method?

<bean class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">

answered Nov 23 '12 at 15:58

Stanislav Bashkyrtsev

14,470
7
42
45

There is nothing as valuable as experience... it worked! Thank you (but what about the jdbc driver unregistering?) – realsim Nov 23 '12 at 16:02
1

Deregistering of drivers just removes them from the internal static list of `DriverManager`. When next time connection pool wants to acquire a new resource, it asks `DriverManager` for it. But if connection was already acquired and stays in the pool, then nothing prevents it from continuing to work. BTW, this also results in Class Loader memory leaks because connection pool threads didn't let classes being destroyed. Please, mark question as solved if you feel you don't have further questions. – Stanislav Bashkyrtsev Nov 23 '12 at 16:24
Have you ever seen this kind of output on context stop? --- SEVERE: The web application [/example] registered the JDBC driver [org.postgresql.Driver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered. --- I wonder why the guys of tomcat forcibly unregister it when it does no harm. – realsim Nov 24 '12 at 20:26
It actually does same harm as threads that were not stopped - classloader leaks. Each class holds a reference to the classloader that loadded it, and classloader holds references to all the classes it loaded. Thus classloader can't be GC'ed, and all the classes as well and you'll get permgen OutOfMemory. Here is how you can get rid of this warning: https://github.com/jtalks-org/poulpe/blob/master/poulpe-view/poulpe-web-controller/src/main/java/org/jtalks/poulpe/web/controller/JdbcDriverUnregisteringListener.java – Stanislav Bashkyrtsev Nov 24 '12 at 21:45
Alright, thank you! As you can see, I already had that in my last code snippet. – realsim Nov 26 '12 at 08:47

How to shutdown connection pool on context unload?

1 Answers1

Linked