java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

[dmalla]

How to Avoid Lock wait timeout exceeded exception?

Hi we are using Alfresco Enterprise v5.0.1 in production. Sometimes users are not able to perform any action on node and when i checked the logs we are getting following error and they are able to perform action after couple of hours once the lock is released. I have researched about this error and got few suggestions like "increasing the lock wait timeout value for InnoDB by setting the innodb_lock_wait_timeout, default is 50 sec and increase to 120 or 500". But I'm not sure whether this will solve our problem or not. Kindly let us know how to avoid these type of issues and help us in solving this issue.

2017-12-11 13:28:00,055 ERROR  [pool-83-thread-1] We've caught an exception!
org.springframework.dao.ConcurrencyFailureException: Failed to update node 3331474; nested exception is org.springframework.dao.CannotAcquireLockException:
### Error updating database. Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
### The error may involve alfresco.node.update_Node-Inline
### The error occurred while setting parameters
### SQL: update alf_node set version = ? , transaction_id = ? , audit_creator = ? , audit_created = ? , audit_modifier = ? , audit_modified = ? , audit_accessed = ? where id = ? and version = (? - 1)
### Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
at org.alfresco.repo.domain.node.AbstractNodeDAOImpl.updateNodeImpl(AbstractNodeDAOImpl.java:1900)
at org.alfresco.repo.domain.node.AbstractNodeDAOImpl.touchNode(AbstractNodeDAOImpl.java:1726)
at org.alfresco.repo.domain.node.AbstractNodeDAOImpl.removeNodeAspects(AbstractNodeDAOImpl.java:2722)
at org.alfresco.repo.node.db.DbNodeServiceImpl.removeAspect(DbNodeServiceImpl.java:835)
at sun.reflect.GeneratedMethodAccessor1207.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.alfresco.repo.lock.mem.LockableAspectInterceptor.invoke(LockableAspectInterceptor.java:221)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at com.sun.proxy.$Proxy13.removeAspect(Unknown Source)
at sun.reflect.GeneratedMethodAccessor1207.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.alfresco.repo.audit.DisableAuditableBehaviourInterceptor.invoke(DisableAuditableBehaviourInterceptor.java:113)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at net.sf.acegisecurity.intercept.method.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:80)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.alfresco.repo.security.permissions.impl.ExceptionTranslatorMethodInterceptor.invoke(ExceptionTranslatorMethodInterceptor.java:46)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.alfresco.repo.audit.AuditMethodInterceptor.invoke(AuditMethodInterceptor.java:159)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.alfresco.repo.transaction.RetryingTransactionInterceptor$1.execute(RetryingTransactionInterceptor.java:79)
at org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:454)
at org.alfresco.repo.transaction.RetryingTransactionInterceptor.invoke(RetryingTransactionInterceptor.java:69)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at com.sun.proxy.$Proxy13.removeAspect(Unknown Source)
at org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:454)
at org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:323)
at org.alfresco.repo.security.authentication.AuthenticationUtil.runAs(AuthenticationUtil.java:548)
at org.alfresco.repo.security.authentication.AuthenticationUtil.runAsSystem(AuthenticationUtil.java:582)
at org.alfresco.repo.security.authentication.AuthenticationUtil.runAs(AuthenticationUtil.java:548)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)

[dmalla]

Please respond to my query and help me.

[afaust]

Is there any long-running operation going on in the background which may have obtained locks and simply does not release them within the default time to wait? Then this error is to be expected and you may need to check that other operation for ways to reduce its impact. So far, I never had to modify the default timeout values for MySQL / InnoDB (at least when I used those - I prefer PostgreSQL nowadays).