[jboss-jira] [JBoss JIRA] Created: (JBRULES-3109) drools-flow: deadlock for concurrent process completions in oracle

[Jordi Alvarez (JIRA)]

drools-flow: deadlock for concurrent process completions in oracle
------------------------------------------------------------------

                 Key: JBRULES-3109
                 URL: https://issues.jboss.org/browse/JBRULES-3109
             Project: Drools
          Issue Type: Bug
      Security Level: Public (Everyone can see)
          Components: drools-core (flow)
    Affects Versions: 5.1.1.FINAL
         Environment: Use of Oracle RDBMS as the dabase system
            Reporter: Jordi Alvarez
            Assignee: Mark Proctor


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.


--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira