Alfresco Lock wait timeout exceeded exception

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Member II

Alfresco Lock wait timeout exceeded exception

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
1 Reply
Highlighted
Member II

Re: Alfresco Lock wait timeout exceeded exception

Fixed following these guidelines:

Alfresco tuning part1