Locking error when running simultaneous process Instances


#1

Hi guys,

I reproduced this error with the unit test that I pushed to this repo:

Inside you will see these two test methods:

  • public void testOneSingleRun(): this one works without problems.
  • public void testMultiThreadedRun(): this one works if the number of threads is 5 or less, but it fails if there are more threads (try starting with 6).

The error is like this:

Caused by: org.h2.jdbc.JdbcSQLException: Deadlock detected. The current transaction was rolled back. Details: "
Session #2 (user: SA) is waiting to lock PUBLIC.ACT_RU_TASK while locking PUBLIC.ACT_HI_TASKINST (exclusive), PUBLIC.ACT_HI_ACTINST (exclusive).
Session #1 (user: SA) is waiting to lock PUBLIC.ACT_HI_TASKINST while locking PUBLIC.ACT_RU_TASK (exclusive)."; SQL statement:

Why is it so, if I’m creating a different processInstance for every thread?
Also, I kept the process definition as simple as possible:

image

My test just does this:

  • start process instance with variables
  • update variables three times
  • saveTask
  • complete the first task

This is close to what our real application does, we are updating processIntance variables from a frontend wizard before completing the corresponding user task.

Can somebody help me to understand the reason why and to avoid this error?
Thanks in advance,

Alfonso.


#2

Hi Alfonso,

H2 uses table level locks by default, so that’s why you get deadlock issues when you do anything in parallel. Enable multi-version concurrency control for row-level exclusive locks: http://www.h2database.com/html/advanced.html#mvcc. And lastly: don’t use h2 for production.

Cheers,
Thorben


#3

Hi Thorben, thanks for your answer,

I verified that this does not happen if I run my unit test against mysql… This test was the closest I got to reproduce and isolate the original error that we get in our application, which was this one and I was not able to solve:

Do you see something suspicious in our datasource that could be causing this error?

        <xa-datasource jndi-name="java:jboss/datasources/EpsilonXADS" pool-name="EpsilonXADS" enabled="true" use-java-context="true" use-ccm="false">
            <xa-datasource-property name="URL">
                jdbc:mysql://localhost:3306/epsilontest-marcel
            </xa-datasource-property>
            <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
            <driver>mysql</driver>
            <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
            <xa-pool>
                <min-pool-size>2</min-pool-size>
                <max-pool-size>50</max-pool-size>
                <prefill>false</prefill>
                <use-strict-min>false</use-strict-min>
                <flush-strategy>FailingConnectionOnly</flush-strategy>
                <no-tx-separate-pools>true</no-tx-separate-pools>
                <pad-xid>false</pad-xid>
                <wrap-xa-resource>true</wrap-xa-resource>
            </xa-pool>
            <security>
                <user-name>epsilon</user-name>
                <password>epsilon</password>
            </security>
            <validation>
                <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
                <validate-on-match>false</validate-on-match>
                <background-validation>false</background-validation>
                <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
            </validation>
            <timeout>
                <set-tx-query-timeout>false</set-tx-query-timeout>
                <blocking-timeout-millis>5000</blocking-timeout-millis>
                <idle-timeout-minutes>1</idle-timeout-minutes>
                <query-timeout>600</query-timeout>
                <use-try-lock>600</use-try-lock>
                <allocation-retry>3</allocation-retry>
                <allocation-retry-wait-millis>5000</allocation-retry-wait-millis>
            </timeout>
        </xa-datasource>
        <drivers>
            <driver name="mysql" module="com.mysql.jdbc">
                <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
            </driver>
            <driver name="h2" module="com.h2database.h2">
                <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
            </driver>
        </drivers>

ENGINE-03005 Execution of UPDATE VariableInstanceEntity