activiti & thread safety

[christoph_retti]

Hi guys, we are using activiti embedded in our product and have it configured using Spring like

  <bean id="activiti.dataSource.dbcp" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" ref="activiti.jdbc.driver" />
    <property name="url" ref="activiti.jdbc.url" />
    <property name="username" ref="activiti.jdbc.username" />
    <property name="password" ref="activiti.jdbc.password" />
    <property name="maxIdle" value="2" />
    <property name="maxActive" value="10" />
    <property name="maxWait" value="10000" />
    <property name="validationQuery" value="select 1" />
    <property name="testOnBorrow" value="false" />
    <property name="testWhileIdle" value="true" />
    <property name="timeBetweenEvictionRunsMillis" value="1200000" />
    <property name="minEvictableIdleTimeMillis" value="1800000" />
    <property name="numTestsPerEvictionRun" value="5" />
    <property name="defaultAutoCommit" value="false" />
  </bean>

  <bean id="activiti.transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="activiti.dataSource.dbcp" />
  </bean>

  <bean id="activiti.processEngineConfiguration" class="org.activiti.spring.SpringProcessEngineConfiguration">
    <property name="dataSource" ref="activiti.dataSource.dbcp" />
    <property name="transactionManager" ref="activiti.transactionManager" />
    <property name="databaseSchemaUpdate" value="true" />
    <property name="jobExecutorActivate" value="false" />
    <property name="identityService" ref="activiti.identityService" />
    <property name="customSessionFactories">
      <list>
        <bean class="biz.i2z.connector.activiti.service.identity.CspUserManagerFactory">
          <constructor-arg ref="activiti.identityService" />
        </bean>
        <bean class="biz.i2z.connector.activiti.service.identity.CspGroupManagerFactory">
          <constructor-arg ref="activiti.identityService" />
        </bean>
      </list>
    </property>
  </bean>

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

When accessing the activiti engine in our multi-threaded environment we sometimes face database locks (we use MS SQL Server) like

WARN : updateTaskData(taskIds=[(contid=1210241547469016b28cedf4e424275a¦pid=10510¦taskid=10556¦execid=10510¦businessid=)], username='svc_cspstep', password='op*******', attributes={}, taskStatus=completed): updating the process instances caused failures: [(contid=1210241547469016b28cedf4e424275a¦pid=10510¦taskid=10556¦execid=10510¦businessid=)] 
### Error updating database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 61) 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.ExecutionEntity.updateExecution-Inline
### The error occurred while setting parameters
### SQL: update ACT_RU_EXECUTION set       REV_ = ?,       PROC_DEF_ID_ = ?,       ACT_ID_ = ?,       IS_ACTIVE_ = ?,       IS_CONCURRENT_ = ?,       IS_SCOPE_ = ?,       IS_EVENT_SCOPE_ = ?,       PARENT_ID_ = ?,       SUPER_EXEC_ = ?,       SUSPENSION_STATE_ = ?,       CACHED_ENT_STATE_ = ?     where ID_ = ?       and REV_ = ?
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

INFO : [CSPSTEP] released (item=10556 step=CspstepInvoiceApproval assignmentDate=24.10.12 15:50, step=CspstepInvoiceApproval) 
Okt 24, 2012 3:50:27 PM org.activiti.engine.impl.interceptor.CommandContext close
SEVERE: Error while closing command context
org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 56) 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.HistoricActivityInstanceEntity.updateHistoricActivityInstance-Inline
### The error occurred while setting parameters
### SQL: update ACT_HI_ACTINST set       EXECUTION_ID_ = ?,       ASSIGNEE_ = ?,       END_TIME_ = ?,       DURATION_ = ?     where ID_ = ?
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 56) 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:147)
                at org.activiti.engine.impl.db.DbSqlSession.flushUpdates(DbSqlSession.java:472)
                at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:370)
                at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:157)
                at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:109)
                at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:49)
                at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
                at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
                at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
                at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
                at org.activiti.engine.impl.TaskServiceImpl.complete(TaskServiceImpl.java:144)
                at biz.i2z.connector.activiti.ActivitiConnector.complete(ActivitiConnector.java:976)
                at biz.i2z.container.workflowaccessor.impl.activiti.ActivitiAccessor.updateTaskInActiviti(ActivitiAccessor.java:2016)
                at biz.i2z.container.workflowaccessor.impl.activiti.ActivitiAccessor.updateTaskData(ActivitiAccessor.java:1971)
                at biz.i2z.container.workflowaccessor.impl.activiti.ActivitiAccessor.updateTaskData(ActivitiAccessor.java:1927)
                at biz.i2z.container.workflowaccessor.impl.activiti.ActivitiAccessor.workOnProcess(ActivitiAccessor.java:1891)
                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
                at java.lang.reflect.Method.invoke(Method.java:601)
                at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
                at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
                at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
                at biz.i2z.container.workflowaccessor.WorkflowAccessorEnrichmentProxy.invoke(WorkflowAccessorEnrichmentProxy.java:132)
                at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
                at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
                at $Proxy75.workOnProcess(Unknown Source)
                at biz.i2z.modules.workflowaccessor.ServiceObject.workOnProcess(ServiceObject.java:468)
                at biz.i2z.modules.workflowaccessor.ServiceObject.workOnProcess(ServiceObject.java:450)
                at biz.i2z.modules.workflowaccessor.ServiceObject.workOnProcess(ServiceObject.java:431)
                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
                at java.lang.reflect.Method.invoke(Method.java:601)
                at biz.i2z.system.ApplicationProcess.processRequest(ApplicationProcess.java:380)
                at biz.i2z.system.ApplicationProcess.ipcStart(ApplicationProcess.java:607)
                at biz.i2z.system.I2zProcess.ipcProcessing(I2zProcess.java:263)
                at biz.i2z.system.ApplicationProcess.run(ApplicationProcess.java:656)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 56) 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:197)
                at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
                at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390)
                at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)
                at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
                at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
                at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
                at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
                at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:322)
                at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
                at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
                at sun.reflect.GeneratedMethodAccessor345.invoke(Unknown Source)
                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
                at java.lang.reflect.Method.invoke(Method.java:601)
                at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:58)
                at $Proxy73.execute(Unknown Source)
                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)
                … 37 more

WARN : updateTaskData(taskIds=[(contid=121024154752789ce1b6e7c6d184dc4b¦pid=10519¦taskid=10563¦execid=10519¦businessid=)], username='svc_cspstep', password='op*******', attributes={}, taskStatus=completed): updating the process instances caused failures: [(contid=121024154752789ce1b6e7c6d184dc4b¦pid=10519¦taskid=10563¦execid=10519¦businessid=)] 
### Error updating database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 56) 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.HistoricActivityInstanceEntity.updateHistoricActivityInstance-Inline
### The error occurred while setting parameters
### SQL: update ACT_HI_ACTINST set       EXECUTION_ID_ = ?,       ASSIGNEE_ = ?,       END_TIME_ = ?,       DURATION_ = ?     where ID_ = ?
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

where in out updateTaskData method these methods are called on the engine

this.processEngine.getTaskService().getVariables(taskId);
this.processEngine.getTaskService().complete(taskId, variables);
this.processEngine.getTaskService().addComment(taskId, null, encodeCompletedByComment(username));
‍‍‍‍

Did we screw up the configuration so that activiti is not thread safe or is that activiti (run in embedded mode) is not thread safe at all and we have to take care to
synchronize the calls?

Any help appreciated,

  regards, chris

[edgarjoao]

Hi,
I had same issue with SQL Server, and I fixed using below configuration:

Cause
SQL Server is escalating row locks (in this case deleting a draft from the CONTENT table) to table locks and stopping other transactions from accessing the table.

Diagnosis
Check whether you are using the recommended transaction isolation level of "Read Committed with Row Versioning" by running this SELECT query. If this query which should result in '1', you are already using it. If it returns '0', proceed to the Workaround section:

SELECT sd.is_read_committed_snapshot_on
FROM sys.databases AS sd
WHERE sd.[name] = '<database_name>';

Workaround
Configure your database to use the isolation level, Read Committed with Row Versioning. You can do this by executing the following query:

ALTER DATABASE <database_name>
   SET READ_COMMITTED_SNAPSHOT ON
   WITH ROLLBACK IMMEDIATE;

Regards,
Edgar

[jbarrez]

Edgar, many thanks for posting this. I'm sure many people will find it helpful!