10 Replies Latest reply on Jul 3, 2002 7:25 PM by natalie wang

    Sybase probs - SET CHAINED error - transaction not clearing

    Chris McCafferty Newbie

      Hi,

      I'm having problems running updates to a Sybase 11.9 database using JConnect version 5. The worrying thing seems to be that a test table is blocked, which could cause deadlocks in the database.

      As a test, I'm trying to execute this code in 3 places: a Session Bean, a MessageDrivenBean (in JBoss 3.0 release) and completely outside JBoss, using a single connection.

      Connection con = PersistenceManager.getDataSource("java:/SybaseDS").getConnection();
      assertNotNull(con);
      PreparedStatement s = con.prepareStatement("update mccaffc_tmp set lastChanged = getDate()");
      int result = s.executeUpdate();
      con.close();

      First - I have the SET CHAINED problem that's been explored (but not completely settled) in other topics in the forums. (the case outside JBoss works fine with a plain vanilla connection.)

      Session bean:

      15:39:43,266 ERROR [LocalTxConnectionManager$LocalConnectionEventListener] ResourceException while closing connection handle!
      javax.resource.ResourceException: Could not cleanup: com.sybase.jdbc2.jdbc.SybSQLException: SET CHAINED command not allowed within multi-statement transaction.
      at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.cleanup(LocalManagedConnection.java:198)
      at org.jboss.resource.connectionmanager.BaseConnectionManager2.unregisterAssociation(BaseConnectionManager2.java:666)
      at org.jboss.resource.connectionmanager.LocalTxConnectionManager$LocalConnectionEventListener.connectionClosed(LocalTxConnectionManager.java:3
      81)
      at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.closeHandle(LocalManagedConnection.java:338)
      at org.jboss.resource.adapter.jdbc.local.LocalConnection.close(LocalConnection.java:97)
      at com.drkw.dusk.persistence.TestPersistenceManager.testQuery(TestPersistenceManager.java:41)
      at java.lang.reflect.Method.invoke(Native Method)
      at junit.framework.TestCase.runTest(TestCase.java:166)
      at junit.framework.TestCase.runBare(TestCase.java:140)
      at junit.framework.TestResult$1.protect(TestResult.java:106)
      at junit.framework.TestResult.runProtected(TestResult.java:124)
      at junit.framework.TestResult.run(TestResult.java:109)
      at junit.framework.TestCase.run(TestCase.java:131)
      at junit.framework.TestSuite.runTest(TestSuite.java:173)
      at junit.framework.TestSuite.run(TestSuite.java:168)
      at com.drkw.dusk.ejb.tester.TestRunner.doRun(TestRunner.java:58)
      at com.drkw.dusk.ejb.tester.TestRunner.run(TestRunner.java:173)
      at com.drkw.dusk.ejb.tester.TesterBean.doRun(TesterBean.java:75)
      at com.drkw.dusk.ejb.tester.TesterBean.runTest(TesterBean.java:50)
      at java.lang.reflect.Method.invoke(Native Method)
      at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:664)
      at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:186)
      at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:96)
      at org.jboss.ejb.plugins.AbstractTxInterceptorBMT.invokeNext(AbstractTxInterceptorBMT.java:144)
      at org.jboss.ejb.plugins.TxInterceptorBMT.invoke(TxInterceptorBMT.java:62)
      at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:77)
      at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:129)
      at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:166)
      at org.jboss.ejb.StatelessSessionContainer.invoke(StatelessSessionContainer.java:313)
      at org.jboss.ejb.Container.invoke(Container.java:705)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:491)
      at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:362)
      at java.lang.reflect.Method.invoke(Native Method)
      at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:241)
      at sun.rmi.transport.Transport$1.run(Transport.java:152)
      at java.security.AccessController.doPrivileged(Native Method)
      at sun.rmi.transport.Transport.serviceCall(Transport.java:148)
      at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:465)
      at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:706)
      at java.lang.Thread.run(Thread.java:484)

      MessageDriven Bean:

      15:41:49,626 WARN [LocalTxConnectionManager$LocalConnectionEventListener] prepare called on a local tx. You are not getting the semantics you expect!
      15:41:49,626 INFO [JBossManagedConnectionPool] ResourceException returning ManagedConnection to pool:
      javax.resource.ResourceException: Could not cleanup: com.sybase.jdbc2.jdbc.SybSQLException: SET CHAINED command not allowed within multi-statement transaction.
      at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.cleanup(LocalManagedConnection.java:198)
      at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.returnConnection(InternalManagedConnectionPool.java:137)
      at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.returnConnection(JBossManagedConnectionPool.java:324)
      at org.jboss.resource.connectionmanager.BaseConnectionManager2.returnManagedConnection(BaseConnectionManager2.java:494)
      at org.jboss.resource.connectionmanager.LocalTxConnectionManager$LocalConnectionEventListener.commit(LocalTxConnectionManager.java:567)
      at org.jboss.tm.TxCapsule.commitResources(TxCapsule.java:1656)
      at org.jboss.tm.TxCapsule.commit(TxCapsule.java:377)
      at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:74)
      at org.jboss.jms.asf.StdServerSession.onMessage(StdServerSession.java:315)
      at org.jboss.mq.SpyMessageConsumer.sessionConsumerProcessMessage(SpyMessageConsumer.java:561)
      at org.jboss.mq.SpyMessageConsumer.addMessage(SpyMessageConsumer.java:377)
      at org.jboss.mq.SpySession.run(SpySession.java:252)
      at org.jboss.jms.asf.StdServerSession.run(StdServerSession.java:173)
      at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:642)
      at java.lang.Thread.run(Thread.java:484)



      The message-driven bean is using container-managed transactions:
      <ejb-jar>
      <enterprise-beans>
      <message-driven>
      <ejb-name>DerivedTablesBuilderMDB</ejb-name>
      <ejb-class>com......DerivedTablesBuilderMDB</ejb-class>
      <message-selector></message-selector>
      <transaction-type>Container</transaction-type>
      <message-driven-destination>
      <destination-type>javax.jms.Topic</destination-type>
      <subscription-durability>NonDurable</subscription-durability>
      </message-driven-destination>
      </message-driven>
      </enterprise-beans>
      <assembly-descriptor>
      <container-transaction>

      <ejb-name>DerivedTablesBuilderMDB</ejb-name>
      <method-name>*</method-name>

      <trans-attribute>Required</trans-attribute>
      </container-transaction>
      </assembly-descriptor>
      </ejb-jar>

      The session bean is using:
      <ejb-jar>
      Test entry point for in-container testing
      <display-name>Tester Session Bean</display-name>
      <enterprise-beans>

      <ejb-name>Tester</ejb-name>
      com...ejb.tester.TesterHome
      com...ejb.tester.Tester
      <ejb-class>com...ejb.tester.TesterBean</ejb-class>
      <session-type>Stateless</session-type>
      <transaction-type>Bean</transaction-type> <!-- I tried Container too, doesn't make a difference -->
      <resource-ref>
      <res-ref-name>jdbc/Database</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
      </resource-ref>

      </enterprise-beans>
      </ejb-jar>


      Enclosed is my sybase-service.xml. It's based on the examples in CVS.

      I've banged my head against this for a while now. I just can't get this to go away. WORSE - after I've run these tests, a connection is left blocking my little test table! Killing JBoss frees the block.

      I'm puzzled why my approach (which worked fine in JBoss 2.4.4 and 3.0RC1) is no longer working. I have tried picking over the sybase-service.xml; I have tried the same tests in hsqldb that ships with JBoss 3.0 - works fine. I tried not closing the connections afterwards, commit()ing the transaction myself but they just throw up more errors. I've also looked at how Connection.setAutoCommit() is used in LocalManagedConnection.

      Can anyone tell why this doesn't work any more?

      Cheers,

      Chris

        • 1. Re: Sybase probs - SET CHAINED error - transaction not clear
          Chris McCafferty Newbie

          Here we go again with trying to attach the service file...

          • 2. Re: Sybase probs - SET CHAINED error - transaction not clear
            Edson Carlos Ericksson Richter Newbie

            I'm having exactly same error. I'm using JBoss 2.4.4 without problems.
            With 3.0 final error occurs in every close() method called...

            I'm using same service configuration as yours. I'm using JConnect 5.5. This error occur only with JBoss 3.0. Not occur with JBoss 2.x, nor Catalina.

            Stack trace follows:

            16:00:57,529 INFO [JBossManagedConnectionPool] ResourceException returning ManagedConnection to pool:
            javax.resource.ResourceException: Could not cleanup: com.sybase.jdbc2.jdbc.SybSQLException: SET CHAINED command not allowed within multi-statement transaction.

            at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.cleanup(LocalManagedConnection.java:198)
            at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.returnConnection(InternalManagedConnectionPool.java:137)
            at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.returnConnection(JBossManagedConnectionPool.java:324)
            at org.jboss.resource.connectionmanager.BaseConnectionManager2.returnManagedConnection(BaseConnectionManager2.java:494)
            at org.jboss.resource.connectionmanager.LocalTxConnectionManager$LocalConnectionEventListener.commit(LocalTxConnectionManager.java:567)
            at org.jboss.tm.TxCapsule.commitResources(TxCapsule.java:1656)
            at org.jboss.tm.TxCapsule.commit(TxCapsule.java:357)
            at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:74)
            at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:190)
            at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:61)
            at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:129)
            at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:166)
            at org.jboss.ejb.StatelessSessionContainer.invoke(StatelessSessionContainer.java:313)
            at org.jboss.ejb.Container.invoke(Container.java:705)
            at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:491)
            at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:362)
            at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
            at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
            at java.lang.reflect.Method.invoke(Method.java:324)
            at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
            at sun.rmi.transport.Transport$1.run(Transport.java:148)
            at java.security.AccessController.doPrivileged(Native Method)
            at sun.rmi.transport.Transport.serviceCall(Transport.java:144)
            at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)
            at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:701)
            at java.lang.Thread.run(Thread.java:536)

            • 3. Re: Sybase probs - SET CHAINED error - transaction not clear
              Lamar Channell Newbie

              Hi,

              I'm having the exact same error using JBoss3.0 and Sybase 11.9.2 with JConnect 5.2. I'm in the process of converting from Borland's Application Server 5.0 to JBoss. This is definitely a show stopper.

              Any suggested workarounds would be deeply appreciated.

              Thanks,
              Lamar

              • 4. Re: Sybase probs - SET CHAINED error - transaction not clear
                Lamar Channell Newbie

                Hi,

                I was able to fix the problem by commenting out the section of code in the suspect method LocalManagedConnection.cleanup, recreating the local-ra-jdbc-libs.jar with the modified class file, and modifying jboss-local-jdbc.rar which contains the above jar file. Everything works so far. I've attached the modified rar file.

                Lamar

                • 5. Re: Sybase probs - SET CHAINED error - transaction not clear
                  Chris McCafferty Newbie

                  Thanks for that - just popped in that rar and it works fine.

                  I hope we can get a fix put into the next release of JBoss 3.0 - our organisation is a BIG user of Sybase and so are the previous couple of companies I've worked for (financial organisations). We're currently looking at JBoss as the future app server in production, something that could be in doubt if it didn't operate with the Jconnect drivers.

                  • 6. Re: Sybase probs - SET CHAINED error - transaction not clear
                    Chris McCafferty Newbie

                    Thanks for that - just popped in that rar and the exception and SET CHAINED message go away.

                    I hope we can get a fix put into the next release of JBoss 3.0 - our organisation is a BIG user of Sybase and so are the previous couple of companies I've worked for (financial organisations). We're currently looking at JBoss as the future app server in production, something that could be in doubt if it didn't operate with the Jconnect drivers.

                    By the way, I still get the 'You are not getting the semantics you expect' message, but I can tackle that separately.

                    • 7. Re: Sybase probs - SET CHAINED error - transaction not clear
                      Chris McCafferty Newbie

                      Ah, I see the forums take a minute or two to pick up a new post.

                      I've also done a little research so any of the jboss coders who don't use Sybase (which will be most, since it's an expensive commercial app) can get a handle on what the underlying problem might be.

                      BACKGROUND

                      http://manuals.sybase.com/onlinebooks/group-iq/iqg1243e/iqapg/@Generic__BookTextView/49578
                      which says:
                      Notes on JDBC connections

                      Autocommit behavior

                      The JDBC specification requires that, by default, a COMMIT is performed after each data modification statement. Currently, the server-side JDBC behavior is to commit. You can control this behavior using a statement such as the following:

                      conn.setAutoCommit( false ) ;

                      where conn is the current connection object.

                      Connection defaults

                      From server-side JDBC, only the first call to getConnection( "jdbc:default:connection" ) creates a new connection with the default values. Subsequent calls return a wrapper of the current connection with all connection properties unchanged. If you set AutoCommit to OFF in your initial connection, any subsequent getConnection calls within the same Java code return a connection with AutoCommit set to OFF.

                      You may wish to ensure that closing a connection resets connection properties to their default values, so subsequent connections are obtained with standard JDBC values. The following type of code achieves this:

                      Connection conn = DriverManager.getConnection("");
                      boolean oldAutoCommit = conn.getAutoCommit();
                      try {
                      // do code here
                      }
                      finally {
                      conn.setAutoCommit( oldAutoCommit );
                      }

                      This discussion applies not only to AutoCommit, but also to other connection properties such as TransactionIsolation and is ReadOnly.


                      Also see this article:
                      http://info.sybase.com/resolution/detail.stm?id_number=10812820
                      A further description of how stored procedures can fix this problem by having this set:
                      sp_procxmode 'sp_Your_stored_procedure_here','anymode'

                      This is further explored in this thread:
                      http://webforums.sybase.com/nntp/nd000042.nsf/85255e6f0052055e85255d7f005ed8bc/ec897c270533a1d86adbecae706f9534?OpenDocument

                      • 8. Re: Sybase probs - SET CHAINED error - transaction not clear
                        Corby Page Newbie

                        David Jencks patched LocalManagedConnection.java a couple of weeks ago, and this fixed the problem.

                        You can either fetch LocalManagedConnection from sourceforge and rebuild your server, or wait for the 3.01 bugfix release next week.

                        Corby

                        • 9. Re: Sybase probs - SET CHAINED error - transaction not clear
                          natalie wang Newbie

                          I am still getting the error after I downloaded the jboss-local-jdbc.rar and put it in
                          jboss-3.0.0_tomcat-4.0.3/server/default/deploy directory. The size I downloaded is 23451, not sure whether it is correct.

                          2002-07-03 15:23:39,853 ERROR [STDERR]java.rmi.ServerException: Transaction failed due to SQLException: SET CHAINED command not allowed within multi-statement transaction.
                          ; nested exception is:
                          javax.ejb.EJBException: Transaction failed due to SQLException: SET CHAINED command not allowed within multi-statement transaction.
                          2002-07-03 15:23:39,855 ERROR [STDERR] javax.ejb.EJBException: Transaction failed due to
                          SQLException: SET CHAINED command not allowed within multi-statement transaction.
                          2002-07-03 15:23:39,856 ERROR [STDERR] at edu.stanford.regadmin.ejb.ExceptionEventBean.findByReferenceId

                          • 10. Re: Sybase probs - SET CHAINED error - transaction not clear
                            natalie wang Newbie

                            I still get the error after I downloaded the jboss-local-jdbc.rar and put it in jboss-3.0.0_tomcat-4.0.3/server/default/deploy. The size of jboss-local-jdbc.rar I downloaded is 23451.

                            2002-07-03 15:23:39,853 ERROR [STDERR] java.rmi.ServerException: Transaction failed due to SQLException: SET CHAINED command not allowed within multi-statement transaction.
                            ; nested exception is:
                            javax.ejb.EJBException: Transaction failed due to SQLException: SET CHAINED command not allowed within multi-statement transaction.
                            2002-07-03 15:23:39,855 ERROR [STDERR] javax.ejb.EJBException: Transaction failed due to
                            SQLException: SET CHAINED command not allowed within multi-statement transaction.
                            2002-07-03 15:23:39,856 ERROR [STDERR] at edu.stanford.regadmin.ejb.ExceptionEventBean.findByReferenceId