Deadlocks on multiserver configuration

[sushantmahajan]

Hi,

I am facing a number of deadlocks issues when executing processes on activiti engine. We have 4 servers working in a cluster, each having an activiti engine instance but working on a common database. This has been done to horizontally scale the system. The database i am using is SQL SERVER 2012.

SQL: update ACT_HI_PROCINST set       PROC_DEF_ID_ = ?,       BUSINESS_KEY_ = ?,       START_TIME_ = ?,       END_TIME_ = ?,       DURATION_ = ?,       END_ACT_ID_ = ?,       DELETE_REASON_ = ?,       NAME_ = ?     where ID_ = ?

   org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40)
SQL: update ACT_RU_JOB      SET REV_ =  ?,        LOCK_EXP_TIME_ = ?,        LOCK_OWNER_ = ?,        RETRIES_ = ?,        EXCEPTION_STACK_ID_ = ?,        EXCEPTION_MSG_ = ?,        DUEDATE_ = ?      where ID_= ?       and REV_ = ?


Also please suggest if some tuning is required in the Activiti Configuration or at the database level when using large number of servers together in a cluster.

Thanks

[jbarrez]

A deadlock with those two is strange: the history should have nothing to do with the job update.
Are you sure these are the two problem areas. I know that SQL server somethings plays hard … adding an index often helps, but then the problem needs to be pinpointed to the exact table and column …

[sushantmahajan]

The issues i am facing are not concentrated but are observed on different activiti tables.
Below are few more such issues-

1. org.activiti.engine.impl.persistence.entity.ResourceEntityManager.findResourcesByDeploymentId(ResourceEntityManager.java:45)

select * from ACT_GE_BYTEARRAY where DEPLOYMENT_ID_ = ? order by NAME_ asc

2. org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:211)

delete from ACT_RU_VARIABLE where                ID_ = ?       or           ID_ = ?       or           ID_ = ?

1) What kind of tuning or configuration changes on SQL server can be done to remove or reduce such deadlocks?
2) Is this architecture where multiple activiti engine instance(on different servers) on a shared database recommended?
3) Will changing Page level locking on SQL server to Row level locking help our cause?

Thanks

[jbarrez]

Those deadlocks look very odd … i cannot see how these would be related at all.

So maybe option 3) is correct. If i understood what Google told me is correct … than it might explain it.

Otherwise … I honestly have no idea how these are caused … if you would be able to provide me a unit test that fails on the relevant database, that would help tremendously