Camunda Asych with MSSQL Producing Deadlocks

[walan...@gmail.com]

Hi ,

I have trying some performance testing for Camunda.
I had observed following issue under concurrency.

Stacktrace :
--------------------------------------------------------------------------
SEVERE: Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 79) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
### The error may involve org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.deleteExecution-Inline
### The error occurred while setting parameters
### SQL: delete from ACT_RU_EXECUTION where ID_ = ? and REV_ = ?
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 79) 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.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:158)
at org.camunda.bpm.engine.impl.db.DbSqlSession$DeletePersistentObjectOperation.execute(DbSqlSession.java:264)
at org.camunda.bpm.engine.impl.db.DbSqlSession.flushDeletes(DbSqlSession.java:735)
at org.camunda.bpm.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:502)
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 79) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

.
.
.
.

SEVERE: Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:
### 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.
### The error may involve org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.updateExecution-Inline
### The error occurred while setting parameters
### 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_ = ?
### 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.

--------------------------------------------------------------------------

I had tried following options to overcome the above issue :
1. Use DBCP Data Source
2. Used BoneCP Data Source
3. Used Atomikos Xa and non XA Datasource
4. Tried with using Jboss DataSource
5. Set READ_COMMITED_TRANSACTION On for SQL Server
6. Used Isolation level - READ_COMMITED , SNAPSHOT etc

The above tryouts performed are on Jetty and Jboss 7.1
In both case we are getting same Exception.

[Bernd Rücker (camunda)]

Hi Walanj.

Without having time to have a deeper look at it - would be interesting if
things change if you remove this constraint:
https://app.camunda.com/jira/browse/CAM-1515

And: Do you use the StrongUuidGenerator or DbUuidGenerator?

Cheers
Bernd

-----Urspr�ngliche Nachricht-----
Von: camunda-...@googlegroups.com
[mailto:camunda-...@googlegroups.com] Im Auftrag von
walan...@gmail.com
Gesendet: Dienstag, 17. Dezember 2013 12:57
An: camunda-...@googlegroups.com
Cc: Amol....@sigma-systems.com
Betreff: [camunda-bpm-users] Camunda Asych with MSSQL Producing Deadlocks

--
You received this message because you are subscribed to the Google Groups
"camunda BPM users" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to camunda-bpm-us...@googlegroups.com.
To post to this group, send email to camunda-...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/camunda-bpm-users/259654d1-b263-43b4-88c
4-3f60f079a14c%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[walan...@gmail.com]

On Tuesday, December 17, 2013 5:39:14 PM UTC+5:30, Bernd Rücker wrote:
> Hi Walanj.
>
>
>
> Without having time to have a deeper look at it - would be interesting if
>
> things change if you remove this constraint:
>
> https://app.camunda.com/jira/browse/CAM-1515
>
>
>
> And: Do you use the StrongUuidGenerator or DbUuidGenerator?
>
>
>
> Cheers
>
> Bernd
>
>
>

> -----Urspr�ngliche Nachricht-----

Thanks Bernd ,
I will try by removing the unique constraints.
I had used StrongUuidGenerator.
Also for testing I had implemted custom history event handler which just logs the message on Completion of Process.

[walan...@gmail.com]

Hi , I had tried with the above Solution.
Still I am getting the Same issue.

Camunda Versions used are : Camunda 7.0.0-Final / 7.1.0-SNAPSHOT

The Process Enghine Configuration is as follows :

<bean id="processEngineConfiguration"
class="org.camunda.bpm.engine.spring.SpringProcessEngineConfiguration">
<property name="processEngineName" value="default" />
<property name="dataSource" ref="mssqlDataSource" />
<property name="transactionManager" ref="springTransactionManager" />
<property name="databaseSchemaUpdate" value="true" />
<property name="jobExecutorActivate" value="true" />
<property name="deploymentResources" value="classpath*:*.bpmn" />
<property name="historyEventHandler" ref="ompHistoryEventHandler" />
<property name="history" value="full" />
<property name="jobExecutor" ref="customJobExecutor" />
<property name="idGenerator" ref="strongUuidGenerator" />
</bean>

[Daniel Meyer]

Hi Walanj Amol,

on some versions of Sql Server you need to tweak the database indexes to
prevent deadlocks.

You could try to add the following indexes:

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_EXECUTION_IDANDREV on ACT_RU_EXECUTION(ID_, REV_);
create index ACT_IDX_EXECUTION_PROC_INST_ID on
ACT_RU_EXECUTION(PROC_INST_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_);

create index ACT_IDX_IDENT_LNK_TASK on ACT_RU_IDENTITYLINK(TASK_ID_);
create index ACT_IDX_IDENT_LNK_PROCDEF on ACT_RU_IDENTITYLINK(PROC_DEF_ID_);

Does that work for you?

Cheers,
Daniel Meyer

[walan...@gmail.com]

Hi Daniel ,
Thanks for reply.
Adding above index resolved the issue for my current load.
Now I will fired a longer running test.

[Daniel Meyer]

Hi Walanj Amol,

thanks for keeping us updated!

Cheers,
Daniel

[Daniel Meyer]

Hi Walanj Amol,

Here is an additional path which has been reported to work: enabling Row Versioning-Based Isolation Levels 

http://technet.microsoft.com/en-us/library/ms175095(v=SQL.105).aspx 

I also created the following Jira Issue to be a ble to track this: https://app.camunda.com/jira/browse/CAM-1646

Cheers,

Daniel

[walan...@gmail.com]

Hi Daniel ,

I had already tried with enabling Row Versioning-Based Isolation Levels and using TRANASACTION_SNAPSHOT

The combo of above index and SNAPSHOT seems to solve the issue.
Also I had disable the SQL Server LOCK ESCALATION for test.

[walan...@gmail.com]

Hi ,

The issues is reproduced .
It seems to be deadlock get reproduced when we add Intermediate Catch Events.

For the Following Process , The issue gets reproduced.
http://camunda.org/share/#/process/1c4f9ae6-379d-4970-b63b-e569a0231c4c

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

### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.ACT_RU_EVENT_SUBSCR' directly or indirectly in database 'OMPPERF' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

### The error may involve org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.deleteExecution-Inline
### The error occurred while setting parameters
### SQL: delete from ACT_RU_EXECUTION where ID_ = ? and REV_ = ?

### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.ACT_RU_EVENT_SUBSCR' directly or indirectly in database 'OMPPERF' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

Also here I am trying to start a child Process in Service task S1 , S2 ..
And then from child Process try to signal back.

For Parent Process :
ProcessEngine processEngine = ProcessEngines.getDefaultProcessEngine();
processEngine.getRuntimeService().startProcessInstanceByKey(calledSubProcess, props);

The prop contains the Parent Process Id and message Name

For Child Process :
Execution execution =
processEngine.getRuntimeService()
.createExecutionQuery()
.processInstanceId(parent)
.messageEventSubscriptionName(messageName)
.singleResult();
processEngine.getRuntimeService().messageEventReceived(messageName, execution.getId());

Thanks,
Amol

[Daniel Meyer]

Hi Amol,

Do I understand you correctly in that you are trying to start a process instance from a service task and then in the started process instance signalling back to the first process immediately? Is there a transaction boundry between the starting of the sub process instance and the signalling back?

Cheers,

Daniel Meyer

[walan...@gmail.com]

Hi Daniel ,
Yes , we are trying to start a process instance from a service task and then in the started process instance signalling back to the first process immediately.

In called process we are releasing the client thread in first task only.
After that just prior to the end event , we signal back to parent process.

[Daniel Meyer]

Hi Walan,

Would it be possible for you to provide us with a failing unit test?

Cheers,

Daniel 

[walan...@gmail.com]

Hi Daniel ,

I haven't write any test for it. Here we are just deploying our war into camunda - jboss distribution. And using the camunda rest engine. we are using third party client like gatling / standalone code with java tpe to fire Rest API whcih start our parent Process. Here we are firing very high load. Almost around 100 to 1000 threads concurrently.

Meanwhile just to update , we have changed our Process model from Service task delegate + intermediate catch event to Asynchronous Service task + JMS
and from MSSQL side we changed the following setting.

Changed Isolation level from SNAPSHOT to READ_COMMITTED_SNAPSHOT
And Enabled ALLOW_SNAPSHOT_ISOLATION.

We SET LOCK_ESCALATION=DISABLE for every table and added following index
create index ACT_IDX_JOB_EXEC on ACT_RU_JOB(EXECUTION_ID_);

With this setting we haven't received any deadlock for almost 50000 orders test which we fired.

But the DB side CPU seems to be continuously in range of 90-100

Currently we are trying the clustered set up and JMS tuning on Jboss for Camunda. Once done I will re-fire the previous test which gets failed with the above new setting and will post the result.

Thanks,
Amol Walanj

[Christian Lipphardt]

Hi all,

we will add additional indices for MSSQL as this solved our testcases with concurrency on sql server.

See https://app.camunda.com/jira/browse/CAM-1646 for the required indices in the description. The drop statements are in the comments.

Cheers,
Christian

[webcyberrob]

Hi Guys,

Im starting to see deadlocks on Oracle...

Camunda version 7.1 Final

Clustered (two nodes) Tomcat 7.x

Oracle RAC 11g

I have three processes such that my master process uses a sequential multi-instance subprocess which contains a call task to dynamically call one or the other of the sub processes (imagine a customer order with multiple line items, each line item requires a different fulfilment process).

I was using this to test the behaviour (performance) of the job executor. The main process uses a script task to build up an order which is then used by the rest of the process. If I launch say 5 instances of the dispatcher process as quickly as possible, I seem to get a deadlock.

These proceses just use script tasks, hence the BPMN files are self contained.

Dispatcher Process:

http://camunda.org/share/#/process/c461eeec-3d60-4f09-8b18-9d49cff5528e

Slave Processes:

http://camunda.org/share/#/process/943ad685-b43f-4c10-b0db-881b15cda48d

http://camunda.org/share/#/process/a4a260ed-09a1-4081-9fba-446270fda18d

Partial Stacktrace:

org.camunda.bpm.engine.impl.interceptor.CommandContext close

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

### Error updating database. Cause: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

### The error may involve org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.deleteExecution-Inline
### The error occurred while setting parameters
### SQL: delete from ACT_RU_EXECUTION where ID_ = ? and REV_ = ?

### Cause: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:147)
at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:158)

at org.camunda.bpm.engine.impl.db.DbSqlSession$DeletePersistentObjectOperation.execute(DbSqlSession.java:252)
at org.camunda.bpm.engine.impl.db.DbSqlSession.flushDeletes(DbSqlSession.java:723)
at org.camunda.bpm.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:490)
at org.camunda.bpm.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:196)
at org.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:126)
at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:99)

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: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

I note that one of my test processes uses an intermediate throw event in the middle of a parallel process construct. This looks very similar to the example from this thread. Perhaps there's a link...

I'll try again to narrow down further...

regards

Rob