无法获取锁定异常（Spring、Hibernate、MySQL）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14230341/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CannotAcquireLockException (Spring, Hibernate, MySQL)
提问by shin
We use Spring, Hibernate and MySQL for our application. But sometimes a query generates CannotAcquireLockException, the code is as below.
我们的应用程序使用 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 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.
它反复产生这个异常，我认为我的方法有些问题。
回答by Namphibian
This is a definite deadlocksituation. This more related to a MySQL error than a Hibernate problem with your classes. First let's review the definition of a deadlock:
这是一个明确的死锁情况。这更多地与 MySQL 错误相关，而不是您的类的 Hibernate 问题。首先让我们回顾一下死锁的定义：
A deadlockis 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
有关更多信息，请参阅此页面：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.
你如何处理这种情况。好吧，您需要阅读以下文章： InnoDB 中的死锁。它包含您需要的大部分信息。这篇文章详细解释了如何跟踪和处理死锁，这是必读的。
Essentially you will need to do the following steps based on the information above:
基本上，您需要根据上述信息执行以下步骤：
- Create a MySQL Trace: Trace all the queries being run on the MySQL server.
- Get the deadlock trace information
- Match the dead lock trace and MySQL trace with each other to determine the cause of the dead lock.
- 创建 MySQL 跟踪：跟踪在 MySQL 服务器上运行的所有查询。
- 获取死锁跟踪信息
- 将死锁跟踪和 MySQL 跟踪相互匹配以确定死锁的原因。
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 deadlocksis 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 indexeswhere they can etc. Another thing you could try is batchingthe transaction on Hibernate and executing in batch.
关于 InnoDB 的文章也有一个补救措施列表，所以我不打算在这里重申它们。请记住，死锁不是致命错误，您只需要处理它。所以也许可以捕获异常并重试事务。还要确保由 Hibernate 生成的查询是最佳的，因为它们正在尽可能使用索引等。您可以尝试的另一件事是在 Hibernate 上批处理事务并批量执行。
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.
我相信通过上面的两个链接，您将能够解决问题，并且您自己解决这个问题将是宝贵的经验。如果您发现任何特定项目您有问题，请将其添加到问题中，让我们处理它。
回答by slm
I'm wondering if your problem is similar to the one discussed in this thread: Deadlock problems with Hibernate/Spring/MS-SQL. The root cause of this particular issue is you're having one thread performing a find/select against the database while another is attempting to do a delete/insert against the database. There were 2 solutions presented within the thread.
我想知道您的问题是否与此线程中讨论的问题类似：死锁问题与 Hibernate/Spring/MS-SQL。此特定问题的根本原因是您让一个线程对数据库执行查找/选择，而另一个线程正在尝试对数据库执行删除/插入操作。线程中提供了 2 个解决方案。
The first optimized the find and delete code so that it was one SQLcommand instead of two.
第一个优化了查找和删除代码，使其成为一个SQL命令而不是两个命令。
# sql sudo code
# original query
find me this row where this=that
delete this row
# better query
delete this row where this=that
The second recommended solution was to create an index on the COLUMNSthat are being used in the WHEREclauses so that the database no longer lock the ROWbut would now lock the key of the index.
推荐的第二个解决方案是COLUMNS在WHERE子句中使用的上创建索引，以便数据库不再锁定ROW而是现在锁定索引的键。
