[jbpm-issues] [JBoss JIRA] Created: (JBPM-2094) Unindexed Foreign Keys cause deadlocks in oracle when trying to kill a worklfow
Unindexed Foreign Keys cause deadlocks in oracle when trying to kill a worklfow
-------------------------------------------------------------------------------
Key: JBPM-2094
URL: https://jira.jboss.org/jira/browse/JBPM-2094
Project: JBoss jBPM
Issue Type: Bug
Security Level: Public (Everyone can see)
Components: Core Engine
Affects Versions: jBPM 3.2.0
Environment: aix/red hat linux, websphere 6.1, jvm 1.5
Reporter: Elhanan Maayan
attempting to execute deleteProcessInstance api using the follwoing code:
LOG.info("Delete process instance " + pi.getId());
try {
jbpmContext.getGraphSession().deleteProcessInstance(pi, true, true);
LOG.info("finished process instance deleted ");
}
catch (RuntimeException ex) {
LOG.error("problems while delete process", ex);
throw ex;
}
if this code runs in parallal at the same time (like for example from 2 different http
requests) then records in jbpm_log table will be deleted, however since jbpm_log has about
13 parent table, and none of them are indexed foreign keys, all 13 will be table locked,
causing one process to be deaclocked.
after creating more indexes manually this seemed to slove the problem:
CREATE INDEX "JBPM_LOG_INDEX1" ON "JBPM_LOG" ("ACTION_") ;
CREATE INDEX "JBPM_LOG_INDEX_CHILD" ON "JBPM_LOG"
("CHILD_") ;
CREATE INDEX "JBPM_LOG_INDEX_DESTINATIONNODE" ON "JBPM_LOG"
("DESTINATIONNODE_") ;
CREATE INDEX "JBPM_LOG_INDEX_NEWBYTEARRAY" ON "JBPM_LOG"
("NEWBYTEARRAY_") ;
CREATE INDEX "JBPM_LOG_INDEX_NODE" ON "JBPM_LOG" ("NODE_")
;
CREATE INDEX "JBPM_LOG_INDEX_OLDBYTEARRAY" ON "JBPM_LOG"
("OLDBYTEARRAY_") ;
CREATE INDEX "JBPM_LOG_INDEX_PARENT" ON "JBPM_LOG"
("PARENT_") ;
CREATE INDEX "JBPM_LOG_INDEX_SOURCENODE" ON "JBPM_LOG"
("SOURCENODE_") ;
CREATE INDEX "JBPM_LOG_INDEX_SWIMINST" ON "JBPM_LOG"
("SWIMLANEINSTANCE_") ;
CREATE INDEX "JBPM_LOG_INDEX_TASKINSTANCE" ON "JBPM_LOG"
("TASKINSTANCE_") ;
CREATE INDEX "JBPM_LOG_INDEX_TOKEN" ON "JBPM_LOG"
("TOKEN_") ;
CREATE INDEX "JBPM_LOG_INDEX_TRANSITION" ON "JBPM_LOG"
("TRANSITION_") ;
CREATE INDEX "JBPM_LOG_INDEX_VARINSTANCE" ON "JBPM_LOG"
("VARIABLEINSTANCE_") ;
however i would expect this to be included in the hbm files as this is not a performance
issue but actually a causing a failure in our application.
CREATE INDEX "JBPM_BYTEARR_INDEX_FILE" ON "JBPM_BYTEARRAY"
("FILEDEFINITION_") ;
CREATE INDEX "JBPM_VARINST_INDEX_TINSTANCE" ON
"JBPM_VARIABLEINSTANCE" ("TASKINSTANCE_") ;
CREATE INDEX "JBPM_VARINST_INDEX_ARR" ON
"JBPM_VARIABLEINSTANCE" ("BYTEARRAYVALUE_") ;
CREATE INDEX "JBPM_MODINST_INDEX_DF" ON "JBPM_MODULEINSTANCE"
("TASKMGMTDEFINITION_") ;
i believe this directly relates to : https://jira.jboss.org/jira/browse/JBPM-931
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira