Deadlock in a Spring+Hibernate+DB2+JTA+XA application

Question

Exception from application log:

12:04:18,503  INFO ExceptionResolver:30 -  [ org.springframework.dao.DeadlockLoserDataAccessException ] Hibernate flushing: could not update: [sero.chase.integration.Beans.Bean#1000]; SQL [update SCHM.v***240u_bean set prop1=?, prop2=?, prop3=?, prop4=?, prop5=?, prop6=?, prop7=?, prop8=?, prop9=?, prop10=?, prop11=?, prop12=?, prop13=?, prop14=?, prop15=?, prop16=?, prop17=?, prop18=?, prop19=?, prop20=?, prop21=?, where bean_id=?]; UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE 00C90088, TYPE OF RESOURCE 00000302, AND RESOURCE NAME SCHM.SAKT240 .X'000017'. SQLCODE=-913, SQLSTATE=57033, DRIVER=3.53.70; nested exception is com.ibm.db2.jcc.b.SqlException: UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE 00C90088, TYPE OF RESOURCE 00000302, AND RESOURCE NAME SCHM.SAKT240 .X'000017'. SQLCODE=-913, SQLSTATE=57033, DRIVER=3.53.70org.springframework.dao.DeadlockLoserDataAccessException: Hibernate flushing: could not update: [sero.chase.integration.Beans.Bean#1000]; SQL [update SCHM.v***240u_bean set prop1=?, prop2=?, prop3=?, prop4=?, prop5=?, prop6=?, prop7=?, prop8=?, prop9=?, prop10=?, prop11=?, prop12=?, prop13=?, prop14=?, prop15=?, prop16=?, prop17=?, prop18=?, prop19=?, prop20=?, prop21=?, where bean_id=?]; UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE 00C90088, TYPE OF RESOURCE 00000302, AND RESOURCE NAME SCHM.SAKT240 .X'000017'. SQLCODE=-913, SQLSTATE=57033, DRIVER=3.53.70; nested exception is com.ibm.db2.jcc.b.SqlException: UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE 00C90088, TYPE OF RESOURCE 00000302, AND RESOURCE NAME MWIAKT1 .SAKT240 .X'000017'. SQLCODE=-913, SQLSTATE=57033, DRIVER=3.53.70
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:265)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.orm.hibernate3.HibernateTransactionManager.convertJdbcAccessException(HibernateTransactionManager.java:805)
    at org.springframework.orm.hibernate3.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:791)
    at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:664)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:393)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:120)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at $Proxy54.save(Unknown Source)
    at sero.chase.integration.DaoImpl.ExampleDaoImpl.save(ExampleDaoImpl.java:151)
    at sero.chase.business.BOImpl.ExampleBOImpl.save(ExampleBOImpl.java:191)
    at sero.chase.ServicesImpl.ExampleServiceImpl.submitAnswer(ExampleServiceImpl.java:183)
    at sero.chase.business.BusDelegatesImpl.ExampleBusDelegateImpl.gradeAnswer(ExampleBusDelegateImpl.java:578)
    at sero.chase.presentation.Controller.ExampleController.gradeAnswer(ExampleController.java:326)
    at sero.chase.presentation.Controller.ExampleController.SubmitAnswer(ExampleController.java:422)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:618)
    at org.springframework.web.servlet.mvc.multiaction.MultiActionController.invokeNamedMethod(MultiActionController.java:471)
    at org.springframework.web.servlet.mvc.multiaction.MultiActionController.handleRequestInternal(MultiActionController.java:408)
    at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
    at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:900)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:827)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1152)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1087)
    at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:118)
    at com.ibm.ws.webcontainer.filter.WebAppFilterChain._doFilter(WebAppFilterChain.java:87)
    at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:840)
    at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:683)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:589)
    at com.ibm.ws.wswebcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:534)
    at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:90)
    at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:751)
    at com.ibm.ws.wswebcontainer.WebContainer.handleRequest(WebContainer.java:1478)
    at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:126)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:458)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:387)
    at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:102)
    at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:165)
    at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
    at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
    at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:136)
    at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:196)
    at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:751)
    at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:881)
    at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1497)
Caused by: com.ibm.db2.jcc.b.SqlException: UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE 00C90088, TYPE OF RESOURCE 00000302, AND RESOURCE NAME MWIAKT1 .SAKT240 .X'000017'. SQLCODE=-913, SQLSTATE=57033, DRIVER=3.53.70
    at com.ibm.db2.jcc.b.bd.a(bd.java:679)
    at com.ibm.db2.jcc.b.bd.a(bd.java:60)
    at com.ibm.db2.jcc.b.bd.a(bd.java:127)
    at com.ibm.db2.jcc.b.fm.b(fm.java:2132)
    at com.ibm.db2.jcc.b.fm.c(fm.java:2115)
    at com.ibm.db2.jcc.t4.db.k(db.java:353)
    at com.ibm.db2.jcc.t4.db.a(db.java:59)
    at com.ibm.db2.jcc.t4.t.a(t.java:50)
    at com.ibm.db2.jcc.t4.tb.b(tb.java:200)
    at com.ibm.db2.jcc.b.gm.Zb(gm.java:2445)
    at com.ibm.db2.jcc.b.gm.e(gm.java:3287)
    at com.ibm.db2.jcc.b.gm.Rb(gm.java:612)
    at com.ibm.db2.jcc.b.gm.executeUpdate(gm.java:595)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeUpdate(WSJdbcPreparedStatement.java:768)
    at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:23)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2399)
    at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2303)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2603)
    at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:92)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
    at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
    at org.hibernate.transaction.JTATransaction.commit(JTATransaction.java:135)
    at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:656)
    ... 50 more

Spring configuration:

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
    xmlns:jee="http://www.springframework.org/schema/jee" 
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans         
                           http://www.springframework.org/schema/beans/spring-beans-3.0.xsd         
                           http://www.springframework.org/schema/context         
                           http://www.springframework.org/schema/context/spring-context-3.0.xsd
                           http://www.springframework.org/schema/jee
                           http://www.springframework.org/schema/jee/spring-jee-3.0.xsd">

    <jee:jndi-lookup id="queueConFac" resource-ref="true" jndi-name="jms/queueConFac" />
    <jee:jndi-lookup id="receiveQ" resource-ref="true" jndi-name="jms/receiveQ" />
    <jee:jndi-lookup id="sendQ" resource-ref="true" jndi-name="jms/sendQ" />
    <jee:jndi-lookup id="XA" resource-ref="true" jndi-name="jdbc/XA" />
    <jee:jndi-lookup id="nonXA" resource-ref="true" jndi-name="jdbc/nonXA" />


    <bean id="jmsTxManager"
    class="org.springframework.transaction.jta.WebSphereUowTransactionManager"/> 

    <bean id="jmsDestResolver" class=" org.springframework.jms.support.destination.JndiDestinationResolver"/>   

    <bean id="exampleListener" class="sero.chase.integration.JMS.Services.JMSReceiver">
        <property name="exampleAppBusDelegate" ref="exampleAppBusDelegate" />
    </bean>

    <bean class="org.springframework.jms.listener.DefaultMessageListenerContainer" >
        <property name="connectionFactory" ref="queueConFac" />
        <property name="destination" ref="receiveQ" />
        <property name="messageListener" ref="exampleListener" />
        <property name="transactionManager" ref="jmsTxManager" />
        <property name="taskExecutor" ref="jmsTaskExecutor" />
    </bean>

    <bean id="jmsTaskExecutor"
      class="org.springframework.scheduling.commonj.WorkManagerTaskExecutor">
      <property name="workManagerName" value="wm/default" />
   </bean>

   <bean id="jmsSender" class="sero.chase.integration.JMS.Services.JMSSender">
        <property name="connectionFactory" ref="queueConFac" />
        <property name="queue" ref="sendQ" />
    </bean>

    <bean id="localeResolver"
        class="org.springframework.web.servlet.i18n.SessionLocaleResolver">
        <property name="defaultLocale" value="en" />
    </bean>

    <bean id="localeChangeInterceptor"
        class="org.springframework.web.servlet.i18n.LocaleChangeInterceptor">
        <property name="paramName" value="language" />
    </bean>

    <bean class="org.springframework.web.servlet.mvc.support.ControllerClassNameHandlerMapping" >
        <property name="interceptors">
            <list>
                <ref bean="localeChangeInterceptor" />
            </list>
        </property>
    </bean>

    <bean id="messageSource" class="org.springframework.context.support.ReloadableResourceBundleMessageSource">
        <property name="basename" value="WEB-INF/resources/langSpecificText"/>
    </bean> 

    <bean id="viewResolver"
        class="org.springframework.web.servlet.view.UrlBasedViewResolver">
        <property name="viewClass"
            value="org.springframework.web.servlet.view.tiles2.TilesView" />
    </bean>

    <bean id="tilesConfigurer"
        class="org.springframework.web.servlet.view.tiles2.TilesConfigurer">
        <property name="definitions">
            <list>
                <value>/WEB-INF/tiles-def.xml</value>
            </list>
        </property>
    </bean>

    <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
        <property name="jndiName" value="jdbc/nonXA" />
    </bean>

    <bean id="sessionFactory"
        class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="dataSource"     ref="nonXA" />
        <property name="configLocation" value="classpath:/hibernate.cfg.nonXA.xml" />
        <property name="entityInterceptor">
            <bean class="sero.chase.integration.Hibernate.DB2Interceptor"/>
        </property>
    </bean>

    <bean id="session.XA.Factory"
        class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="dataSource"     ref="XA" />
        <property name="configLocation" value="classpath:/hibernate.cfg.XA.xml" />
        <property name="entityInterceptor">
            <bean class="sero.chase.integration.Hibernate.DB2Interceptor"/>
        </property>
    </bean>

    <bean id="transactionManager"
        class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>

    <bean id="transaction.XA.Manager"
        class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="sessionFactory" ref="session.XA.Factory" />
    </bean>

    <bean id="transactionAttributeSource"
        class="org.springframework.transaction.interceptor.NameMatchTransactionAttributeSource">
        <property name="properties">
            <props>
                <prop key="*">PROPAGATION_REQUIRED</prop>
            </props>
        </property>
    </bean>

    <!-- App Bean Definitions (Two dao configurations excluding several other bean configurations are displayed below) -->

    <bean id="exampleDao"
        class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean"
        lazy-init="true">
        <property name="transactionManager"         ref="transactionManager" />
        <property name="transactionAttributeSource" ref="transactionAttributeSource" />
        <property name="target">
            <bean class="sero.chase.integration.PersistenceImpl.ExamplePersistenceImpl">
                <property name="sessionFactory"     ref="sessionFactory" />
            </bean>
        </property>
    </bean>

     <bean id="exampleXADao"
        class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean"
        lazy-init="true">
        <property name="transactionManager"         ref="transaction.XA.Manager" />
        <property name="transactionAttributeSource" ref="transactionAttributeSource" />
        <property name="target">
            <bean class="sero.chase.integration.PersistenceImpl.ExamplePersistenceImpl">
                <property name="sessionFactory"     ref="session.XA.Factory" />
            </bean>
        </property>
    </bean>

</beans>

Hibernate Non XA configuration:

<hibernate-configuration>

        <session-factory>

             <property name="hibernate.dialect">
                sero.chase.integration.Hibernate.DB2390Dialect
            </property>

            <property name="hibernate.default_schema">SCHM</property>
            <property name="query.substitutions">yes 'Y', no 'N'</property>
            <property name="jdbc.use_streams_for_binary">true</property>
            <property name="show_sql">true</property>
            <property name="format_sql">true</property>
            <property name="transaction.factory_class">org.hibernate.transaction.JTATransactionFactory</property>
            <property name="jta.UserTransaction">java:comp/UserTransaction</property>

            <property name="hibernate.cache.provider_class">
                org.hibernate.cache.EhCacheProvider
            </property>

            <!--===============-->
            <!-- mapping files -->
            <!--===============-->

            <mapping resource="sero/chase/integration/hbm/Example.hbm.xml" />

        </session-factory>

    </hibernate-configuration>

Hibernate XA configuration:

<hibernate-configuration>

        <session-factory>

            <property name="hibernate.dialect">
                sero.chase.integration.Hibernate.DB2390Dialect
            </property>

            <property name="hibernate.default_schema">SCHMA</property>
            <property name="query.substitutions">yes 'Y', no 'N'</property>
            <property name="jdbc.use_streams_for_binary">true</property>
            <property name="show_sql">true</property>
            <property name="format_sql">true</property>
            <property name="hibernate.transaction.factory_class">org.hibernate.transaction.CMTTransactionFactory </property>
            <property name="hibernate.transaction.manager_lookup_class">org.hibernate.transaction.WebSphereExtendedJTATransactionLookup</property> 

            <property name="hibernate.cache.provider_class">
                org.hibernate.cache.EhCacheProvider
            </property>

            <!--===============-->
            <!-- mapping files -->
            <!--===============-->

            <mapping resource="sero/chase/integration/hbm/Example.hbm.xml" />

        </session-factory>

    </hibernate-configuration>

Code snippet from my service implementation class where most of the business logic happens:

public void someDeadlockCausingServiceMethod() {
    //The read1() method below is going to executing a select hql statement on Table A once the call goes all the way down to the Dao layer.
    List<SomeBeanInBusinessLayer> beanList = exampleBO.read1();

    //Do some processing with the values obtained from the read1() method
    ...
    //

    //saveOrUpdate() method below is going to execute an update hql statement on Table A once the call goes all the way down to the Dao layer 
    //where the values from someBeanInBusinessLayer is going to be copied into someBeanInDaoLayer before saving.
    exampleBO.saveOrUpdate(someBeanInBusinessLayer)

    //The read2() method below is going to execute a select hql statement that contains two inner selects (on Table B, C and D) once the call goes all
    //the way down to the Dao layer.
    List<SomeBeanInBusinessLayer2> beanList2 = exampleBO.read2();

    //Do some processing with the values obtained from the read2() method inside a for loop
    for(SomeBeanInBusinessLayer2 s: beanList2) {

        //Read values from table E
        List<SomeBeanInBusinessLayer3> beanList3 = exampleBO.read3(s.getProp2());

        SomeBeanInBusinessLayer2 someBeanInBusinessLayer2 = new SomeBeanInBusinessLayer2();
        someBeanInBusinessLayer2.setProp1(s.getProp2());
        someBeanInBusinessLayer2.setProp1(someBeanInBusinessLayer3.getProp2());
        //... more processing...
        //Below method will execute an insert hql on Table F
        exampleBO.saveOrUpdate(someBeanInBusinessLayer2);

        SomeBeanInBusinessLayer3 someBeanInBusinessLayer3 = new SomeBeanInBusinessLayer3();
        someBeanInBusinessLayer3.setProp1(s.getProp5());
        //... more processing...
        //Below method will execute an insert hql on Table G
        exampleBO.saveOrUpdate(someBeanInBusinessLayer3);
    }
}

public void anotherDeadlockCausingServiceMethod() {
    //The read1() method below is going to executing a select hql statement on Table A once the call goes all the way down to the Dao layer.
    List<SomeBeanInBusinessLayer> beanList = exampleBO.read1();

    //The read2() method below is going to executing a select hql statement on Table F once the call goes all the way down to the Dao layer.
    List<SomeBeanInBusinessLayer2> beanList2 = exampleBO.read2();

    //The read1() method below is going to executing a select hql statement on Table G once the call goes all the way down to the Dao layer.
    List<SomeBeanInBusinessLayer3> beanList3 = exampleBO.read3();

    //Do some processing with the values obtained...

    //Do an update on Table A
    exampleBO.saveOrUpdate(someBeanInBusinessLayer1)

    //Do an update on Table F
    exampleBO.saveOrUpdate(someBeanInBusinessLayer2)
}

Code snippet from my Dao layer1:

public void load(BeanDTO beanDTO) {
    Object param1 = beanDTO.getBeanList().getProp1();
    Object param2 = beanDTO.getBeanList().getProp2();
    List<SomeBeanInDaoLayer> beanList = null;
    Object[] params = {param1, param2};
          UserTransaction ut = null;
    try {
      Context context = new InitialContext();
      ut = (UserTransaction) context.lookup(Constant.USR_TRANSACTION);
      ut.begin();   
            beanList = beanDao2.load(params);
            ut.commit();
    }
    catch(Exception e) {
                    try {
                         ut.rollback();
                    }
                    catch(Exception e1) {
                    if(logger.isDebugEnabled()) {
           logger.debug("DB Exception", e1);
        }
                    }
        int error = ExceptionResolver.resolve(e);
        if(logger.isDebugEnabled()) {
            logger.debug("DB Exception", e);
        }
        beanDTO.setErrorCode(error);
    }
    beanDTO.setBeanList(beanList);
}

public void save(BeanDTO beanDTO) {
    List<SomeBeanInDaoLayer> beanList = beanDTO.getBeanList();
    for(SomeBeanInDaoLayer bean: beanList) {
        try {
                      Context context = new InitialContext();
                ut = (UserTransaction) context.lookup(Constant.USR_TRANSACTION);
                ut.begin(); 
                      beanDao2.save(bean);
                      ut.commit();
        }
        catch(Exception e) {
                       try {
                         ut.rollback();
                       }
                       catch(Exception e1) {
                         if(logger.isDebugEnabled()) {
               logger.debug("DB Exception", e1);
             }
                       }
            int err = ExceptionResolver.resolve(e);
            if(logger.isInfoEnabled()) {
                logger.info("DB Exception", e);
            }
            beanDTO.setErrorCode(err);
        }
    }
}

Code snipper from my Dao Layer2:

public List<Bean> load(Object[] params) {
    String hql = "from Bean where beanProp1 = ? and beanProp2 = ?";
    return (List<Bean>) getHibernateTemplate().find(hql, params);
}

public void save(Bean bean) {
    getHibernateTemplate().saveOrUpdate(bean);
}

1. This application is a testing system where in users can take test concurrently.
2. Initially the transaction demarcation was not at my Dao layer but say at my Service Implementation class (actually all the way at my controller class) where several reads and updates were tied up into one transaction within a begin-commit block. Since I was seeing several deadlocks I moved the demarcation to Dao layer so that there's only one hql statement between my begin-commit block to see if it prevents the deadlock but have had no luck.
3. Tried setting properties like hibernate.connection.autocommit to true, hibernate.transaction.flush_before_completion to true and hibehibernate.transaction.auto_close_session to true but had no luck.
4. Never a row read by one user is updated by another user. Each user reads and updates different rows even though they access the same DB2 tables. Only at the time of running the process for building a set of questions for a test, two users would read the same rows if they were taking the same type of test. It is very similar to the someDeadlockCausingMethod described above where the test questions are prepared from a set of tables that contain questions and answers. From iterating through this result set inside a for-loop, new rows are inserted into another table to save the details of each question that will appear on an user's test. This step is necessary in the application because even though two users take the same test, a set of random questions are taken from the pool of all questions for each user.
5. Now that the test is prepared for user taking the test, the next logical step in the application is to read the rows from a table that contain just the details of the questions pertaining to the user taking the test. So a concurrent user would read the same table during this process but never the same row. The user is presented with one question at a time. Once the user answers a question, the row that was read to get the question pertaining to just this user will be updated with the answer choice. Again never the same rows are getting updated for two concurrent users. This method is analogous to anotherDeadlockCausingMethod() described above.
6. I hope you got an idea of what the application does. The fact that never the same rows are read or updated by concurrent users surprised me on how a resource can get locked. Then I figured out that page-locking was in place for the tables getting updated. So I went and asked the DBA if he could change it to row-locking on the tables that were getting updated. He is concerned about the performance overhead on DB2 to implement row-locking and is worried if it may affect other applications using DB2. So he doesn't want to do it unless I can't find any other solution.
7. Please forget the XA/JMS portion. Assume that portion is commented out for time being. For the most part of the application non-XA datasource is used where I see the deadlocks.

   Can someone please tell me how do I go about resolving the deadlock? I want to understand what went wrong in the design. Any help is greatly appreciated in advance

Answer 1

Finally I managed a clean run without a deadlock doing the following:

1. hibernate.connection.isolation = 2
2. Added 'for update of with cs' at the end of my select statements.
3. I was using the saveOrUpdate() hibernate method for both update and insert. Now instead I use save() for insert and update() for update.

One thing I didn't understand was why using 'with ur' at the end of my select statements didn't resolve the deadlocks in comparison to the 'with cs' I'm using now. Wondering has the isolation level of the database, which is 'rs' got to do anything with it?