Commons Dbcp
  1. Commons Dbcp
  2. DBCP-116

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

    Details

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

      Operating System: other
      Platform: Other

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

        Activity

        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 [ commons-dev@jakarta.apache.org ]
        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 ]

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development