Tuesday, November 5, 2013

Robust DB connection pool configuration

    Martin Fowler claims: 'Any fool can write code that a computer can understand. Good programmers write code that humans can understand'. I would add: 'Any fool can write code that survives a happy path'. Some time ago I posted a data source configuration for H2 DB. Was the configuration really robust?
For the sake of simplicity let's abandon data source configuration and let's focus on a raw connection pool configuration - thanks to that everything can be tested as a standalone application without Tomcat and JNDI. I prefer Tomcat connection pool which can be used in a standalone application. This is the code that contains connection pool configuration and performs two DB queries:
package test;

import java.sql.Connection;
import java.sql.Statement;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolConfiguration;
import org.apache.tomcat.jdbc.pool.PoolProperties;

public class DbConnectionValidationExample {

 public static void main(String[] args) throws Exception {
  PoolConfiguration config = new PoolProperties();
  config.setDriverClassName("org.h2.Driver");
  config.setUrl("jdbc:h2:tcp://localhost/mem:db1");
  config.setUsername("sa");
  config.setPassword("");
  
  config.setInitialSize(1);
  config.setMinIdle(1);
  config.setMaxIdle(1);
  config.setMaxActive(1);
  
  DataSource ds = new DataSource(config);
  
  try (Connection connection = ds.getConnection(); Statement statement = connection.createStatement()) {
   statement.execute("select 1");
   
   System.out.println("Statement 1 executed!");
  } catch(Exception ex) {
   throw ex;
  }
  
  // close a connection on DB side - I simply restarted a DB server process
  Thread.sleep(20000);
  
  try (Connection connection = ds.getConnection(); Statement statement = connection.createStatement()) {
   statement.execute("select 1");
   
   System.out.println("Statement 2 executed!");
  } catch(Exception ex) {
   throw ex;
  }
 }
}   
and Maven dependencies:
<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <version>1.3.173</version>
</dependency>

<dependency>
  <groupId>org.apache.tomcat</groupId>
  <artifactId>tomcat-jdbc</artifactId>
  <version>7.0.47</version>
</dependency>
Everything works perfectly fine without closing a connection on DB side:
Statement 1 executed!
Statement 2 executed!
The happy path is as follows:
1). The connection is retrieved from a pool (connection is already there thanks to 'setInitialSize(1)' invocation).
2). A query is executed and the connection is released (goes back to the connection pool) automatically - I used a feature from Java 7 - 'try with resources' to ensure auto closing (auto releasing).
3). The whole cycle is repeated - the same connection is reused.
But what if the connection that exists in the connection pool (it's idle but it's there) will be closed on DB side because of e.g. DB server restart or aggressive DB server connections management? In that case the following output will be produced:
Statement 1 executed!
Exception in thread "main" org.h2.jdbc.JdbcSQLException:
Connection is broken: "session closed" [90067-173]
 at org.h2.message.DbException.getJdbcSQLException(DbException.java:331)
In the case of closed connection, the connection should be reestablished before being returned to the client code, otherwise you will receive the exception. However, it will not be done without proper DB connection pool configuration. We need do specify so called validation query:
config.setValidationQuery("select 1"); // for Oracle it will be 'select 1 from dual'
config.setValidationQueryTimeout(10); // in s
config.setValidationInterval(10000); // in ms
config.setTestOnBorrow(true); // extremely important line. Without it validation query will not be executed!
Thanks to that Tomcat DB connection pool performs 'select 1' query before returning it to the client code. If there is an exception - the connection will be reestablished. Now, the output is:
Statement 1 executed!
Statement 2 executed!
even with DB process restart. 'testOnBorrow' property is Tomcat connection pool specific. In a different DB connection pool implementation you may not need it but please double check it if you do not want to be woken up in the middle of the night just to refill connection pool because of stale connections.

What is your DB connection pool configuration like? Is it robust? Or maybe robustness is just an illusion because you specified validation query but you forgot about testOnBorrow?


No comments :

Post a Comment