13 Replies Latest reply on Apr 4, 2017 12:09 PM by andey

    autocommit question

    wang.565

      I've created a web application using Sun Creator 2.1 and try to deploy it in JBoss 4.0.4. However, I saw the following exception:

      14:33:26,329 ERROR [STDERR] java.sql.SQLException: You cannot commit with autocommit set!
      14:33:26,329 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.jdbcCommit(BaseWra pperManagedConnection.java:545)
      14:33:26,345 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.WrappedConnection.commit(WrappedConnection.java :334)
      14:33:26,345 ERROR [STDERR] at com.sun.sql.rowset.internal.CachedRowSetXReader.readData(CachedRowSetXReader.ja va:244)
      14:33:26,345 ERROR [STDERR] at com.sun.sql.rowset.CachedRowSetXImpl.execute(CachedRowSetXImpl.java:950)
      14:33:26,345 ERROR [STDERR] at com.sun.sql.rowset.CachedRowSetXImpl.execute(CachedRowSetXImpl.java:1410)
      ...

      As you can see, I used cachedrowset in my application. my data-source configuration in jboss is something like;

      <local-tx-datasource> <connection-url>jdbc:microsoft:sqlserver://myserver:1433;DatabaseName=myt est;SelectMethod=Cursor</connection-url> <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-cla ss>
      <connection-property name="auto-commit">false</connection-property>
      <min-pool-size>5</min-pool-size>
      <max-pool-size>20</max-pool-size>
      <idle-timeout-minutes>5</idle-timeout-minutes>
      </local-tx-datasource>

      Is there a simple work-around on this problem?

      Thanks.

        • 1. Re: autocommit question
          weston.price

          This is correct.

          The <local-tx-datasource> will automatically set the auto commit value of the connection to false depending upon the context of the global transaction. Typically, there is no reason for you to use this datasource *and* set the auto commit value yourself. If you want to use this datasource outside of the context of a global transaction you can simply aquire the connection and set the value yourself. Note, this is not really the way this data source is intended for use. As a result, you have a few options:


          1) Use the <no-tx-datasource>. Use of this datasource will put the responsiblity of the transaction on you. After acquiring the DataSource you will want to do the setAutoCommit(false), do your JDBC work, and then commit. This type of datasource funtions much like a non-managed datasource running outside of the application server though you still get pooling, can leverage security etc. With you particular config, you can do this simply by renaming the

           <local-tx-datasource>
          
           to
          
           <no-tx-datasource>
          
          
          


          and remove the auto commit property.

          2) Start a global transaction in your WebApplication via the UserTransaction object bound into JNDI to scope the work you want your JDBC connection to be enlisted in. See the JTA spec for examples.

          3) Use EJB(X) and leverage CMT and declarative transaction management to do all of this for you.

          More on datasource configuration can be found here:

          http://wiki.jboss.org/wiki/Wiki.jsp?page=ConfigDataSources




          • 2. Re: autocommit question
            weston.price

            Note, the reason your setting is not working is because of the name. The

            
            <connection-property name="auto-commit">false</connection-property>
            
            


            should be renamed to

            
            <connection-property name="autoCommit">false</connection-property>
            
            


            But again, if you read my previous post, there are better, cleaner alternatives that the approach above. One of the drawbacks of doing this is the *-ds.xml file is that *all* connections created will have this property set. If you want to use the connection outside the context of a global transaction setting this property should really be done programmatically by you in your code.





            • 3. Re: autocommit question
              wang.565

              Thanks for the reply. I did change auto-commit to autoCommit, it gives the same exception.
              I will try some alternatives listed. It seems there is no easy workaround on this problem. The reason is that a lot of webapplication code is generated by Sun Creator (for example, all those cachedrowset stuff). It works fine in Sun Application Server. So if there is some kind of one or a few lines change in configuration to make it compatiable with JBoss, that would be perfect.
              Thanks.

              • 4. Re: autocommit question
                weston.price

                The simple answer is that the code is attempting to do a commit on the underlying datasource in the wrong context. Again, the easiest thing for you to do is to use the <no-tx-datasource> and control the autocommit value yourself programmatically at runtime.

                Chances are Sun Application Server via a service specific deployment descriptor or the runtime environment is managing this setting somehow for your deployment. This effecitvely (again, my best guess) amounts to a proprietary feature that does not integrate with JBoss.



                • 5. Re: autocommit question

                  Actually, the real issue is that JBoss's jdbc resource adapter
                  keeps track of the auto-commit state itself so it can optimize its usage:
                  http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossJCALazyAutoCommit

                  Part of that, is the assumption that all connections are in getAutoCommit()==true
                  state when they are created (as per spec).

                  Support for this anti-spec behaviour would require the JBoss JDBC
                  resource adapter to do a getAutoCommit() on every new connection
                  rather than assuming a value of true.

                  1 of 1 people found this helpful
                  • 6. Re: autocommit question
                    weston.price

                     


                    Support for this anti-spec behaviour would require the JBoss JDBC
                    resource adapter to do a getAutoCommit() on every new connection
                    rather than assuming a value of true.


                    Yes. The code appears to assume the autocommit setting to be false on connection creation, clearly a violation.

                    • 7. Re: autocommit question
                      amit4uall

                      I am still getting the exception even after changing
                      <local-tx-datasource>
                      to
                      <no-tx-datasource>


                      My oracle-ds.xml looks like:

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



                      <no-tx-datasource>
                      <jndi-name>jdbc/syfact</jndi-name>
                      <connection-url>jdbc:oracle:thin:@172.16.90.112:1521:syfactge</connection-url>

                      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
                      <user-name>abc</user-name>
                      xyz
                      <min-pool-size>5</min-pool-size>
                      <max-pool-size>100</max-pool-size>

                      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>





                      <type-mapping>Oracle9i</type-mapping>

                      </no-tx-datasource>



                      and the exception is:
                      java.sql.SQLException: You cannot commit with autocommit set!
                      at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.jdbcComm
                      it(BaseWrapperManagedConnection.java:545)
                      at org.jboss.resource.adapter.jdbc.WrappedConnection.commit(WrappedConne
                      ction.java:334)
                      at com.syfact.core.util.DBConduit.endTransaction(DBConduit.java:1500)...



                      Thanks and Regards,
                      Amit

                      • 8. Re: autocommit question
                        vickyk

                         

                        "amit4uall" wrote:

                        and the exception is:
                        java.sql.SQLException: You cannot commit with autocommit set!
                        at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.jdbcComm
                        it(BaseWrapperManagedConnection.java:545)
                        at org.jboss.resource.adapter.jdbc.WrappedConnection.commit(WrappedConne
                        ction.java:334)
                        at com.syfact.core.util.DBConduit.endTransaction(DBConduit.java:1500)...



                        Thanks and Regards,
                        Amit

                        Your problem is not related to this thread you would be calling the commit() on the connection whose autocommit property is set to true.

                        You are seeing this

                        SQLException - if a database access error occurs or this Connection object is in auto-commit mode

                        http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#commit()


                        • 9. Re: autocommit question
                          amit4uall

                           

                          "vickyk" wrote:

                          Your problem is not related to this thread you would be calling the commit() on the connection whose autocommit property is set to true.


                          Maybe that's the case, but the same piece of code is working fine on tomcat and websphere and the problem occurs when i deploy it on jboss after configuring a JDBC datasource.

                          • 10. Re: autocommit question
                            amit4uall

                            Thanks for your help. You diagnosed the problem correctly.
                            I was calling commit() on the connection whose autocommit property was set to true.


                            But I still have doubts regarding different application servers behaving differently for that same code(Is it so that only jboss checks the autocommit property?).

                            • 11. Re: autocommit question
                              vickyk

                               

                              "amit4uall" wrote:

                              But I still have doubts regarding different application servers behaving differently for
                              that same code(Is it so that only jboss checks the autocommit property?).

                              I am not aware of how other AS take care of all this , we are adhering to the specs .
                              You got to test the behavior with other AS , I can't explian you the behavior of other AS's as we don't write them :)

                              • 12. Re: autocommit question
                                iamdvr

                                I have tried to run autoCommit(true) for all actions to DB. But due to JCA (ironjacamar-jdbc-1.3.4.Final ) handling of connection it made it impossible.

                                 

                                There is a jca  (ironjacamar-jdbc-1.3.4.Final )  connector which tries to manage the connection locally, by wrapping it inside ./org/jboss/jca/adapters/jdbc/jdk7/WrappedConnectionJDK7.java.

                                 

                                 

                                There is two local variables in ./org/jboss/jca/adapters/jdbc/BaseWrapperManagedConnection.java

                                 

                                 

                                   /** JDBC auto-commit */

                                   protected boolean jdbcAutoCommit = true;

                                 

                                 

                                   /** Underlying auto-commit */

                                   protected boolean underlyingAutoCommit = true;

                                 

                                 

                                 

                                 

                                #1) is supposed to hold what is the current setting of jdbc connection auto commit.

                                #2) is supposed to initialize with underlying physical connection auto commit value.

                                 

                                 

                                But #2 is not doing that thing and just hard initiated with value true.

                                 

                                 

                                If the underlying database give connection with autocommit false then it would not be able to run first statement in autocommit true even if we set #1 as true. Because Both #1 and #2 have same value and the method checkTransaction does logic check as below. whcih cann be passed and won't do underlying con object setAutoCommit method.

                                 

                                 

                                 

                                 

                                 

                                 

                                   void checkTransaction() throws SQLException

                                   {

                                         // Check autocommit

                                         if (jdbcAutoCommit != underlyingAutoCommit)

                                         {

                                            con.setAutoCommit(jdbcAutoCommit);

                                            underlyingAutoCommit = jdbcAutoCommit;

                                         }

                                   }

                                 

                                 

                                 

                                 

                                The solution would be

                                 

                                 

                                #1) Run jca connection setAutoCommit(false) and execute dummy SELECT statement .. and then run jca connection setAutoCommit(true) and proceed with application workflow

                                 

                                 

                                The problem with this approach is unnecessary SELECT call

                                 

                                 

                                #2) Fxi the JCA connector to initialize underlyingAutoCommit property with value of actual database connection getautoCommit value

                                 

                                 

                                There is a flag jta="flase" for data source which should try to make conenction unmanaged but I have not tried that.

                                • 13. Re: autocommit question
                                  andey

                                  JBoss datasources follow the JCA specs with regards to setting autocommit on connections. I have attach a couple of articles below which describe the behavior[1], [2]. It also includes a snippet from the JCA specification describing the behaviour of auto commit for JCA connections.

                                   

                                  So if the connection is retrieved outside of a transaction, then JBoss will set the autocommit to on, as per the spec.

                                   

                                  If you wish to set it to false, you would need to do it programmatically on the connection itself. Another option is to get the connection directly, however then you loose the benefits of pooling.

                                   

                                   

                                  Since you are seeing "You cannot commit with autocommit set!', as you suspected the autocommit will be set to on. As per the JCA spec, JBoss will be setting the autocommit to on when the connection is used outside of a transaction, so you must not be performing this operation within an application server transaction.

                                   

                                   

                                  [1] https://access.redhat.com/solutions/193963

                                  [2] https://access.redhat.com/solutions/63124

                                   

                                   

                                  The JCA Spec 1.5, section 15.5.3.1 states: It requires that auto-commit is set to true by driver:

                                  ---------------------------------------------------------------

                                  A resource adapter must manage the auto-commit mode as follows:

                                  A transactional resource adapter must set the auto-commit mode of Connection instances to on when used outside a transaction.

                                  Therefore the non-spec compliant connection property "autoCommit=false" won't work, JBoss is required by the spec to set the autocommit to true.

                                  ------------------------------------------------------------------

                                   

                                  To get around this, you can programatically set auto-commit to false on the connection object by calling "setAutoCommit(false)"

                                   

                                  Please see here[3] for more information:

                                   

                                  [3]https://access.redhat.com/solutions/185293