• Post Reply Bookmark Topic Watch Topic
  • New Topic

transaction deadlock problem(sybase)

vidya mahavadi
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I am using JPA on Sybase. The context is I have few MDBs reading few thousands of messages off a topic and I am trying to instantiate/populate couple of entity beans that have one-to-many relationship and persist them to database (sybase). If I declate the number of MDBs in the container as 1, then the feed goes through smoothly. But it takes far too much time to consume all the messages.
@ActivationConfigProperty(propertyName = "maxSession", propertyValue = "1")

If I make the number of MDB instances to more than 1, occationally it is throwing up the following exception.

2007-10-22 10:41:13,181 DEBUG [org.jboss.mq.server.JMSDestinationManager] Exception occured, rolling back transaction:
org.jboss.mq.SpyJMSException: Could not commit tx: 4422; - nested throwable: (com.sybase.jdbc2.jdbc.SybSQLException: Your server command (family id #0, process id #145) encountered a deadlock situation. Please re-run your command.
at org.jboss.mq.pm.jdbc2.PersistenceManager.commitPersistentTx(PersistenceManager.java:855)
at org.jboss.mq.pm.Tx.commit(Tx.java:207)
at org.jboss.mq.pm.TxManager.commitTx(TxManager.java:113)
at org.jboss.mq.server.JMSDestinationManager.transact(JMSDestinationManager.java:444)
at org.jboss.mq.server.JMSServerInterceptorSupport.transact(JMSServerInterceptorSupport.java:126)
at org.jboss.mq.security.ServerSecurityInterceptor.transact(ServerSecurityInterceptor.java:197)
at org.jboss.mq.server.TracingInterceptor.transact(TracingInterceptor.java:352)
at org.jboss.mq.server.JMSServerInvoker.transact(JMSServerInvoker.java:132)
at org.jboss.mq.il.jvm.JVMServerIL.transact(JVMServerIL.java:175)
at org.jboss.mq.Connection.send(Connection.java:1110)
at org.jboss.mq.SpyXAResourceManager.commit(SpyXAResourceManager.java:166)
at org.jboss.mq.SpyXAResource.commit(SpyXAResource.java:88)
at org.jboss.tm.TransactionImpl$Resource.commit(TransactionImpl.java:2253)
at org.jboss.tm.TransactionImpl.commitResources(TransactionImpl.java:1784)
at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:340)
at org.jboss.tm.TxManager.commit(TxManager.java:240)
at org.jboss.resource.adapter.jms.inflow.JmsServerSession$XATransactionDemarcationStrategy.end(JmsServerSession.java:471)
at org.jboss.resource.adapter.jms.inflow.JmsServerSession.run(JmsServerSession.java:260)
at org.jboss.resource.work.WorkWrapper.execute(WorkWrapper.java:204)
at org.jboss.util.threadpool.BasicTaskWrapper.run(BasicTaskWrapper.java:275)
at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:743)
at java.lang.Thread.run(Thread.java:595)

I am using container managed EntityManager and following is the sample code snippet where I am trying to persist the message.

FiccValuationData ficcValuationData = new FiccValuationData();
FiccTradeData tradeData = new FiccTradeData();
//Code to populate the two entities (not included)

Please let me know if anyone has got ideas on this.

Paul Sturrock
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The usual recommendations to fix deadlock are:
  • Check and chenage the order of your SQL
  • Reduce the length of the transaction the deadlocked statement appears in

  • However, these two recommendations are often difficult to follow. Your SQL may be generated by something else (as it is in JPA) or your transaction may be long because it absolutely has to be.

    In most decent databases there will be a utility to track deadlock processes. I don't know Sybase, but there should be one. The first thing I would do is turn this on and see what it says.

    I'd expect you to get a list of the statements involved in the deadlock. After this you can examine the execution plans of each statemnent to look for points where they might be locking more resources than they should. For example an update without a primary key in the where clause might be causing a page lock on much more data than it needs to (since the update could feasable update every row in the table). Often in these cases adding an index to specific columns will fix the problem.
    Happiness is not a goal ... it's a by-product of a life well lived - Eleanor Roosevelt. Tiny ad:
    the new thread boost feature: great for the advertiser and smooth for the coderanch user
    • Post Reply Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!