[jbpm-issues] [JBoss JIRA] Reopened: (JBPM-2094) Unindexed Foreign Keys cause deadlocks in oracle

Unindexed Foreign Keys cause deadlocks in oracle ------------------------------------------------ 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: Runtime Engine Affects Versions: jBPM 3.2.0 Environment: aix/red hat linux, websphere 6.1, jvm 1.5 Reporter: Elhanan Maayan Assignee: Alejandro Guizar Fix For: jBPM 3.2.x 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_") ; 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_") ; 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.