Atomikos Forum

Hello

Our firewall is killing idle connections after 30 minutes. This is not unusual in corporate infrastructures.

We would like to have the atomikos datasource pool to validate connections, not only when performing the SQL request like now, but periodically, to evict erroneous connections if necessary.

See this article for instance:
http://www.tomcatexpert.com/blog/2012/01/24/using-tomcat-7-jdbc-connection-pool-production

The validationInterval from Tomcat JDBC pool specifies the interval at which a daemon thread will perform validation for ALL connection in the pool.

In Atomikos Datasource Pool, the maintenanceInterval property specifies the interval at which 2 operations will be performed: shrinkPool() and reapPool()

See com.atomikos.datasource.pool.ConnectionPool.init()
    
maintenanceTimer = new PooledAlarmTimer(maintenanceInterval * 1000 );
        maintenanceTimer.addAlarmTimerListener(new AlarmTimerListener() {
  public void alarm(AlarmTimer timer) {
    shrinkPool();
    reapPool();
  }
});
TaskManager.getInstance().executeTask (maintenanceTimer);

shrinkPool tries to shrink the pool size back to minPoolSize.
reapPool kills connections that have been non idle and in use for a time longer than the reapTimeout parameter.

So in the Atomikos maintenanceInterval no operation is performed to test the validity of all connections in the pool: the underlying connection test is only performed when actually borrowing a connection from the pool, just before the execution of the SQL request.

See
com.atomikos.datasource.pool.ConnectionPool.borrowConnection() ->
com.atomikos.datasource.pool.AbstractXPooledConnection.createConnectionProxy() ->
com.atomikos.datasource.pool.AbstractXPooledConnection.testUnderlyingConnection()

We think that the solution is, on ConnectionPool init, to create a daemon thread that regularly test ALL connections in the pool..

Some people had similar issue in the past:
default82df.html?community.6.2498.1

Is this planned to be implemented in Atomikos ? Or maybe we can propose a patch ?

For those interested in this functionality here is the patch we applied to version 3.7.0 of atomikos-jta and atomikos-jdbc

Index: D:/project/common/varia/libraries/atomikos/transactions-jta/src/main/java/com/atomikos/datasource/pool/ConnectionPoolProperties.java
===================================================================
--- D:/project/common/varia/libraries/atomikos/transactions-jta/src/main/java/com/atomikos/datasource/pool/ConnectionPoolProperties.java    (revision 329)
+++ D:/project/common/varia/libraries/atomikos/transactions-jta/src/main/java/com/atomikos/datasource/pool/ConnectionPoolProperties.java    (revision 330)
@@ -97,5 +97,17 @@
      * @return The level, or -1 if not set.
      */
     int getDefaultIsolationLevel();
-    
+
+    /**
+    * Get the time to check if the connection is active.
+    * @return
+    */
+    int getIdleConnectionTestPeriod();
+
+    /**
+    * Get the query to validate if the connection is active.
+    * @return
+    */
+    String getValidationQuery();
+
 }
Index: D:/project/common/varia/libraries/atomikos/transactions-jta/src/main/java/com/atomikos/datasource/pool/ConnectionPool.java
===================================================================
--- D:/project/common/varia/libraries/atomikos/transactions-jta/src/main/java/com/atomikos/datasource/pool/ConnectionPool.java    (revision 329)
+++ D:/project/common/varia/libraries/atomikos/transactions-jta/src/main/java/com/atomikos/datasource/pool/ConnectionPool.java    (revision 330)
@@ -87,6 +87,7 @@
         maintenanceTimer.addAlarmTimerListener(new AlarmTimerListener() {
             public void alarm(AlarmTimer timer) {
                 shrinkPool();
+                idlePool();
                 reapPool();
             }
         });
@@ -216,7 +217,25 @@
         }
         connections.removeAll(connectionsToRemove);
     }
-    
+
+    private synchronized void idlePool() {
+        if (connections == null || properties.getIdleConnectionTestPeriod() <= 0)
+            return;
+
+        if (Configuration.isDebugLoggingEnabled()) Configuration.logDebug(this + ": trying to check idle connection in pool");
+
+        long idleConnectionTestPeriod = properties.getIdleConnectionTestPeriod();
+
+        for (int i = 0; i < connections.size(); i++) {
+            XPooledConnection xpc = (XPooledConnection) connections.get(i);
+            long lastRelease = xpc.getLastTimeReleased();
+            long now = System.currentTimeMillis();
+            if (Configuration.isDebugLoggingEnabled()) Configuration.logDebug(this + ": connection idle for " + (now - lastRelease) + "ms");
+            if (xpc.isAvailable() && now - lastRelease >= idleConnectionTestPeriod * 1000L)
+                xpc.checkConnection();
+        }
+    }
+
     private synchronized void reapPool()
     {
         long maxInUseTime = properties.getReapTimeout();
Index: D:/project/common/varia/libraries/atomikos/transactions-jta/src/main/java/com/atomikos/datasource/pool/XPooledConnection.java
===================================================================
--- D:/project/common/varia/libraries/atomikos/transactions-jta/src/main/java/com/atomikos/datasource/pool/XPooledConnection.java    (revision 329)
+++ D:/project/common/varia/libraries/atomikos/transactions-jta/src/main/java/com/atomikos/datasource/pool/XPooledConnection.java    (revision 330)
@@ -94,5 +94,8 @@
     
     void unregisterXPooledConnectionEventListener(XPooledConnectionEventListener listener);
 
-
+    /**
+     * Check if the connection is already valid.
+     */
+    void checkConnection();
 }
Index: D:/project/common/varia/libraries/atomikos/transactions-jta/src/main/java/com/atomikos/datasource/pool/AbstractXPooledConnection.java
===================================================================
--- D:/project/common/varia/libraries/atomikos/transactions-jta/src/main/java/com/atomikos/datasource/pool/AbstractXPooledConnection.java    (revision 329)
+++ D:/project/common/varia/libraries/atomikos/transactions-jta/src/main/java/com/atomikos/datasource/pool/AbstractXPooledConnection.java    (revision 330)
@@ -133,4 +133,9 @@
     protected abstract Reapable doCreateConnectionProxy ( HeuristicMessage hmsg ) throws CreateConnectionException;
 
     protected abstract void testUnderlyingConnection() throws CreateConnectionException;
+    
+    protected String getValidationQuery() {
+        return props.getValidationQuery();
+    }
+
 }
Index: D:/project/common/varia/libraries/atomikos/transactions-jdbc/src/main/java/com/atomikos/jdbc/AtomikosXAPooledConnection.java
===================================================================
--- D:/project/common/varia/libraries/atomikos/transactions-jdbc/src/main/java/com/atomikos/jdbc/AtomikosXAPooledConnection.java    (revision 329)
+++ D:/project/common/varia/libraries/atomikos/transactions-jdbc/src/main/java/com/atomikos/jdbc/AtomikosXAPooledConnection.java    (revision 330)
@@ -169,5 +169,25 @@
         return ret;
     }
     
+    public void checkConnection() {
+        if ( isErroneous() ) return;
+        String validationQuery = getValidationQuery();
+        if (validationQuery != null) {
+            if ( Configuration.isDebugLoggingEnabled() ) Configuration.logDebug ( this + ": validating connection with query [" + validationQuery + "]" );
+            Statement stmt = null;
+            try {
+                stmt = connection.createStatement();
+                stmt.execute(validationQuery);
+                stmt.close();
+            } catch ( Exception e) {
+                Configuration.logWarning(this + ":Error executing validationQuery" ,  e );
+            }
+            if ( Configuration.isDebugLoggingEnabled() ) Configuration.logDebug ( this + ": connection validated OK" );
+        }
+        else {
+            if ( Configuration.isDebugLoggingEnabled() ) Configuration.logDebug ( this + ": no validation query, skipping test" );
+        }
+    }
 
+
 }
Index: D:/project/common/varia/libraries/atomikos/transactions-jdbc/src/main/java/com/atomikos/jdbc/nonxa/AtomikosNonXAPooledConnection.java
===================================================================
--- D:/project/common/varia/libraries/atomikos/transactions-jdbc/src/main/java/com/atomikos/jdbc/nonxa/AtomikosNonXAPooledConnection.java    (revision 329)
+++ D:/project/common/varia/libraries/atomikos/transactions-jdbc/src/main/java/com/atomikos/jdbc/nonxa/AtomikosNonXAPooledConnection.java    (revision 330)
@@ -197,4 +197,25 @@
         }
         return ret;
     }
+
+  public void checkConnection() {
+        String validationQuery = getValidationQuery();
+        if ( isErroneous() ) return;
+        if (validationQuery != null) {
+            if ( Configuration.isDebugLoggingEnabled() ) Configuration.logDebug ( this + ": validating connection with query [" + validationQuery + "]" );
+            Statement stmt = null;
+            try {
+                stmt = connection.createStatement();
+                stmt.execute(validationQuery);
+                stmt.close();
+            } catch ( Exception e) {
+                Configuration.logWarning(this + ":Error executing validationQuery" ,  e );
+            }
+            if ( Configuration.isDebugLoggingEnabled() ) Configuration.logDebug ( this + ": connection validated OK" );
+        }
+        else {
+            if ( Configuration.isDebugLoggingEnabled() ) Configuration.logDebug ( this + ": no validation query, skipping test" );
+        }
+    }
+
 }
Index: D:/project/common/varia/libraries/atomikos/transactions-jdbc/src/main/java/com/atomikos/jdbc/AbstractDataSourceBean.java
===================================================================
--- D:/project/common/varia/libraries/atomikos/transactions-jdbc/src/main/java/com/atomikos/jdbc/AbstractDataSourceBean.java    (revision 329)
+++ D:/project/common/varia/libraries/atomikos/transactions-jdbc/src/main/java/com/atomikos/jdbc/AbstractDataSourceBean.java    (revision 330)
@@ -77,6 +77,9 @@
     private String resourceName;
 
     private int defaultIsolationLevel = DEFAULT_ISOLATION_LEVEL_UNSET;
+
+    private int idleConnectionTestPeriod = 300;
+    private String validationQuery;
     
     protected void throwAtomikosSQLException ( String msg ) throws AtomikosSQLException
     {
@@ -415,5 +418,24 @@
         return defaultIsolationLevel;
     }
 
+    public int getIdleConnectionTestPeriod()
+    {
+        return idleConnectionTestPeriod;
+    }
+
+    public void setIdleConnectionTestPeriod(int idleConnectionTestPeriod)
+    {
+        this.idleConnectionTestPeriod = idleConnectionTestPeriod;
+    }
+
+    public String getValidationQuery()
+    {
+        return validationQuery;
+    }
+
+    public void setValidationQuery(String validationQuery)
+    {
+        this.validationQuery = validationQuery;
+    }
     
 }