Oooh, you calmed me. :) Looks like trying another database is my next step. :)
Thank you! Vasily -----Original Message----- From: David Jencks [mailto:[EMAIL PROTECTED] Sent: Thursday, June 01, 2006 1:14 AM To: [email protected] Subject: Re: SQL transaction deadlock The tranql derby xa connectors use the appropriate derby XADataSources under the covers. Have you tried a different database such as db2? I haven't investigated very deeply but I think that derby may object to lock conflicts more easily than some other databases. thanks david jencks On May 31, 2006, at 12:23 PM, Zakharov, Vasily M wrote: > 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-interface> > > <connectionfactory-impl-class>org.tranql.connector.jdbc.DataSource > </connectionfactory-impl-class> > <connection-interface>java.sql.Connection</connection-interface> > > <connection-impl-class>org.tranql.connector.jdbc.ConnectionHandle > </connection-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.ejbPostCrea > te >> (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 >>
