Stanley, Thank you very much for such a detailed reply.
Your guess about separate, multiple statement transactions is most probably true. However, the application I'm running is SPECjAppServer2004, that is the industry standard J2EE benchmark that works fine on many production servers, so I suspect it the last. My first suspect is my configuration :), and the second is G or its components. Concerning the data source, it's very interesting. In tranql-connector-derby-embed-xa-1.1.rar connector I'm using, ra.xml file contains the following: <connectionfactory-interface>javax.sql.DataSource</connectionfactory-int erface> <connectionfactory-impl-class>org.tranql.connector.jdbc.DataSource</conn ectionfactory-impl-class> <connection-interface>java.sql.Connection</connection-interface> <connection-impl-class>org.tranql.connector.jdbc.ConnectionHandle</conne ction-impl-class> I've also checked the whole Geronimo, and found no references to either javax.sql.XADataSource or org.apache.derby.jdbc.EmbeddedXADataSource (except Derby itself). So I'm not sure there's a place in configs where I can put org.apache.derby.jdbc.EmbeddedXADataSource to. :( Vasily -----Original Message----- From: Stanley Bradbury [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 31, 2006 9:57 PM To: [email protected] Subject: Re: SQL transaction deadlock Hi - It seems like this may be caused by multiple XA transactions interfering with each other. The two select statements alone would not require exclusive locks even at the most restrictive isolation level ( TRANSACTION_ SERIALIZABLE ). They should never deadlock within a single statement transaction (e.g. autocommit) which, of course, you say you are not using because of XA . I'm going to guess that both threads are separate, multiple statement transactions that are holding exclusive locks because of a previous data modification statement (? CMPCreateMethod ?). They may need to be serialized in some fashion? I don't know much about Containers and XA but think you should be setting them up to use the Derby XA datasource: # org.apache.derby.jdbc.EmbeddedXADataSource Derby's implementation of a javax.sql.XADataSource. HTH Zakharov, Vasily M wrote: > Santosh, > > Unfortunately, that didn't help. :( > Also, commitbeforeautocommit only works for local transactions, and I > need XA. > > Thank you anyway. :) > > Vasily > > > -----Original Message----- > From: Santosh Koti [mailto:[EMAIL PROTECTED] > Sent: Friday, May 26, 2006 7:35 AM > To: [email protected] > Subject: RE: SQL transaction deadlock > > Vasily, > > I also had faced a similar kind of problem (if not exactly) , try these > options : > > 1) Increase ur connection pools > 2) Increase the timeout also > 3) In ur connection pool deployment set this: > commitbeforeautocommit=true & then redeploy ur db > plan & test. > > PS: Not sure, may work , just give a try. > > Thanks, > Santosh. > "Don't talk about yourself; it will be done when you leave. " > > > -----Original Message----- > From: Zakharov, Vasily M [mailto:[EMAIL PROTECTED] > > Sent: Friday, May 26, 2006 4:32 AM > To: [email protected] > Subject: SQL transaction deadlock > > Hi, all, > > Here's another strange transaction rollback I'm having while working > with Derby through Geronimo. > > The question is, if this is an application problem that makes > inconsistent requests, or it could be a hole in Geronimo/TranQL/Derby > connector/database? > > javax.ejb.TransactionRolledbackLocalException > at > org.openejb.transaction.ContainerPolicy$TxRequired.invoke(ContainerPolic > y.java:123) > SNIP > org.openejb.transaction.ContainerPolicy$TxRequired.invoke(ContainerPolic > y.java:119) > at > org.openejb.transaction.TransactionContextInterceptor.invoke(Transaction > ContextInterceptor.java:80) > at > org.openejb.slsb.StatelessInstanceInterceptor.invoke(StatelessInstanceIn > terceptor.java:98) > at > org.openejb.transaction.ContainerPolicy$TxRequired.invoke(ContainerPolic > y.java:140) SNIP > Caused by: javax.ejb.EJBException: Unable to load data for field > at > org.tranql.ejb.CMPFieldFaultTransform.get(CMPFieldFaultTransform.java:66 > ) > at org.tranql.ejb.OneToManyCMR.set(OneToManyCMR.java:77) > at > org.tranql.ejb.SingleValuedCMRAccessor.set(SingleValuedCMRAccessor.java: > 66) SNIP > org.spec.jappserver.mfg.workorderent.ejb.WorkOrderCmp20EJB.ejbPostCreate > (WorkOrderCmp20EJB.java:239) > at > org.spec.jappserver.mfg.workorderent.ejb.WorkOrderCmp20EJB$$FastClassByC > GLIB$$5801b0f0.invoke(<generated>) > at > org.openejb.entity.cmp.CMPCreateMethod.execute(CMPCreateMethod.java:213) > SNIP > org.openejb.transaction.ContainerPolicy$TxRequired.invoke(ContainerPolic > y.java:119) > ... 38 more > Caused by: org.tranql.ql.QueryException: Error executing statement: > SELECT T.WO_NUMBER FROM M_WORKORDER T WHERE T.WO_ASSEMBLY_ID = ? > at > org.tranql.sql.jdbc.JDBCQueryCommand.execute(JDBCQueryCommand.java:79) > at > org.tranql.ejb.MultiValuedCMRFaultHandler.fieldFault(MultiValuedCMRFault > Handler.java:65) > at > org.tranql.ejb.CMPFieldFaultTransform.get(CMPFieldFaultTransform.java:54 > ) > ... 52 more > Caused by: SQL Exception: A lock could not be obtained due to a > deadlock, cycle of locks and waiters is: > Lock : ROW, M_WORKORDER, (3,30) > Waiting XID : {5683, S} , APP, SELECT T.WO_NUMBER FROM M_WORKORDER T > WHERE T.WO_ASSEMBLY_ID = ? > Granted XID : {5684, X} > > Lock : ROW, M_WORKORDER, (3,31) > Waiting XID : {5684, S} , APP, SELECT T.WO_NUMBER FROM M_WORKORDER T > WHERE T.WO_ASSEMBLY_ID = ? > Granted XID : {5683, X} > > . The selected victim is XID : 5683. > at > org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) > at >
