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/msg0549... 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