无法获取锁定异常 （Spring，Hibernate，MySQL）

Question

我们的应用程序使用Spring、Hibernate和MySQL。但有时候查询会生成CannotAcquireLockException，代码如下:

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映射：

<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>

堆栈跟踪如下：

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)

它不断生成这个异常，我认为我的方法有些问题。

- shin

Answer 1

这是一个明显的死锁（deadlock）情况，更多地与MySQL错误有关，而不是与您的类相关的Hibernate问题。首先，让我们回顾一下“死锁”的定义：

死锁是指两个或多个竞争行为相互等待对方完成，因此都无法完成的情况。

欲了解更多信息，请参见此页面：http://en.wikipedia.org/wiki/Deadlock

如何处理这种情况。好吧，您需要阅读以下文章：InnoDB中的死锁。它包含您所需的大部分信息。本文详细解释了如何跟踪和处理死锁，必读。

基于上述信息，您将需要执行以下步骤：

1. 创建MySQL跟踪：跟踪在MySQL服务器上运行的所有查询。
2. 获取死锁跟踪信息
3. 将死锁跟踪和MySQL跟踪相匹配，以确定死锁的原因。

InnoDB的文章也列出了一些补救措施，因此我不会在这里重复。只要记住，“死锁”不是致命错误，您只需要处理它。因此，可以捕获异常并重试事务。还要确保Hibernate生成的查询在适当的情况下使用索引等最优化。您还可以尝试将Hibernate中的事务进行批处理并批量执行。

我相信通过上面提供的两个链接，您将能够解决问题，并且对您自己处理此问题将是有价值的经验。如果您发现任何特定的问题，请将其添加到问题中，我们将对其进行处理。

- Namphibian

Answer 2

我在想你的问题是否与这个主题讨论中的问题相似：Hibernate/Spring/MS-SQL死锁问题。这个特定问题的根本原因是一个线程正在对数据库执行查找/选择，而另一个线程正在尝试对数据库进行删除/插入操作。该主题中提出了两种解决方案。

第一种优化了查找和删除代码，使其成为一个SQL命令而不是两个。

# sql sudo code

# original query
find me this row where this=that
delete this row

# better query
delete this row where this=that

第二个建议的解决方案是在用于WHERE子句中的COLUMNS上创建索引，这样数据库将不再锁定ROW，而是现在锁定索引的键。

- slm

Answer 3

在MySQL列表中可能存在未完成的会话，它可能仍在运行。这是由于您的应用程序中程序员的错误或使用数据库的程序员所导致的。

例如，您启动了一个会话，但由于错误，该进程卡住了而没有回滚。这种情况很少发生。

请手动删除所有会话。

获取所有会话：

SHOW FULL PROCESSLIST;

然后通过ID将它们删除

KILL 192;

例子。

mysql> SHOW PROCESSLIST;
+-----+------+-----------------+------+---------+------+-------+---------------+
| Id  | User | Host            | db   | Command | Time | State | Info      |
+-----+------+-----------------+------+---------+------+-------+----------------+
| 143 | root | localhost:61179 | cds  | Query   |    0 | init  | SHOW PROCESSLIST |
| 192 | root | localhost:53793 | cds  | Sleep   |    4 |       | NULL      |
+-----+------+-----------------+------+---------+------+-------+----------------+
2 rows in set (0.00 sec)

mysql> KILL 192;
Query OK, 0 rows affected (0.00 sec)

- Mafei