commons-issues mailing list archives

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

                 Key: DBCP-338
             Project: Commons Dbcp
          Issue Type: Bug
    Affects Versions: 1.4, 1.3, 1.2.2, 1.2.1, 1.2, 1.1, 1.0
            Reporter: Phil Steitz
             Fix For: 1.3.1, 1.4.1

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:


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:@";
    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();;

    Ora01453Example() {
        GenericObjectPool connectionPool = new GenericObjectPool(null);
        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(URL, USERNAME,
        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
                connectionFactory, connectionPool, null, null, false, true);
        dataSource = new PoolingDataSource(connectionPool);

    void run() throws SQLException {
        // 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

        // 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 {
        } finally {

    Connection getConnection() throws SQLException {
        Connection con = dataSource.getConnection();
                "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 {
        } finally {


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.

View raw message