DO NOT REPLY [Bug 35591] New: - transactionIsolation, testOnBorrow and autoCommmit=false crashes for oracle

[bu...@apache.org]

DO NOT REPLY TO THIS EMAIL, BUT PLEASE POST YOUR BUG�
RELATED COMMENTS THROUGH THE WEB INTERFACE AVAILABLE AT
<http://issues.apache.org/bugzilla/show_bug.cgi?id=35591>.
ANY REPLY MADE TO THIS MESSAGE WILL NOT BE COLLECTED AND�
INSERTED IN THE BUG DATABASE.

http://issues.apache.org/bugzilla/show_bug.cgi?id=35591

           Summary: transactionIsolation,  testOnBorrow and
                    autoCommmit=false crashes for oracle
           Product: Commons
           Version: Nightly Builds
          Platform: Other
        OS/Version: other
            Status: NEW
          Severity: normal
          Priority: P2
         Component: Dbcp
        AssignedTo: commons-dev@jakarta.apache.org
        ReportedBy: fischer@seitenbau.de


I'm using dbcp nightly build 20050626 (i.e. bug 31811 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).

-- 
Configure bugmail: http://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug, or are watching the assignee.

---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org