Java connection pool is not limiting the number of TCP connections opened to the DB server
<p>I'm using Hibernate properties to define the connection pool size, along with the <code>LocalSessionFactoryBean</code> and <code>DriverManagerDataSource</code> to create an <code>org.hibernate.SessionFactory</code> and I am observing 1000's of TCP connections being opened to the DB server instead of the upper limit of 100 I would expected with the connection pool size.</p>

<p>The code for setting the connection Hibernate pool looks like this. Note the max size of 100, however I am observing 1000's of connections to the DB server from my workstation - I'm using TCPView on a Windows machine to connect to a MariabDB instance on a Centos OS VM managed by Vagrant (VirtualBox)</p>

<pre><code>Properties hibernateProperties = new Properties();
hibernateProperties.put("hibernate.dialect", "org.hibernate.dialect.MySQL5InnoDBDialect");
hibernateProperties.put("hibernate.show_sql", false);
hibernateProperties.put("hibernate.generate_statistics", false);
hibernateProperties.put("hibernate.hbm2ddl.auto", "update");
hibernateProperties.put("hibernate.use_sql_comments", false);

hibernateProperties.put("hibernate.c3p0.min_size", 10);
hibernateProperties.put("hibernate.c3p0.max_size", 100);
hibernateProperties.put("hibernate.c3p0.timeout", 1500);
hibernateProperties.put("hibernate.c3p0.max_statements", 5000);
hibernateProperties.put("hibernate.c3p0.idle_test_period", 60);

LocalSessionFactoryBean localSessionFactoryBean = new   LocalSessionFactoryBean();
localSessionFactoryBean.setDataSource(dataSource);
localSessionFactoryBean.setHibernateProperties(hibernateProperties);
localSessionFactoryBean.setMappingResources("job.persistence.xml");
</code></pre>

<p>I would expect to see these connections maxing out at 100 and being reused for later executions of loop, but I am observing over 1000 sitting in TIME_WAIT for about a minute before they are closed down. Here is a sample of the output from TCPView:</p>

<pre><code>[System Process]    0   TCP localhost   62794   192.168.98.102  3306    TIME_WAIT                                       
[System Process]    0   TCP localhost   62796   192.168.98.102  3306    TIME_WAIT                                       
[System Process]    0   TCP localhost   62797   192.168.98.102  3306    TIME_WAIT                                       
[System Process]    0   TCP localhost   62795   192.168.98.102  3306    TIME_WAIT                                       
[System Process]    0   TCP localhost   62798   192.168.98.102  3306    TIME_WAIT                                       
[System Process]    0   TCP localhost   62801   192.168.98.102  3306    TIME_WAIT   
</code></pre>

<p>I'm obviously doing something wrong but not sure what. I am using Spring to manage the DAO that provides the data access logic. The DAO is registered as a prototype Spring scope while the Singleton <code>SessionFactory</code> is injected into the DAO like this:</p>

<pre><code>@Bean(name="jobDao")
@Scope(SpringBeanScope.Prototype)
public JobDao jobDao(SessionFactory jobSessionFactory) { //...}
</code></pre>

<p>Within the DAO, I am calling <code>sessionFactory.getCurrentSession()</code> to access the DB session. The following is an extract from the generic DAO base that demonstrates this:</p>

<pre><code>protected Session currentSession() {
    return sessionFactory.getCurrentSession();
}

@Transactional
@Override
public void Add(TEntity entity) {

    currentSession().save(entity);
}
</code></pre>

<p>Does anyone know why so many TCP connections are being opened when the connection pool limit is 100?</p>

<p><strong>UPDATE</strong></p>

<p>Since this issue is on a Windows dev machine, I put together a small .NET Console application that uses a parallel for loop to execute a simple SQL select statement to a max thread count of 64, creating a new connection every time:</p>

<pre><code>public void Test()
{
    ParallelOptions options = new ParallelOptions();
    options.MaxDegreeOfParallelism = 64;

    Parallel.For(0,
                1000,
                options,
                (i, state) =&gt;
                {
                    ExecuteSql();
                });
}

private void ExecuteSql()
{
    SqlDataAdapter adapter = new SqlDataAdapter(sql, new SqlConnection(connectionString));

    DataSet orders = new DataSet();
    adapter.Fill(orders, "Order");

    Console.WriteLine("Thread {0} returned {1} rows", Thread.CurrentThread.ManagedThreadId, orders.Tables[0].Rows.Count);
}
</code></pre>

<p>The TCPView results look as follows, which is what I would expect to see in a connection pooled solution, i.e. <strong><em>TCP connections being reused</em></strong></p>

<pre><code>DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61316   dev-database01  ms-sql-s    ESTABLISHED 1   88  776 2,684,495       43,800      16      
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61317   dev-database01  ms-sql-s    ESTABLISHED 1   88  507 1,998,709       6,326       1       
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61318   dev-database01  ms-sql-s    ESTABLISHED 2   176 862 3,081,722       49,640      19      
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61319   dev-database01  ms-sql-s    ESTABLISHED 2   176 952 3,128,657       14,600      9       
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61320   dev-database01  ms-sql-s    ESTABLISHED 2   176 1,149   3,569,440       25,747      8       
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61321   dev-database01  ms-sql-s    ESTABLISHED 2   176 1,166   3,788,974                       
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61322   dev-database01  ms-sql-s    ESTABLISHED 2   176 884 3,197,392       8,713       2       
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61323   dev-database01  ms-sql-s    ESTABLISHED 2   176 535 1,816,150                       
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61324   dev-database01  ms-sql-s    ESTABLISHED 2   176 631 2,197,973                       
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61327   dev-database01  ms-sql-s    ESTABLISHED 2   176 1,037   3,344,226       18,980      5       
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61328   dev-database01  ms-sql-s    ESTABLISHED 3   264 1,271   4,057,097       30,660      13      
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61331   dev-database01  ms-sql-s    ESTABLISHED 2   176 780 2,639,988       8,760       2       
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61333   dev-database01  ms-sql-s    ESTABLISHED 2   176 1,041   3,352,777       31,248      12      
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61334   dev-database01  ms-sql-s    ESTABLISHED 6   995 729 2,387,668                       
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61335   dev-database01  ms-sql-s    ESTABLISHED 6   995 601 1,917,537       23,937      6       
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61336   dev-database01  ms-sql-s    ESTABLISHED                                     
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61339   dev-database01  ms-sql-s    ESTABLISHED                                     
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61340   dev-database01  ms-sql-s    ESTABLISHED                                     
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61342   dev-database01  ms-sql-s    ESTABLISHED                                     
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61343   dev-database01  ms-sql-s    ESTABLISHED                                     
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61344   dev-database01  ms-sql-s    ESTABLISHED                                     
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61345   dev-database01  ms-sql-s    ESTABLISHED                                     
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61346   dev-database01  ms-sql-s    ESTABLISHED                                     
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61356   dev-database01  ms-sql-s    ESTABLISHED                                     
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61357   dev-database01  ms-sql-s    ESTABLISHED                                     
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61358   dev-database01  ms-sql-s    ESTABLISHED                                     
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61359   dev-database01  ms-sql-s    ESTABLISHED                                                         
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61362   dev-database01  ms-sql-s    ESTABLISHED                                     
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61363   dev-database01  ms-sql-s    ESTABLISHED                                     
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61364   dev-database01  ms-sql-s    ESTABLISHED                                     
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61365   dev-database01  ms-sql-s    ESTABLISHED                                     
DatabaseTcpPortTester.vshost.exe    5036    TCP localhost   61369   dev-database01  ms-sql-s    ESTABLISHED                                     
[System Process]    0   TCP localhost   61395   dev-database01  epmap   TIME_WAIT
</code></pre>