Deadlock found when trying to get lock (MySQL 5.0 with innodb)

Hi!

We are using apache ibatis with our MySQL 5.0 database (using innodb
tables), in our web application running on Tomcat 5. Recently we
started getting a number of errors relating to
java.sql.SQLException: Deadlock found when trying to get lock; Try
restarting transaction message from server: "Lock wait timeout
exceeded; try restarting transaction";

We get such errors generally on inserts or updates while applying a
parameter map - see full exception dump below. It takes a tomcat/web
application restart to fix the problem. We've checked "SHOW INNODB
STATUS" on our database and it does not show any deadlock errors (no
LAST DEADLOCK ERROR section).

We would appreciate any suggestions or ideas you have to fix or
alleviate this problem. Thanks :)


Full exception dump:
note: my.application.* is our application code

2006-12-08 23:48:32,347 ERROR [TP-Processor24] servlet.FrameworkServlet
(FrameworkServlet.java:412) - Could not complete request
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient
operation; uncategorized SQLException for SQL []; SQL state [41000];
error code [1205];
--- The error occurred in
my/application/engine/dao/ibatis/maps/Section.xml.
--- The error occurred while applying a parameter map.
--- Check the updateSectionExperience-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: Deadlock found when trying to get
lock; Try restarting transaction message from server: "Lock wait
timeout exceeded; try restarting transaction"; nested exception is
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
my/application/engine/dao/ibatis/maps/Section.xml.
--- The error occurred while applying a parameter map.
--- Check the updateSectionExperience-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: Deadlock found when trying to get
lock; Try restarting transaction message from server: "Lock wait
timeout exceeded; try restarting transaction"
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
my/application/engine/dao/ibatis/maps/Section.xml.
--- The error occurred while applying a parameter map.
--- Check the updateSectionExperience-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: Deadlock found when trying to get
lock; Try restarting transaction message from server: "Lock wait
timeout exceeded; try restarting transaction"
Caused by: java.sql.SQLException: Deadlock found when trying to get
lock; Try restarting transaction message from server: "Lock wait
timeout exceeded; try restarting transaction"
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement. executeUpdate(GeneralStatement.java:91)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update( SqlMapExecutorDelegate.java:500)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMa pSessionImpl.java:85)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMap ClientImpl.java:62)
at
org.springframework.orm.ibatis.SqlMapClientTemplate$10.doInS qlMapClient(SqlMapClientTemplate.java:329)
at
org.springframework.orm.ibatis.SqlMapClientTemplate.execute( SqlMapClientTemplate.java:165)
at
org.springframework.orm.ibatis.SqlMapClientTemplate.update(S qlMapClientTemplate.java:327)
at
org.springframework.orm.ibatis.SqlMapClientTemplate.update(S qlMapClientTemplate.java:349)
at
my.application.engine.dao.ibatis.SqlMapSectionDao.updateSect ionExperience(Unknown
Source)
at
my.application.engine.domain.logic.SystemImpl.updateSectionE xperience(Unknown
Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcce ssorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMe thodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsin gReflection(AopUtils.java:335)
at
org.springframework.aop.framework.ReflectiveMethodInvocation .invokeJoinpoint(ReflectiveMethodInvocation.java:181)
at
org.springframework.aop.framework.ReflectiveMethodInvocation .proceed(ReflectiveMethodInvocation.java:148)
at
org.springframework.transaction.interceptor.TransactionInter ceptor.invoke(TransactionInterceptor.java:96)
at
org.springframework.aop.framework.ReflectiveMethodInvocation .proceed(ReflectiveMethodInvocation.java:170)
at
org.springframework.aop.framework.JdkDynamicAopProxy.invoke( JdkDynamicAopProxy.java:176)
at $Proxy0.updateSectionExperience(Unknown Source)
at
my.application.engine.web.SectionFormController.processFinis h(Unknown
Source)
at
org.springframework.web.servlet.mvc.AbstractWizardFormContro ller.validatePagesAndFinish(AbstractWizardFormController.jav a:667)
at
org.springframework.web.servlet.mvc.AbstractWizardFormContro ller.processFormSubmission(AbstractWizardFormController.java :490)
at
org.springframework.web.servlet.mvc.AbstractFormController.h andleRequestInternal(AbstractFormController.java:259)
at
org.springframework.web.servlet.mvc.AbstractController.handl eRequest(AbstractController.java:139)
at
org.springframework.web.servlet.mvc.SimpleControllerHandlerA dapter.handle(SimpleControllerHandlerAdapter.java:44)
at
org.springframework.web.servlet.DispatcherServlet.doDispatch (DispatcherServlet.java:717)
at
org.springframework.web.servlet.DispatcherServlet.doService( DispatcherServlet.java:658)
at
org.springframework.web.servlet.FrameworkServlet.processRequ est(FrameworkServlet.java:392)
at
org.springframework.web.servlet.FrameworkServlet.doPost(Fram eworkServlet.java:357)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFi lter(ApplicationFilterChain.java:252)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(App licationFilterChain.java:173)
at
org.apache.catalina.core.StandardWrapperValve.invoke(Standar dWrapperValve.java:214)
at
org.apache.catalina.core.StandardValveContext.invokeNext(Sta ndardValveContext.java:104)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPip eline.java:520)
at
org.apache.catalina.core.StandardContextValve.invokeInternal (StandardContextValve.java:198)
at
org.apache.catalina.core.StandardContextValve.invoke(Standar dContextValve.java:152)
at
org.apache.catalina.core.StandardValveContext.invokeNext(Sta ndardValveContext.java:104)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPip eline.java:520)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHo stValve.java:137)
at
org.apache.catalina.core.StandardValveContext.invokeNext(Sta ndardValveContext.java:104)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorRepo rtValve.java:118)
at
org.apache.catalina.core.StandardValveContext.invokeNext(Sta ndardValveContext.java:102)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPip eline.java:520)
at
org.apache.catalina.core.StandardEngineValve.invoke(Standard EngineValve.java:109)
at
org.apache.catalina.core.StandardValveContext.invokeNext(Sta ndardValveContext.java:104)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPip eline.java:520)
at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase. java:929)
at
org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapte r.java:160)
at
org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler. java:300)
at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.ja va:374)
at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java :743)
at
org.apache.jk.common.ChannelSocket.processConnection(Channel Socket.java:675)
at org.apache.jk.common.SocketConnection.runIt(ChannelSocket.ja va:866)
at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.ru n(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: Deadlock found when trying to get
lock; Try restarting transaction message from server: "Lock wait
timeout exceeded; try restarting transaction"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2247)
at
com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.j ava:1371)
at
org.apache.commons.dbcp.DelegatingPreparedStatement.execute( DelegatingPreparedStatement.java:168)
at sun.reflect.GeneratedMethodAccessor40.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMe thodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at
com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.inv oke(PreparedStatementLogProxy.java:62)
at $Proxy3.execute(Unknown Source)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate (SqlExecutor.java:84)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement. sqlExecuteUpdate(GeneralStatement.java:200)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement. executeUpdate(GeneralStatement.java:78)
... 58 more

Caused by:
java.sql.SQLException: Deadlock found when trying to get lock; Try
restarting transaction message from server: "Lock wait timeout
exceeded; try restarting transaction"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2247)
at
com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.j ava:1371)
at
org.apache.commons.dbcp.DelegatingPreparedStatement.execute( DelegatingPreparedStatement.java:168)
at sun.reflect.GeneratedMethodAccessor40.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMe thodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at
com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.inv oke(PreparedStatementLogProxy.java:62)
at $Proxy3.execute(Unknown Source)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate (SqlExecutor.java:84)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement. sqlExecuteUpdate(GeneralStatement.java:200)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement. executeUpdate(GeneralStatement.java:78)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update( SqlMapExecutorDelegate.java:500)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMa pSessionImpl.java:85)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMap ClientImpl.java:62)
at
org.springframework.orm.ibatis.SqlMapClientTemplate$10.doInS qlMapClient(SqlMapClientTemplate.java:329)
at
org.springframework.orm.ibatis.SqlMapClientTemplate.execute( SqlMapClientTemplate.java:165)
at
org.springframework.orm.ibatis.SqlMapClientTemplate.update(S qlMapClientTemplate.java:327)
at
org.springframework.orm.ibatis.SqlMapClientTemplate.update(S qlMapClientTemplate.java:349)
at
my.application.engine.dao.ibatis.SqlMapSectionDao.updateSect ionExperience(Unknown
Source)
at
my.application.engine.domain.logic.SystemImpl.updateSectionE xperience(Unknown
Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcce ssorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMe thodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsin gReflection(AopUtils.java:335)
at
org.springframework.aop.framework.ReflectiveMethodInvocation .invokeJoinpoint(ReflectiveMethodInvocation.java:181)
at
org.springframework.aop.framework.ReflectiveMethodInvocation .proceed(ReflectiveMethodInvocation.java:148)
at
org.springframework.transaction.interceptor.TransactionInter ceptor.invoke(TransactionInterceptor.java:96)
at
org.springframework.aop.framework.ReflectiveMethodInvocation .proceed(ReflectiveMethodInvocation.java:170)
at
org.springframework.aop.framework.JdkDynamicAopProxy.invoke( JdkDynamicAopProxy.java:176)
at $Proxy0.updateSectionExperience(Unknown Source)
at
my.application.engine.web.SectionFormController.processFinis h(Unknown
Source)
at
org.springframework.web.servlet.mvc.AbstractWizardFormContro ller.validatePagesAndFinish(AbstractWizardFormController.jav a:667)
at
org.springframework.web.servlet.mvc.AbstractWizardFormContro ller.processFormSubmission(AbstractWizardFormController.java :490)
at
org.springframework.web.servlet.mvc.AbstractFormController.h andleRequestInternal(AbstractFormController.java:259)
at
org.springframework.web.servlet.mvc.AbstractController.handl eRequest(AbstractController.java:139)
at
org.springframework.web.servlet.mvc.SimpleControllerHandlerA dapter.handle(SimpleControllerHandlerAdapter.java:44)
at
org.springframework.web.servlet.DispatcherServlet.doDispatch (DispatcherServlet.java:717)
at
org.springframework.web.servlet.DispatcherServlet.doService( DispatcherServlet.java:658)
at
org.springframework.web.servlet.FrameworkServlet.processRequ est(FrameworkServlet.java:392)
at
org.springframework.web.servlet.FrameworkServlet.doPost(Fram eworkServlet.java:357)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFi lter(ApplicationFilterChain.java:252)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(App licationFilterChain.java:173)
at
org.apache.catalina.core.StandardWrapperValve.invoke(Standar dWrapperValve.java:214)
at
org.apache.catalina.core.StandardValveContext.invokeNext(Sta ndardValveContext.java:104)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPip eline.java:520)
at
org.apache.catalina.core.StandardContextValve.invokeInternal (StandardContextValve.java:198)
at
org.apache.catalina.core.StandardContextValve.invoke(Standar dContextValve.java:152)
at
org.apache.catalina.core.StandardValveContext.invokeNext(Sta ndardValveContext.java:104)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPip eline.java:520)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHo stValve.java:137)
at
org.apache.catalina.core.StandardValveContext.invokeNext(Sta ndardValveContext.java:104)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorRepo rtValve.java:118)
at
org.apache.catalina.core.StandardValveContext.invokeNext(Sta ndardValveContext.java:102)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPip eline.java:520)
at
org.apache.catalina.core.StandardEngineValve.invoke(Standard EngineValve.java:109)
at
org.apache.catalina.core.StandardValveContext.invokeNext(Sta ndardValveContext.java:104)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPip eline.java:520)
at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase. java:929)
at
org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapte r.java:160)
at
org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler. java:300)
at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.ja va:374)
at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java :743)
at
org.apache.jk.common.ChannelSocket.processConnection(Channel Socket.java:675)
at org.apache.jk.common.SocketConnection.runIt(ChannelSocket.ja va:866)
at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.ru n(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)