6 Replies Latest reply on Jul 10, 2002 5:29 PM by John Bresnahan

    autocommit and NoTxConnectionManager

    Colin Daly Newbie


      Hi,

      JBossRC2

      we have a datasource to a legacy database which
      does not handle transactions and after migrating
      from 2.4.4 we hit the autocommit problem.

      So we changed the code attribute in our
      -service.xml to NoTxConnectionManager and commented
      out the TransactionManager attribute. We confirmed
      this because the datasource does not have a TransactionManager attribute in JNDI.
      However, we are still getting autocommit exceptions
      from the JDBC driver.

      is there anything else which must change
      in the -service.xml to get this to work.
      We tried changing every occurence of LocalTx/
      LocalTransaction to NoTx/NoTransaction and many
      combinations of both but no luck.
      (The OldRARDeployment name attibute cannot be
      changed from Local or our datasource stops getting deployed in JNDI.)

      I'm out of options... can anyone help.

      regards,
      Colin.

        • 1. Re: autocommit and NoTxConnectionManager
          David Jencks Master

          More info please. What exactly is the problem and where is it occurring? The NoTxConnectionManager doesn't try to do anything whatsoever with transactions.... so it is not at fault IMO

          Do you have a jca adapter or are you using a jca-jdbc wrapper? It is likely that the Local wrapper calls setAutocommit on your connections. If this is the problem, you can probably spend a couple minutes looking at the Local wrapper and commenting out the checkTransaction implementation.

          • 2. Re: autocommit and NoTxConnectionManager
            shanness Newbie

            Hi David,

            Thanks for the prompt reply, it helped. It seems one of our legacy data sources won't accept AUTOCOMMIT, yet it is non-transactional.

            Anyhow, I have patched the local wrapper (connector/src/main/org/jboss/resource/adapter/jdbc/local/LocalManagedConnection.java)
            as you suggested, and it fixed the problem.

            I don't know how many other data sources have the same problem as mine (Transoft), but would it be possbile to make this an attribute so I don't have to maintain a patched jboss?

            Thanks again David.

            Regards,

            Craig O'Shannessy

            • 3. Re: autocommit and NoTxConnectionManager
              wayne townsend-merino Newbie

              I also need this, but wouldn't it be better to make the NoTxConnectionManager deployable?

              • 4. Re: autocommit and NoTxConnectionManager
                David Jencks Master

                I don't understand what you are trying to say. All 3 connection managers are deployable; that is what the db-service.xml files have, the deployment descriptor for one or the other.

                Also, the suggested change was to the Local jdbc wrapper, which is a jca adapter, and sort of marginally part of jboss, not the connection manager, which is definitely part of jboss.

                We could have a no -tx wrapper, more or less to go along with the local tx and xa. However I would like to know just how many "jdbc" drivers don't support any transactions at all before spending much time on this.

                • 5. Re: autocommit and NoTxConnectionManager
                  Cl Michel Newbie

                  Sybase jConnect 5.2 and 5.5 for Adaptive Server Enterprise 11+ has the same problem (I posted a msg a few days ago on http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t=forums/

                  On the JonAS forum, I've seen that they "solved" the
                  setAutoCommit() problem by catching the "SET CHAINED not allowed" exception, doing a new "commit" and then reissuing the setAutoCommit().

                  Not really a solution, certainly not portable, but can help on the short term.

                  Anyway, many thanks to the jBoss team for their job...

                  From ConnectionImpl.java:

                  /**
                  * In a JDBC-XA driver, autocommit is false if we are in a global Tx
                  */
                  public void setAutoCommit(boolean autoCommit) throws SQLException {
                  logger.log(BasicLevel.DEBUG, " entering");
                  try {
                  if (autoCommit == false) {
                  if (autocommit_unset == false) { // cache for optimization
                  actConn.setAutoCommit(false);
                  autocommit_set = false;
                  autocommit_unset = true;
                  }
                  } else {
                  if (autocommit_set == false) { // cache for optimization
                  actConn.setAutoCommit(true);
                  autocommit_set = true;
                  autocommit_unset = false;
                  }
                  }
                  } catch (SQLException e) {
                  String s = e.getMessage().toLowerCase();
                  if (s.indexOf("set chained command not allowed") != -1) {
                  logger.log(BasicLevel.DEBUG, "failed...");
                  logger.log(BasicLevel.DEBUG, "Committing then retrying");
                  try {
                  // These lines for Sybase only, hoping they don't broke
                  // anything for others DBs.
                  actConn.commit();
                  actConn.setAutoCommit(autoCommit); // Shouldn't fail now.
                  logger.log(BasicLevel.DEBUG, "succeeded after retry");
                  } catch (SQLException se) {
                  logger.log(BasicLevel.ERROR,"" + autoCommit + ") failed again after retry");
                  logger.log(BasicLevel.ERROR,"Exception: "+se);
                  xac.notifyError(e);
                  throw(e);
                  }
                  } else {
                  logger.log(BasicLevel.ERROR,"setAutoCommit(" + autoCommit + ") failed: "+e);
                  xac.notifyError(e);
                  throw(e);
                  }
                  }
                  }

                  • 6. Re: autocommit and NoTxConnectionManager
                    John Bresnahan Newbie

                    I have a similar problem to this. I am using NoTxConnectionManager in an RA. Within the ra i contact the java:/DefaultDS datastore. On the connection to it i setAutoCommit(true) and i get the following error:

                    15:34:45,213 ERROR [STDERR] java.sql.SQLException: You cannot set autocommit during a managed transaction!