6 Replies Latest reply on Dec 18, 2007 2:08 AM by advaittrivedi

    shouldn't local-tx-datasource have auto-commit = false?

    jhudson

      Hello,

      I am using the configuration below (local-tx-datasource) because I would like to use transactions. Based on the documentation found here:
      http://wiki.jboss.org/wiki/Wiki.jsp?page=ConfigDataSources
      I would assume that the connections would have auto-commit set to false (as an exception will occur if I try to call setAutoCommit on the java.sql.Connection I retrieve from the datasource).

      I receive the following error

      com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Can't call rollback when autocommit=true


      Why is autocommit=true? I don't understand... How can I change that?

      I am using jboss-4.0.5 (default)

      <local-tx-datasource>
      <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
      <jndi-name>jdbc/MyAdBox</jndi-name>
      <connection-url>
      jdbc:mysql://localhost/mydb?autoReconnectForPools=true&dumpQueriesOnException=true&useServerPrepStmts=false
      </connection-url>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <user-name>****</user-name>
      ****
      <idle-timeout-minutes>10</idle-timeout-minutes>
      <min-pool-size>0</min-pool-size>
      <max-pool-size>100</max-pool-size>

      <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name>
      <!-- valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name -->

      <type-mapping>mySQL</type-mapping>

      </local-tx-datasource>



      Thank you very much for any help you might be able to offer.

        • 1. Re: shouldn't local-tx-datasource have auto-commit = false?
          weston.price

          How are you starting your transaction? Are you using EJB, Servlets etc? Also, what version of JBoss are you using?

          • 2. Re: shouldn't local-tx-datasource have auto-commit = false?
            jhudson

            Sorry, I should have provided that information before... thanks very much for the reply.

            I'm using hibernate 3.1.3 and jboss-4.0.5 with mysql 5.0.11 and the latest connector

            I have a HAR which puts the session factory in the JNDI context
            <server>
             <mbean code="org.jboss.hibernate.jmx.Hibernate" name="jboss.har:service=Hibernate">
             <depends>jboss.jca:service=DataSourceBinding,name=jdbc/MyAdBox</depends>
            
             <attribute name="DatasourceName">java:/jdbc/MyAdBox</attribute>
             <attribute name="SessionFactoryName">java:/hibernate/MyAdBoxHibernateFactory2</attribute>
             <attribute name="CacheProviderClass">org.hibernate.cache.OSCacheProvider</attribute>
             <attribute name="Dialect">org.hibernate.dialect.MySQLDialect</attribute>
             <attribute name="QueryCacheEnabled">true</attribute>
             <attribute name="ShowSqlEnabled">false</attribute>
             </mbean>
            </server>


            And, this is how I would handle a transaction...

            Session s = null;
            Transaction t = null;
            try {
             s = getSession();
             t = s.beginTransaction()
            
             // transactional code here
            
             t.commit();
            }
            catch (Throwable throwable) {
             if (null != t) t.rollback();
            }
            finally {
             if (null != s) s.close();
            }


            • 3. Re: shouldn't local-tx-datasource have auto-commit = false?
              weston.price

              Could you post your *-ds.xm file?

              • 4. Re: shouldn't local-tx-datasource have auto-commit = false?
                weston.price

                Whoops, sorry, I just saw it ;-)

                Ok, there are two real choices you have here:

                1) Leverage typical JDBC transactions with Hibernate.
                By default, Hibernate, if not told otherwise, will use JDBC tranasactions. Being that no CMT is being started in this case, the underlying connection will have it's autoCommit value set to true (as per the JCA specification), regardless of the type of datasource being used. Note, Hibernate may turn this off/on but JBossJCA will not.

                2) Use CMT or UserTransactions to begin a transaction and have Hibernate participate in this scheme by correctly configuring Hibernate to use a different TransactionStrategy/Lookup.
                Once you do this, JBossJCA will correctly set the autocommit flag on the underlying connection to participate in the global transaction.

                Again, I need some more information as to what context are you using Hibernate. Are you doing this from a Servlet, from an EJB etc?

                • 5. Re: shouldn't local-tx-datasource have auto-commit = false?
                  weston.price

                  Note, if you don't want to participate in Global transactions, you can just as easily use the no-txn-datasource. Again, Hibernate will turn on/off autocommit depending upon the context.

                  • 6. Re: shouldn't local-tx-datasource have auto-commit = false?
                    advaittrivedi

                    So, jhudson what was the solution you finally applied to resolve this one. Can you post a code snipped?