Pool Analysis

Brett Wooldridge edited this page Mar 25, 2014 · 40 revisions

Performance is great, but it cannot come at the cost of reliability. This page contains a critical analysis of popular JDBC connection pools.

“Criticism may not be agreeable, but it is necessary. It fulfills the same function as
pain in the human body. It calls attention to an unhealthy state of things.” 
- Winston Churchill

If you are a connection pool maintainer and correct any of these issues, please open an issue in the issue tracker to update this page.


HikariCP

This is where we sing our own praises:

  • Tests connections at the point of getConnection(), with an optimization or two
  • Encapsulates internal pool queries (test query and initSQL query) in their own transaction
  • Tracks and closes abandoned Statements at Connection.close() time
  • Executes a rollback() on Connections returned to the pool
  • Clears SQL warnings before returning a Connection to a client
  • Resets default auto-commit, transaction isolation levels, catalog, and read-only status
  • Traps and examines SQLException objects for disconnection errors

BoneCP

Connection Testing

The biggest single issue with BoneCP is the inability to configure the pool to test connections at the time of getConnection(). Every other pool here can be configured in that way. This is a sacrifice of reliability for speed.

SQL Warnings

A connection pool should clear SQL warnings via Connection.clearWarnings() either when the Connection is returned to the pool or before it is taken from the pool. BoneCP does not do this.

Auto-commit Handling

When the connection is configured for autocommit=false a connection pool should perform the test query or isValid() test within its own transaction, otherwise there is a transaction in progress when the connection is handed to the consumer. Calling a method like setReadOnly() on a "fresh" connection from BoneCP will throw an exception. The same is true for the "initSQL" that can be run on a Connection when it is created. BoneCP does not encapsulate the Connection test or the initSQL in its own transaction.

Unsafe Defaults

By default does not:

  • close abandoned open statements

Tomcat-JDBC

Connection State Reset

Tomcat does not by default reset connection auto-commit and transaction isolation levels for Connections in the pool. The user must configure a JDBCInterceptor called ConnectionState to do so.

SQL Warnings

A connection pool should clear SQL warnings via Connection.clearWarnings() either when the Connection is returned to the pool or before it is taken from the pool. Tomcat does not do this.

Auto-commit Handling

When the connection is configured for autocommit=false a connection pool should perform the test query or isValid() test within its own transaction, otherwise there is a transaction in progress when the connection is handed to the consumer. Calling a method like setReadOnly() on a "fresh" connection from Tomcat will throw an exception. The same is true for the "initSQL" that can be run on a Connection when it is created. Tomcat does not encapsulate the Connection test or the initSQL in its own transaction.

Abandoned Statement Handling

The JDBC contract specifies that when a Connection is closed, any open Statements that have not been closed yet should automatically be closed, otherwise a resource leak on the database server-side could occur. Tomcat does not track Statements by default, but can be configured with a StatementFinalizer interceptor that purports to serve this function. Unfortunately, the StatementFinalizer tracks Statements using a list of WeakReference objects, and therefore when the JVM comes under GC-pressure, abandoned Statements may be garbage collected before Tomcat has a chance to close them. This can result in subtle resource leaks that only occur under GC-pressure and therefore may be very difficult to track down. Further, with the StatementFinalizer enabled, Tomcat is unable to complete the Statement Cycle benchmark and deadlocks.

Unsafe Defaults

By default does not:

  • test connections at getConnection() time
  • execute rollback() on connections returned to the pool
  • close abandoned open statements (and cannot due to a design flaw)
  • reset connection state (auto-commit, transaction isolation, etc.)

C3P0

C3P0 is the oldest codebase among the pools here. If properly configured it "checks all of the boxes" in terms of correctness, as far we can tell. "As far as we can tell" is it itself telling, as the C3P0 codebase is huge compared to its competitors, and correspondingly complex in its structure. Checking the "correctness" of C3P0 is extremely difficult because of this -- analyzing 120 classes vs. 34 for Vibur and 21 for HikariCP.

We count over 230 uses of synchronized blocks and methods, and wait() and notifyAll() across various classes. Compare to 0 for HikariCP and Vibur-DBCP which employ lock-free designs. Extensive and wide spread use of locks leads to deadlock-prone code, and evidence of this can be found by Googling "c3p0 deadlock".

We do give C3P0 credit for seemingly covering all of the fundamentals. Unfortunately, C3P0 also consistently comes in last in every one of the microbenchmarks.

Unsafe Defaults

By default does not:

  • test connections at getConnection() time

Vibur-DBCP

While not well known currently, Vibur is an interesting attempt at a high-performance pool. With a little work, Vibur-DBCP could be a contender. Here is where we think Vibur gets it wrong:

Abandoned Statement Handling

The JDBC contract specifies that when a Connection is closed, any open Statements that have not been closed yet should automatically be closed, otherwise a resource leak on the database server-side could occur. Only in the case of cached PreparedStatements or CallableStatement does Vibur perform such resource cleanup. Even when caching is enabled ordinary Statement objects are not tracked for cleanup. When caching is disabled no Statements are tracked at all.

Auto-commit Handling

When the connection is configured for autocommit=false a connection pool should perform the test query or isValid() test within its own transaction, otherwise there is a transaction in progress when the connection is handed to the consumer. Calling a method like setReadOnly() on a "fresh" connection from Vibur will throw an exception. Vibur does not encapsulate connection tests.

When the connection is configured for autocommit=false a connection pool should perform the test query or isValid() test within its own transaction, otherwise the query becomes part of the user's transaction. Vibur does not encapsulate the Connection test in its own transaction.

SQL Warnings

A connection pool should clear SQL warnings via Connection.clearWarnings() either when the Connection is returned to the pool or before it is taken from the pool. Vibur does not do this.

Exception Detection

Vibur, like HikariCP and BoneCP, can detect when a SQLException indicates that a Connection has been severed. This is a good thing, but there are two issues. Vibur's list of detected SQL error codes is more limited than either HikariCP or BoneCP. Surprisingly, when Vibur detects that an exception has occurred on one connection, it ejects all connections from the pool, not just the connection that encountered the exception. Maybe this is "pro-active" but it is also likely to create a stampede of new connections to the database. The jury is still out on whether this is a good idea or not, we tend to lean toward not.

Unsafe Defaults

By default does not reset connection auto-commit and transaction isolation levels.


Complexity

While the number of lines of code is not a direct indication of complexity, there is an indisputable correlation between verbosity and understandability. It is well accepted in the industry that the number of bugs per 1000 lines of code is fairly consistent across projects and languages. Besides test suites, visual inspection is one of the surest ways to detect logical errors and race conditions in code. HikariCP has never experienced a documented deadlock or livelock.

Pool1 Files2 Code3
Vibur4 34 1927
HikariCP 21 2228
Tomcat 31 6345
BoneCP 49 7293
C3P0 120 15550
1Released versions as of 2014/03/15
2Excluding tests.
3Excluding comments.
4Vibur code counts include the Vibur Object Pool which comprises roughly 50% of the pool.
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.