Connections not being released to pool in multithreaded program

Question

Hello all

For some reason my connections are not being released. I have been sitting with this problem for the good part of the day, so now I am hoping one of you can help me.

The DataSource is located in a Swagger jaxws server. So on each request I am retrieving a connection from the pool. This is my DataSource class which returns a connection from the pool:

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbcp2.BasicDataSource;
/**
 *
 * @author Lagoni
 */
public class DataSource {
    private static DataSource datasource;
    private BasicDataSource ds;
    private DataSource() throws IOException, SQLException, PropertyVetoException {
        ds = new BasicDataSource();
        ds.setDriverClassName("org.postgresql.Driver");
        ds.setUsername("username");
        ds.setPassword("pw");
        ds.setUrl("jdbc:postgresql://host" + 5432 + "/db");

        ds.setMaxWaitMillis(20000); //wait 10 seconds to get new connection
        ds.setMaxTotal(5);
        ds.setMaxIdle(5);
        ds.setTestWhileIdle(true);
        ds.setTestOnReturn(true);
        ds.setTimeBetweenEvictionRunsMillis(1000);
        ds.setSoftMinEvictableIdleTimeMillis(100); 
        ds.setMinEvictableIdleTimeMillis(10); 
        ds.setMaxConnLifetimeMillis(1000*60*10);
    }

    public static DataSource getInstance() throws IOException, SQLException, PropertyVetoException {
        if (datasource == null) {
            datasource = new DataSource();
        }
        return datasource;
    }

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

}

For each function which requires the use of a database connection retrieve it by calling:

Connection con = DataSource.getInstance().getConnection();

This is where i am getting "Cannot get a connection, pool error Timeout waiting for idle object". I am making sure only one connection is being used per thread. So if the function needs to make multiple calls to the database it reuses the con variable.

I have come to the conclusion that I should never call con.close() since it then will return a null connection to the pool. When a function is done with the connection the following are being called:

resultSet.close();
statement.close();

Is there something I am forgetting to do before connections are being declared idle? Or am I simply not implementing the connection pool correct? Should I try use another kind of pool or?

I am using the following maven dependencies:

<dependency>
  <groupId>org.apache.commons</groupId>
  <artifactId>commons-dbcp2</artifactId>
  <version>2.1.1</version>
</dependency>
<dependency>
  <groupId>org.apache.commons</groupId>
  <artifactId>commons-pool2</artifactId>
  <version>2.4.3</version>
</dependency>
<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.1.4</version>
</dependency>

Edit 1

Would this be a solution?

   try(Connection con = DataSource.getInstance().getConnection()){
        UploadedFile file = new FileServerImplementation().uploadFile(fileType, fileName, folderId, projectId, tokenString, fileInputStream, con);
        if(file != null){
            return Response.ok().entity(file).build();
        }
    }

Here the uploadFile method is the following:

public UploadedFile uploadFile(String fileType, String fileName, Long folderId, Long projectId, String tokenString, InputStream fileInputStream, Connection con) throws SQLException, IOException, PropertyVetoException{
    IOController ioController = new IOController();
    DatabaseController controller = DatabaseController.getInstance();
    UploadedFile file = null;
    if(ioController.uploadFile(fileType, fileName, controller.getFolderPath(folderId, con), projectId, fileInputStream)){
        file = controller.uploadFile(fileType, fileName, folderId, projectId, con);
    }else{
        System.out.println("Error uploading " + fileName + " to folder!");
    }
    return file;
}

Where IOController saves the file to on the disk, and afterwards the method uploadFile uploads some data to the database. Or should I each time I call a method in the DatabaseController class get a new connection from the pool?

Solution

I ended up using edit 1 as an approach. I just had to make sure i did not create unnecessary connection without closing them again.

You mentioned that con.close() returns null, how did you come to this conclusion? Most connection pool implementations rely on the programmer calling con.close() to properly release the connection back into the pool. — jon5477
I will try once more to use con.close in the end. But didnt work the first time I tried it. — JonasLagoni

OldCurmudgeon OldCurmudgeon · Accepted Answer · 2017-11-20T16:21:59

When using a connection pool you need to close the connection to release it to the pool for re-use. Do not hold the connection in the thread.

Connections not being released to pool in multithreaded program

Hello all

Edit 1

Solution

1 Answers