Uploaded image for project: 'Commons DBCP'
  1. Commons DBCP
  2. DBCP-116

[dbcp] transactionIsolation, testOnBorrow and autoCommmit=false crashes for Oracle

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • Nightly Builds
    • 1.2.2
    • 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).

      Attachments

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            Unassigned Unassigned
            fischer@seitenbau.de Thomas Fischer
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Issue deployment