Multiple DB connections using a connection pool vs Single connection with multiple statements

Question

I am developing a server working with MySQL, and I have been trying to understand advantage of working with a connection pool vs a single connection that is kept open, and being passed down to the different methods through out the application.

The idea of working with a connection pool is understood, however there could be scenarios that this could create a bottleneck, that wouldn't be in case of working without the pool.

Better explain my meaning using code:

Lets say the following method is called simultaneously connectionPoolSize + 1 (e.g. 10) times, meaning that we have exhausted our connections from the connection pool, the last query attempt will fail since no connections available:

public void getData(con) {
    Connection con = null;
    Statement s = null;
    ResultSet rs = null;

    try {
        con = connectionPool.getConnection();
        s = con.createStatement();
        rs = s.executeQuery("SELECT * FROM MY_TABLE;");

        // Some long process that takes a while....

    catch(Exception e) {
        throw new Exception(e.getMessage())
    } finally {
        s.close();
        rs.close();
        con.close();
    }
}

However if we are using a single connection, that is kept open, and all methods can use it, there is no need for any of the methods to wait for the connection to be sent back to pool (which as we saw above, could take some time).

e.g. call this method also 10 times, this would work

public void getData(con) {
    
    Statement s = null;
    ResultSet rs = null;

    try {
        s = con.createStatement();
        rs = s.executeQuery("SELECT * FROM MY_TABLE;");

        // Some long process that takes a while....
        // But this time we don't care that this will take time,  
        // since nobody is waiting for us to release the connection


    catch(Exception e) {
        throw new Exception(e.getMessage())
    } finally {
        s.close();
        rs.close();
    }
}

Obviously the statements and result sets will still be kept open until the method is finished, but this doesn't affect the connection itself, so it doesn't hold back any other attempts to use this connection.

I assume there is some further insight that I am missing, I understand the standard is working with connection pools, so how do you handle these issues?

Answer 1

Depends on your use case. Suppose you are building a web application that would be used by multiple users simultaneously. Now if you have a single connection, all the queries from multiple user threads will be queued. And single db connection will process them one by one. So in a multi-user system(mostly all normal cases), single db connection will be a bottleneck & won't work. Additionally, you need to take care of thread safety in case you are writing & committing data to db.

If you need truly simultaneous query execution in db, then you should go ahead with connection pool. Then different user threads can use different connections & can execute queries in parallel.

Answer 2

Connection pools are used to keep a number of opened connections ready for use and to eliminate the need to open a new connection each time it is required.

If your application is single threaded then you probably don’t need a pool and can use a single connection instead.

Even though sharing a connection between multiple threads is permitted there are some pitfalls of this approach. Here is a description for Java DB: https://docs.oracle.com/javadb/10.8.3.0/devguide/cdevconcepts89498.html. You should check if this is also the case for MySQL.

In many cases it is easier to have an individual connection for each thread.