Tomcat jdbc connection pool - rollback abandoned transaction

Question

We configure tomcat-jdbc connection pool with removeAbandoned=true. If connection is abandoned the option does work, but the connection is simply closed. With Oracle this means that current transaction is committed (see this question). This is not good because unfinished transaction should not be committed.

How to configure a pool so that if connection is abandoned then current transaction is first rolled back and after this the connection is closed?

I tried rollbackOnReturn=true but the pool doesn't seem to use it for abandoned connections.

Edit: we use defaultAutoCommit=false

Edit: one case this happened was debugging of integration test; our transaction table was truncated because of such commit

Answer 1

According to http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#close():

"It is strongly recommended that an application explicitly commits or rolls back an active transaction prior to calling the close method. If the close method is called and there is an active transaction, the results are implementation-defined."

This test, using Mysql rather than Oracle confirms this fact:

import static org.junit.Assert.assertEquals;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;


public class DBTest {

    public Connection openConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "password");
        c.setAutoCommit(false);
        return c;
    }

    @Test
    public void testSO25886466() throws SQLException, ClassNotFoundException {

        {
            Connection c = openConnection();
            PreparedStatement delete = c.prepareStatement("delete from temp");
            delete.executeUpdate();
            c.commit();
            c.close();
        }

        {
            Connection c = openConnection();
            PreparedStatement insert = c.prepareStatement("insert into temp values ('a', 'b')");
            insert.execute();
            //c.commit(); as the op says, DONT commit!!
            c.close(); //WITHOUT having closed the statement or committing the transaction!!
        }

        {
            Connection c = openConnection();
            PreparedStatement select = c.prepareStatement("select count(*) from temp");
            select.execute();
            ResultSet rs = select.getResultSet();
            while(rs.next()){
                assertEquals(0/*i'd expect zero here!*/, rs.getInt(1));
            }
            rs.close();
            select.close();
            c.close();
        }
    }
}

According to http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html:

(boolean) Flag to remove abandoned connections if they exceed the removeAbandonedTimeout. If set to true a connection is considered abandoned and eligible for removal if it has been in use longer than the removeAbandonedTimeout Setting this to true can recover db connections from applications that fail to close a connection. See also logAbandoned The default value is false.

I would recommend not setting removeAbandoned so that Oracle closes the connection after a timeout on the server side, rather than Tomcat closing it. Oracle will probably not commit the transaction in that case, but you would need to test this.

Alternatively, could you increase the removeAbandonedTimeout setting, so that your program can finish, and no connections get abandoned?

Another problem you have is that your application has become tied to Oracle because you are relying on the driver implementation where the spec has a hole in it. If you can, program against specs, so that you are free to migrate your application to a different database, although I know that is hard in practice.

A completely different solution would be to take an open source connection pool, and extend it with an AOP interceptor which can intercept calls to close and work out if the transaction has been committed, and if not, call rollback on the connection. That's quite a complex solution though... :-)

Answer 2

Ok.... I think if you cannot rule out abandoned connections you have only 3 Options:

1. Change the Method from Tomcats ConnectionPool which closes abandoned Connection to do Rollback+Close
2. Change the Close-Method of you Connection to do Rollback+Close
3. Let the DB rollback+close the Connection after a timeout (Disable Tomcats handler)

For Option 1 you can either edit the Method in Tomcat Source-Code, replace it via HotSwap or Javassist or disable it alltogether and write your own Method which loops over all connections and detects which are abandoned and closes them

For Option 2 you can either write your own Wrapper for the Connection-Interface, which will replace the close() call with rollback+close and configure TomCat to wrap the Connection into your Wrapper-Class, or you can use HotSwap or Javassist to replace the Close-Method in your connection-Class at runtime.

For Option 3 you disable the Whole Handling of abandoned Connections alltogether and configure your DB to kill off idle connections after a certain timeout. But this will have the downside of also regularly killing off connections in your connection pool when they are not used for a long time...

Answer 3

You could just register a JDBCInterceptor to do this modification, so you can rollback before its closed - look here: http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#JDBC_interceptors . Abandon will call release which will call disconnect so the Interceptor will be notified about this. For example you can do it like this:

package test;
import java.sql.SQLException;
import oracle.jdbc.OracleConnection;

import org.apache.tomcat.jdbc.pool.ConnectionPool;
import org.apache.tomcat.jdbc.pool.JdbcInterceptor;
import org.apache.tomcat.jdbc.pool.PooledConnection;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


public class RollbackInterceptor extends JdbcInterceptor {

  /**
   * Logger.
   */
private static final Logger LOG = LoggerFactory.getLogger(RollbackInterceptor.class);

/**
* {@inheritDoc}
*/
@Override
public void reset(ConnectionPool parent, PooledConnection con) {
  return;
}

/**
 * {@inheritDoc}
 */
@Override
public void disconnected(ConnectionPool parent, PooledConnection con, boolean finalizing) {
  // if its oracle make sure we rollback here before disconnect just in case a running TX is open
  try {
    if (con.getConnection().isWrapperFor(OracleConnection.class)) {
      if (!con.getConnection().getAutoCommit()) {
        LOG.error("Connection {} with Auto-Commit false is going to be closed. Doing an explicit Rollback here!", con);
        try {
          con.getConnection().rollback();
        } catch (SQLException e) {
          LOG.error("Failed to rollback connection {} before closing it.", con, e);
        }
      }
    }
  } catch (SQLException e) {
    LOG.error("Failed to check auto commit of connection {}", con, e);
  }
  super.disconnected(parent, con, finalizing);
}

}