Deadlock exception during timer job executions

[ganeshr]

Hi,

  I'm seeing the below exception in my log file more frequently. Not sure why this exception is occurring.
  We are using activiti 5.15. Can any help me resolving this issue…

Here is the log message:

org.activiti.engine.impl.jobexecutor.AcquireJobsRunnable:97 - exception during job acquisition:
### Error updating database.  Cause: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

### The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.updateTimer-Inline
### The error occurred while setting parameters
### SQL: update ACT_RU_JOB      SET REV_ =  ?,        LOCK_EXP_TIME_ = ?,        LOCK_OWNER_ = ?,        RETRIES_ = ?,        EXCEPTION_STACK_ID_ = ?,        EXCEPTION_MSG_ = ?,        DUEDATE_ = ?      where ID_= ?       and REV_ = ?
### Cause: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

### The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.updateTimer-Inline
### The error occurred while setting parameters
### SQL: update ACT_RU_JOB      SET REV_ =  ?,        LOCK_EXP_TIME_ = ?,        LOCK_OWNER_ = ?,        RETRIES_ = ?,        EXCEPTION_STACK_ID_ = ?,        EXCEPTION_MSG_ = ?,        DUEDATE_ = ?      where ID_= ?       and REV_ = ?
### Cause: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

   at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:147)
   at org.activiti.engine.impl.db.DbSqlSession.flushUpdates(DbSqlSession.java:558)
   at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:442)
   at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:169)
   at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:116)
   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:70)
   at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
   at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:131)
   at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
   at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31)
   at org.activiti.engine.impl.jobexecutor.AcquireJobsRunnable.run(AcquireJobsRunnable.java:59)
   at java.lang.Thread.run(Thread.java:744)
Caused by: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
   at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
   at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
   at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
   at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
   at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
   at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010)
   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
   at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
   at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3677)
   at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
   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:41)
   at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)
   at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)
   at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:108)
   at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:145)
   … 11 more

[jbarrez]

Which version? The latest version has many bugfixes wrt these kind of problems.

[ganeshr]

We are using 5.17 version.

[jbarrez]

Then it is certainly worthwhile upgrading to the latest version. A lot of changes have been made in this area.

[elpyaz]

I am facing the same issue like ganeshr. 

Hi ganeshr any luck?

Hi Barrez,  could you please help me, how can I disable jobExecutors on few nodes. 

If we enable the executor on few nodes, how to handle the fail over

[ganeshr]

We have upgraded to latest version at that time and used async job executor instead. And all of our nodes have async job executor enabled. 

We are using below configuration,  please verify and may be this might help you.

<bean id="processEngineConfiguration" class="org.activiti.spring.SpringProcessEngineConfiguration">

<property name="databaseType" value="oracle" />
<property name="dataSource" ref="dataSource" />
<property name="transactionManager" ref="workflowTransactionManager" />
<property name="history" value="full" />
<property name="databaseSchemaUpdate" value="false" />
<property name="jobExecutorActivate" value="false" />
<property name="asyncExecutorEnabled" value="true" />
<property name="asyncExecutorActivate" value="true" />
<property name="bulkInsertEnabled" value="true" />
<property name="processEngineName" value="default"></property>
<property name="enableDatabaseEventLogging" value="false" />
<property name="delegateExpressionFieldInjectionMode" value= "MIXED" />
<property name="idGenerator">
<bean class="org.activiti.engine.impl.persistence.StrongUuidGenerator" />
</property>
<property name="customMybatisMappers">
<set>
<value>com.workflow.customsql.CustomSqlQueryMapper</value>
</set>
</property>
<property name="typedEventListeners">
<map>
<entry key="VARIABLE_UPDATED">
<list>
<ref bean="variableUpdateEventListner" />
</list>
</entry>
</map>
</property>
</bean>