You mentioned earlier that removing all the indexes in the jbpm_job table greatly reduces blocking issues. Are these numbers produced without indexes?
Yep, all the numbers are produced without indexes on the jbpm_job table.
What about Oracle, does it perform better or worse without indexes in the job table?
On Oracle the indexes are in place. I will remove the indexes and run a test. Will let you know as soon as have the results.
Do you believe the engine would be better off without them?
Looking at how the jbpm_job table is utilized, yes, I think these specific indexes can be removed. Just to clarify. From what I have seen in our use-case, under normal circumstances the jbpm_job table has very few entries at any point in time. Seems like entries are added and removed constantly. That being the case, I'm not sure I can see a benefit of spending the extra time/resources maintaining indexes which are never utilized.
By the way, this is as good a time to explain the use-case that is failing for us. We have a workflow with three steps synchronous steps. The first step takes a couple to a few hundred milliseconds to complete, the second step can take anywhere from 5 to 30 seconds to complete and then the last step usually takes a half a second. When I mentioned jobs earlier, I was referring to workflows contained the three steps just mentioned.
At some point we added indexes to other tables because Oracle deadlocked without them.
I'm missing something, but can't seem to understand how lack of an index can play a role in a deadlock here. I can see how full table scans on a table with a lot of data like jbpm_log can produce long delays and heavy load on db, but a deadlock seems kind a weird.
Thank you for taking the time to look at this issue!