[DBCP-338] ORA-01453 on connections with previous errors

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Cannot Reproduce
• Affects Version/s: 1.0, 1.1, 1.2, 1.2.1, 1.2.2, 1.3, 1.4
• Fix Version/s: 1.3.1, 1.4.1

Environment

Oracle driver 11.1.0.7.0.

• Assignee: Unassigned
• Reporter: Phil Steitz
• Created: 13/Jun/10 18:06
• Updated: 24/Feb/15 03:23
• Resolved: 14/Feb/14 15:00

Description

Adapted from a post to commons-user by Tim Dudgeon:

When an Oracle connection with autocommit=true encounters an error executing a DDL statement, the connection is left in a transactional state that can lead to ORA-01453 when the connection is subsequently retrieved from the pool. The following code illustrates the problem:

package foo;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

public class Ora01453Example {

    private static final String URL = "jdbc:oracle:thin:@192.168.56.101:1521:orcl";
    private static final String USERNAME = "ijc";
    private static final String PASSWORD = "ijc";
    private PoolingDataSource dataSource;

    public static void main(String[] args) throws SQLException {
        Ora01453Example instance = new Ora01453Example();
        instance.run();
    }

    Ora01453Example() {
        GenericObjectPool connectionPool = new GenericObjectPool(null);
        connectionPool.setMaxActive(5);
        connectionPool.setMaxIdle(2);
        connectionPool.setMaxWait(10000);
        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(URL, USERNAME, PASSWORD);
        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
                connectionFactory, connectionPool, null, null, false, true);
        dataSource = new PoolingDataSource(connectionPool);
        dataSource.setAccessToUnderlyingConnectionAllowed(true);
    }

    void run() throws SQLException {
        System.out.println("Running...");
        // get the connection
        Connection con = getConnection();
        try {
            // this will fail, either first time or second
            executeSql(con, "create table qwerty (id varchar2(100))");
            executeSql(con, "create table qwerty (id varchar2(100))");
        } catch (SQLException e) {
            System.out.println("Failed as expected");
        } finally {
            // close connection so it goes back to pool
            con.close();
        }

        // get a connection from pool again.
        con = getConnection();
        System.out.println("Setting transaction level");
        // try to set isolation level - will fail (assuming same connection is retrieved)
        try {
            con.setAutoCommit(false);
            con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        } finally {
            con.close();
        }
    }

    Connection getConnection() throws SQLException {
        Connection con = dataSource.getConnection();
        System.out.println(
                "Got Connection: " + con.hashCode()
                + " autoCommit=" + con.getAutoCommit()
                + " isolation=" + con.getTransactionIsolation());
        return con;
    }

    void executeSql(Connection con, String sql) throws SQLException {
        Statement stmt = con.createStatement();
        try {
            stmt.execute(sql);
        } finally {
            stmt.close();
        }
    }
}

Assuming the problem is that the driver creates, but does not commit a transaction on the failed DDL statement (despite autocommit=true), the simplest DBCP workaround would be to remove the autocommit test that guards the rollback in PooloableConnectionFactory#passivate. That would have performance impacts, so it may be better to take an approach similar to DBCP-116, where we add a configuration parameter to either force rollback on each passivate or extend the rollbackAfterValidation config parameter defined there to PoolableConnectionFactory.