Commons Dbcp
  1. Commons Dbcp
  2. DBCP-116

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


    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: Nightly Builds
    • Fix Version/s: 1.2.2
    • Labels:
    • Environment:

      Operating System: other
      Platform: Other


      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 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 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 :
      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.setReadOnly(false); // not needed to get error

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

      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).


        Thomas Fischer created issue -
        Henri Yandell made changes -
        Field Original Value New Value
        issue.field.bugzillaimportkey 35591 12342348
        Henri Yandell made changes -
        Assignee Jakarta Commons Developers Mailing List [ ]
        Project Commons [ 12310458 ] Commons Dbcp [ 12310469 ]
        Affects Version/s Nightly Builds [ 12311648 ]
        Component/s Dbcp [ 12311109 ]
        Key COM-2196 DBCP-116
        Henri Yandell made changes -
        Affects Version/s Nightly Builds [ 12311711 ]
        Phil Steitz made changes -
        Bugzilla Id 35591
        Fix Version/s 1.2.2 [ 12311976 ]
        Phil Steitz made changes -
        Resolution Fixed [ 1 ]
        Status Open [ 1 ] Resolved [ 5 ]
        Henri Yandell made changes -
        Status Resolved [ 5 ] Closed [ 6 ]


          • Assignee:
            Thomas Fischer
          • Votes:
            0 Vote for this issue
            0 Start watching this issue


            • Created: