9 Replies Latest reply on May 11, 2006 7:13 AM by Matthias Loeblich

    Required indexes missing in JBPM DB script.

    Pravin kamble Newbie

      required indexes missing in JBPM DB script.
      Due to this deadlock occurs in JBPM DB.

      Following is the Oracle DB log:

      Changing data in table JBPM_TOKEN will lock table JBPM_PROCESSINSTANCE
      Create an index on table JBPM_PROCESSINSTANCE with the following columns to remove lock problem
      Column = ROOTTOKEN_ (1)
      .

      Can somebody provide me sql script with required indexes?Since i don't know the JBPM schema well.
      This problem occurs due to heavy traffics on DB.



        • 1. Re: Required indexes missing in JBPM DB script.
          Daniel Brum Novice

          You should turn on SQL trace in the hibernate properties and analyze the queries to figure out which indexes you will need. I did that with MySQL once to make it process the sql queries more efficiently.

          • 2. Re: Required indexes missing in JBPM DB script.
            Pravin kamble Newbie

            I did that & created the required indexes.But this is the problem with JBPM DB on heavy traffic of user.JBPM should provide the script to create required indexex to avoid this problem as a pracuationary measures.

            • 3. Re: Required indexes missing in JBPM DB script.
              Daniel Brum Novice

              Would you mind posting the indexes you created here so others who try and use jBPM with Oracle can find the solution?

              thanks.

              PS: I agree with your comment.

              • 4. Re: Required indexes missing in JBPM DB script.
                Ronald van Kuijk Master

                I partly agree. Some people use integer colums to find task, some use other colums. jBPM cannot provide all these indexes out of the box, unless we say 'index everything)

                We can however provide information either in the wiki or in some faq to give directions.

                • 5. Re: Required indexes missing in JBPM DB script.
                  Koen Aers Master

                  While it is true that jBPM should come with reasonable default settings, I think it is the job of the DBA to tune the database according to the expected use. Will there be many process creations? Many queries? These considerations may lead to different indexes...

                  Regards,
                  Koen

                  • 6. Re: Required indexes missing in JBPM DB script.
                    Pravin kamble Newbie

                    Hi Dan,
                    Following are the indexes we ceated to avoid deadlocks.

                    create index JBPM_LOG_ust_n7 on JBPM_LOG(PARENT_) tablespace bce_index ;

                    create index JBPM_TASKINSTANCE_ust_n4 on JBPM_TASKINSTANCE(TOKEN_) tablespace bce_index ;

                    create index JBPM_TOKEN_ust_n3 on JBPM_TOKEN(PROCESSINSTANCE_) tablespace bce_index ;


                    But still we are facing the deadlock issue.Here is the DB log.
                    Caused by: java.sql.BatchUpdateException: ORA-00060: deadlock detected while waiting for resource

                    DEADLOCK DETECTED

                    Current SQL statement for this session:

                    update JBPM_TOKEN set NAME_=:1, START_=:2, END_=:3, NODEENTER_=:4, NEXTLOGINDEX_=:5, ISABLETOREACTIVATEPARENT_=:6, ISTERMINATIONIMPLICIT_=:7, NODE_=:8, PROCESSINSTANCE_=:9, PARENT_=:10 where ID_=:11

                    The following deadlock is not an ORACLE error. It is a

                    deadlock due to user error in the design of an application

                    or from issuing incorrect ad-hoc SQL. The following

                    information may aid in determining the deadlock:

                    Deadlock graph:

                    ---------Blocker(s)-------- ---------Waiter(s)---------

                    Resource Name process session holds waits process session holds waits

                    TM-0000258e-00000000 14 22 SX 13 27 SX SSX

                    TX-0007001a-0000612e 13 27 X 14 22 X

                    session 22: DID 0001-000E-00001106 session 27: DID 0001-000D-00002EA8

                    session 27: DID 0001-000D-00002EA8 session 22: DID 0001-000E-00001106

                    Rows waited on:

                    Session 27: obj - rowid = 00002596 - AAACWWAAQAAAAAAAAA

                    (dictionary objn - 9622, file - 16, block - 0, slot - 0)

                    Session 22: obj - rowid = 0000258E - AAACWOAAQAAAAMGABc

                    (dictionary objn - 9614, file - 16, block - 774, slot - 92)

                    Information on the OTHER waiting sessions:

                    Session 27:

                    pid=13 serial=55637 audsid=738276 user: 99/COMWFLSVC

                    O/S info: user: jbsadmin, term: unknown, ospid: , machine: s0161cdc

                    program: JDBC Thin Client

                    application name: JDBC Thin Client, hash value=0

                    Current SQL Statement:

                    delete from JBPM_PROCESSINSTANCE where ID_=:1

                    End of information on OTHER waiting sessions.

                    • 7. Re: Required indexes missing in JBPM DB script.
                      Pravin kamble Newbie

                      I have design the common BPM application which is used by different applications.It leads to so many process definition & process instances.
                      Due to mission of indexes, i am facing the deadlock issue on DB.
                      I created the few indexes on DB, but still the problem is still there.
                      Please provide some more focus on this.

                      • 8. Re: Required indexes missing in JBPM DB script.
                        Koen Aers Master

                        I have the impression that your deadlocks are due to your clientcode using jBPM. Try to analyze the situation when this happens and what the different callstacks of your client applications are. Do you use locks in your client applications?

                        Regards,
                        Koen

                        • 9. Re: Required indexes missing in JBPM DB script.
                          Matthias Loeblich Newbie

                          Small Hit: Oracle locks the mastertable if there is a DML-Statment on the detailtable and there is no index on foreign key.


                          Regards
                          Matthias