[jira] Resolved: (DBCP-116) [dbcp] transactionIsolation, testOnBorrow and autoCommmit=false crashes for Oracle

["Phil Steitz (JIRA)" <ji...@apache.org>]

     [ http://issues.apache.org/jira/browse/DBCP-116?page=all ]

Phil Steitz resolved DBCP-116.
------------------------------

    Resolution: Fixed

Patch applied.  Thanks!

> [dbcp] transactionIsolation,  testOnBorrow and autoCommmit=false crashes for Oracle
> -----------------------------------------------------------------------------------
>
>                 Key: DBCP-116
>                 URL: http://issues.apache.org/jira/browse/DBCP-116
>             Project: Commons Dbcp
>          Issue Type: Bug
>    Affects Versions: Nightly Builds
>         Environment: Operating System: other
> Platform: Other
>            Reporter: Thomas Fischer
>             Fix For: 1.2.2
>
>         Attachments: patch_35591.txt, TestSerializableOracleConnection.java, TestSerializableOracleConnection.java
>
>
> 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).

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

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