Transaction Isolation Level and MySQL

[arandall]

I have been having trouble with the following code;

@Override
public void notify(DelegateExecution execution) throws Exception {
    String parentOrderID = (String) execution.getVariable("parentOrderId");

    ProcessInstance instance = runtimeService.createProcessInstanceQuery()
        .processInstanceBusinessKey(parentOrderID)
        .includeProcessVariables()
        .singleResult();

    Execution parentExecution = execution.getEngineServices()
        .getRuntimeService().createExecutionQuery()
        .processInstanceId(instance.getId())
        .messageEventSubscriptionName("childOrderComplete")
        .singleResult();

    execution.getEngineServices()
        .getRuntimeService()
        .messageEventReceivedAsync("childOrderComplete", parentExecution.getId());
}
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

It would appear in some cases that the parentExecution variable is null preventing the signal being fired from the originating process. In an attempt to fix this issue I wrapped a retry loop around the code however this didn't help.

After a lot of debugging sessions I discovered that within the Activiti code the following SQL statement returns no results.

select 
    distinct RES.*, 
    P.KEY_ as ProcessDefinitionKey, 
    P.ID_ as ProcessDefinitionId      
from ACT_RU_EXECUTION RES     
inner join ACT_RE_PROCDEF P 
    on RES.PROC_DEF_ID_ = P.ID_          
inner join ACT_RU_EVENT_SUBSCR EVT0 
    on RES.ID_ = EVT0.EXECUTION_ID_        
WHERE  RES.PROC_INST_ID_ = '15073'
    and (EVT0.EVENT_TYPE_ = 'message' 
        and EVT0.EVENT_NAME_ = 'childOrderComplete')        
order by RES.ID_ asc    
LIMIT 2147483647
OFFSET 0;
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

When I execute it in another MySQL session it returns a single result as expected. This lead me on to suspect that it is related to the transaction isolation level in MySQL.

MySQL is configured by default with the “REPEATABLE READ” option. This will result in a snapshot being taken after the first SELECT statement it executed so any following reads will be consistent from that snapshot.

It would appear that Activiti does not commit or rollback the db session after running the createExecutionQuery resulting in subsequent reads getting no results.

If I place a breakpoint prior to Activiti executing the SQL query above and running, via my debugger, the java command 'sqlSession.getConnection().rollback();' Activiti performs as expected.

Here is the MySQL query log, which shows that the transaction is started, but never released. This has the code above with a retry interval of 10 seconds.

      42120 Query   select @@session.tx_read_only
      42120 Query   insert into ACT_HI_VARINST (ID_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, NAME_, REV_, VAR_TYPE_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_)
      42120 Query   select @@session.tx_read_only
      42120 Query   insert into ACT_RU_IDENTITYLINK (ID_, REV_, TYPE_, USER_ID_, GROUP_ID_, TASK_ID_, PROC_INST_ID_, PROC_DEF_ID_)
      42120 Query   select @@session.tx_read_only
      42120 Query   insert into ACT_HI_IDENTITYLINK (ID_, TYPE_, USER_ID_, GROUP_ID_, TASK_ID_, PROC_INST_ID_)
      42120 Query   select @@session.tx_read_only
      42120 Query   insert into ACT_RU_VARIABLE (ID_, REV_,
      42120 Query   select @@session.tx_read_only
      42120 Query   insert into ACT_HI_VARINST (ID_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, NAME_, REV_, VAR_TYPE_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_)
      42120 Query   commit
      42120 Query   select @@session.tx_read_only
      42120 Query   select @@session.tx_read_only
      42120 Query   rollback
      42120 Query   SET autocommit=1
      42120 Query   SET autocommit=0
      42120 Query   select @@session.tx_read_only
      42120 Query   select distinct RES.* , P.KEY_ as ProcessDefinitionKey, P.ID_ as ProcessDefinitionId <— 1st Attempt
      42120 Query   select @@session.tx_read_only
      42120 Query   select distinct RES.* , P.KEY_ as ProcessDefinitionKey, P.ID_ as ProcessDefinitionId <— 2nd Attempt
      42120 Query   select @@session.tx_read_only
      42120 Query   select distinct RES.* , P.KEY_ as ProcessDefinitionKey, P.ID_ as ProcessDefinitionId <— 3rd Attempt
      42120 Query   select @@session.tx_read_only
      42120 Query   select distinct RES.* , P.KEY_ as ProcessDefinitionKey, P.ID_ as ProcessDefinitionId <— 4th Attempt
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Unfortunately I am unable to reliably reproduce this problem however restarting Activiti clears it for a few runs before it can be hit again.

What should the transaction level be set to in MySQL?

Does any one have any suggestions on how I can resolve this issue?

Activiti v5.14
MySQL v5.6.16

[arandall]

Since changing the transaction isolation in MYSQL to READ COMMITTED I have been unable to hit this issue.

https://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_read-committed

<code>
[mysqld]
transaction-isolation = READ-COMMITTED
</code>

Reading details about Oracle and Postgres they use read committed by default suggesting that this setting should also be used for MySQL  and be added to the Acticiti user guide.

Can someone confirm the isolation that should be used for Activiti?

Thanks.

[jbarrez]

We do assume READ_COMMITTED, yes.

However, I always assumed that Repeatable read would work also, as it is more restrictive …