Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
Nightly Builds
-
None
-
Operating System: other
Platform: Other
-
35591
Description
I'm using dbcp nightly build 20050626 (i.e. COM-1666 is fixed) to work with a
SharedPoolDataSource with an underlying oracle9i database and the 9.2.0.4 oracle
jdbc driver.
defaultTransactionIsolation is set to 8 (serializable), testOnBorrow is set to
true and defaultAutoCommit is set to false. (as an aside, with the oracle 10g
driver 10.1.0.4 the error also appears for defaultAutoCommit=true, but this is
an oracle problem)
I retrieve a connection, commit it, and close it. The first time works fine. The
second time while retrieving the connection, I get the ORA-01453: SET
TRANSACTION must be first statement of transaction error in the method
SharedPoolDataSource.setupDefaults upon execution of the line 215 :
con.setTransactionIsolation(defaultTransactionIsolation);
I have debugged the pool behaviour, extracted the jdbc commands and am able to
reproduce the behaviour with the following code:
Connection connection = DriverManager.getConnection(sid, user, password);
connection.createStatement().execute("Select 1 from dual");
connection.setAutoCommit(false);
connection.setTransactionIsolation(
Connection.TRANSACTION_SERIALIZABLE);
//connection.setReadOnly(false); // not needed to get error
connection.commit();
connection.createStatement().execute("Select 1 from dual");
// connection.rollback(); // this would solve the problem
// might be inserted into the validateObject()
// method of KeyedCPDSConnectionFactory
// connection.setAutoCommit(false); // not needed to get error
connection.setTransactionIsolation(
Connection.TRANSACTION_SERIALIZABLE); // this causes the error
I am not sure whether the rollback after the validation query fits into the
general philosophy of dbcp. Before I have come to use dbcp, I had programmed my
own pool and had done a rollback on every connection returned to the pool, in
order not to hand out a connection with a started transaction. In my opinion,
this is a good thing, but one might also argue against it because it eats
performance.
Another solution would be to reverse the validation query /
setTransactionIsolation order, but it seems to me that this is very deep in the
pool architecture.
Still another solution would be to reset autocommit to true when a connection is
returned into the pool, but personally I do not like this solution (it has no
additional merit like the rollback solution).