Bug 866926

Summary: Create index for the foreign key constraint on the JBPM_TASKINSTANCE.PROCINST_ column
Product: [JBoss] JBoss Enterprise SOA Platform 5 Reporter: Martin Weiler <mweiler>
Component: JBPM - standalone, JBPM - within SOAAssignee: Shelly McGowan <smcgowan>
Status: VERIFIED --- QA Contact: Ivo Bek <ibek>
Severity: high Docs Contact:
Priority: medium    
Version: 5.3.0 GACC: atangrin, ldimaggi, mbaluch, oskutka, rwagner, soa-p-jira
Target Milestone: ER4   
Target Release: 5.3.1   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Users have been experiencing deadlocks in JBPM due to a missing index. Deadlocks are now avoided with the addition of a new index according to the following: # the foreign key constraint (already present in jBPM ddl script): alter table JBPM_TASKINSTANCE add constraint FK_TSKINS_PRCINS foreign key (PROCINST_) references JBPM_PROCESSINSTANCE; # new index to add to the schema: create index IDX_TASKINST_PRCINS on JBPM_TASKINSTANCE (PROCINST_);
Story Points: ---
Clone Of: Environment:
Last Closed: Type: Task
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Description Martin Weiler 2012-10-16 10:41:24 UTC
Description of problem:
We are experiencing problems due to a lock on JBPM DB. We persist JBPM on Oracle 11.2.0.2.0.
The problem may be related to a missing index, we added the following index:
# the foreign key constraint (already present in jBPM ddl script):
alter table JBPM_TASKINSTANCE add constraint FK_TSKINS_PRCINS foreign key (PROCINST_) references JBPM_PROCESSINSTANCE;

# new index to add to the schema:
create index IDX_TASKINST_PRCINS on JBPM_TASKINSTANCE (PROCINST_);

Comment 2 Marco Rietveld 2012-12-06 11:30:29 UTC
Added the index, but the way they word the problem makes me curious as to whether or not this actually was the solution (for them): 

"The problem may be related to a missing index" (emphasis on may.. ). 

However, knowing jBPM 3 and it's DB schema, I wouldn't be all that surprised.

Comment 3 Ivo Bek 2013-02-01 09:24:59 UTC
Verified in SOA-P-5.3.1.ER4