topic Activiti Deadlock and Optimistic Exceptions in Alfresco Forum https://connect.hyland.com/t5/alfresco-forum/activiti-deadlock-and-optimistic-exceptions/m-p/34488#M14550 <HTML><HEAD></HEAD><BODY><P>Hi all,</P><P></P><P><STRONG>Problem</STRONG> :</P><P>Recently when we started doing load testing we encountered a lot of activiti optimistic exceptions and deadlock exceptions.</P><P></P><P><STRONG>Configuration</STRONG> :</P><UL><LI>We are using Activiti 5.19 for managing our workflows.</LI><LI>We have very large running processes which take 6 hours. We have multiple nodes (4) behind our load balancer. We have one job executor per node configured.</LI><LI>All our processes are activiti:async = true, activiti:exclusive =true.</LI><LI>We have a lot of call activities nested inside the flows. First flow to Last flow we have around 11 nested flows inside.</LI><LI>We have many multi instance calls through out the workflow. And we have lot of scenarios where parallel sub process end at the same time.&nbsp;</LI><LI>We have Audit level logging - processEngineConfiguration.setHistoryLevel(HistoryLevel.AUDIT);</LI><LI>So I was wondering if you can suggest us some configuration that we need to do when using multiple nodes to avoid these deadlocks or optimistic exceptions.</LI></UL><P><STRONG>Spring process configuration:</STRONG></P><PRE style="color: #000000; background-color: #ffffff; font-size: 10.8pt;">processEngineConfiguration.setDataSource(dataSource);<BR /> processEngineConfiguration.setDatabaseSchemaUpdate(<SPAN style="color: #008000; font-weight: bold;">"true"</SPAN>);<BR /> processEngineConfiguration.setHistoryLevel(HistoryLevel.AUDIT);<BR /> processEngineConfiguration.setAsyncExecutorEnabled(<SPAN style="color: #000080; font-weight: bold;">true</SPAN>);<BR /> processEngineConfiguration.setAsyncExecutorActivate(<SPAN style="color: #000080; font-weight: bold;">true</SPAN>);<BR /> processEngineConfiguration.setFailedJobCommandFactory(<SPAN style="color: #000080; font-weight: bold;">new </SPAN>NoRetryFailedJobCommandFactory());<BR /> processEngineConfiguration.setSchemaCommandConfig(<SPAN style="color: #000080; font-weight: bold;">new </SPAN>CommandConfig());<BR /><SPAN style="color: #808080;">// processEngineConfiguration.setAsyncExecutor(getCustomJobExecutor());<BR /></SPAN><SPAN style="color: #808080;"> //setting activiti optimistic locking exception retry<BR /></SPAN><SPAN style="color: #808080;"> </SPAN>List&lt;CommandInterceptor&gt; listOfInterceptors = <SPAN style="color: #000080; font-weight: bold;">new </SPAN>ArrayList&lt;&gt;();<BR /> RetryInterceptor retryInterceptor = <SPAN style="color: #000080; font-weight: bold;">new </SPAN>RetryInterceptor();<BR /> retryInterceptor.setNumOfRetries(<SPAN style="color: #0000ff;">6</SPAN>);<BR /> retryInterceptor.setWaitTimeInMs(<SPAN style="color: #0000ff;">1000</SPAN>);<BR /> retryInterceptor.setWaitIncreaseFactor(<SPAN style="color: #0000ff;">5</SPAN>);<BR /> listOfInterceptors.add(retryInterceptor);<BR /> processEngineConfiguration.setCustomPreCommandInterceptors(listOfInterceptors);</PRE><P>Mysql DB Configuration :</P><PRE style="color: #000000; background-color: #ffffff; font-size: 10.8pt;"><SPAN style="color: #008000; font-weight: bold;">db.initialsize</SPAN>: <SPAN style="color: #0000ff;">50</SPAN>,<BR /><SPAN style="color: #008000; font-weight: bold;">db.maxactive</SPAN>: <SPAN style="color: #0000ff;">50</SPAN>,<BR /><SPAN style="color: #008000; font-weight: bold;">db.maxidle</SPAN>: <SPAN style="color: #0000ff;">15</SPAN>,<BR /><SPAN style="color: #008000; font-weight: bold;">db.minidle</SPAN>: <SPAN style="color: #0000ff;">5</SPAN>,<BR /><SPAN style="color: #008000; font-weight: bold;">db.maxwait</SPAN>: <SPAN style="color: #0000ff;">10000</SPAN>,<BR /><SPAN style="color: #008000; font-weight: bold;">db.minEvictableIdleTimeMillis</SPAN>: <SPAN style="color: #0000ff;">50000</SPAN>,<BR /><SPAN style="color: #008000; font-weight: bold;">db.timeBetweenEvictionRunsMillis</SPAN>: <SPAN style="color: #0000ff;">30000</SPAN>,</PRE><P>Exception details :</P><P></P><P><STRONG>Optimistic Exception Details :</STRONG></P><P>[pool-2-thread-4] ERROR o.a.e.i.a.ExecuteAsyncRunnable - Job 200444800 failed<BR />org.activiti.engine.ActivitiException: 6 retries failed with ActivitiOptimisticLockingException. Giving up.<BR /> at org.activiti.engine.impl.interceptor.RetryInterceptor.execute(RetryInterceptor.java:58) ~[activiti-engine-5.19.0.jar:5.19.0]<BR /> at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40) ~[activiti-engine-5.19.0.jar:5.19.0]<BR /> at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35) ~[activiti-engine-5.19.0.jar:5.19.0]<BR /> at org.activiti.engine.impl.asyncexecutor.ExecuteAsyncRunnable.run(ExecuteAsyncRunnable.java:69) ~[activiti-engine-5.19.0.jar:5.19.0]<BR /> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_144]<BR /> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_144]<BR /> at java.lang.Thread.run(Thread.java:748) [na:1.8.0_144]</P><P></P><P><STRONG>Deadlock Exception details :</STRONG></P><P>org.apache.ibatis.exceptions.PersistenceException: <BR />### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction<BR />### The error may involve org.activiti.engine.impl.persistence.entity.ExecutionEntity.updateExecution-Inline<BR />### The error occurred while setting parameters<BR />### SQL: update ACT_RU_EXECUTION set REV_ = ?, BUSINESS_KEY_ = ?, PROC_DEF_ID_ = ?, ACT_ID_ = ?, IS_ACTIVE_ = ?, IS_CONCURRENT_ = ?, IS_SCOPE_ = ?, IS_EVENT_SCOPE_ = ?, PARENT_ID_ = ?, SUPER_EXEC_ = ?, SUSPENSION_STATE_ = ?, CACHED_ENT_STATE_ = ?, NAME_ = ? where ID_ = ? and REV_ = ?<BR />### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction<BR /> at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)<BR /> at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:150)<BR /> at org.activiti.engine.impl.db.DbSqlSession.flushUpdates(DbSqlSession.java:871)<BR /> at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:612)<BR /> at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:212)<BR /> at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:138)<BR /> at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:66)<BR /> at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:47)<BR /> at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)<BR /> at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:45)<BR /> at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:37)<BR /> at org.activiti.engine.impl.interceptor.RetryInterceptor.execute(RetryInterceptor.java:49)<BR /> at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40)<BR /> at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35)<BR /> at org.activiti.engine.impl.asyncexecutor.ExecuteAsyncRunnable.run(ExecuteAsyncRunnable.java:69)<BR /> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)<BR /> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)<BR /> at java.lang.Thread.run(Thread.java:748)<BR />Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction<BR /> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)<BR /> at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)<BR /> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)<BR /> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)<BR /> at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)<BR /> at com.mysql.jdbc.Util.getInstance(Util.java:408)<BR /> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:951)<BR /> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)<BR /> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)<BR /> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)<BR /> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)<BR /> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2501)<BR /> at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)<BR /> at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)<BR /> at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)<BR /> at sun.reflect.GeneratedMethodAccessor80.invoke(Unknown Source)<BR /> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)<BR /> at java.lang.reflect.Method.invoke(Method.java:498)<BR /> at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:55)<BR /> at com.sun.proxy.$Proxy140.execute(Unknown Source)<BR /> at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)<BR /> at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)<BR /> at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)<BR /> at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100)<BR /> at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)<BR /> at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)<BR /> ... 16 more</P><P></P><P>org.apache.ibatis.exceptions.PersistenceException: <BR />### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction<BR />### The error may involve org.activiti.engine.impl.persistence.entity.HistoricVariableInstanceEntity.updateHistoricVariableInstance-Inline<BR />### The error occurred while setting parameters<BR />### SQL: update ACT_HI_VARINST set REV_ = ?, BYTEARRAY_ID_ = ?, DOUBLE_ = ?, LONG_ = ?, TEXT_ = ?, TEXT2_ = ?, VAR_TYPE_ = ?, LAST_UPDATED_TIME_ = ? where ID_ = ? and REV_ = ?<BR />### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction<BR /> at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)<BR /> at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:150)<BR /> at org.activiti.engine.impl.db.DbSqlSession.flushUpdates(DbSqlSession.java:871)<BR /> at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:612)<BR /> at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:212)<BR /> at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:138)<BR /> at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:66)<BR /> at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:47)<BR /> at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)<BR /> at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:45)<BR /> at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:37)<BR /> at org.activiti.engine.impl.interceptor.RetryInterceptor.execute(RetryInterceptor.java:49)<BR /> at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40)<BR /> at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35)<BR /> at org.activiti.engine.impl.asyncexecutor.ExecuteAsyncRunnable.run(ExecuteAsyncRunnable.java:69)<BR /> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)<BR /> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)<BR /> at java.lang.Thread.run(Thread.java:748)<BR />Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction<BR /> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)<BR /> at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)<BR /> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)<BR /> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)<BR /> at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)<BR /> at com.mysql.jdbc.Util.getInstance(Util.java:408)<BR /> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:951)<BR /> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)<BR /> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)<BR /> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)<BR /> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)<BR /> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2501)<BR /> at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)<BR /> at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)<BR /> at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)<BR /> at sun.reflect.GeneratedMethodAccessor105.invoke(Unknown Source)<BR /> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)<BR /> at java.lang.reflect.Method.invoke(Method.java:498)<BR /> at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:55)<BR /> at com.sun.proxy.$Proxy141.execute(Unknown Source)<BR /> at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)<BR /> at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)<BR /> at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)<BR /> at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100)<BR /> at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)<BR /> at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)<BR /> ... 16 more</P></BODY></HTML> Fri, 20 Oct 2017 16:55:05 GMT archana071 2017-10-20T16:55:05Z Activiti Deadlock and Optimistic Exceptions https://connect.hyland.com/t5/alfresco-forum/activiti-deadlock-and-optimistic-exceptions/m-p/34488#M14550 Hi all,Problem :Recently when we started doing load testing we encountered a lot of activiti optimistic exceptions and deadlock exceptions.Configuration :We are using Activiti 5.19 for managing our workflows.We have very large running processes which take 6 hours. We have multiple nodes (4) behind o Fri, 20 Oct 2017 16:55:05 GMT https://connect.hyland.com/t5/alfresco-forum/activiti-deadlock-and-optimistic-exceptions/m-p/34488#M14550 archana071 2017-10-20T16:55:05Z Re: Activiti Deadlock and Optimistic Exceptions https://connect.hyland.com/t5/alfresco-forum/activiti-deadlock-and-optimistic-exceptions/m-p/34489#M14551 <HTML><HEAD></HEAD><BODY><P>I'd be interested in what your async tasks are doing. Are the ones that hit these issues short-lived or long-lived? It would be great if we could put together a reproducer. Apologies that it has been a while (I came across this when looking at another forum thread). Your use-case is certainly of interest (and architecturally sounds like the kind of case we want Activiti 7 to help with).</P></BODY></HTML> Tue, 13 Feb 2018 10:16:36 GMT https://connect.hyland.com/t5/alfresco-forum/activiti-deadlock-and-optimistic-exceptions/m-p/34489#M14551 ryandawson 2018-02-13T10:16:36Z