[jboss-user] [jBPM] New message: "Re: jbpm 4.3 - org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update"

[Zengping Tian]

User development,

A new message was posted in the thread "jbpm 4.3 - org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update":

http://community.jboss.org/message/522012#522012

Author  : Zengping Tian
Profile : http://community.jboss.org/people/zptian

Message:
--------------------------------------------------------------
While we strip down the logic to remove the subprocess. It seems running fine with multiple job executors(haven't seen issue so far).
 
When we check the sql statements to create database tables for db2, it doesn't define the unique constrain of ID_ on the JBPM4_EXECUTION table.  But that field is unique in the sql statements for HSQL and MySQL at least. the Unique constrain is defined in the mapping file. Think that's why we see different issues in db2.
 
We also did some analysis on the deadlock issue we experienced on db2.
 Have identified the statements to lock tables and release them.  Have captured the stack trace and the java classes(the beauty of open sources ) below.
 
 *Deadlock in Hibernate*  
>From our observation , in Hibernate a group of tables are inserted/updated in one Transaction and all tables involved(not the whole database as we thought on Friday night, thought we just happened to check the tables were involved in the lock) are LOCKED before the commit on the jdbc connection was called.
 In details once the statement.executeUpdate() is called on a query, the table involves in the query is locked until the commit statement is called. So just before the commit statement all tables involved are actually locked. At that moment if another thread involves in any operation(eg. read or update) on any of the tables, it will cause a DEADLOCK in the default isolation level(think it’s repeatable read in DB2Dialect)
 In our case it involves multiple tables to create an jbpm instance, they are:
 Jbpm4_execution
Jbpm4_hist_procinst
Jbpm4_job
Jbpm4_variable
Jbpm4_lob
 From Hibernate’s perspective, I  think that might be expected behaviour as a group of tables may have some relations, eg. 1-m, m-m relations, so it’s nature to deal with them in one transaction. If not lock them together( I have tested by change the DB2Dialect to update with NC) it will have some wired behaviours – later on jbpm cannot find some rows of data it expects them to be there.
Behaviour of JBPM  
On the jbpm side, we know:
1)      If an instance key isn’t unique, it will definitely cause problem. We observe this situation while we have subprocess (all subprocesses are created as an instance which has no key. Given the limitation in the subprocess key field that we cannot use expression, this is an issue). The Custom activity is trying to create subprocess as well so have this similar issue
2)      A standard jbpm process without subprocess seems running fine with multiple job executors(??? We just observe this from our test with the strip down process definition ). That means the tables involving in executing one activity( not include subprocess activity of course ) are properly isolated in the jbpm level.
 If both 1) and 2) are true, think we have no problem to use it in our environment. We just need to take the inconvenience of exception handling in the DF CARMI call, and be creative to control the 1-m relations between an instance and its sub-tasks….
 
 *A stack trace to identify the statement which lock the tables:*  http://community.jboss.org/servlet/JiveServlet/showImage/1857/jbpm_db2_lock_tables.png
 
 *A stack trace to identify the unlock statement:* http://community.jboss.org/servlet/JiveServlet/showImage/1858/jbpm_db2_unlock_tables.png

--------------------------------------------------------------

To reply to this message visit the message page: http://community.jboss.org/message/522012#522012