startProcessInstanceByKey: Unique Key violation on DB

[thomas1]

Hi,

I upgraded to 5.5 and get the following exception now after running same dozen workflows:

org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: com.ibm.websphere.ce.cm.DuplicateKeyException: ORA-00001: unique constraint (SWT1CO.SYS_C0041136) 

violated

### The error may involve org.activiti.engine.impl.persistence.entity.VariableInstanceEntity.insertByteArray-Inline
### The error occurred while setting parameters
### Cause: com.ibm.websphere.ce.cm.DuplicateKeyException: ORA-00001: unique constraint (SWT1CO.SYS_C0041136) violated

        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:120)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:107)
        at org.activiti.engine.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:410)
        at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:337)
        at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:147)
        at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:103)
        at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:49)
        at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
        at org.activiti.engine.impl.RuntimeServiceImpl.startProcessInstanceByKey(RuntimeServiceImpl.java:49)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This is the primary key constraint on ACT_GE_BYTEARRAY:
ALTER TABLE SWT1CO.ACT_GE_BYTEARRAY ADD (
CONSTRAINT SYS_C0041136 PRIMARY KEY ( ID_ ));

I pass a map containing five keys to startProcessInstanceByKey.

The process engine is running in an EJB on Websphere created this way:

            ProcessEngineConfiguration processEngineConfiguration = ProcessEngineConfiguration.createProcessEngineConfigurationFromResourceDefault();
            processEngineConfiguration.setDataSource(this.activitiDataSource);
            processEngineConfiguration.setTransactionsExternallyManaged(true);
            processEngineConfiguration.setDatabaseSchemaUpdate("false");
            processEngineConfiguration.setHistory("full");
            ProcessEngine processEngine = processEngineConfiguration.buildProcessEngine();‍‍‍‍‍‍

Could this be a problem because of parallel execution?


Thanks a lot,
Thomas

[walterjs]

I believe it might. You are managing the transactions yourself, so potentially getting the next id from the db happens concurrently. Where do you commit the transactions? See if setting auto-commit on your db makes a difference (just for testing).

[thomas1]

This will be quite difficult because we use Container Managed Persistence for our EJBs.

Mean while I have two new exceptions:

### Error updating database.  Cause: com.ibm.websphere.ce.cm.DuplicateKeyException: ORA-00001: unique constraint (SWT1CO.SYS_C0093358) violated

### The error may involve org.activiti.engine.impl.persistence.entity.HistoricProcessInstanceEntity.insertHistoricProcessInstance-Inline
### The error occurred while setting parameters
### Cause: com.ibm.websphere.ce.cm.DuplicateKeyException: ORA-00001: unique constraint (SWT1CO.SYS_C0093358) violated

The second one is what you expected for this one I guess?

org.activiti.engine.ActivitiOptimisticLockingException: PropertyEntity[next.dbid] was updated by another transaction concurrently

I created a new post for this one: http://forums.activiti.org/en/viewtopic.php?f=6&t=1697

[walterjs]

What happens when you wrap a transaction around each "start" call?

[thomas1]

That's what I do at the moment.

But my workflow is quite long running (about 200ms) because I call a EJB within the workflow:

<process id="wfTest" name="Test">
  <startEvent id="start" name="Start" />
 
  <sequenceFlow id="sequenceflow1" sourceRef="start" targetRef="masterDataService" />
 
  <serviceTask id="masterDataService" name="MD Service"
   activiti:class="test.EjbWorkflowTask">
   <extensionElements>
    <activiti:field name="ejbJNDIName" stringValue="ejblocal:MasterDataService/local" />
    <activiti:field name="ejbMethod" stringValue="handleMessage" />
   </extensionElements>
  </serviceTask>

  <sequenceFlow id="sequenceflow2" sourceRef="masterDataService" targetRef="endOK">
   <conditionExpression xsi:type="tFormalExpression">
       <![CDATA[${WorkflowData.nodeResult == WorkflowData.nodeResultOK}]]>
     </conditionExpression>
  </sequenceFlow>
 
  <sequenceFlow id="sequenceflow3" sourceRef="masterDataService" targetRef="endError">
   <conditionExpression xsi:type="tFormalExpression">
       <![CDATA[${WorkflowData.nodeResult == WorkflowData.nodeResultERROR}]]>
     </conditionExpression>
  </sequenceFlow>
 
  <endEvent id="endOK" name="End" />
  <endEvent id="endError" name="End mit Fehler" />
</process>
test.EjbWorkflowTask is a helper class that looks up the EJB and calls it.


Regards,
Thomas

[walterjs]

So you do this?

for (String key : processes) {
    startTransaction();
    runtimeService.startProcessInstanceByKey(key, variables);
    commitTransaction();
}
It would be usful to see your code.

Cheers
Walter

[thomas1]

No that would execute the workflows in a sequence - the startProcessInstanceByKey command is synchronous?

I have a MDB that leads to the parallel execution. 20 MDBs are allowed to run in parallel, for this test 1.000 messages are loaded into the message queue.

The call hierarchy (a bit simplified because some more EJBs are involved but that should not matter):
MDB -> WorkflowEngine EJB does startProcessInstanceByKey -> Worker EJB access some database

The MDB begins a transaction, calls the stateless WorkflowEngine EJB (uses CMT) that holds a process engine and calls startProcessInstanceByKey. The Activiti workflow calls another EJB that uses CMT as well. Finally the MDB commits the transaction.

This way I have 20 MDBs, 20 WorkflowEngine EJBs (20 ProcessEngines accessing the same database in parallel!) and 20 Worker EJBs.
Is it allowed to let several ProcessEngines access the same database?

I am on vacation next week, after that I can provide some stripped down code if necessary.

[walterjs]

The only thing I can think of  is that the process engine is not using the transaction that the MDB starts. I don't see where you associate the transaction manager with the process engine. Have a look at the JtaProcessEngineConfiguration.