commons-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Phil Steitz (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DBCP-338) ORA-01453 on connections with previous errors
Date Sun, 13 Jun 2010 20:20:13 GMT

     [ https://issues.apache.org/jira/browse/DBCP-338?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Phil Steitz updated DBCP-338:
-----------------------------

    Environment: Oracle driver 11.1.0.7.0. 
    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:

{code}

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

{code}

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.



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

When a 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:

{code}

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

{code}

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.




> ORA-01453 on connections with previous errors
> ---------------------------------------------
>
>                 Key: DBCP-338
>                 URL: https://issues.apache.org/jira/browse/DBCP-338
>             Project: Commons Dbcp
>          Issue Type: Bug
>    Affects Versions: 1.0, 1.1, 1.2, 1.2.1, 1.2.2, 1.3, 1.4
>         Environment: Oracle driver 11.1.0.7.0. 
>            Reporter: Phil Steitz
>             Fix For: 1.3.1, 1.4.1
>
>
> 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:
> {code}
> 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();
>         }
>     }
> }
> {code}
> 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.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message