Tomcat/Spring connection pool - list out connection items

Question

Trying to debug an application that has the similar issue as Missing connections in tomcat jdbc connection pool . I'm currently using :

Tomcat 6.0.32 - Requirement of the Company
Tomcat JDBC Pool 1.0.9.3
Java 1.6.x - Requirement of the Company
Spring 3.2.14
myBatis 3.2.8
Quartz 2.2.2

There is a mixture of Spring Web requests as well as Quartz triggered jobs utilizing the Pool. What happens is after about 5 runs, I have no more available connections in the Pool, initialSize=5, maxActive=10. Increasing the maxActive does nothing, it simply takes longer and more Quartz runs before the pool is empty.

2016-05-19 08:59:46,027 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_QuartzSchedulerThread   QuartzSchedulerThread.run  276 : batch acquisition of 1 triggers
2016-05-19 09:00:00,001 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_QuartzSchedulerThread   QuartzSchedulerThread.run  276 : batch acquisition of 0 triggers
2016-05-19 09:00:00,003 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-6   JobRunShell.run  201 : Calling execute on job DEFAULT.refMigrDetail
2016-05-19 09:00:00,003 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-6   DataSourceUtils.doGetConnection  110 : Fetching JDBC Connection from DataSource
2016-05-19 09:00:24,409 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_QuartzSchedulerThread   QuartzSchedulerThread.run  276 : batch acquisition of 0 triggers
2016-05-19 09:00:30,004 ERROR org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-6   CMRoreReferenceMigrator.run   93 : org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: [org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-6] Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[10 in use].
2016-05-19 09:00:49,378 DEBUG org.springframework.scheduling.quartz.SchedulerFactoryBean#0_QuartzSchedulerThread   QuartzSchedulerThread.run  276 : batch acquisition of 0 triggers

What I've done so far :

Switched from Apache Commons DBCP to Tomcat JDBC Pool. This is working.
Turned on DEBUG for Tomcat JDBC Pool. Also working
Enabled JMX on Tomcat to monitor the Connection Pool with jvisualvm - This did NOT work as Spring is actually creating the Pool, not Tomcat.

With the DEBUG turned on for Tomcat JDBC Pool, the logging doesn't reveal where/why the connections are NOT being released. For the JMX route, since I'm NOT using Spring-Boot, I can't seem to enable JMX within Spring to see the connection pool.

My question is, is there a way to grab the Spring Connection Pool object/instance from SPRING (not Tomcat) and then iterate thru it for all connections and list out some simple properties on each connection such as active, in-use, closed, etc...

My thought being that I just write some Java code that dumps out the connection pool list to the log before and after the Quartz job. Dumps out the list on key Spring Web transactions that I exercise in the Web application before and after as well.

My Spring connection pool configuration, DB2 database :

<bean id="rrfecfDataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
    <property name="driverClassName" value="${rrfecf.datasource.jdbcDriver}" />
    <property name="url" value="${rrfecf.dataousrce.databaseUrl}" />
    <property name="username" value="${rrfecf.datasource.username}" />
    <property name="password" value="${password:rrfecf.datasource}" />
    <property name="initialSize" value="5" />
    <property name="maxActive" value="10" />
    <property name="defaultAutoCommit" value="false" />
    <property name="maxIdle" value="5" />
    <property name="minIdle" value="0" />
    <property name="testOnBorrow" value="true" />
    <property name="testOnReturn" value="false" />
    <property name="testWhileIdle" value="true" />
    <property name="validationQuery" value="SELECT CURRENT_DATE FROM sysibm.sysdummy1" />
    <property name="timeBetweenEvictionRunsMillis" value="600000" /> <!-- 10 minutes -->
    <property name="minEvictableIdleTimeMillis" value="900000" /> <!-- 15 minutes -->
</bean>

myBatis Spring configuration :

<bean id="rrfecfSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="configLocation" value="WEB-INF/sqlMapConfig.xml"/>
    <property name="dataSource" ref="rrfecfDataSource" />
</bean>

lincolnadym lincolnadym · Accepted Answer · 2016-05-20T14:01:52

This 'seems' to be working, not sure if there is a better way :

Made an ApplicationContextAware Bean in spring.
Wired that ApplicationContextAware Bean to the Quartz classes.
Within the Quartz classes I'm :
- Using the ApplicationContextAware Bean to get the ConnectionPool.
- Using a for loop to iterate thru all the connections and dump out properties

ApplicationContextAware class :

import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;

/**
 * <p>
 * The <code>BatchApplicationContextProvider</code> provides...
 * </p>
 */

public class BatchApplicationContextProvider implements ApplicationContextAware {

    public static final String ID              = BatchApplicationContextProvider.class.getName ();
    private String             SHORT_NAME      = "BatchApplicationContextProvider()";
    @SuppressWarnings("unused")
    private String              SYSTEM_IDENTITY = String.valueOf ( System.identityHashCode ( this ) );

    private ApplicationContext  context;

    /**
     * 
     */
    public BatchApplicationContextProvider() {
        // TODO Auto-generated constructor stub
    }

    /* (non-Javadoc)
     * @see org.springframework.context.ApplicationContextAware#setApplicationContext(org.springframework.context.ApplicationContext)
     */
    @Override
    public void setApplicationContext(ApplicationContext arg0)
        throws BeansException {
        this.context = arg0;
    }

    public ApplicationContext getApplicationContext() {
        return context;
    }

    public org.apache.tomcat.jdbc.pool.DataSource   getConnectionPool () { 
        return ( DataSource ) this.context.getBean ( "rrfecfDataSource" );
    }

}

Sample Usage :

public void        dumpConnectionPool () { 
    org.apache.tomcat.jdbc.pool.DataSource  lthePool = null;
    ConnectionPool  lbusyPool = null;
    try { 
        lthePool   = getBatchContext ().getConnectionPool ();
        lbusyPool  = lthePool.getPool ();
        if ( lbusyPool != null ) { 
            if ( lbusyPool.getSize () > 0 ) { 
                getLog().info ( SHORT_NAME + ".run() - ConnectionPool - size....................[" + lbusyPool.getSize () + "] idle [" + lbusyPool.getIdle () + "] active [" + lbusyPool.getActive () + "]"  );
                //getLog().info ( SHORT_NAME + ".run() - ConnectionPool - idle....................[" + lbusyPool.getIdle () + "]" );
                //getLog().info ( SHORT_NAME + ".run() - ConnectionPool - active..................[" + lbusyPool.getActive () + "]" );
                //PoolConfiguration       lpoolCfg = lbusyPool.getPoolProperties ();
            }
        }

        lbusyPool = null;
        lthePool = null;

    }
    catch ( Exception ltheXcp ) { 
        log.error ( ltheXcp );
    }
    finally { 

    }


}

I'm hoping someone has a more elegant way...this seems like brute force to me, but I need to solve this issue in the Web application at this point.

EDIT : There is a problem with the dumpConnectionPool()...the for loop appears to hang onto all the connections when it iterates thru, making them all 'active'. Working on this...

EDIT : Take out the for loop...setting a connection = null doesn't appear to release it back to the Connection Pool.

SUCCESS :

While not pretty, the dumpConnectionPool() is showing the issue manifest itself :

The dumpConnectionPool() before the Quartz runs, shows 5 idle, 0 active :

2016-05-20 11:20:00,078  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.run  138 : -----------------------------------------------------------------------------------------------------------------
2016-05-20 11:20:00,078  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.dumpConnectionPool  257 : RunReferenceMigrator().run() - ConnectionPool - size....................[5] idle [5] active [0]
2016-05-20 11:20:00,078  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.run  140 : -----------------------------------------------------------------------------------------------------------------

The dumpConnectionPool() AFTER the Quartz runs, shows 4 idle, 1 active :

2016-05-20 11:24:45,063  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.run  161 : -----------------------------------------------------------------------------------------------------------------
2016-05-20 11:24:45,063  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.dumpConnectionPool  257 : RunReferenceMigrator().run() - ConnectionPool - size....................[5] idle [4] active [1]
2016-05-20 11:24:45,063  INFO org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-2   RunReferenceMigrator.run  163 : -----------------------------------------------------------------------------------------------------------------

Tomcat/Spring connection pool - list out connection items

1 Answers