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

Sybase probs - SET CHAINED error - transaction not clearing

mccaffc Jun 26, 2002 12:24 PM

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> 
<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

mccaffc Jun 26, 2002 12:25 PM (in response to mccaffc)

Here we go again with trying to attach the service file...
Actions
2. Re: Sybase probs - SET CHAINED error - transaction not clear

viking Jun 26, 2002 4:00 PM (in response to mccaffc)

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)
Actions
3. Re: Sybase probs - SET CHAINED error - transaction not clear

channell Jun 26, 2002 5:42 PM (in response to mccaffc)

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
Actions
4. Re: Sybase probs - SET CHAINED error - transaction not clear

channell Jun 27, 2002 6:46 PM (in response to mccaffc)

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
Actions
5. Re: Sybase probs - SET CHAINED error - transaction not clear

mccaffc Jun 28, 2002 10:47 AM (in response to mccaffc)

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.
Actions
6. Re: Sybase probs - SET CHAINED error - transaction not clear

mccaffc Jun 28, 2002 10:51 AM (in response to mccaffc)

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.
Actions
7. Re: Sybase probs - SET CHAINED error - transaction not clear

mccaffc Jun 28, 2002 11:09 AM (in response to mccaffc)

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
Actions
8. Re: Sybase probs - SET CHAINED error - transaction not clear

cepage Jun 28, 2002 3:49 PM (in response to mccaffc)

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
Actions
9. Re: Sybase probs - SET CHAINED error - transaction not clear

myebook Jul 3, 2002 7:19 PM (in response to mccaffc)

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
Actions
10. Re: Sybase probs - SET CHAINED error - transaction not clear

myebook Jul 3, 2002 7:25 PM (in response to mccaffc)

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
Actions

Go to original post