HikariCP multithreading separate connection for each thread

Question

To the folks of the stackoverflow community. I was looking for some help with an issue i am facing with HikariCP connection pooling.

High level: I am trying to create several threads using a thread pool and my plan is to give each worker thread its own separate connection from the HikariCP, but what HikariCP is doing is that it is sharing a common connection across multiple threads. I am using

public synchronized Connection getConnection() throws SQLException
{
  synchronized (dataSource) {
  return dataSource.getConnection();
  }
}

to retrieve a DB connection. Now when I close a connection, I am seeing issues in other threads saying that the connection got closed and the batch of records that thread is processing get dropped.

Here are the stmts from my log file:

2016-06-08 11:52:11,000 DEBUG [com.boxer.delete.SmartProcessWorker] (pool-1-thread-6:) Closing DB connection ConnectionJavassistProxy(1551909727) wrapping oracle.jdbc.driver.T4CConnection@7b4f840f
2016-06-08 11:52:11,002 DEBUG [com.boxer.delete.SmartProcessWorker] (pool-1-thread-9:) Closing DB connection ConnectionJavassistProxy(1511839669) wrapping oracle.jdbc.driver.T4CConnection@343b8714
2016-06-08 11:52:11,014 ERROR [com.boxer.delete.SmartProcessWorker] (pool-1-thread-5:) SQLException trying to Execute pstmt batch
2016-06-08 11:52:11,014 ERROR [com.boxer.delete.SmartProcessWorker] (pool-1-thread-5:) Connection is closed
java.sql.SQLException: Connection is closed
        at com.zaxxer.hikari.proxy.ConnectionProxy.checkClosed(ConnectionProxy.java:275)
        at com.zaxxer.hikari.proxy.ConnectionJavassistProxy.commit(ConnectionJavassistProxy.java)
        at com.boxer.delete.SmartProcessWorker.processRecords(SmartProcessWorker.java:219)
        at com.boxer.delete.SmartProcessWorker.run(SmartProcessWorker.java:174)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:745)

Now, can someone help me with how to get a Db connection from hikari datasource which is not apparently being shared by any other thread ?

Answer 1

Short answer: don't do that. JDBC connections are not meant to be shared between threads.

From the author of HikariCP (source):

Multithreaded access to Connections was deprecated in JDBC and is not supported by HikariCP either.

HikariCP is fast enough that you can obtain a Connection, execute SQL, and then return it back to the pool many times in the course of a request.

It is a Best Practice to only hold Connections in local variables, preferably in a try-with-resources block, or possibly passed on the stack, but never in a class member field. If you follow that pattern it is virtually impossible to leak a Connection or accidentally share across threads.