DeadLock issue in MultiThreading due to foreign key constraint.
Problem Statement :-
we created Entitlement(Parent Record) and its corresponding LineItem(Child, also have parent in same table).
We update Entitlement, delete lineItem and insert new lineItem with updated Entitlement. We also create parent child relationship with in lineItem.
When we run our application with 50 threads, it create deadlock in some threads.
Technology Used:-
Spring(3.0) + Hibernate(3.0){Used C3P0 connection pool} + MySql(5.5)
Problem Identification:-
We found that we get deadlock due to the lock on lineitem table. The main reason is Foreign Key constraint on LinkedLineItemId column.
Exception:-
We get below exception when we run the code with 50 thread, but the applications run fine on 20 thread.
org.hibernate.exception.LockAcquisitionException: could not execute statement
at org.hibernate.dialect.MySQLDialect$1.convert(MySQLDialect.java:412)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:124)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:189)
at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:96)
at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:58)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2987)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3499)
at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81)
at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:395)
at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:229)
at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:209)
at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:193)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
at sun.reflect.GeneratedConstructorAccessor21.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1065)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:186)
We used two tables(Entitlement & lineItem)in our application.
Tables:-
1.Entitlement:-
CREATE TABLE `entitlement` (
`EntId` int(11) NOT NULL AUTO_INCREMENT,
`Eid` varchar(100) DEFAULT NULL,
`Qty` int(11) DEFAULT NULL,
`RemainingQty` int(11) DEFAULT NULL,
`Descr` varchar(200) DEFAULT NULL,
PRIMARY KEY (`EntId`)
) ENGINE=InnoDB AUTO_INCREMENT=583 DEFAULT CHARSET=utf8;
2.LineItem:-
CREATE TABLE `lineitem` (
`LineItemId` int(11) NOT NULL AUTO_INCREMENT,
`EntId` int(11) DEFAULT NULL,
`AttrID` int(11) DEFAULT NULL,
`AttrValue` varchar(2000) DEFAULT NULL,
`LinkedLineItemID` int(11) DEFAULT NULL,
PRIMARY KEY (`LineItemId`),
KEY `FK_LineItem_Entitlement` (`EntId`),
KEY `fk_line_item` (`LinkedLineItemID`),
CONSTRAINT `FK_LineItem_Entitlement` FOREIGN KEY (`EntId`) REFERENCES `entitlement` (`EntId`),
CONSTRAINT `fk_line_item` FOREIGN KEY (`LinkedLineItemID`) REFERENCES `lineitem` (`LineItemId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1609 DEFAULT CHARSET=utf8;
Java Code:-
Controller Class:-
@RequestMapping(method = RequestMethod.GET)
public ModelAndView activation(HttpServletRequest request,HttpServletResponse response) {
ModelAndView model = new ModelAndView("success");
try{
String eId = emsService.createEntitlement();
emsService.updateEntitlement(eId);
}catch(Exception ex){
ex.getStackTrace();
model.addObject("message", "Error occured.");
ex.printStackTrace();
}
return model;
}
Service Class:-
@Transactional(propagation = Propagation.REQUIRED, rollbackFor=Throwable.class)
public String createEntitlement() {
String eid = "eid"+System.currentTimeMillis();
Entitlement ent = new Entitlement(0,eid,111,111,"dsadadasdasdasd");
LineItem li = new LineItem(0, 1, "attributeVal");
LineItem li2 = new LineItem(0, 1, "attributeVal");
li.setEntitlement(ent);
li2.setEntitlement(ent);
List<LineItem> lList = new ArrayList<LineItem>();
lList.add(li);
lList.add(li2);
ent.setLineItem(lList);
ent.getLineItem();
emsDao.saveEntitlement(ent);
return eid;
}
@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;
}
DAO class:-
public Session getSession()
{
Session session=sessionFactory.getCurrentSession();
return session;
}
public Entitlement getEntitlement(String eId) {
String query="from Entitlement ent where Eid = :eid";
Query q=getSession().createQuery(query);
q.setParameter("eid", eId);
List<Entitlement> li= q.list();
Entitlement ent = (Entitlement) (li.get(0));
return ent;
}
public boolean updateEntitlement(Entitlement eTo) {
getSession().saveOrUpdate(eTo);
getSession().flush();
return true;
}
public void saveEntitlement(Entitlement ent) {
getSession().saveOrUpdate(ent);
}
public void deleteLineItem(LineItem lineItem) {
getSession().delete(lineItem);
}
public void evictEntitlement(Entitlement ent) {
getSession().evict(ent);
getSession().flush();
}
public void deleteEntitlement(Entitlement ent) {
getSession().delete(ent);;
getSession().flush();
}
Entitlement Class:-
@Entity
@Table(name="Entitlement")
public class Entitlement implements Serializable, Cloneable{
private static final long serialVersionUID = 1L;
@Id
@Column(name="EntId")
@GeneratedValue(strategy=GenerationType.IDENTITY)
private int id;
@Column(name="Eid")
private String Eid;
@Column(name="Qty")
private int quantity;
@Column(name="RemainingQty")
private int remainingQuantity;
@Column(name="Descr")
private String descr;
@OneToMany(mappedBy = "entitlement", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private Collection<LineItem> lineItem;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getEid() {
return Eid;
}
public void setEid(String eid) {
Eid = eid;
}
public int getQuantity() {
return quantity;
}
public void setQuantity(int quantity) {
this.quantity = quantity;
}
public int getRemainingQuantity() {
return remainingQuantity;
}
public void setRemainingQuantity(int remainingQuantity) {
this.remainingQuantity = remainingQuantity;
}
public String getDescr() {
return descr;
}
public void setDescr(String descr) {
this.descr = descr;
}
public Entitlement(int id, String eid, int quantity,
int remainingQuantity, String descr) {
super();
this.id = id;
Eid = eid;
this.quantity = quantity;
this.remainingQuantity = remainingQuantity;
this.descr = descr;
}
public Entitlement() {
super();
}
public Collection<LineItem> getLineItem() {
return lineItem;
}
public void setLineItem(List<LineItem> lList) {
this.lineItem = lList;
}
@Override
public Object clone(){
try{
return super.clone();
}catch(Exception e){
}
throw new AssertionError();
}
}
LineItem Class:-
@Entity
@Table(name="LineItem")
public class LineItem implements Serializable, Cloneable{
private static final long serialVersionUID = 1L;
@Id
@Column(name="LineItemId")
@GeneratedValue(strategy=GenerationType.IDENTITY)
private int id;
@Column(name="AttrId")
private int AttrId;
@Column(name="AttrValue")
private String AttrValue;
@ManyToOne
@JoinColumn(name="LinkedLineItemId")
private LineItem linkedItem;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn (name="EntId",referencedColumnName="EntId",nullable=true,unique=true)
private Entitlement entitlement;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAttrId() {
return AttrId;
}
public void setAttrId(int attrId) {
AttrId = attrId;
}
public String getAttrValue() {
return AttrValue;
}
public void setAttrValue(String attrValue) {
AttrValue = attrValue;
}
public LineItem() {
super();
}
public LineItem(int id, int attrId, String attrValue) {
super();
this.id = id;
AttrId = attrId;
AttrValue = attrValue;
}
public Entitlement getEntitlement() {
return entitlement;
}
public void setEntitlement(Entitlement entitlement) {
this.entitlement = entitlement;
}
public LineItem getLinkedItem() {
return linkedItem;
}
public void setLinkedItem(LineItem linkedItem) {
this.linkedItem = linkedItem;
}
@Override
public Object clone(){
try{
return super.clone();
}catch(Exception e){
}
throw new AssertionError();
}
}