Sybase probs - SET CHAINED error - transaction not clearing
mccaffc Jun 26, 2002 12:24 PMHi,
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