Skip navigation links

MySQL Forums :: PHP :: Deadlock found when trying to get lock


Advanced Search

Deadlock found when trying to get lock
Posted by: Chan BaBa ()
Date: May 10, 2007 10:14PM

Deadlock found when trying to get lock; try restarting transaction

This exception is throwed up in this case:

- INNODB table
- auto increment field
- two or more concurrent thread
- SQL command updating via transaction

using MySQL version 5.0.22-standard (on Windows 2000 or Linux)
Error register log & Innodb log are as follows. How to solve this problem ?

error register log as follow:
"
2007¦~5¤ë9¤é ¤U¤Č04:24:16 org.apache.catalina.core.AprLifecycleListener lifecycl
eEvent
¸ę°T: The Apache Tomcat Native library which allows optimal performance in produ
ction environments was not found on the java.library.path: C:\Program Files\Java
\jre1.5.0_10\bin;.;C:\WINNT\system32;C:\WINNT;D:\bea\jdk142_04\bin;C:\WINNT\syst
em32;C:\WINNT;C:\WINNT\System32\Wbem;D:\apache-ant-1.6.2\bin;C:\Documents and Se
ttings\vincent.siu\My Documents
2007¦~5¤ë9¤é ¤U¤Č04:24:16 org.apache.coyote.http11.Http11BaseProtocol init
¸ę°T: Initializing Coyote HTTP/1.1 on http-8080
2007¦~5¤ë9¤é ¤U¤Č04:24:16 org.apache.catalina.startup.Catalina load
¸ę°T: Initialization processed in 1187 ms
2007¦~5¤ë9¤é ¤U¤Č04:24:16 org.apache.catalina.core.StandardService start
¸ę°T: Starting service Catalina
2007¦~5¤ë9¤é ¤U¤Č04:24:16 org.apache.catalina.core.StandardEngine start
¸ę°T: Starting Servlet Engine: Apache Tomcat/5.5.20
2007¦~5¤ë9¤é ¤U¤Č04:24:16 org.apache.catalina.core.StandardHost start
¸ę°T: XML validation disabled
2007¦~5¤ë9¤é ¤U¤Č04:24:17 org.apache.catalina.startup.HostConfig deployWAR
¸ę°T: Deploying web application archive ssms.war
[basic-struts] [2007-05-09 16:24:40,836] ERROR [main] SchemaUpdate.execute(155)
| Unsuccessful: alter table class_teacher add index FK74C2241BD84C1F64 (USER_SYS
_ID), add constraint FK74C2241BD84C1F64 foreign key (USER_SYS_ID) references ser
vice_record (USER_SYS_ID)
[basic-struts] [2007-05-09 16:24:40,851] ERROR [main] SchemaUpdate.execute(156)
| Cannot add or update a child row: a foreign key constraint fails (`ssms/#sql-4
5c_11`, CONSTRAINT `FK74C2241BD84C1F64` FOREIGN KEY (`USER_SYS_ID`) REFERENCES `
service_record` (`USER_SYS_ID`))
[basic-struts] [2007-05-09 16:24:48,804] WARN [main] Settings.getLocale(143) | S
ettings: Could not parse struts.locale setting, substituting default VM locale
2007¦~5¤ë9¤é ¤U¤Č04:24:52 org.apache.coyote.http11.Http11BaseProtocol start
¸ę°T: Starting Coyote HTTP/1.1 on http-8080
2007¦~5¤ë9¤é ¤U¤Č04:24:53 org.apache.jk.common.ChannelSocket init
¸ę°T: JK: ajp13 listening on /0.0.0.0:8009
2007¦~5¤ë9¤é ¤U¤Č04:24:53 org.apache.jk.server.JkMain start
¸ę°T: Jk running ID=0 time=0/31 config=null
2007¦~5¤ë9¤é ¤U¤Č04:24:53 org.apache.catalina.storeconfig.StoreLoader load
¸ę°T: Find registry server-registry.xml at classpath resource
2007¦~5¤ë9¤é ¤U¤Č04:24:53 org.apache.catalina.startup.Catalina start
¸ę°T: Server startup in 36672 ms
[basic-struts] [2007-05-09 16:25:52,133] WARN [http-8080-Processor25] LoginInfoDAOImpl.getLoginInfo(
48) | uh oh, loginInfo 'npac.ssms.model.LoginInfoPk@178a4e8[sessionId=38552714BB607B0589CF1F73081EE0
AD,userSysId=24,logInOutIndicator=IN]' not found...
[basic-struts] [2007-05-09 16:25:55,867] ERROR [http-8080-Processor24] UseMenuDisplayerTag.doStartTa
g(214) | Can't find bundle for base name org.apache.struts.action.MESSAGE, locale en_US
[basic-struts] [2007-05-09 16:25:59,992] WARN [http-8080-Processor23] LoginInfoDAOImpl.getLoginInfo(
48) | uh oh, loginInfo 'npac.ssms.model.LoginInfoPk@14a0f81[sessionId=B8AB57150851EAA6D338EFAB663BFD
9E,userSysId=129,logInOutIndicator=IN]' not found...
[basic-struts] [2007-05-09 16:26:00,898] ERROR [http-8080-Processor22] UseMenuDisplayerTag.doStartTa
g(214) | Can't find bundle for base name org.apache.struts.action.MESSAGE, locale zh_HK
[basic-struts] [2007-05-09 16:26:01,617] WARN [http-8080-Processor22] IndividualAuthDAOImpl.getIndiv
idualAuth(50) | uh oh, individualAuth 'npac.ssms.model.IndividualAuthPk@1855788[menuFuncSysId=31,use
rSysId=129]' not found...
[basic-struts] [2007-05-09 16:26:03,242] WARN [http-8080-Processor23] IndividualAuthDAOImpl.getIndiv
idualAuth(50) | uh oh, individualAuth 'npac.ssms.model.IndividualAuthPk@1fa04e7[menuFuncSysId=4,user
SysId=129]' not found...
[basic-struts] [2007-05-09 16:26:29,273] WARN [http-8080-Processor24] WaitingListDAOImpl.getWaitingL
ist(48) | uh oh, waitingList '229' not found...
[basic-struts] [2007-05-09 16:26:48,992] WARN [http-8080-Processor21] IndividualAuthDAOImpl.getIndiv
idualAuth(50) | uh oh, individualAuth 'npac.ssms.model.IndividualAuthPk@a886a8[menuFuncSysId=4,userS
ysId=129]' not found...
[basic-struts] [2007-05-09 16:26:52,929] WARN [http-8080-Processor21] JDBCExceptionReporter.logExcep
tions(77) | SQL Error: 1213, SQLState: 40001
[basic-struts] [2007-05-09 16:26:52,929] ERROR [http-8080-Processor21] JDBCExceptionReporter.logExce
ptions(78) | Deadlock found when trying to get lock; try restarting transaction
[basic-struts] [2007-05-09 16:26:52,945] ERROR [http-8080-Processor21] AbstractFlushingEventListener
.performExecutions(301) | Could not synchronize database state with session
org.hibernate.exception.LockAcquisitionException: could not update: [npac.ssms.model.ClassPlan#10]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:82)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.jav
a:2375)
at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersi
ster.java:2257)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.jav
a:2557)
at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:92)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingE
ventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:
27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:54)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:419
)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(
AbstractPlatformTransactionManager.java:611)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(Abstrac
tPlatformTransactionManager.java:581)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAft
erReturning(TransactionAspectSupport.java:307)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInte
rceptor.java:117)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvo
cation.java:176)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)
at $Proxy73.register(Unknown Source)
at npac.ssms.action.EnrollmentAction.doRegister(EnrollmentAction.java:187)
at npac.ssms.action.EnrollmentAction.register(EnrollmentAction.java:162)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java
:399)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.
java:262)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:224)
at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorI
nterceptor.java:123)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:219)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:218)
at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:216)
at com.opensymphony.xwork2.interceptor.ParametersInterceptor.intercept(ParametersInterceptor
.java:161)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:219)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:218)
at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:216)
at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:83)

at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:219)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:218)
at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:216)
at com.opensymphony.xwork2.interceptor.PrepareInterceptor.intercept(PrepareInterceptor.java:
115)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:219)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:218)
at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:216)
at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigIntercepto
r.java:170)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:219)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:218)
at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:216)
at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappin
gInterceptor.java:176)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:219)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:218)
at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:216)
at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:50)
at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:507)
at org.apache.struts2.dispatcher.FilterDispatcher.doFilter(FilterDispatcher.java:421)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.j
ava:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

at org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter.doFilterInternal(OpenEn
tityManagerInViewFilter.java:113)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:77
)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.j
ava:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java
:264)
at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityIntercepto
r.java:107)
at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityIntercep
tor.java:72)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java
:274)
at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:
110)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java
:274)
at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcess
ingFilter.java:125)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java
:274)
at org.acegisecurity.wrapper.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContex
tHolderAwareRequestFilter.java:81)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java
:274)
at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:217)

at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java
:274)
at org.acegisecurity.ui.logout.LogoutFilter.doFilter(LogoutFilter.java:106)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java
:274)
at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContext
IntegrationFilter.java:229)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java
:274)
at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148)
at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.j
ava:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncoding
Filter.java:78)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:77
)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.j
ava:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Htt
p11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.ja
va:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)
Caused by: com.mysql.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying t
o get lock; try restarting transaction
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1169)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:693)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1404)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1318)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1303)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatemen
t.java:105)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.jav
a:2357)
... 104 more
[basic-struts] [2007-05-09 16:26:53,054] ERROR [http-8080-Processor21] [default].invoke(253) | Servl
et.service() for servlet default threw exception
javax.persistence.RollbackException: Error while commiting the transaction
at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:71)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:419
)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(
AbstractPlatformTransactionManager.java:611)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(Abstrac
tPlatformTransactionManager.java:581)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAft
erReturning(TransactionAspectSupport.java:307)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInte
rceptor.java:117)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvo
cation.java:176)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)
at $Proxy73.register(Unknown Source)
at npac.ssms.action.EnrollmentAction.doRegister(EnrollmentAction.java:187)
at npac.ssms.action.EnrollmentAction.register(EnrollmentAction.java:162)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java
:399)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.
java:262)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:224)
at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorI
nterceptor.java:123)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:219)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:218)
at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:216)
at com.opensymphony.xwork2.interceptor.ParametersInterceptor.intercept(ParametersInterceptor
.java:161)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:219)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:218)
at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:216)
at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:83)

at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:219)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:218)
at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:216)
at com.opensymphony.xwork2.interceptor.PrepareInterceptor.intercept(PrepareInterceptor.java:
115)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:219)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:218)
at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:216)
at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigIntercepto
r.java:170)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:219)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:218)
at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:216)
at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappin
gInterceptor.java:176)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:219)
at com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.jav
a:218)
at com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:216)
at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:50)
at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:507)
at org.apache.struts2.dispatcher.FilterDispatcher.doFilter(FilterDispatcher.java:421)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.j
ava:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

at org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter.doFilterInternal(OpenEn
tityManagerInViewFilter.java:113)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:77
)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.j
ava:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java
:264)
at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityIntercepto
r.java:107)
at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityIntercep
tor.java:72)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java
:274)
at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:
110)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java
:274)
at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcess
ingFilter.java:125)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java
:274)
at org.acegisecurity.wrapper.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContex
tHolderAwareRequestFilter.java:81)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java
:274)
at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:217)

at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java
:274)
at org.acegisecurity.ui.logout.LogoutFilter.doFilter(LogoutFilter.java:106)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java
:274)
at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContext
IntegrationFilter.java:229)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java
:274)
at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148)
at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.j
ava:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncoding
Filter.java:78)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:77
)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.j
ava:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Htt
p11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.ja
va:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)
Caused by: org.hibernate.exception.LockAcquisitionException: could not update: [npac.ssms.model.Clas
sPlan#10]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:82)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.jav
a:2375)
at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersi
ster.java:2257)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.jav
a:2557)
at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:92)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingE
ventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:
27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:54)
... 92 more
Caused by: com.mysql.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying t
o get lock; try restarting transaction
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1169)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:693)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1404)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1318)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1303)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatemen
t.java:105)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.jav
a:2357)
... 104 more
"

and innodb log :
"

=====================================
070509 16:31:30 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 17 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 110, signal count 110
Mutex spin waits 3378, rounds 7203, OS waits 6
RW-shared spins 42, OS waits 21; RW-excl spins 84, OS waits 80
------------------------
LATEST FOREIGN KEY ERROR
------------------------
070509 16:24:40 Transaction:
TRANSACTION 0 776210, ACTIVE 0 sec, OS thread id 1780 inserting, thread declared
inside InnoDB 500
mysql tables in use 2, locked 2
5 lock struct(s), heap size 320
MySQL thread id 17, query id 23393 localhost 127.0.0.1 ssms_owner copy to tmp ta
ble
alter table class_teacher add index FK74C2241BD84C1F64 (USER_SYS_ID), add constr
aint FK74C2241BD84C1F64 foreign key (USER_SYS_ID) references service_record (USE
R_SYS_ID)
Foreign key constraint fails for table `ssms/#sql-45c_11`:
,
CONSTRAINT `FK74C2241BD84C1F64` FOREIGN KEY (`USER_SYS_ID`) REFERENCES `servic
e_record` (`USER_SYS_ID`)
Trying to add in child table, in index `PRIMARY` tuple:
DATA TUPLE: 11 fields;
0: len 4; hex 80000002; asc ;; 1: len 4; hex 80000002; asc ;; 2: len 6;
hex 0000000bd812; asc ;; 3: len 7; hex 5f737461747573; asc _status;; 4: l
en 4; hex 80000001; asc ;; 5: len 4; hex 80000001; asc ;; 6: len 8; hex
8000124101e7a721; asc A !;; 7: len 4; hex 80000001; asc ;; 8: len 8; he
x 8000124101e7a721; asc A !;; 9: len 4; hex 80000001; asc ;; 10: len 4;
hex 80000001; asc ;;

But in parent table `ssms/service_record`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 4; hex 8000001a; asc ;; 1: len 6; hex 0000000bd1a4; asc ;; 2:
len 7; hex 800000002d011d; asc - ;; 3: SQL NULL; 4: SQL NULL; 5: SQL NULL;
6: SQL NULL; 7: len 4; hex 80000001; asc ;; 8: len 4; hex 80000001; asc
;; 9: len 8; hex 800012410726b743; asc A & C;; 10: len 4; hex 80000001; asc
;; 11: len 8; hex 800012410726b743; asc A & C;; 12: len 4; hex 80000001; a
sc ;; 13: len 7; hex 68766766687663; asc hvgfhvc;; 14: len 4; hex 00000000;
asc ;;

------------------------
LATEST DETECTED DEADLOCK
------------------------
070509 16:26:52
*** (1) TRANSACTION:
TRANSACTION 0 776532, ACTIVE 0 sec, OS thread id 2052 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 15 lock struct(s), heap size 1024, undo log entries 1
MySQL thread id 21, query id 34660 localhost 127.0.0.1 ssms_owner Updating
update class_plan set UPDATED_AT=?, UPDATED_BY=?, REC_STATUS_SYS_ID=?, TOTAL_SES
SIONS=?, DURATION=?, CLASS_REF=?, IN_CHARGE_STAFF_SYS_ID=?, REMARKS=?, CLASS_STA
TUS_SYS_ID=?, CLASS_QUOTA=?, AVAILABLE_QUOTA=?, RESERVED_QUOTA=?, COLLABORATE_UN
IT=?, CLASS_START_DATE=?, CLASS_END_DATE=?, COURSE_SYS_ID=?, ENROLL_START_DATE=?
, ENROLL_END_DATE=?, WAITING_LIST_QUOTA=?, DAY_OF_WEEK=? where CLASS_PLAN_SYS_ID
=? and UPDATED_AT=?
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1150 n bits 128 index `PRIMARY` of table `ssms/c
lass_plan` trx id 0 776532 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 25; compact format; info bits 0

0: len 4; hex 8000000a; asc ;; 1: len 6; hex 0000000bd92f; asc /;; 2:
len 7; hex 000000015d2751; asc ]'Q;; 3: len 4; hex 80000024; asc $;; 4: l
en 17; hex 422d54502d3035342d303730352d303031; asc B-TP-054-0705-001;; 5: len 4;
hex 80000001; asc ;; 6: len 3; hex 8faeae; asc ;; 7: len 3; hex 8faeb5;
asc ;; 8: len 1; hex 82; asc ;; 9: len 2; hex 8046; asc F;; 10: len 2; hex
802a; asc *;; 11: len 2; hex 801c; asc ;; 12: len 2; hex 801c; asc ;; 13: l
en 2; hex 8002; asc ;; 14: len 8; hex 8000124101e59f80; asc A ;; 15: len
8; hex 8000124107bcfc00; asc A ;; 16: len 2; hex 8078; asc x;; 17: len 0
; hex ; asc ;; 18: len 0; hex ; asc ;; 19: len 4; hex 80000001; asc ;; 20: l
en 4; hex 80000001; asc ;; 21: len 8; hex 8000124101e84e47; asc A NG;; 2
2: len 4; hex 80000012; asc ;; 23: len 8; hex 800012410791b085; asc A
;; 24: len 4; hex 80000018; asc ;;

*** (2) TRANSACTION:
TRANSACTION 0 776533, ACTIVE 0 sec, OS thread id 1780 starting index read, threa
d declared inside InnoDB 500
mysql tables in use 1, locked 1
15 lock struct(s), heap size 1024, undo log entries 1
MySQL thread id 17, query id 34662 localhost 127.0.0.1 ssms_owner Updating
update class_plan set UPDATED_AT=?, UPDATED_BY=?, REC_STATUS_SYS_ID=?, TOTAL_SES
SIONS=?, DURATION=?, CLASS_REF=?, IN_CHARGE_STAFF_SYS_ID=?, REMARKS=?, CLASS_STA
TUS_SYS_ID=?, CLASS_QUOTA=?, AVAILABLE_QUOTA=?, RESERVED_QUOTA=?, COLLABORATE_UN
IT=?, CLASS_START_DATE=?, CLASS_END_DATE=?, COURSE_SYS_ID=?, ENROLL_START_DATE=?
, ENROLL_END_DATE=?, WAITING_LIST_QUOTA=?, DAY_OF_WEEK=? where CLASS_PLAN_SYS_ID
=? and UPDATED_AT=?
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1150 n bits 128 index `PRIMARY` of table `ssms/c
lass_plan` trx id 0 776533 lock mode S locks rec but not gap
Record lock, heap no 9 PHYSICAL RECORD: n_fields 25; compact format; info bits 0

0: len 4; hex 8000000a; asc ;; 1: len 6; hex 0000000bd92f; asc /;; 2:
len 7; hex 000000015d2751; asc ]'Q;; 3: len 4; hex 80000024; asc $;; 4: l
en 17; hex 422d54502d3035342d303730352d303031; asc B-TP-054-0705-001;; 5: len 4;
hex 80000001; asc ;; 6: len 3; hex 8faeae; asc ;; 7: len 3; hex 8faeb5;
asc ;; 8: len 1; hex 82; asc ;; 9: len 2; hex 8046; asc F;; 10: len 2; hex
802a; asc *;; 11: len 2; hex 801c; asc ;; 12: len 2; hex 801c; asc ;; 13: l
en 2; hex 8002; asc ;; 14: len 8; hex 8000124101e59f80; asc A ;; 15: len
8; hex 8000124107bcfc00; asc A ;; 16: len 2; hex 8078; asc x;; 17: len 0
; hex ; asc ;; 18: len 0; hex ; asc ;; 19: len 4; hex 80000001; asc ;; 20: l
en 4; hex 80000001; asc ;; 21: len 8; hex 8000124101e84e47; asc A NG;; 2
2: len 4; hex 80000012; asc ;; 23: len 8; hex 800012410791b085; asc A
;; 24: len 4; hex 80000018; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1150 n bits 128 index `PRIMARY` of table `ssms/c
lass_plan` trx id 0 776533 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 25; compact format; info bits 0

0: len 4; hex 8000000a; asc ;; 1: len 6; hex 0000000bd92f; asc /;; 2:
len 7; hex 000000015d2751; asc ]'Q;; 3: len 4; hex 80000024; asc $;; 4: l
en 17; hex 422d54502d3035342d303730352d303031; asc B-TP-054-0705-001;; 5: len 4;
hex 80000001; asc ;; 6: len 3; hex 8faeae; asc ;; 7: len 3; hex 8faeb5;
asc ;; 8: len 1; hex 82; asc ;; 9: len 2; hex 8046; asc F;; 10: len 2; hex
802a; asc *;; 11: len 2; hex 801c; asc ;; 12: len 2; hex 801c; asc ;; 13: l
en 2; hex 8002; asc ;; 14: len 8; hex 8000124101e59f80; asc A ;; 15: len
8; hex 8000124107bcfc00; asc A ;; 16: len 2; hex 8078; asc x;; 17: len 0
; hex ; asc ;; 18: len 0; hex ; asc ;; 19: len 4; hex 80000001; asc ;; 20: l
en 4; hex 80000001; asc ;; 21: len 8; hex 8000124101e84e47; asc A NG;; 2
2: len 4; hex 80000012; asc ;; 23: len 8; hex 800012410791b085; asc A
;; 24: len 4; hex 80000018; asc ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 0 776556
Purge done for trx's n:o < 0 776535 undo n:o < 0 0
History list length 10
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 1612
MySQL thread id 23, query id 36637 localhost 127.0.0.1 root
show innodb status
---TRANSACTION 0 776531, not started, OS thread id 1928
MySQL thread id 19, query id 34653 localhost 127.0.0.1 ssms_owner
---TRANSACTION 0 776555, not started, OS thread id 2052
MySQL thread id 21, query id 36078 localhost 127.0.0.1 ssms_owner
---TRANSACTION 0 776449, not started, OS thread id 1764
MySQL thread id 20, query id 26452 localhost 127.0.0.1 ssms_owner
---TRANSACTION 0 776332, not started, OS thread id 1924
MySQL thread id 16, query id 24512 localhost 127.0.0.1 ssms_owner
---TRANSACTION 0 776530, not started, OS thread id 1784
MySQL thread id 15, query id 34646 localhost 127.0.0.1 ssms_owner
---TRANSACTION 0 776533, not started, OS thread id 1780
MySQL thread id 17, query id 34669 localhost 127.0.0.1 ssms_owner
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1944 OS file reads, 328 OS file writes, 114 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
3 inserts, 3 merged recs, 3 merges
Hash table size 34679, used cells 1086, node heap has 2 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 82622399
Log flushed up to 0 82622399
Last checkpoint at 0 82622399
0 pending log writes, 0 pending chkp writes
61 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 15062354; in additional pool allocated 2097152
Buffer pool size 512
Free buffers 0
Database pages 510
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1813, created 27, written 248
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 1628, state: waiting for server activity
Number of rows inserted 15, updated 9, deleted 0, read 123371
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Options: ReplyQuote


Subject Written By Posted
Deadlock found when trying to get lock Chan BaBa 05/10/2007 10:14PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.