AnsweredAssumed Answered

Transaction Isolation Level and MySQL

Question asked by arandall on Mar 19, 2014
Latest reply on Mar 24, 2014 by jbarrez
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

Outcomes