AnsweredAssumed Answered

Alfresco Lock wait timeout exceeded exception

Question asked by massimo.callisto on Feb 7, 2017
Latest reply on Feb 14, 2017 by massimo.callisto

Hi,

we are using an Alfresco Community 5.0 in which the registered (nominal) users are around 2k. In some cases we get an exception from Alfresco logs that I reported below.

Here the configuration:

  • Server Alfresco Ubuntu 14 64 bit/12GB RAM/8 vcpu cores
  • Server MySQL Ubuntu 14 64 bit/12GB RAM/8 vcpu cores

 

Alfresco global properties about database is the following:

db.driver=com.mysql.jdbc.Driver 
db.username=alfresco
db.password=*****
db.name=alfresco
db.url=jdbc:mysql://*****:3306/alfresco?useUnicode=yes&characterEncoding=UTF-8
db.pool.max=275
db.pool.validate.query=SELECT 1

Database max_connections is about 300.

 

Please, do you have any suggestion about hot to solve ot make the proper tuning on Alfresco/database?

 

2017-02-07 08:50:51,325  ERROR [post.cleanup.PostCleaner] [DefaultScheduler_Worker-5] Exception during cleanup of posts  org.springframework.dao.CannotAcquireLockException: ### Error updating database.  Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction 
### The error may involve alfresco.activities.delete_activity_posts_older_than_date-Inline ### The error occurred while setting parameters ### SQL: delete from alf_activity_post        where post_date < ?       and status = ? ### 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.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:265)        
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)        
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)        
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371)        
at com.sun.proxy.$Proxy12.delete(Unknown Source)        
at org.mybatis.spring.SqlSessionTemplate.delete(SqlSessionTemplate.java:268)        
at org.alfresco.repo.domain.activities.ibatis.ActivityPostDAOImpl.deletePosts(ActivityPostDAOImpl.java:102)        
at org.alfresco.repo.activities.post.cleanup.PostCleaner.executeWithLock(PostCleaner.java:146)        
at org.alfresco.repo.activities.post.cleanup.PostCleaner.execute(PostCleaner.java:117)        
at org.alfresco.repo.activities.post.cleanup.PostCleanupJob.execute(PostCleanupJob.java:50)        
at org.quartz.core.JobRunShell.run(JobRunShell.java:216)        
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:563)
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction        
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094)        
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4208)        
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4140)        
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)        
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)        
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2826)        
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)        
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1302)        
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)        
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)        
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)        
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:44)        
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:69)        
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:48)        
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:105)        
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:71)        
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:152)        
at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:165)        
at sun.reflect.GeneratedMethodAccessor432.invoke(Unknown Source)        
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)        
at java.lang.reflect.Method.invoke(Method.java:483)        
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)         ... 8 more

Outcomes