Uploaded image for project: 'jBPM'
  1. jBPM
  2. JBPM-3589

drools-flow: deadlock for concurrent process completions in oracle

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • jBPM 5.2
    • jBPM 5.0
    • Persistence
    • None
    • Documentation (Ref Guide, User Guide, etc.)
    • Low
    • Hide

      In an JPA-Hibernate-oracle environment: delete multiple process instances concurrently, either by using processes that complete themselves with timers or by launching several processes concurrently. There is no need that processes have anything in common.

      If timers are used to increase concurrency, it should be taken into account that multiple requests can be performed over the same process instance, and an additional blocking system should be provided in order to not produce other concurrency errors (this is our situation).

      Show
      In an JPA-Hibernate-oracle environment: delete multiple process instances concurrently, either by using processes that complete themselves with timers or by launching several processes concurrently. There is no need that processes have anything in common. If timers are used to increase concurrency, it should be taken into account that multiple requests can be performed over the same process instance, and an additional blocking system should be provided in order to not produce other concurrency errors (this is our situation).

      Hello, we are using drools-flow with JPA, using Hibernate and Oracle RDBMS.

      We do have multiple concurrent stateful sessions.

      We also make extensive use of timers.

      Additionally, we need a blocking system that guarantees that only one request is executing concurrently for a stateful session. We have checked that out bloquing system is working correctly. This blocking system takes into account timers also.

      In this context, we have detected a situation in which deadlocks are produced when several process instances are tried to be deleted concurrently. The deleted process instances do not need to have anything in common.

      The database sessions involved in the deadlock report always to be performed when they are executing:

      DELETE FROM ProcessInstanceInfo
      WHERE InstanceId = :1 AND OPTLOCK = :2

      Which corresponds to the removal of a process.

      Looking into database locks acquired by sessions it can be seen that the table over whichthe deadlock is produced is EVENTTYPES.

      The problem is indeed caused when the cascade on delete is applied.

      Whenever a ProcessInstanceInfo row is deleted, this produces to delete all EVENTTYPES records associated with that process instance.
      Oracle locks the EVENTTYPES table with mode S/Row-X (SSX) . Additionally, it locks the deleted rows with mode Row-X (SX).

      Whenever several ProcessInstanceInfo rows are deleted concurrently, there are several database sessions performing these locks concurrently, which is the cause of the deadlock.

      A more generic description of the situation can be found in:
      http://www.mail-archive.com/hibernate-devel@lists.sourceforge.net/msg05495.html

      As it is suggested in this link, the creation of an index over EVENTTYPES foreign key for ProcessInstanceInfo solves the problem.

      The index we have created is a simple one over the foreign key, as follows:
      CREATE INDEX IDX_EVENTTYPES ON EVENTTYPES (INSTANCEID);

      It would be nice that the automatically created tables contains that index. Alternatively, the documentation should contain some addendum regarding the database tables created, in order to create the index manually, at least for the Oracle database.

              marco.rietveld Marco Rietveld (Inactive)
              jordi_alvarez Jordi Alvarez (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: