Jdbc MySQL Connection Pooling Practices to Avoid Exhausted Connection Pool

JDBC MySql connection pooling practices to avoid exhausted connection pool

The exception indicates a typical case of application code which leaks database connections. You need to ensure that you acquire and close all of them (Connection, Statement and ResultSet) in a try-with-resources block in the very same method block according the normal JDBC idiom.

public void create(Entity entity) throws SQLException {
    try (
        Connection connection = dataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL_CREATE);
    ) { 
        statement.setSomeObject(1, entity.getSomeProperty());
        // ...
        statement.executeUpdate();
    }
}

Or when you're not on Java 7, in a try-finally block. Closing them in finally will guarantee that they are also closed in case of exceptions.

public void create(Entity entity) throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;

    try { 
        connection = dataSource.getConnection();
        statement = connection.prepareStatement(SQL_CREATE);
        statement.setSomeObject(1, entity.getSomeProperty());
        // ...
        statement.executeUpdate();
    } finally {
        if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }
}

Yes, you still need to close connections yourself, even when using connection pooling. It's a common mistake among starters that they think that it will then automatically handle the close. This is not true. The connection pool namely returns a wrapped connection which does something like the following in the close():

public void close() throws SQLException {
    if (this.connection is still eligible for reuse) {
        do not close this.connection, but just return it to pool for reuse;
    } else {
        actually invoke this.connection.close();
    }
}

Not closing them would cause the connection not being released back to the pool for reuse and thus it will acquire a new one again and again until the DB runs out of connections which will cause your application to crash.

MySQL and JDBC Connection Pool: unclosed statements

The answer is yes, it can cause issues. As is discussed here in SO:

Closing JDBC Connections in Pool
JDBC MySql connection pooling practices to avoid exhausted connection pool

if you don't close connection-related resources in reverse order after you're done with them (or in a finally block), you're at risk. Connection pools vary on how they handle these, but it is worrisome - a minimum - that an improperly closed set of resources is thrown back into the pool.

In case it was unclear (and you may already know this), proper closing of resources is discussed further here:

How to properly clean up JDBC resources in Java?

Note that in forthcoming Java 7, there will be some help here:

http://www.javaspecialists.eu/archive/Issue190.html

in which a new try-with-resources statement is introduced in Java, which automatically closes any AutoCloseable resources referenced in the try statement.

JDBC Connection Pooling: Connection Reuse?

Connection pooling works by re-using connections. Applications "borrow" a connection from the pool, then "return" it when finished. The connection is then handed out again to another part of the application, or even a different application.

This is perfectly safe as long as the same connection is not is use by two threads at the same time.

The key point with connection pooling is to avoid creating new connections where possible, since it's usually an expensive operation. Reusing connections is critical for performance.

Optimal number of connections in connection pool

There is a very simple answer to this question:

The number of connections in the connection pool should be equal the number of the exec threads configured in WebLogic.

The rationale is very simple: If the number of the connections is less than the number of threads, some of the thread maybe waiting for a connection thus making the connection pool a bottleneck. So, it should be equal at least the number the exec threads (thread pool size).

Closing JDBC Connections in Pool

When using Connection Pool, should one close the Connection at the end? If so, isn't the purpose of pooling lost? And if not, how does the DataSource know when a particular instance of Connection is freed up and can be reused? I am a little confused on this one, any pointers appreciated.

Yes, certainly you need to close the pooled connection as well. It's actually a wrapper around the actual connection. It wil under the covers release the actual connection back to the pool. It's further up to the pool to decide whether the actual connection will actually be closed or be reused for a new getConnection() call. So, regardless of whether you're using a connection pool or not, you should always close all the JDBC resources in reversed order in the finally block of the try block where you've acquired them. In Java 7 this can be further simplified by using try-with-resources statement.

Is the following method anything close to standard? Looks like an attempt to get a connection from the pool, and if DataSource cannot be established, use the old fashioned DriverManager. We are not even sure which part is getting executed at runtime. Repeating the question above, should one close the Connection coming out of such a method?

The example is pretty scary. You just need to lookup/initialize the DataSource only once during application's startup in some constructor / initialization of an applicationwide DB config class. Then just call getConnection() on the one and same datasource throughout the rest of application's lifetime. No need for synchronization nor nullchecks.

Missing connections in tomcat jdbc connection pool

Taking a look at the source of ConnectionPool.java you seem to hit this code snippet in the borrowConnection() method:

        //we didn't get a connection, lets see if we timed out
        if (con == null) {
            if ((System.currentTimeMillis() - now) >= maxWait) {
                throw new SQLException("[" + Thread.currentThread().getName()+"] " +
                    "Timeout: Pool empty. Unable to fetch a connection in " + (maxWait / 1000) +
                    " seconds, none available["+busy.size()+" in use].");
            } else {
                //no timeout, lets try again
                continue;
            }
        }

So according to this, your connection is Null.

The value of con is retrieved on the line:

PooledConnection con = idle.poll();

if you track the code, you will see idle is (depending on your configuration, but by default) FairBlockingQueue. You may checkout the implementation for hints.

In general you always have to close ResultSets, Statements, and Connections and used connections should be correctly released back to the pool.
Not doing so correctly may result in connections never been closed => never being available again for reuse (connection pool "leaks").

I suggest you construct some detailed logging over the state of the pool and monitor it to isolate the problem.

Some guidelines from Apache for preventing database connection pool leaks:

removeAbandoned="true"

abandoned database connections are removed and recycled

removeAbandonedTimeout="60"

set the number of seconds a database connection has been idle before it is considered abandoned

logAbandoned="true"

log a stack trace of the code which abandoned the database connection resources. Keep in mind that "logging of abandoned Connections adds overhead for every Connection borrow because a stack trace has to be generated."

I still think slightly increasing the maxWait value (1200, 1500, 1700 - just experiment, there will be no difference in the response times from user perspective) will clear those rare cases, in which you still have problems.

Jdbc MySQL Connection Pooling Practices to Avoid Exhausted Connection Pool