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.
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.
Would you mind posting the indexes you created here so others who try and use jBPM with Oracle can find the solution?
PS: I agree with your comment.
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.
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...
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
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:
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:
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.
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.
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?
Small Hit: Oracle locks the mastertable if there is a DML-Statment on the detailtable and there is no index on foreign key.