Process Engine deadlocks in Mssql / DB2

1. Problem:

Some users experience deadlocks when running camunda BPM on MSSQL (Microsoft Sql Server).

SEVERE: Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:

1. 1. 1. Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 218) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
      2. The error may involve org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.updateExecution-Inline
      3. The error occurred while setting parameters
      4. SQL: update ACT_RU_EXECUTION set REV_ = ?, PROC_DEF_ID_ = ?, ACT_ID_ = ?, ACT_INST_ID_ = ?, IS_ACTIVE_ = ?, IS_CONCURRENT_ = ?, IS_SCOPE_ = ?, IS_EVENT_SCOPE_ = ?, PARENT_ID_ = ?, SUPER_EXEC_ = ?, SUSPENSION_STATE_ = ?, CACHED_ENT_STATE_ = ? where ID_ = ? and REV_ = ?
      5. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 218) 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.camunda.bpm.engine.impl.db.DbSqlSession.flushUpdates(DbSqlSession.java:699)
         at org.camunda.bpm.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:500)
         at org.camunda.bpm.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:212)
         at org.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:155)
         at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:49)
         at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
         at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
         at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
         at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:32)
         at org.camunda.bpm.engine.impl.jobexecutor.ExecuteJobsRunnable.executeJob(ExecuteJobsRunnable.java:79)
         at org.camunda.bpm.engine.impl.jobexecutor.ExecuteJobsRunnable.run(ExecuteJobsRunnable.java:67)
         at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
         at java.lang.Thread.run(Thread.java:744)
         Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 218) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

1. Proposed Fix(es):

It seems like the problem can be fixed by

• Adding some more indexes to the database
• By enabling Row Versioning-Based Isolation Levels (Unconfirmed) http://technet.microsoft.com/en-us/library/ms175095(v=SQL.105).aspx

Ad 1) Create the following additional Indexes in the database:

create index ACT_IDX_EXECUTION_PROC on ACT_RU_EXECUTION(PROC_DEF_ID_);
create index ACT_IDX_EXECUTION_PARENT on ACT_RU_EXECUTION(PARENT_ID_);
create index ACT_IDX_EXECUTION_SUPER on ACT_RU_EXECUTION(SUPER_EXEC_);

create index ACT_IDX_EVENT_SUBSCR_EXEC on ACT_RU_EVENT_SUBSCR(EXECUTION_ID_);

create index ACT_IDX_BA_DEPLOYMENT on ACT_GE_BYTEARRAY(DEPLOYMENT_ID_);

create index ACT_IDX_IDENT_LNK_TASK on ACT_RU_IDENTITYLINK(TASK_ID_);

create index ACT_IDX_INCIDENT_EXEC on ACT_RU_INCIDENT(EXECUTION_ID_);
create index ACT_IDX_INCIDENT_PROCINST on ACT_RU_INCIDENT(PROC_INST_ID_);
create index ACT_IDX_INCIDENT_PROC_DEF_ID on ACT_RU_INCIDENT(PROC_DEF_ID_);
create index ACT_IDX_INCIDENT_CAUSE on ACT_RU_INCIDENT(CAUSE_INCIDENT_ID_);
create index ACT_IDX_INCIDENT_ROOT_CAUSE on ACT_RU_INCIDENT(ROOT_CAUSE_INCIDENT_ID_);

create index ACT_IDX_JOB_EXCEPTION_STACK on ACT_RU_JOB(EXCEPTION_STACK_ID_);

create index ACT_IDX_VARIABLE_BA on ACT_RU_VARIABLE(BYTEARRAY_ID_);
create index ACT_IDX_VARIABLE_EXEC on ACT_RU_VARIABLE(EXECUTION_ID_);
create index ACT_IDX_VARIABLE_PROCINST on ACT_RU_VARIABLE(PROC_INST_ID_);

create index ACT_IDX_TASK_EXEC on ACT_RU_TASK(EXECUTION_ID_);
create index ACT_IDX_TASK_PROCINST on ACT_RU_TASK(PROC_INST_ID_);
create index ACT_IDX_TASK_PROC_DEF_ID on ACT_RU_TASK(PROC_DEF_ID_);

See also: https://groups.google.com/forum/#!topic/camunda-bpm-users/RPE7xeEyOws