8 Replies Latest reply on Nov 22, 2011 6:36 AM by Yuriy R

    JBPM 3.2.5,MSSQL 2005 - blocking in JBPM_JOB table

    Sash K Newbie

      When running on MSSQL 2005 we are experiencing blocking issues in JBPM_JOB table which essentially stalls processing of jobs for extended period of time. Looks like exclusive lock is held on the job table.  Tried turning off lock escalation, but that had no affect.   The only thing that seems to work is setting transaction isolation level to TRANSACTION_READ_UNCOMMITTED.   Is it safe to to set transaction isolation level to READ_UNCOMMITTED?   Is there another, safer solution to this problem?     Thank you in advance for any insight you can offer!

        • 1. Re: JBPM 3.2.5,MSSQL 2005 - blocking in JBPM_JOB table
          Sash K Newbie

          A bit more information.  Looks like the issue is with having a lot jobs that execute quickly, which results in a lot of inserts and deletes, which then results in a lot of updates to the indexes.  Having removed all the indexes in the jbpm_job table has greatly reduced blocking issues, but not eliminated them.  Is there anyway to configure jbpm not to delete jobs, but just mark them as complete?  I would rather have a cleanup script to remove completed jobs on a cycle and reduce index locking frequency.

          • 2. Re: JBPM 3.2.5,MSSQL 2005 - blocking in JBPM_JOB table
            Alejandro Guizar Master

            Is it safe to to set transaction isolation level to READ_UNCOMMITTED?

            No, it is not. HSQLDB supports read-uncommitted isolation only and jBPM exhibits erratic behavior in concurrent processes, particularly in process variables and join nodes. Apart from HSQLDB, jBPM is continuously tested with READ_COMMITTED isolation.

            Is there another, safer solution to this problem?

            Yes. Try a newer jBPM version. At least one fix has gone towards improving job execution.

            Looks like the issue is with having a lot jobs that execute quickly,  which results in a lot of inserts and deletes, which then results in a  lot of updates to the indexes.  Having removed all the indexes in the  jbpm_job table has greatly reduced blocking issues, but not eliminated  them. Is there anyway to configure jbpm not to delete jobs, but just mark them as complete?

            No, but it would make a fine addition. At some point I thought of a configuration switch to delete process instances once they finished, to avoid filling up the database. Along with a switch to not delete jobs and a procedure to clean up old jobs and, optionally, process instances, jBPM would have a complete  cleaning solution.

            1 of 1 people found this helpful
            • 3. Re: JBPM 3.2.5,MSSQL 2005 - blocking in JBPM_JOB table
              Sash K Newbie

              Upgraded to jbpm 3.2.9.  The situation improved somewhat, but still having serious blocking issues.   Before the upgrade over 200 jobs would create blocking issues in mssql server, after upgrade can safely do 500 jobs.  Going to 1000 jobs creates blocking issues, freezing processing for many hours.   On oracle there are no blocking issues, but that was to be expected since oracle does not block.   Upgraded to mssql 2008 as well, but also as expected, did not solve the issue.   Is there some specific information I can provide that would help in troubleshooting ?  

               

              Thank you.

              • 4. Re: JBPM 3.2.5,MSSQL 2005 - blocking in JBPM_JOB table
                Alejandro Guizar Master
                Upgraded to jbpm 3.2.9.  The situation improved somewhat, but still having serious blocking issues.   Before the upgrade over 200 jobs would create blocking issues in mssql server, after upgrade can safely do 500 jobs.  Going to 1000 jobs creates blocking issues, freezing processing for many hours.   On oracle there are no blocking issues, but that was to be expected since oracle does not block.   Upgraded to mssql 2008 as well, but also as expected, did not solve the issue.   Is there some specific information I can provide that would help in troubleshooting ?

                You mentioned earlier that removing all the indexes in the jbpm_job table  greatly reduces blocking issues. Are these numbers produced without indexes? Do you believe the engine would be better off without them? What about Oracle, does it perform better or worse without indexes in the job table? At some point we added indexes to other tables because Oracle deadlocked without them.

                • 5. Re: JBPM 3.2.5,MSSQL 2005 - blocking in JBPM_JOB table
                  Sash K Newbie
                  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?

                  Performs the same.  Tried with 3000, works fine.

                   

                  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!

                  • 6. Re: JBPM 3.2.5,MSSQL 2005 - blocking in JBPM_JOB table
                    Alejandro Guizar Master
                    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.

                    I'm going to explore the possibility of removing the job indexes. Can you please create a JIRA issue for this?

                    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.

                    If possible, could you attach your workflow and test harness to the JIRA issue? Please remove any sensitive information you would not like to see published.

                    • 7. Re: JBPM 3.2.5,MSSQL 2005 - blocking in JBPM_JOB table
                      Sash K Newbie

                      Found a solution that works if you are using mssql 2005 and above. Can allow snapshot on mssql to make it behave more like oracle.  The following two statements should do the trick:

                      • ALTER DATABASE YOUR_DB_NAME SET ALLOW_SNAPSHOT_ISOLATION ON;
                      • ALTER DATABASE YOUR_DB_NAME SET READ_COMMITTED_SNAPSHOT ON;
                      • 8. Re: JBPM 3.2.5,MSSQL 2005 - blocking in JBPM_JOB table
                        Yuriy R Newbie

                        But can we set only JBPM isolation level to READ_COMMITTED_SNAPSHOT ?

                        Not whole DB but only JBPM.

                        Did you try this ?