Serious multithreading problem with embedded Activiti

[gergelydombi]

Hi guys!

We are using Activiti in our project. It was a very good experience so far for the whole team, both the product and
the documentation was great.

Our application has a Spring 3 / Hibernate backend and we use the Activiti engine embedded into our Spring configuration.
The Spring integration looked pretty good throughout the development of the application.

We are just before the handover phase and we ran an internal performance test. It was quite shocking that the Activiti engine began to throw Database Deadlock exceptions with as few as 4 concurrent users.

We analyzed our code thoroughly, and replaced our initial database choice with several other databases but the
problem remained.

We created a test case to separate our application logic from this weird multithreaded behaviour, and it show that in fact it is the Activiti engine that causes the deadlock. Our test case is pretty simple, a sequencial series of human tasks, service tasks and receive tasks (to force persistence in some cases). Our test code starts several threads to initialize one process instances per thread. A process variable is set prior to the start of the process that is used later in a boundary event (timer).

The full source code and executable binaries (with all the dependencies) of the test case can be downloaded from http://www63.zippyshare.com/v/62854622/file.html.

We would be keen to create a JIRA issue on this, and would be happy to be involved in the fix.

Lock aquisition exception occurs in the following scenario:

### The error may involve org.activiti.engine.impl.persistence.entity.ExecutionEntity.insertExecution-Inline
### The error occurred while setting parameters
### Cause: org.hibernate.exception.LockAcquisitionException: Deadlock detected. The current transaction was rolled back. Details: "
Session #14 (user: SA) is waiting to lock PUBLIC.ACT_RU_VARIABLE while locking PUBLIC.ACT_RE_PROCDEF (shared), PUBLIC.ACT_RU_VARIABLE (shared), PUBLIC.ACT_RU_EXECUTION (exclusive).
Session #12 (user: SA) is waiting to lock PUBLIC.ACT_RU_EXECUTION while locking PUBLIC.ACT_RE_PROCDEF (shared), PUBLIC.ACT_RU_VARIABLE (shared)."; SQL statement:
insert into ACT_RU_EXECUTION (ID_, REV_, PROC_INST_ID_, BUSINESS_KEY_, PROC_DEF_ID_, ACT_ID_, IS_ACTIVE_, IS_CONCURRENT_, IS_SCOPE_, PARENT_ID_, SUPER_EXEC_)
    values (
      ?,
      1,
      ?,
      ?,
      ?,
      ?,
      ?,
      ?,
      ?,
      ?,
      ?
    ) [40001-158]
 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.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.RuntimeServiceImpl.startProcessInstanceByKey(RuntimeServiceImpl.java:49)
 at hu.sonrisa.example.service.impl.WorkflowServiceImpl.startProcess(WorkflowServiceImpl.java:97)
 at hu.sonrisa.example.ActivitiThread.run(ActivitiThread.java:64)
 at java.lang.Thread.run(Thread.java:662)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[ronald_van_kuij]

Gergely,

Activiti comes with a (limited) set of indeces, mainly of FK's. If you do queries on e.g. process variables in your code, that can result in full table scans and locks. (See another post in the forum about jmeter tests). So analyzing that is what you should do first. For the other poster it prevented lots of issues.

The reason Activiti cannot (will not) provide all indeces, is that to many indeces slows the db down considerably. So They should be tuned to the specific usage.

hth (to some extend)