Transaction Deadlock

[flavio_donze]

I get the following exception wenn I run two workflows at the same time.

com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
   at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:213) ~[sqljdbc4.jar:na]
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493) ~[sqljdbc4.jar:na]
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388) ~[sqljdbc4.jar:na]
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338) ~[sqljdbc4.jar:na]
   at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5574) ~[sqljdbc4.jar:na]
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1734) ~[sqljdbc4.jar:na]
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179) ~[sqljdbc4.jar:na]
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154) ~[sqljdbc4.jar:na]
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:320) ~[sqljdbc4.jar:na]
   at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) ~[org.apache.commons.dbcp_1.4.0.jar:1.4]
   at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) ~[org.apache.commons.dbcp_1.4.0.jar:1.4]
   at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41) ~[mybatis-3.1.1.jar:3.1.1]
   at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66) ~[mybatis-3.1.1.jar:3.1.1]
   at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45) ~[mybatis-3.1.1.jar:3.1.1]
   at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:108) ~[mybatis-3.1.1.jar:3.1.1]
   at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75) ~[mybatis-3.1.1.jar:3.1.1]
   at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:145) ~[mybatis-3.1.1.jar:3.1.1]
   at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:158) ~[mybatis-3.1.1.jar:3.1.1]
   at org.activiti.engine.impl.db.DbSqlSession$DeleteById.execute(DbSqlSession.java:146) ~[activiti-engine-5.10.jar:5.10]
   at org.activiti.engine.impl.db.DbSqlSession.flushDeletes(DbSqlSession.java:483) ~[activiti-engine-5.10.jar:5.10]
   at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:371) ~[activiti-engine-5.10.jar:5.10]
   at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:157) ~[activiti-engine-5.10.jar:5.10]
   at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:109) ~[activiti-engine-5.10.jar:5.10]
   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:49) ~[activiti-engine-5.10.jar:5.10]
   at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42) ~[activiti-spring-5.10.jar:na]
   at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130) ~[org.springframework.transaction_3.0.5.RELEASE.jar:3.0.5.RELEASE]
   at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40) ~[activiti-spring-5.10.jar:na]
   at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33) ~[activiti-engine-5.10.jar:5.10]
   at org.activiti.engine.impl.RuntimeServiceImpl.signal(RuntimeServiceImpl.java:137) ~[activiti-engine-5.10.jar:5.10]
   at com.softmodeler.workflow.service.impl.WorkflowService.start(WorkflowService.java:221) ~[com.softmodeler.workflow/:na]
   at com.softmodeler.workflow.service.impl.WorkflowService.startWorkflow(WorkflowService.java:186) ~[com.softmodeler.workflow/:na]
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I found out (http://stackoverflow.com/questions/6662504/activiti-deadlock-occurs-under-jmeter-performance-test) that I have to remove the "receiveTask" that I added.
The problem is that I added this "receiveTask" only for the reason that activiti stores its workflow into the db (http://forums.activiti.org/en/viewtopic.php?f=6&t=2375&start=0).

This is the workflow definition part:

      <sequenceFlow id="startToWait" sourceRef="start" targetRef="wait"/>
      <receiveTask id="wait" name="wait"/>
      <sequenceFlow id="waitToTranslate" sourceRef="wait" targetRef="translate"/>   
‍‍‍‍‍

On the code side I then to this.

      try {
         identiyService.setAuthenticatedUserId(creator);
         ProcessInstance instance = runtimeService.startProcessInstanceByKey(processKey, variables);
         processInstanceId = instance.getId();
      } catch (Throwable e) {
         throw ServerUtil.createServerException(e);
      } finally {
         identiyService.setAuthenticatedUserId(null);
      }
      
      Execution execution = runtimeService.createExecutionQuery().processInstanceId(processInstanceId).activityId("wait") //$NON-NLS-1$
            .singleResult();
      if (execution != null) {
         identiyService.setAuthenticatedUserId(null);
         runtimeService.signal(execution.getId());
      }
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This whole hack is done that I can access workflow data durring the workflow, using the following code in a workflow, the currently running process is not listed.

ProcessInstance instance = runtimeService.startProcessInstanceByKey(processKey, variables);
for (ProcessInstance in : runtimeService.createProcessInstanceQuery().list()) {
   System.out.println("RUNNING>: " + in.getId() + " " + in.getProcessDefinitionId());
}
‍‍‍‍‍‍

So I was wondering is there a better solution? Workaround?
Should I report a bug?

greets and thanks for any help!
Flavio

[trademak]

Hi Flavio,

For your use case, using the async attribute would make much more sense then introducing this receive task.
Could you try and test it with the async="true" attribute?

Best regards,

[flavio_donze]

Hi trademak

Thanks for your quick reply!

The async="true" does solve the transaction problem and I can remove the hack!

The problem that I got now is the exception handling.
The workflow that causes the deadlocks does not have any user interaction, it consists of a bunch of serviceTasks.
Before adding the async="true", in case one of the serviceTasks threw an exception it was caught by the client and displayed in a message box.
Using the async="true", the client does not get notified if something went wrong, since the workflow is running asynchronous on the server.
I would like to catch the exception in the workflow and maybe create a user task or send a mail.

I studied the Activiti User Guide, but I can't find anything that fits, e.g. a listener or "catch" task.

Any idea how I can accomplish this?

greets and thanks
Flavio

[ronald_van_kuij]

It is better to post new questions in new topics with a specific subject. That way they are easier to find for others with a similar issue.

[flavio_donze]

Done that: http://forums.activiti.org/en/viewtopic.php?f=6&t=6349

[flavio_donze]

I created a little test to reproduce this deadlock issue.

I tried to upload an attachment, but it does not seem to work "Could not upload attachment to ./files/387_8192b0846287b030284985893c1b7563.".

So here the code:

SimpleCommit.bpmn20.xml

<?xml version="1.0" encoding="UTF-8"?>

<definitions id="definitions"
             xmlns="http://www.omg.org/spec/BPMN/20100524/MODEL"
             xmlns:activiti="http://activiti.org/bpmn"
             targetNamespace="http://www.activiti.org/bpmn2.0">

<process id="SimpleCommit" isExecutable="true">
  <startEvent id="start"/>

  <sequenceFlow id="startToWait" sourceRef="start" targetRef="wait"/>
  <receiveTask id="wait" name="wait"/>
  <sequenceFlow id="waitToSleepTask" sourceRef="wait" targetRef="sleepTask"/>

  <serviceTask id="sleepTask" activiti:class="com.softmodeler.workflow.SleepTask">
  </serviceTask>

  <sequenceFlow id="sleepTaskToEnd" sourceRef="sleepTask" targetRef="end"/>

  <endEvent id="end"/>
</process>

</definitions>

SleepTask.java

package com.softmodeler.workflow;

import org.activiti.engine.delegate.DelegateExecution;
import org.activiti.engine.delegate.JavaDelegate;

/**
* @author created by Author: fdo, last update by $Author: $
* @version $Revision: $, $Date: $
*/
public class SleepTask implements JavaDelegate {

@Override
public void execute(DelegateExecution execution) throws Exception {
  Thread.sleep(5000);
}

}


Excecution Code

public void startTest() {
  try {
   Thread thread1 = new Thread(new Runnable() {
    @Override
    public void run() {
     try {
      System.out.println("start 1");
      start("testuser1", "SimpleCommit");
      System.out.println("end 1");
     } catch (ServerException e) {
      e.printStackTrace();
     }
    }
   });
   thread1.start();
   Thread thread2 = new Thread(new Runnable() {
    @Override
    public void run() {
     try {
      System.out.println("start 2");
      start("testuser2", "SimpleCommit");
      System.out.println("end 2");
     } catch (ServerException e) {
      e.printStackTrace();
     }
    }
   });
   thread2.start();
  } catch (Exception e) {
   e.printStackTrace();
  }
}

@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
protected String start(String initiator, String processKey) throws ServerException {
  String processInstanceId = null;
  try {
   identiyService.setAuthenticatedUserId(initiator);
   ProcessInstance instance = runtimeService.startProcessInstanceByKey(processKey);
   processInstanceId = instance.getId();
  } catch (Throwable e) {
   throw ServerUtil.createServerException(e);
  } finally {
   identiyService.setAuthenticatedUserId(null);
  }

  Execution execution = runtimeService.createExecutionQuery().processInstanceId(processInstanceId).activityId("wait") //$NON-NLS-1$
    .singleResult();
  if (execution != null) {
   // remove current user otherwise subprocesses are also listed (remove as well)
   identiyService.setAuthenticatedUserId(null);
   runtimeService.signal(execution.getId());
  }

  return processInstanceId;
}

Maybe this can be added as a test?
greets
Flavio

[jbarrez]

Does this happen on every database or only on sql server?

[flavio_donze]

Right now I'm using MSSQL 2008 R2.

I will try to setup a postgresql environment.

[ronald_van_kuij]

Might be related to an interesting mssql 'feature', lock escalation

http://www.sqlhacks.com/FAQs/Lock-Escalation

[flavio_donze]

I tested it with postgresql and there was no deadlock.

I will have a lock at the lock-escalation, thanks for the hint Ronald.