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