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

        Hide
        Phil Steitz added a comment -

        Patch applied. Thanks!

        Show
        Phil Steitz added a comment - Patch applied. Thanks!
        Hide
        Thomas Fischer added a comment -

        Created an attachment (id=16332)
        test case to ensure that the patch works

        This is a modification of the first test case. If run against the patched dbcp
        version, no error should occur. The error can be reproduced by setting the
        class variable ROLLBACK_AFTER_VALIDATION in the testcase to false.

        Show
        Thomas Fischer added a comment - Created an attachment (id=16332) test case to ensure that the patch works This is a modification of the first test case. If run against the patched dbcp version, no error should occur. The error can be reproduced by setting the class variable ROLLBACK_AFTER_VALIDATION in the testcase to false.
        Hide
        Thomas Fischer added a comment -

        Created an attachment (id=16331)
        proposed patch

        This patch resolves the problem using an additional configuration parameter,
        rollbackAfterValidation. If set to true, dbcp issues a rollback after a
        validation query is executed. The default is not to rollback, so backward
        compatibility is assured.
        The public API was only extended, no existing method signature was modified.

        Show
        Thomas Fischer added a comment - Created an attachment (id=16331) proposed patch This patch resolves the problem using an additional configuration parameter, rollbackAfterValidation. If set to true, dbcp issues a rollback after a validation query is executed. The default is not to rollback, so backward compatibility is assured. The public API was only extended, no existing method signature was modified.
        Hide
        Thomas Fischer added a comment -

        Created an attachment (id=16330)
        testcase to show described behaviour

        The bug is produced in the test testRepeated. The other tests prove everything
        else works as expected.
        The classpatch must contain dbcp, its dependent libraries, and an oracle driver
        jar(I used jdbc14.jar from oracle 9.2.0.6).
        Somebody wanting to run the test must be able to connect to an oracle instance
        (I used oracle 9.2.0.6). The system properties commons.dbcp.user,
        commons.dbcp.password, and commons.dbcp.url must contain the connection info
        for the oracle connection.

        Show
        Thomas Fischer added a comment - Created an attachment (id=16330) testcase to show described behaviour The bug is produced in the test testRepeated. The other tests prove everything else works as expected. The classpatch must contain dbcp, its dependent libraries, and an oracle driver jar(I used jdbc14.jar from oracle 9.2.0.6). Somebody wanting to run the test must be able to connect to an oracle instance (I used oracle 9.2.0.6). The system properties commons.dbcp.user, commons.dbcp.password, and commons.dbcp.url must contain the connection info for the oracle connection.
        Hide
        Thomas Fischer added a comment -

        I would volunteer to patch and write a testcase for the bug. My idea would be to
        add another configuration parameter, rollbackAfterValidation, which is set false
        by default. If it is set true, it performs a rollback after every validation
        query, which allows to set the connection to serializable afterwards.
        I just would like to make sure that this general direction is ok for dbcp. Some
        feedback would be nice.

        Thanks,

        Thomas

        Show
        Thomas Fischer added a comment - I would volunteer to patch and write a testcase for the bug. My idea would be to add another configuration parameter, rollbackAfterValidation, which is set false by default. If it is set true, it performs a rollback after every validation query, which allows to set the connection to serializable afterwards. I just would like to make sure that this general direction is ok for dbcp. Some feedback would be nice. Thanks, Thomas

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development