MS Sqlserver deadlock thrown on finding process instances

Flowable Engine
1

Hi,
oh no - those deadlocks - oh no - damned you, microsoft ! Anyone with some experiences on deadlocks on SELECT statements with Microsoft SqlServer?

Admittedly, we are a bit behind: Still using activiti 5.21 ; and we are not even using the new ASyncExecutor introduced in 5.17 (which is described as being more database friendly)

Symptoms: We have occasional deadlock exceptions on the
org.activiti.engine.impl.persistence.entity.ExecutionEntityManager.findProcessInstanceAndVariablesByQueryCriteria class.
We have quite a lot of interaction with an external system that often needs to know/check if there are certain process running - which explains why there are so many queries at the same time.

Some googl’ing around has led me to believe that I will do as described in the Camundo docs and enable READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION and see if it improves things, probably at the cost of some higher resource usage. (see this link: http://stage.docs.camunda.org/manual/7.3/guides/user-guide/#process-engine-database-configuration-custom-configuration-for-microsoft-sql-server

But if anyone has some experience to share, that would be welcome. Besides upgrading to flowable 6 :slight_smile: or migrating to Oracle, that is…

2

Hi Pieter,

Deadlocks are indeed painful with MSSQL. READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION does indeed help to reduce deadlocks. What also helps is to add indexes (when they are missing) for the queries where you see the deadlocks often appearing.

Best regards,

Tijs

3

Hi,
I’ve added the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION on may 2nd, and see how it goes.
So for the good common cause of documentation , if other people experience the same:
If you have worked on an Oracle database for a large part of your professional life, as I have, it’s quite surprising (understatement…) what the MS Sqlserver behavior in the default READ_COMMITTED isolation level is: Executing a Select statement can cause locks for update/delete statements.
I’ve found this link to be quite enlightening: http://www.dba-in-exile.com/2012/11/isolation-levels-in-oracle-vs-sql-server.html

And in my previous professional life I was also drilled in not creating an extra index for a specific customer - in order to avoid potential conflicts in future updates of the DB schema, so Iike to avoid that :slight_smile: