AnsweredAssumed Answered

Deadlock problem with 'delete from ACT_GE_BYTEARRAY...'

Question asked by saram1 on Mar 25, 2014
Latest reply on Apr 8, 2014 by saram1
Hello

I use engine to manage my workflow. It's well developed and had no problem so far. I prepared some final performance tests because in production it can reach 10k open processes. So I prepared simple workflow with random wait timer node. Start parameters was:
processes: 10k
random range: 3600s
random bias: 600s
So waitTime is calculated like:
new Random().nextInt(3600) + 600;

In my test environment after 600s i have spawned ca 1200 processes. So at this point spawning and resuming goes in parallel. The first occurrence of problem is at ca 3300 processes spawned and ca 100 processes resumed. So we can assume that there is ca 3k processes suspended.
Below is error log:
<blockcode>
2014-03-25 09:35:57.409 ERROR [org.activiti.engine.impl.interceptor.CommandContext] Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: java.sql.SQLException: Transaction (Process ID 250) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
### The error may involve org.activiti.engine.impl.persistence.entity.ByteArrayEntity.deleteByteArray-Inline
### The error occurred while setting parameters
### SQL: delete from ACT_GE_BYTEARRAY where ID_ = ? and REV_ = ?
### Cause: java.sql.SQLException: Transaction (Process ID 250) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
   at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:150)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:161)
   at org.activiti.engine.impl.db.DbSqlSession$CheckedDeleteOperation.execute(DbSqlSession.java:225)
   at org.activiti.engine.impl.db.DbSqlSession.flushDeletes(DbSqlSession.java:577)
   at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:445)
   at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:170)
   at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:117)
   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:66)
   at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31)
   at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40)
   at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35)
   at org.activiti.engine.impl.jobexecutor.ExecuteJobsRunnable.run(ExecuteJobsRunnable.java:46)
   at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
   at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Transaction (Process ID 250) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
   at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
   at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2886)
   at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2328)
   at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:638)
   at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:614)
   at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:573)
   at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:761)
   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:100)
   at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)
   … 14 more
</blockcode>

When I break process spawning loop I noticed that deadlock still occurs. The frequency was ca 1 error for 100 resumed processes. IMO error occurs after resuming process and failed transaction is NOT rerun by engine. It resulting resumed process finishes with success but engine tries rerun it after some time (I guess that suspend process state still persists in DB). That means process resume operation is not atomic. I do not want to point you on AE possible error or something. It's also possible there there is problem with database configuration or something. I read it happens before to some guys, but I could not find proper solution so far…
My point is to handle this error and apply workaround. I have two possibilities:
  • rerun killed transaction
  • prevent WE to resume suspended process (JobExecutor'll try resume it next time)
Here come my question. What is best and simplest way to made it?



If you need more details of my implementation here it comes:
<li> BPMN20

<definitions id="definitions" targetNamespace="http://activiti.org/bpmn20"
   xmlns:activiti="http://activiti.org/bpmn" xmlns="http://www.omg.org/spec/BPMN/20100524/MODEL">
   <process id="AQ_Wait" name="AQ_Wait">
      <sequenceFlow sourceRef='theStart' targetRef='configureWorker' />
      <sequenceFlow sourceRef='configureWorker' targetRef='stage1_prepare' />      
      <sequenceFlow sourceRef='stage1_prepare' targetRef='timerEvent1' />      
        <intermediateCatchEvent id="timerEvent1" name="timerEvent1">
            <timerEventDefinition>
                <timeDuration>${nodeResult}</timeDuration>
            </timerEventDefinition>
        </intermediateCatchEvent>
      <sequenceFlow sourceRef='timerEvent1' targetRef='stage2_final' />      
      <sequenceFlow sourceRef='stage2_final' targetRef='assembleResponse' />
      <sequenceFlow sourceRef='assembleResponse' targetRef='activityCollector' />
      <sequenceFlow sourceRef='activityCollector' targetRef='theEnd' />
      
      <startEvent id="theStart" />
      <serviceTask id="configureWorker"
         activiti:class="com.*.*.workflow.AqWait" >
      </serviceTask>
      <serviceTask id="stage1_prepare"
          activiti:expression="#{worker.stage1prepare()}"
          activiti:resultVariable="nodeResult" >
      </serviceTask>      
      <serviceTask id="stage2_final"
          activiti:expression="#{worker.stage2final()}"
          activiti:resultVariable="nodeResult" >
      </serviceTask>      
      <serviceTask id="assembleResponse"
         activiti:expression="#{worker.assembleResponse(nodeResult)}"
         activiti:resultVariable="jpdl_result"/>
      <serviceTask id="activityCollector" activiti:delegateExpression="${activityCollector}" />
      <endEvent id="theEnd" />
   </process>
</definitions>

<li> applicationContext

   <bean id="processEngine" class="org.activiti.spring.ProcessEngineFactoryBean">
      <property name="processEngineConfiguration" ref="processEngineConfiguration" />
   </bean>

   <bean id="processEngineConfiguration"
      class="org.activiti.engine.impl.cfg.StandaloneProcessEngineConfiguration">
      <property name="jdbcDriver" value="net.sourceforge.jtds.jdbc.Driver" />
      <property name="jdbcUrl" value="jdbc:jtds:sqlserver://192.168.104.57:1433;databaseName=test" />
      <property name="jdbcUsername" value="sa" />
      <property name="jdbcPassword" value="*" />
      <property name="databaseSchemaUpdate" value="true" />
      <property name="jobExecutorActivate" value="false" />
      <property name="mailServerHost" value="mail.my-corp.com" />
      <property name="mailServerPort" value="5025" />
      <property name="classLoader" ref="deploymentClassLoader" />
      <property name="customDefaultBpmnParseHandlers">
         <list>
            <ref bean="processStopListener"/>  
         </list>
      </property>
      <property name="history" value="full" />      
   </bean>

<li> maven deps

  <dependencies>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-aop</artifactId>
      <version>3.2.2.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework.ws</groupId>
      <artifactId>spring-ws-core</artifactId>
      <version>2.1.2.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-aspects</artifactId>
      <version>3.2.2.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>3.2.2.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>3.2.2.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jms</artifactId>
      <version>3.2.2.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-orm</artifactId>
      <version>3.2.2.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>3.2.2.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-web</artifactId>
      <version>3.2.2.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>3.2.2.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework.security</groupId>
      <artifactId>spring-security-core</artifactId>
      <version>3.1.3.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework.security</groupId>
      <artifactId>spring-security-web</artifactId>
      <version>3.1.3.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework.security</groupId>
      <artifactId>spring-security-config</artifactId>
      <version>3.1.3.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>3.2.2.RELEASE</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework.webflow</groupId>
      <artifactId>spring-webflow</artifactId>
      <version>2.3.1.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>com.caucho</groupId>
      <artifactId>hessian</artifactId>
      <version>3.1.5</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-entitymanager</artifactId>
      <version>4.2.4.Final</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>c3p0</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.2.1</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-log4j12</artifactId>
      <version>1.6.6</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>hsqldb</groupId>
      <artifactId>hsqldb</artifactId>
      <version>1.8.0.7</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>com.oracle</groupId>
      <artifactId>ojdbc6</artifactId>
      <version>11.2.0.3</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>net.sourceforge.jtds</groupId>
      <artifactId>jtds</artifactId>
      <version>1.2.7-vx-p-1</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.2.2</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>joda-time</groupId>
      <artifactId>joda-time</artifactId>
      <version>2.2</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
      <version>1.3.172</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-core</artifactId>
      <version>4.2.4.Final</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-io</artifactId>
      <version>1.3.2</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.activiti</groupId>
      <artifactId>activiti-bpmn-model</artifactId>
      <version>5.14</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.activiti</groupId>
      <artifactId>activiti-bpmn-converter</artifactId>
      <version>5.14</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.activiti</groupId>
      <artifactId>activiti-engine</artifactId>
      <version>5.14</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.activiti</groupId>
      <artifactId>activiti-spring</artifactId>
      <version>5.14</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.activiti</groupId>
      <artifactId>activiti-simple-workflow</artifactId>
      <version>5.14</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>servlet-api</artifactId>
      <version>2.5</version>
      <scope>provided</scope>
    </dependency>
  </dependencies>

Outcomes