7 Replies Latest reply on May 23, 2002 10:22 PM by David Jencks

    AutoCommit

    Kim Madsen Newbie

      I am using JBoss 3.0 RC2 with an Oracle database.
      I have a problem with the pooling of connections for local transactions.
      Basically, if I get a connection from the pool do a connection.setAutoCommit(false) on it, this does not get reset when returned to the pool. I assumed that the connection pool was responsible for restoring the original state of the connections, so it becomes transparent for the user, that the connections are recycled. Is that an incorrect assumption, or could there be a problem with org.jboss.resource.connectionmanager.LocalTxConnectionManager?

      I have included the configuration of the connection manager below.
      Any advice would be appreciated.

      Cheers, Kim

      <?xml version="1.0" encoding="UTF-8"?>




      OracleDbRealm

      <depends optional-attribute-name="ManagedConnectionFactoryName">


      ExcediaDB



      <!-- <config-property name="ConnectionURL" type="java.lang.String">jdbc:oracle:thin:@127.0.0.1:1521:kajej</config-property> -->
      <config-property name="ConnectionURL" type="java.lang.String">jdbc:oracle:oci8:@(description=(address=(host=127.0.0.1)(protocol=tcp)(port=1521))(connect_data=(sid=wiggum)))</config-property>
      <config-property name="DriverClass" type="java.lang.String">oracle.jdbc.driver.OracleDriver</config-property>



      <depends optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,name=JBoss LocalTransaction JDBC Wrapper



      <depends optional-attribute-name="ManagedConnectionPool">


      10
      60
      5000
      15
      ByContainer



      <depends optional-attribute-name="CachedConnectionManager">jboss.jca:service=CachedConnectionManager

      <depends optional-attribute-name="JaasSecurityManagerService">jboss.security:name=JaasSecurityManager

      java:/TransactionManager

      jboss.jca:service=RARDeployer







      OracleDbNTRealm

      <depends optional-attribute-name="ManagedConnectionFactoryName">


      ExcediaDBNT



      <!-- <config-property name="ConnectionURL" type="java.lang.String">jdbc:oracle:thin:@127.0.0.1:1521:wiggum</config-property> -->
      <config-property name="ConnectionURL" type="java.lang.String">jdbc:oracle:oci8:@(description=(address=(host=127.0.0.1)(protocol=tcp)(port=1521))(connect_data=(sid=wiggum)))</config-property>
      <config-property name="DriverClass" type="java.lang.String">oracle.jdbc.driver.OracleDriver</config-property>



      <depends optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,name=JBoss LocalTransaction JDBC Wrapper



      <depends optional-attribute-name="ManagedConnectionPool">


      10
      60
      5000
      15
      ByContainer



      <depends optional-attribute-name="CachedConnectionManager">jboss.jca:service=CachedConnectionManager

      <depends optional-attribute-name="JaasSecurityManagerService">jboss.security:name=JaasSecurityManager

      java:/TransactionManager

      jboss.jca:service=RARDeployer






        • 1. Re: AutoCommit
          David Jencks Master

          There's probably a problem with the wrapper setting autocommit.

          How do you know the autocommit isn't being reset? A testcase would be extremely helpful.

          • 2. Re: AutoCommit
            Erwin Guib Newbie

            I'm just curious ...

            why are you calling setAutoCommit(..) on a connection that you get from the pool.
            When used from EJBeans the commit rules should basically be defined by the deployment descriptor!?
            - bean vs. container managed transaction
            - transaction attribute of bean methods (Requires, Supports ...)
            - context.getUserTransaction().begin() and commit/rollback for BMT

            Erwin

            • 3. Re: AutoCommit
              Jeff Brown Newbie

              Erwin,

              Could you give me some more info on this or point me to a source where I can learn more as I am having a similar problem.

              Thanks,
              Jeff

              • 4. Re: AutoCommit
                Erwin Guib Newbie

                Jeff,

                I'm not really sure what's the "point" your asking about!?

                If your asking about the usage of DataSource/Connection and Transaction from EJBeans I suggest you to take a look at the EJB 2.0 Spec (ch. 17 Support for Transaction).
                I'm sure there are also a whole buch of good books/tutorials out there on EJB where you could take a look (but I don't have an hint now, I'm working heavily with the spec).

                Hope this helps, Erwin

                • 5. Re: AutoCommit
                  Jeff Brown Newbie

                  Erwin, that was the direction I needed. Implemented based on the guidelines in the spec and it works great. Thanks for pointing me in the right direction.

                  Jeff

                  • 6. Re: AutoCommit
                    Kim Madsen Newbie

                    David Jencks, you said a unit test would be helpfull.
                    I have already got a cactus unit test for this in my code. It depends on some of the my own library code, but the principal should clear. I have pasted the testcase below.

                    In reply to Eguib who asked why I was changing the autocommit mode. I am doing this for code that runs in the appserver but outside of EJBs and managed transactions.

                    Hope this help.

                    Thanks for your help.

                    /** Test of connections autocommit mode.
                    * 1) Take all connection from pool and check that they all
                    * have the same autocommit mode.
                    * 2) Change the autocommit and give back connection to the pool.
                    * 3) Take all the connection from the pool again and check that
                    * the autocommit mode has been reset. I.e that it was not
                    * not influenced by the change in step 2.
                    * Currently it does not seem to get reset.
                    * 4) Give all connections back to the pool again.
                    */
                    public void testGetAllDBConnection() throws Exception {
                    Collection connections = new ArrayList();
                    boolean initial_mode = false;
                    try {
                    while (true) {
                    Connection connection = LocatorDbFact.instance().getDbConnection();
                    assertNotNull(connection);
                    if (connections.size() == 0) {
                    initial_mode = connection.getAutoCommit();
                    } else {
                    assertEquals("Expected commited mode to be the same"
                    + " for all connections. Error in connection "
                    + connections.size()+1,
                    initial_mode, connection.getAutoCommit());
                    }
                    connections.add(connection);
                    }
                    } catch (ExceptionLocator e) {
                    diag.debug("Exception thrown when no more commections");
                    }
                    diag.debug("Got " + connections.size() + " connections.");

                    diag.debug("Change autocommit mode and close");
                    Iterator i = connections.iterator();
                    while (i.hasNext()) {
                    Connection connection = (Connection) i.next();
                    connection.setAutoCommit(!initial_mode);
                    connection.close();
                    }
                    connections = new ArrayList();

                    diag.debug("Expect to get back connection with unchanged commit mode");
                    try {
                    while (true) {
                    Connection connection = LocatorDbFact.instance().getDbConnection();
                    assertNotNull(connection);

                    // Assertion is negated because of problem with connection pool.
                    // E.g. !initial_mode should be initial_mode.
                    // But I have changed it for the test to run succesfully.
                    // The error is that the autocommit mode is not reset,
                    // when the connection is returned to the pool.
                    // Therefore the next time a connection is used,
                    // the connection might be in the wrong mode.
                    assertEquals("Expected commited mode to be the same"
                    + " for all connections. Error in connection "
                    + connections.size()+1,
                    !initial_mode, connection.getAutoCommit());

                    connections.add(connection);
                    }
                    } catch (ExceptionLocator e) {
                    diag.debug("Exception thrown when no more commections");
                    }
                    diag.debug("Got " + connections.size() + " connections.");

                    diag.debug("Change autocommit mode and close");
                    i = connections.iterator();
                    while (i.hasNext()) {
                    Connection connection = (Connection) i.next();
                    connection.close();
                    }
                    }

                    • 7. Re: AutoCommit
                      David Jencks Master

                      Thanks!! This should be fixed now, could you check please that your test passes as well as mine?

                      (org.jboss.test.jca.test.LocalWrapperCleanupUnitTestCase)