[jboss-user] [jBPM] - Re: JBPM 3.2.5, MSSQL 2005 - blocking in JBPM_JOB table

[Sash K]

Sash K [http://community.jboss.org/people/swishey] created the discussion

"Re: JBPM 3.2.5,MSSQL 2005 - blocking in JBPM_JOB table"

To view the discussion, visit: http://community.jboss.org/message/563167#563167

--------------------------------------------------------------
> > 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  https://jira.jboss.org/browse/JBPM-2094 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!
--------------------------------------------------------------

Reply to this message by going to Community
[http://community.jboss.org/message/563167#563167]

Start a new discussion in jBPM at Community
[http://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/jboss-user/attachments/20100922/ac07427b/attachment.html