Tomcat jdbc连接池-回滚放弃的事务 [英] Tomcat jdbc connection pool - rollback abandoned transaction

问题描述

我们使用 removeAbandoned = true 配置tomcat-jdbc连接池.如果放弃了连接，则该选项确实起作用，但仅关闭了连接.对于Oracle，这意味着当前事务已提交(请参阅此问题).这不好，因为不应完成未完成的交易.

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?

我尝试了 rollbackOnReturn = true ，但是该池似乎没有将其用于废弃的连接.

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

编辑:我们使用 defaultAutoCommit = false

编辑:发生的一种情况是集成测试的调试；由于这种提交，我们的交易表被截断了

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

推荐答案

根据

强烈建议应用程序明确提交或在调用close方法之前回滚活动事务.如果调用close方法，并且有一个活动事务，结果是实现定义的."

此测试使用Mysql而不是Oracle确认了这一事实:

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();
        }
    }
}

根据 http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html :

(boolean)标志，如果已放弃的连接超过限制，则删除removeAbandonedTimeout.如果设置为true，则视为连接已废弃且有资格将其移除(如果使用时间超过了removeAbandonedTimeout 将此设置为true可以恢复数据库来自无法关闭连接的应用程序的连接.看还logAbandoned默认值为false.

(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.

我建议不要设置 removeAbandoned ，以便Oracle在服务器端超时后关闭连接，而不是Tomcat关闭连接.在这种情况下，Oracle可能不会提交事务，但是您需要对此进行测试.

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.

或者，您是否可以增加 removeAbandonedTimeout 设置，以便您的程序可以完成并且不放弃任何连接?

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

您遇到的另一个问题是，您的应用程序已与Oracle绑定在一起，因为您依赖的是规范中有漏洞的驱动程序实现.如果可以的话，请按照规范进行编程，以便您可以自由地将应用程序迁移到其他数据库，尽管我知道这在实践中很困难.

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.

一个完全不同的解决方案是采用一个开放源代码连接池，并使用AOP拦截器对其进行扩展，该拦截器可以拦截对 close 的调用，并确定是否已提交事务(如果未提交)，请在连接上调用 rollback .不过，这是一个非常复杂的解决方案...:-)

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... :-)

这篇关于Tomcat jdbc连接池-回滚放弃的事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！