Java: Concurency with HikariDataSource Object

Question

I have a class which looks like that:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class ConnectionPool {
    private HikariDataSource hds;
    private final String propertyFileName;

    public ConnectionPool(String propertyFileName) {
        if (propertyFileName == null) {
            throw new IllegalArgumentException("propertyFileName can't be null");
        }

        this.propertyFileName = propertyFileName;
        reloadFile();
    }

    public void reloadFile() {
        if (hds != null) {
            hds.close();
        }

        hds = new HikariDataSource(new HikariConfig(propertyFileName));
    }

    public HikariDataSource getHikariDataSource() {
        return hds;
    }

    public String getPropertyFileName() {
        return propertyFileName;
    }

    public void executeQuery(final String sql, final CallBack<ResultSet, SQLException> callBack) {
        new Thread(new Runnable() {
            @Override
            public void run() {
                Connection connection = null;
                PreparedStatement preparedStatement = null;
                ResultSet resultSet = null;

                try {
                    connection = hds.getConnection();
                    preparedStatement = connection.prepareStatement(sql);
                    resultSet = preparedStatement.executeQuery();
                    callBack.call(resultSet, null);
                } catch (SQLException e) {
                    callBack.call(null, e);
                } finally {
                    if (resultSet != null) {
                        try {
                            resultSet.close();
                        } catch (SQLException ignored) {}
                    }

                    if (preparedStatement != null) {
                        try {
                            preparedStatement.close();
                        } catch (SQLException ignored) {}
                    }

                    if (connection != null) {
                        try {
                            connection.close();
                        } catch (SQLException ignored) {}
                    }
                }
            }
        }).start();
    }

    public void executeUpdate(final String sql, final CallBack<Integer, SQLException> callBack) {
        //TODO
    }

    public void execute(final String sql, final CallBack<Boolean, SQLException> callBack) {
        //TODO
    }

    public void connection(final String sql, final CallBack<Connection, SQLException> callBack) {
        //TODO
    }
}

The problem is that the reloadFile() method can be called from a different thread as hds is used. So it's possible that hds is closed while I use a connection object of it in another thread. What's the best way to solve this problem? Should I wait a few seconds after creating the new HikariDataSource object befor closing the old one (until the queries are finished)?

Edit: Another question: Should hds be volatile, so that the changes of hds are visible for all threads?

Answer 1

Have had a very very quick and brief look in the source code in HikariDataSource. In its close(), it is calling its internal HikariPool's shutdown() method, for which it will try to properly close the pooled connections.

If you want to even avoid any chance that in-progress connection from force closing, one way is to make use of a ReadWriteLock:

public class ConnectionPool {
    private HikariDataSource hds;
    private ReentrantReadWriteLock dsLock = ....;
    //....

    public void reloadFile() {
        dsLock.writeLock().lock();
        try {
            if (hds != null) {
                hds.close();
            }

            hds = new HikariDataSource(new HikariConfig(propertyFileName));
        } finally {
            dsLock.writeLock().unlock();
        }
    }

    public void executeQuery(final String sql, final CallBack<ResultSet, SQLException> callBack) {
        new Thread(new Runnable() {
            @Override
            public void run() {
                Connection connection = null;
                PreparedStatement preparedStatement = null;
                ResultSet resultSet = null;

                dsLock.readLock().lock();
                try {
                    connection = hds.getConnection();
                    // ....
                } catch (SQLException e) {
                    callBack.call(null, e);
                } finally {
                    // your other cleanups
                    dsLock.readLock().unlock();
                }
            }
        }).start();
    }
    //....
}

This will make sure that

1. multiple thread can access your datasource (to get connection etc)
2. Reload of datasource needs to wait until thread using the datasource to complete
3. No thread is able to use the datasource to get connection when it is reloading.

Answer 2

Why exactly are you trying to cause HikariCP to reload? Many of the important pool parameters (minimumIdle,maximumPoolSize,connectionTimeout,etc.) are controllable at runtime through the JMX bean without restarting the pool.

Restarting the pool is a good way to "hang" your application for several seconds while connections are closed and rebuilt. If you can't do what you need through the JMX interface, Adrian's suggestion seems like quite a reasonable solution.

Other solutions are possible, but have more complexity.

EDIT: Just for my own entertainment, here is the more complex solution...

public class ConnectionPool {
   private AtomicReference<HikariDataSource> hds;

   public ConnectionPool(String propertyFileName) {
      hds = new AtomicReference<>();
      ...
   }

   public void reloadFile() {
      final HikariDataSource ds = hds.getAndSet(new HikariDataSource(new HikariConfig(propertyFileName)));
      if (ds != null) {
         new Thread(new Runnable() {
           public void run() {
              ObjectName poolName = new ObjectName("com.zaxxer.hikari:type=Pool (" + ds.getPoolName() + ")");
              MBeanServer mBeanServer = ManagementFactory.getPlatformMBeanServer();
              HikariPoolMXBean poolProxy = JMX.newMXBeanProxy(mBeanServer, poolName, HikariPoolMXBean.class);

              poolProxy.softEvictConnections();
              do {
                 Thread.sleep(500);
              } while (poolProxy.getActiveConnections() > 0);
              ds.close();
           }
         }).start();
       }
   }

   public HikariDataSource getHikariDataSource() {
      return hds.get();
   }

   public void executeQuery(final String sql, final CallBack<ResultSet, SQLException> callBack) {
      new Thread(new Runnable() {
         @Override
         public void run() {
            ...
            try {
               connection = getHikariDataSource().getConnection();
               ...
            }
         }
      }).start();
   }
}

This will swap out the pool (atomically) and will start a thread that waits until all active connections have returned before shutting down the orphaned pool instance.

This assumes that you let HikariCP generate unique pool names, i.e. do not set poolName in your properties, and that registerMbeans=true.

Answer 3

A few options:

Synchronize all access to the data source so that only one thread can ever be messing with it. Not scaleable, but workable.

Roll your own connection pooling, such as Apache Commons Pooling so that each access, regardless of thread, requests a data source and pooling creates one as necessary. Can mess with data ACID, just depends on whether dirty data is needed, when data is flushed, transactionality, etc.

Each thread could also have its own data source using ThreadLocal so that each thread is totally independent of each other. Again, quality of data might be an issue, resources might be an issue if you've got "lots" of threads (depends on your definition) and too many open connections cause resource issues on either the client or server.