Derby
  1. Derby
  2. DERBY-898

setAutoCommit(false) is not working properly for local transaction with ClientXADataSource

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 10.1.1.0, 10.1.2.1
    • Fix Version/s: 10.1.3.1, 10.2.1.6
    • Component/s: Network Server
    • Labels:
      None

      Description

      Network Server is not honoring local
      transaction rollback using ClientXADataSource. Run the following standalone JDBC code.
      The output shows that after rolling back the local transaction,
      the inserted data is still present.

      final org.apache.derby.jdbc.ClientXADataSource ds =
      new org.apache.derby.jdbc.ClientXADataSource();
      ds.setServerName("localhost");
      ds.setPortNumber(1527);

      ds.setDatabaseName("WOMBAT");
      ds.setTraceLevel(-1);

      ds.setSecurityMechanism(ds.CLEAR_TEXT_PASSWORD_SECURITY);
      ds.setUser("dbuser1");
      ds.setPassword("dbpwd1");
      //ds.setLogWriter(new
      java.io.PrintWriter(System.out));

      XAConnection xaConn = ds.getXAConnection();
      Connection conn = xaConn.getConnection();

      conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_R
      EAD);

      conn.setAutoCommit(true);

      System.out.println("Database product: " +
      conn.getMetaData().getDatabaseProductName());
      System.out.println("Database version: " +
      conn.getMetaData().getDatabaseProductVersion());
      System.out.println("Driver name: " +
      conn.getMetaData().getDriverName());
      System.out.println("Driver version: " +
      conn.getMetaData().getDriverVersion());

      Statement stmt = conn.createStatement();

      try

      { stmt.execute("drop table cmtest"); }

      catch (SQLException sqlX) {} // ok, didn't exist

      stmt.execute("CREATE TABLE cmtest (id integer not null
      primary key, name varchar(60))");
      stmt.close();

      conn.setAutoCommit(false);

      PreparedStatement pstmt = conn.prepareStatement(
      "INSERT INTO cmtest (id, name) VALUES(?,?)",
      ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_READ_ONLY);

      pstmt.setInt(1, 13);
      pstmt.setString(2, "blah1");
      pstmt.executeUpdate();

      pstmt.setInt(1, 2);
      pstmt.setString(2, "blah2");
      pstmt.executeUpdate();

      conn.rollback();

      PreparedStatement pstmt2 = conn.prepareStatement(
      "SELECT * FROM cmtest WHERE id = ?",
      ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_READ_ONLY);

      pstmt2.setInt(1, 13);

      ResultSet rset = pstmt2.executeQuery();

      if (rset.next())

      { System.out.println("Test fails. First insert was not rolled back."); System.out.println("The data is still present. It is: " + rset.getObject(1) + ", " + rset.getObject(2)); }

      else
      System.out.println("Test passes. First insert was
      rolled back.");

      Here's the output,

      Database product: Apache Derby
      Database version: 10.1.2.2
      Driver name: Apache Derby Network Client JDBC Driver
      Driver version: 10.1.2.2
      Test fails. First insert was not rolled back.
      The data is still present. It is: 13, blah1

      On some brief investigation I see that the Network Server embedded connection is in autocomit mode so is autocommitting the transaction before the rollback. Network server should always have autocommit false and let the client drive the commit.

      1. DERBY-898.diff
        20 kB
        Kathey Marsden

        Activity

        Hide
        Kathey Marsden added a comment -

        fixed in trunk and 10.1

        Show
        Kathey Marsden added a comment - fixed in trunk and 10.1
        Hide
        Kathey Marsden added a comment -

        Date: Thu Feb 9 06:02:18 2006
        New Revision: 376296

        URL: http://svn.apache.org/viewcvs?rev=376296&view=rev

        Show
        Kathey Marsden added a comment - Date: Thu Feb 9 06:02:18 2006 New Revision: 376296 URL: http://svn.apache.org/viewcvs?rev=376296&view=rev
        Hide
        Daniel John Debrunner added a comment -

        I think it would be good to get the names to reflect the reality that CDC/Foundation/JSR169 is a sub-set of the JDBC 3.0 spec,
        not that JDBC 3.0 is an extended version of JSR169.

        Possible names

        savepointJdbc30_JSR169
        savepointJdbc30_XA

        or

        savepointJdbc30
        savepointJdbc30_XA

        IThe XA iaccurately depicts what is being tested, rather than just saying extended.

        Show
        Daniel John Debrunner added a comment - I think it would be good to get the names to reflect the reality that CDC/Foundation/JSR169 is a sub-set of the JDBC 3.0 spec, not that JDBC 3.0 is an extended version of JSR169. Possible names savepointJdbc30_JSR169 savepointJdbc30_XA or savepointJdbc30 savepointJdbc30_XA IThe XA iaccurately depicts what is being tested, rather than just saying extended.
        Hide
        Kathey Marsden added a comment -

        OK. So I decided to go with option 1), but wonder what the naming convention should be for this.
        Right now I have simpleSavepointJdbc30.java for the base test that runs with all jvms and extendedSavepointJdbc30.java for the one that has the non J2ME/CDC/FP functionality, but would there be a better naming convention?

        Show
        Kathey Marsden added a comment - OK. So I decided to go with option 1), but wonder what the naming convention should be for this. Right now I have simpleSavepointJdbc30.java for the base test that runs with all jvms and extendedSavepointJdbc30.java for the one that has the non J2ME/CDC/FP functionality, but would there be a better naming convention?
        Hide
        Kathey Marsden added a comment -

        Checked in fix to 10.1

        Date: Fri Feb 3 13:00:35 2006
        New Revision: 374742

        URL: http://svn.apache.org/viewcvs?rev=374742&view=rev

        Show
        Kathey Marsden added a comment - Checked in fix to 10.1 Date: Fri Feb 3 13:00:35 2006 New Revision: 374742 URL: http://svn.apache.org/viewcvs?rev=374742&view=rev
        Hide
        Kathey Marsden added a comment -

        DERBY-898 setAutoCommit(false) is not working properly with connections objtained with ClientXADataSource.

        The problem in this case was that the server side connection was set to autocommit true. Even when the client has autocommit on, the server side connection should be autocommit false and let the client drive the commits.

        • Changes server side connection for network server XA to autocommit false.
        • Adds connections obtained from an XADataSource to the savepointJdbc30 test to verify that autocommit is being set properly and also verify that DERBY-899 (a dup of this issue) is fixed.

        I will commit to 10.1 later today.

        Show
        Kathey Marsden added a comment - DERBY-898 setAutoCommit(false) is not working properly with connections objtained with ClientXADataSource. The problem in this case was that the server side connection was set to autocommit true. Even when the client has autocommit on, the server side connection should be autocommit false and let the client drive the commits. Changes server side connection for network server XA to autocommit false. Adds connections obtained from an XADataSource to the savepointJdbc30 test to verify that autocommit is being set properly and also verify that DERBY-899 (a dup of this issue) is fixed. I will commit to 10.1 later today.

          People

          • Assignee:
            Kathey Marsden
            Reporter:
            Kathey Marsden
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development