How to Prevent JDBC Resource Leaks with JDBC and with jOOQ

Posted on by lukaseder
In a recent consulting gig, I was analysing a client’s connection pool issue in a productive system, where during some peak loads, all the Java processes involving database interactions just started queueing up until nothing really worked anymore. No exceptions, though, and when the peak load was gone in the evening, everything returned back to normal. The database load looked pretty healthy at the time, so no actual database problem was involved – the problem had to be a client side problem. Weblogic operations teams quickly identified the connection pool to be the bottleneck. All the connections were constantly allocated to some client process. The immediate thought was: A resource leak is happeneing, and it didn’t show before because this was an exceptional situation: Around the beginning of the new year when everyone wanted to download their electronic documents from the bank (and some new features introduced many more document related database calls).

The obvious problem

That particular system still runs a lot of legacy code in Java 6 style, which means, there are tons of code elements of the following kind: 

Connection connection = null;
try {

  // Get the connection from the pool through JNDI
  connection = JDBCHelper.getConnection();
}
finally {

  // Release the connection
  JDBCHelper.close(connection);  
}
While the above code is perfectly fine, and 99% of all database interactions were of the above type, there was an occasional instance of someone badly copy-pasting some code and doing something like this: 

Connection connection = JDBCHelper.getConnection();
PreparedStatement stmt = null;

try {
  stmt = connection.prepareStatement("SELECT ...");
}
finally {

  // Release the statement
  JDBCHelper.close(stmt);
}

// But the connection is never released
Sometimes, things were even more subtle, as a utility method expected a connection like this: 

// Utility method doesn't have to close the connection:
public void databaseCalls(Connection connection) {
  try {
    stmt = connection.prepareStatement("SELECT ...");
  }
  finally {

    // Release the statement
    JDBCHelper.close(stmt);
  }
}

public void businessLogic() {
  // Oops, subtle connection leak
  databaseCalls(JDBCHelper.getConnection());
}

Thoroughly fixing these things

There’s a quick fix to all these problems. The easiest fix is to just continue rigorously using the JDBCHelper.close() method (or just call connection.close() with appropriate error handling) every time. But apparently, that’s not easy enough as there will always be a non-vigilant developer (or a junior developer who doesn’t know these things), who will get it wrong, who will simply forget things. I mean, even the official JDBC tutorial gets it “wrong” on their first page: https://docs.oracle.com/javase/tutorial/jdbc/overview/index.html The bad example being: 

public void connectToAndQueryDatabase(
    String username, String password) {

    Connection con = DriverManager.getConnection(
                         "jdbc:myDriver:myDatabase",
                         username,
                         password);

    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(
        "SELECT a, b, c FROM Table1");

    while (rs.next()) {
        int x = rs.getInt("a");
        String s = rs.getString("b");
        float f = rs.getFloat("c");
    }
}
All resources leak in this example! Of course, it’s just an example, and of course, it’s not a terrible situation, because resources can usually clean up themselves when they go out of scope, i.e. when the GC kicks in. But as software engineers we shouldn’t rely on that, and as the productive issues have shown, there are always edge cases, where precisely this lack of vigilance will cause great harm. After all,
It works on my machine
… is simply not an excuse. We should design our software for productive use.

Fix #1: Use try-with-resources. Always

If you want to stay on the safe side, always follow this rule:
The scope that acquires the resource, closes the resource
As long as you’re working with JDBC, save yourself the trouble of writing those JDBCUtilities classes that close non-null resources and safely catch exceptions that may arise. Just use try-with-resources, all the time. For instance, take the example from the Oracle JDBC tutorial, which should read: 

public void connectToAndQueryDatabase(
     String username, String password) {

    // All of these resources are allocated in this method. Thus,
    // this method's responsibility is to also close / free all
    // these resources.
    try (Connection con = DriverManager.getConnection(
            "jdbc:myDriver:myDatabase", username, password);
         Statement stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery(
            "SELECT a, b, c FROM Table1")) {

        while (rs.next()) {
            int x = rs.getInt("a");
            String s = rs.getString("b");
            float f = rs.getFloat("c");
        }
    }
}
This already feels that much better and cleaner, doesn’t it? All the resources are acquired in the above method, and the try-with-resources block will close all of them when they go out of scope. It’s just syntax sugar for something we’ve been doing manually all the time. But now, we will (hopefully) never again forget! Of course, you could introduce automatic leak detection in your integration tests, because it’s rather easy to proxy the JDBC DataSource and count all connection acquisitions and closings. An example can be seen in this post: https://vladmihalcea.com/2016/07/12/the-best-way-to-detect-database-connection-leaks/

Fix #2: Use jOOQ, which manages resources for you

Historically, JDBC works on lazy resources that are kept around for a while. The assumption in 1997 (when JDBC was introduced) was that database interactions were relatively slow and it made sense to fetch and process one record at a time, even for moderately sized result sets. In fact, it was even common to abort fetching records from a cursor when we’ve had enough results and close it eagerly before consuming all the rows. Today, these assumptions are (mostly) no longer true, and jOOQ (like other, more modern database APIs) invert the lazy/eager API default behaviour. In jOOQ, the JDBC types have the following corresponding counterparts:
  • JDBC DataSource / Connection => jOOQ ConnectionProvider:
    jOOQ doesn’t know the concept of an “open connection” like JDBC. jOOQ only has this ConnectionProvider which works in a similar way to JDBC’s / JavaEE’s DataSource. The semantics here is that the connection / session is “managed” and jOOQ will acquire / release it once per statement. This happens automatically, so users don’t have to worry about any connection resource.
  • JDBC Statement (and subtypes) => jOOQ Query:
    While the JDBC statement (especially the PreparedStatement) is a resource that binds some server-side objects, such as an execution plan, for instance, jOOQ again doesn’t have such a resourceful thing. The Query just wraps the SQL string (or AST) and bind variables. All resources are created lazily only when the query is actually executed – and released immediately after execution. Again, users don’t have to worry about any statement resource.
  • JDBC ResultSet => jOOQ Result:
    The JDBC ResultSet corresponds to a server-side cursor, another object that possibly binds quite a few resources, depending on your fetch mode. Again, in jOOQ no resources are bound / exposed, because jOOQ by default eagerly fetches your entire result set – the assumption being that a low-level optimisation here doesn’t add much value for moderately sized result sets
With the above inverted defaults (from lazy to eager resource allocation / freeing), the jOOQ-ified Oracle JDBC tutorial code would look like this: Working with a standalone Connection 

public void connectToAndQueryDatabase(
    String username, String password) {

    // If you're using a standalone connection, you can pass that
    // one to jOOQ, but you're still responsible of closing it
    // again:
    try (Connection con = DriverManager.getConnection(
            "jdbc:myDriver:myDatabase", username, password)) {

        // There is no statment resource anymore, and the result
        // is fetched eagerly from the database, so you don't have
        // to worry about it
        for (Record record : DSL.using(con).fetch(
                "SELECT a, b, c FROM Table1")) {
            int x = record.get("a", int.class);
            String s = record.get("b", String.class);
            float f = record.get("c", float.class);
        }
    }
}
Working with a connection pool / DataSource 

// You probably have some means of injecting / discovering
// a JDBC DataSource, e.g. from Spring, or from your JavaEE
// container, etc.
@Inject
DataSource ds;

public void connectToAndQueryDatabase(
    String username, String password) {

    // With a DataSource, jOOQ will automatically acquire and
    // close the JDBC Connection for you, so the last remaining
    // resource has also disappeared from your client code.
    for (Record record : DSL
           .using(ds, SQLDialect.ORACLE)
           .fetch("SELECT a, b, c FROM Table1")) {
        int x = record.get("a", int.class);
        String s = record.get("b", String.class);
        float f = record.get("c", float.class);
    }
}
With jOOQ, all resource management is automatic, by default, because by default, you don’t want to worry about this low level stuff. It’s not 1997 anymore. The JDBC API really is too low level for most use-cases. If you do want to optimise resource management and not fetch everything eagerly, you can, of course. jOOQ will allow you to fetch your results lazily, in two ways: Using a Cursor 

@Inject
DataSource ds;

public void connectToAndQueryDatabase(
    String username, String password) {

    // jOOQ's Cursor type is a resource, just like JDBC's
    // ResultSet. It actually keeps a reference to an open
    // ResultSet, internally. This is an opt-in
    // feature, though, only to be used if desired.
    try (Cursor<Record> cursor : DSL
            .using(ds, SQLDialect.ORACLE)
            .fetchLazy("SELECT a, b, c FROM Table1")) {

        for (Record record : cursor) {
            int x = record.get("a", int.class);
            String s = record.get("b", String.class);
            float f = record.get("c", float.class);
        }
    }
}
Using a Java 8 Stream (lazy, resourceful version) 

@Inject
DataSource ds;

public void connectToAndQueryDatabase(
    String username, String password) {

    // This can also work with a stream
    try (Stream<Record> stream : DSL
        .using(ds, SQLDialect.ORACLE)
        .fetchStream("SELECT a, b, c FROM Table1")) {

        stream.forEach(record -> {
            int x = record.get("a", int.class);
            String s = record.get("b", String.class);
            float f = record.get("c", float.class);
        });
    }
}
Unfortunately, there are no auto-closing streams in Java, which is why we have to resort to using the try-with-resources statement, breaking the fluency of jOOQ’s API. Do note though, that you can use the Stream API in an eager fashion: Using a Java 8 Stream (eager version) 

@Inject
DataSource ds;

public void connectToAndQueryDatabase(
    String username, String password) {

    // Fetch the jOOQ Result eagerly into memory, then stream it
    // Again, no resource management
    DSL.using(ds, SQLDialect.ORACLE)
       .fetch()
       .stream("SELECT a, b, c FROM Table1")
       .forEach(record -> {
            int x = record.get("a", int.class);
            String s = record.get("b", String.class);
            float f = record.get("c", float.class);
        });
}

Conclusion

Developers, unfortunately, often suffer from
Works on my machine
This leads to problems that can be discovered only in production, under load. When it comes to resources, it is important to constantly remind ourselves that …
The scope that acquires the resource, closes the resource
JDBC (and the JDK’s IO APIs), “unfortunately”, deal with resources on a very low level. This way, their default behaviour is very resource-efficient. For instance, when you only need to read a file header, you don’t load the entire file into memory through the InputStream. You can explicitly, manually, only load the first few lines. But in many applications, this default and its low level nature gets in the way of correctness (accidental resource leaks are easy to create), and convenience (a lot of boiler plate code needs to be written). With database interactions, it’s usually best to migrate your JDBC code towards a more modern API like jOOQ, which abstracts resource handling away in its API and inverts the lazy/eager semantics: Eager by default, lazy on demand. More information about the differences between jOOQ and JDBC can be seen here, in the manual.

Published by lukaseder

I made jOOQ

24 thoughts on “How to Prevent JDBC Resource Leaks with JDBC and with jOOQ”

  4. A good connection pool can also be handy. For example HikariCP can log a warning if a connection is out of the pool more than a defined duration, cf leakDetectionThreshold parameter.

    Reply

      1. Yes sure, the warning log looks like this: 13:17:59.662 WARN com.zaxxer.hikari.pool.ProxyLeakTask – Connection leak detection triggered for conn1: url=jdbc:h2:mem:test user=, stack trace follows
        java.lang.Exception: Apparent connection leak detected
        at org.plume.demo.WebApplication.willLeakConnection(WebApplication.java:49)
        at org.plume.demo.WebApplication.main(WebApplication.java:31)

        Reply

  6. As stated, it would be safe to assume that by using a DataSource, jooq will automatically close the underlying Connection to database, when query is done.

    Quoting from above code inline comment:

    // With a DataSource, jOOQ will automatically acquire and
// close the JDBC Connection for you, so the last remaining
// resource has also disappeared from your client code

    Will it make any difference to use a DSL context as a Supplier to a Completable Future, as of below?

    public  CompletableFuture<List> supplyFuture(String query, Class clazz) {
        return CompletableFuture.supplyAsync( ()  - > DSL
                .using(this.db.getDataSource(), SQLDialect.REDSHIFT)
                .resultQuery(query)
                .fetch()
                .into(clazz))
                .exceptionally(ex -> {
                    logger.debug("Exception: " + ex.getMessage());
                    return Collections.emptyList();
                });
    }

    Do we still assume (and eventually expect) that underlying JDBC connection will be closed and this resource will return to the connection pool (we use HikariCP with Play Framework , by the way) ?

    Thanks in advance!!

    Reply

    1. Interesting question, thanks for asking!

      The jOOQ ConnectionProvider contract governs only what happens with jOOQ API, not with external third party API. Thus, your call to CompletableFuture.supplyAsync() is not affected by your supplying jOOQ with a data source.

      In your example, there’s a synchronous jOOQ fetch() call that acquires a connection and releases it again, before your lambda Supplier terminates.

      Note that any block of code that you supply a CompletableFuture with is executed synchronously. The asynchronicity only happens when composing different such blocks.

      Hope this helps

      Reply

      1. Sure it does, thanks for your rapid response !!

        We are experiencing ghost connections to the database, never returning to connection pool. This is why we would like to make sure any “asynchronicity” would not affect the connection pool.

        As a matter of fact, am “supplying” a Completable Future to “clients” of a database controller in our Play application, which they block on a result. DB controller does not block, works more like a db connection provider, than a direct data provider.

        Any comments of course are valuable, but just would like to depict our concept.

        Thank you very much, indeed.

        Reply

        1. Hmm, OK. What I said was true for jOOQ. jOOQ has the ConnectionProvider SPI contract, and usually, DataSource implementations work well with that. However, it may well be that your datasource isn’t really ready for asynchronicity.

          What I’m saying is that the issues you’re experiencing would be the same regardless if you use jOOQ or JDBC directly with this pattern inside of your lamdba:

          try (Connection connection = dataSource.getConnection()) {
  // Query here...
}

          Might be a good question for Stack Overflow! If you do ask there, would you mind provide a link to it from here?

          Reply

  7. I probably already got my answer, but I want to make sure..

    Do I manually need to close the connection (and cursor) in case I use try-with-resources option and return the Cursor object?

    for example:

    
try (Connection con = dataSource.getConnection()) {
    DSLContext create = DSL.using(con);
    Select<Record> query = ...build query code
    logger.debug("Executing SQL: " + query.toString());
    return query.fetchSize(500).fetchLazy();
}
    Reply

    1. As a side note, the reason I’m using stream in the first place is because the data pulled in the query won’t fit into memory. and also I don’t want my query logic function to also handle the response generated, therefore I return the cursor and handle the data in an outer function.

      Reply

    2. There are two separate things to note here. First, you have a DataSource, so it is probably better to pass that to DSL.using(dataSource), so you don’t have to manage the JDBC Connection lifecycle. jOOQ will do that for you. In fact, you should reuse your DSLContext for several queries, as it will in turn cache quite a few things that are shareable across queries (e.g. when you use the DefaultRecordMapper and its internal reflection cache).

      Secondly, your returned Cursor contains a JDBC ResultSet, which by default cannot outlive the JDBC Connection, so once you close the connection, the Cursor will be stale. Much better, assuming your method is this:

      Cursor<Record> method() {
    DSLContext create = DSL.using(dataSource, dialect);
    ...
    return query.fetchSize(500).fetchLazy();
}

      You should then call it like this:

      try (Cursor<Record> cursor = method()) {
    doStuffWith(cursor);
}
      Reply

      1. Thanks for the quick response!

        When u say share my DSLContext, does it mean I can init it once in say a static variable or spring singleton?

        Reply

        1. Also, I don’t see a constructor for

          DSL.using(dataSource);

          As far as I know I’m using the latest version, and the only constructor that accepts dataSource, also requires the SqlDialect object:

          public static DSLContext using(DataSource datasource, SQLDialect dialect)

          What am I missing?

          Reply

        2. Yes you can do that, as long as your DataSource is thread safe (most implementations are, as they provide thread-bound transactions), and if you never modify any parts of the Configuration or Settings after creating the DSLContext.

          Most people just inject a DSLContext using Spring, or even Spring Boot.

          Reply

          1. Thanks the the replay again.

            I’m using Oracle, but couldn’t find a dialect for it. I found many examples online with ‘SQLDialect.ORACLE’, however it’s not available.

            Using Jooq v3.11.11

            Reply

Leave a Reply