Re: DeadLock issue in MultiThreading due to foreign key constraint.

[Rishi anjan]

Thanks for providing solutions and apology for delay response.

But below are the cons for the provided solutions:-

Plan A: we can't remove foreign key. As it will create some data integrity in some point.
Plan B: we can retry transactions. but as failure rate is high so it will slow down our application performance, so searching for another solution.

Same code is running smoothly in SQL Server.

Following are the details for your refrence:-

we are using spring's HibernateTransactionManager for managing transactions.

Spring Configuration file:-
--------------------------

<tx:annotation-driven transaction-manager="transactionManager"/>

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

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="${org.quartz.dataSource.quartzDS.driver}" />
<property name="jdbcUrl" value="${org.quartz.dataSource.quartzDS.URL}" />
<property name="user" value="${org.quartz.dataSource.quartzDS.user}" />
<property name="password" value="${org.quartz.dataSource.quartzDS.password}" />
<property name="preferredTestQuery" value="${org.quartz.dataSource.quartzDS.testQuery}" />
<property name="testConnectionOnCheckout" value="${org.quartz.dataSource.quartzDS.testConnection}" />
<property name="maxPoolSize" value="${maxPoolSize}" />
<property name="maxStatements" value="0" />
<property name="minPoolSize" value="${minPoolSize}" />
</bean>

<bean id="sessionFactory"
class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="dataSource">
<ref bean="dataSource" />
</property>


<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.generate_statistics">false</prop>
<prop key="hibernate.connection.release_mode">auto</prop>
<prop key="hibernate.transaction.auto_close_session">false</prop>
<prop key="hibernate.max_fetch_depth">100000</prop>
<prop key="hibernate.jdbc.batch_size">50</prop>
<prop key="hibernate.jdbc.fetch_size">30</prop>
<prop key="hibernate.c3p0.idle_test_period">${hibernate.c3p0.idle_test_period}</prop>
<prop key="hibernate.c3p0.timeout">5000</prop>
</props>
</property>
<property name="annotatedClasses">
<list>
<value>com.sfnt.vo.Entitlement</value>
<value>com.sfnt.vo.Activation</value>
<value>com.sfnt.vo.LineItem</value>
</list>
</property>
</bean>




Technology Used:-
Spring(3.0) + Hibernate(3.0){Used C3P0 connection pool} + MySql(5.5)

All the application code and DataBase table details was already posted.
When we run our application with 50 threads, it create deadlock in some threads.

Below are the code from which we get the DeadLock Exception.

@Transactional(propagation = Propagation.REQUIRED, rollbackFor=Throwable.class)
public boolean updateEntitlement(String eId)
{
Entitlement eTo = emsDao.getEntitlement(eId);
List<LineItem> lList = (List<LineItem>) eTo.getLineItem();
List<LineItem> lList1 = new ArrayList<LineItem>();
LineItem li1 = lList.get(0);
for (int i = 1; i < lList.size(); i++)
{
emsDao.deleteLineItem(lList.get(i));
LineItem li0 = (LineItem) lList.get(i).clone();
li0.setId(0);
lList1.add(li0);
}
lList.clear();
eTo.setQuantity(100);
emsDao.saveEntitlement(eTo);
Entitlement eClone=(Entitlement)eTo.clone();
emsDao.evictEntitlement(eTo);
eClone.setId(0);
eClone.setLineItem(lList1);
emsDao.saveEntitlement(eClone);
LineItem li = new LineItem(0, 2, "attributeVal");
LineItem li2 = new LineItem(0, 2, "attributeVal");
li.setEntitlement(eTo);
li2.setEntitlement(eTo);
li.setLinkedItem(li1);
li2.setLinkedItem(li1);
lList.add(li);
lList.add(li2);
eTo.setLineItem(lList);
emsDao.updateEntitlement(eTo);
return true;
}