11 Replies Latest reply on Mar 10, 2008 7:57 AM by Vicky Kak

    autocommit question

    Andrew Wang Newbie

      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 M. Price Master

          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 M. Price Master

            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
              Andrew Wang Newbie

              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 M. Price Master

                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
                  Adrian Brock Master

                  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.

                  • 6. Re: autocommit question
                    Weston M. Price Master

                     


                    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
                      Amit Saxena Newbie

                      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
                        Vicky Kak Master

                         

                        "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
                          Amit Saxena Newbie

                           

                          "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
                            Amit Saxena Newbie

                            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
                              Vicky Kak Master

                               

                              "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 :)