CannotAcquireLockException (Spring, Hibernate, MySQL)

Question

We use Spring, Hibernate and MySQL for our application. But sometimes a query generates CannotAcquireLockException, the code is as below.

public Ledger[] storeOrUpdateLedgers(Ledger[] ledgers,int iClinicId) throws DataAccessException {

    List<Ledger> ledgerList = new ArrayList<Ledger>();
    for(int i = 0; i < ledgers.length; i++) {
        ledgers[i].setiClinicId(iClinicId);
        ledgerList.add(ledgers[i]);
    }

    for(int i = 0; i < ledgerList.size(); i++) {
        getHibernateTemplate().clear();
        getHibernateTemplate().saveOrUpdate(ledgerList.get(i));
        getHibernateTemplate().flush();
    }

}

public class Ledger implements Serializable {

    private int iLedgerId;
    private int iClinicId;
    private int iPatientId;
    private int iProviderId;
    private int iVisitId;
    private int iPaymentId;
    private int iClaimId;
    private int iProcedureId;
    private String sDate;
    private double dAmount;
    private byte btType;
    private String sDesc;
    private byte btCurrParty;
    private int iCurrPartyId;
    private byte btRespParty;
    private int iRespPartyId;
    private boolean active;
    private int iParentId;
    private int iReasonId;
    private String sDos;
    private int iU_ID;
    private String sEntryDate;   // no mapping required


    public int getiU_ID() {
        return iU_ID;
    }

    public void setiU_ID(int iUID) {
        iU_ID = iUID;
    }

    public int getiLedgerId() {
        return iLedgerId;
    }

    public void setiLedgerId(int iLedgerId) {
        this.iLedgerId = iLedgerId;
    }

    public int getiClinicId() {
        return iClinicId;
    }

    public void setiClinicId(int iClinicId) {
        this.iClinicId = iClinicId;
    }

    public int getiPatientId() {
        return iPatientId;
    }

    public void setiPatientId(int iPatientId) {
        this.iPatientId = iPatientId;
    }

    public int getiProviderId() {
        return iProviderId;
    }

    public void setiProviderId(int iProviderId) {
        this.iProviderId = iProviderId;
    }

    public int getiVisitId() {
        return iVisitId;
    }

    public void setiVisitId(int iVisitId) {
        this.iVisitId = iVisitId;
    }

    public int getiPaymentId() {
        return iPaymentId;
    }

    public void setiPaymentId(int iPaymentId) {
        this.iPaymentId = iPaymentId;
    }

    public int getiClaimId() {
        return iClaimId;
    }

    public void setiClaimId(int iClaimId) {
        this.iClaimId = iClaimId;
    }

    public int getiProcedureId() {
        return iProcedureId;
    }

    public void setiProcedureId(int iProcedureId) {
        this.iProcedureId = iProcedureId;
    }

    public String getsDate() {
        return sDate;
    }

    public void setsDate(String sDate) {
        this.sDate = sDate;
    }

    public double getdAmount() {
        return dAmount;
    }

    public void setdAmount(double dAmount) {
        this.dAmount = dAmount;
    }

    public byte getbtType() {
        return btType;
    }

    public void setbtType(byte btType) {
        this.btType = btType;
    }

    public String getsDesc() {
        return sDesc;
    }

    public void setsDesc(String sDesc) {
        this.sDesc = sDesc;
    }

    public byte getbtCurrParty() {
        return btCurrParty;
    }

    public void setbtCurrParty(byte btCurrParty) {
        this.btCurrParty = btCurrParty;
    }

    public int getiCurrPartyId() {
        return iCurrPartyId;
    }

    public void setiCurrPartyId(int iCurrPartyId) {
        this.iCurrPartyId = iCurrPartyId;
    }

    public byte getbtRespParty() {
        return btRespParty;
    }

    public void setbtRespParty(byte btRespParty) {
        this.btRespParty = btRespParty;
    }

    public int getiRespPartyId() {
        return iRespPartyId;
    }

    public void setiRespPartyId(int iRespPartyId) {
        this.iRespPartyId = iRespPartyId;
    }

    public boolean isActive() {
        return active;
    }

    public void setActive(boolean active) {
        this.active = active;
    }

    public int getiParentId() {
        return iParentId;
    }

    public void setiParentId(int iParentId) {
        this.iParentId = iParentId;
    }

    public int getiReasonId() {
        return iReasonId;
    }

    public void setiReasonId(int iReasonId) {
        this.iReasonId = iReasonId;
    }

    public String getsDos() {
        return sDos;
    }

    public void setsDos(String sDos) {
        this.sDos = sDos;
    }

    public String getsEntryDate() {
        return sEntryDate;
    }

    public void setsEntryDate(String sEntryDate) {
        this.sEntryDate = sEntryDate;
    }

}

Hibernate mapping:

<class name="com.iris.allofactor.data.vo.Ledger" table="LEDGER">
    <id name="iLedgerId" column="LEDGER_ID" unsaved-value="0">
        <generator class="native"/>
    </id>
    <property name="iClinicId" column="CLINIC_ID"></property>
    <property name="iPatientId" column="PATIENT_ID"></property>
    <property name="iProviderId" column="PROVIDER_ID"></property>
    <property name="iVisitId" column="VISIT_ID"></property>
    <property name="iPaymentId" column="PAYMENT_ID"></property>
    <property name="iClaimId" column="CLAIM_ID"></property>
    <property name="iProcedureId" column="PROCEDURE_ID"></property>
    <property name="sDate" column="DATE"></property>
    <property name="dAmount" column="AMOUNT"></property>
    <property name="btType" column="TYPE"></property>
    <property name="sDesc" column="DESCRIPTION"></property>
    <property name="btCurrParty" column="CURR_PARTY"></property>
    <property name="iCurrPartyId" column="CURR_PARTY_ID"></property>
    <property name="btRespParty" column="RESP_PARTY"></property>
    <property name="iRespPartyId" column="RESP_PARTY_ID"></property>
    <property name="active" column="ACTIVE"></property>
    <property name="iParentId" column="PARENT_ID"></property>
    <property name="iReasonId" column="REASON_ID"></property>
    <property name="sDos" column="DOS"></property>
    <property name="iU_ID" column="USER_ID"></property>
</class>

Stacktrace follows:

at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:244)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)
at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
at org.springframework.orm.hibernate3.HibernateTemplate.flush(HibernateTemplate.java:890)
at com.iris.allofactor.data.dao.hibernate.HibernateLedgerDao.storeOrUpdateLedgers(HibernateLedgerDao.java:97)
at com.iris.allofactor.data.dao.impl.LedgerAuditBODaoImpl.storeOrUpdateLedgers(LedgerAuditBODaoImpl.java:64)
at com.iris.allofactor.data.dao.impl.ChargesDaoImpl.storeOrUpdateCharges(ChargesDaoImpl.java:844)
at com.iris.allofactor.data.dao.impl.ClaimEncounterBODaoImpl.addorEditClaimWhileClaimIdAndVisitIdIsPresent(ClaimEncounterBODaoImpl.java:1072)
at com.iris.allofactor.data.dao.impl.ClaimEncounterBODaoImpl.storeOrUpdateClaim(ClaimEncounterBODaoImpl.java:819)
at com.iris.allofactor.data.dao.facade.DaoFacadeImpl.storeOrUpdateClaim(DaoFacadeImpl.java:1915)
at sun.reflect.GeneratedMethodAccessor2549.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy2.storeOrUpdateClaim(Unknown Source)
at com.iris.allofactor.services.impl.ClaimServiceImpl.addorEditClaim(ClaimServiceImpl.java:447)
at com.iris.allofactor.services.soap.impl.ClaimWebServiceImpl.addorEditClaim(ClaimWebServiceImpl.java:337)
at sun.reflect.GeneratedMethodAccessor2548.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:453)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:619)

It generates this exception repeatedly, I think there is some trouble in my method.

Answer 1

This is a definite deadlock situation. This more related to a MySQL error than a Hibernate problem with your classes. First let's review the definition of a deadlock:

A deadlock is a situation in which two or more competing actions are each waiting for the other to finish, and thus neither ever does.

See this page for more information: http://en.wikipedia.org/wiki/Deadlock

How do you deal with this situation. Well, you need to read the following article: Deadlocks in InnoDB. It contains most of the information you need. This article explains in detail how to trace and deal with deadlocks it is a must read.

Essentially you will need to do the following steps based on the information above:

1. Create a MySQL Trace: Trace all the queries being run on the MySQL server.
2. Get the deadlock trace information
3. Match the dead lock trace and MySQL trace with each other to determine the cause of the dead lock.

The article on InnoDB's has a list of remedies as well so I am not going to reiterate them here. Just keep in mind deadlocks is not a fatal error, you just need to handle it. So maybe catch the exception and retry the transaction. Also make sure your queries being generated by Hibernate is optimal in the sense that they are using indexes where they can etc. Another thing you could try is batching the transaction on Hibernate and executing in batch.

I am sure with the two link above you will be able to handle the problem and it will be valuable experience for you to work through this yourself. If you find any particular item you have problems with add it to the question and let's deal with that.