Hi,

I have been banging my sore head on this for days.

Getting hit with org.hibernate.StaleObjectStateException in our web app when I save an object the second time. I am the only user. There is an issue with the hibernate versioning.

Key points
* We use hibernate version property to detect change. See mapping below
* The db has a trigger which increments the version column value by 1. See trigger below

So the deal is that after I try to persist my object, the version number in the object becomes out of sync with the db. The db value is 1 but my object is holding 0. So when I persist it the second time, hibernate doesn't like it and believes the row was updated by someone else which is was not.

The version increments is database controlled. So I have told hibernate this by using 'generated="always"'. It does not work. I have tried other settings like select-before-update="true" and other ones but it doesn't work. Same error. The only thing that seems to work is to:
1) Issue a refresh of the object in the dao after it saves. Gotta be bad for performance though. Yuck.
2) Use a org.springframework.orm.hibernate3.support.OpenSessionInViewFilter. We are trying to avoid using this. It should not be necessary.
3) Remove the db trigger that increments the version column value and give Hibernate full control with this instead.

I like option 3 above but our db guy is reluctant to remove the trigger. This is the first project I've come across that uses a db trigger for the version. Usually we give hibernate full control but I may not have that option on this project.

I have told hibernate that the versioning is database controlled so I am not sure why hibernate is not dealing with it properly. It should know that once it persists something, it should re-query the version value because the db has probably changed it (+1). And yes the session is probably opening and closing with each dao call. Probably has something to do with this issue and why using the OpenSessionInViewFilter seems to work.

All relevant logs, code and mappings below. Please let me know if you see something that I have set incorrectly or that I should try.

cheers,
Daniel



App Log
[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)' 02:12:10 au.gov.wa.commerce.associations.web.job.AssessmentTaskController init DEBUG line 96 -*** save 1
[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)' 02:12:10 au.gov.wa.commerce.associations.persistence.job.JobDAOImpl save DEBUG line 23 -saving Job instance
[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)' 02:12:11 au.gov.wa.commerce.associations.persistence.job.JobDAOImpl save DEBUG line 27 -save successful
[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)' 02:12:11 au.gov.wa.commerce.associations.web.job.AssessmentTaskController init DEBUG line 99 -*** save 2
[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)' 02:12:11 au.gov.wa.commerce.associations.persistence.job.JobDAOImpl save DEBUG line 23 -saving Job instance
1339007 [[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'] ERROR org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session
org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [au.gov.wa.commerce.associations.common.model.job.checklist.AssessmentChecklistItem#9234]

6Spy.log (jdbc wrapper) - The last sql statement
1278051132103|0|16|statement|update ACC.ACC_ASSESS_CHECKLIST_ITEM set JOB_TYPE_CHECKLIST_ITM_ID=?, TASK_ASSESSMENT_ID=?, ANSWER=?, COMMENTS=?, CLAUSE_NUMBER=?, DB_CREATED_BY=?, DB_CREATED_ON=?, DB_MODIFIED_BY=?, DB_MODIFIED_ON=? where ASSESSMENT_CHECKLIST_ITEM_ID=? and VERSION=?|update ACC.ACC_ASSESS_CHECKLIST_ITEM set JOB_TYPE_CHECKLIST_ITM_ID=8, TASK_ASSESSMENT_ID=346, ANSWER='', COMMENTS='', CLAUSE_NUMBER='', DB_CREATED_BY='ACC', DB_CREATED_ON='2010-07-02 14:12:11.0', DB_MODIFIED_BY='', DB_MODIFIED_ON='' where ASSESSMENT_CHECKLIST_ITEM_ID=9234 and VERSION=0

The version number in the database at this point is 1 and not 0, hence the sql failure.


<hibernate-mapping package="au.gov.wa.commerce.associations.common.model.job.checklist">
<class name="AssessmentChecklistItem" schema="ACC" table="ACC_ASSESS_CHECKLIST_ITEM">
<id name="assessmentChecklistItemId" type="java.lang.Long">
<column name="ASSESSMENT_CHECKLIST_ITEM_ID" precision="12" scale="0" />
<generator class="sequence">
<param name="sequence">ACC_ASSESS_CHECKLIST_ITEM_SEQ</param>
</generator>
</id>
<version name="version" type="java.lang.Long" generated="always">
<column name="VERSION" precision="12" scale="0" />
</version>
<many-to-one name="assessmentTask" class="au.gov.wa.commerce.associations.common.model.job.AssessmentTask" fetch="select">
<column name="TASK_ASSESSMENT_ID" precision="12" scale="0" not-null="true" />
</many-to-one>
...
..

Our job dao impl
public void save(Job job) {
log.debug("saving Job instance");
try {
sessionFactory.getCurrentSession().saveOrUpdate(job);
sessionFactory.getCurrentSession().flush();
log.debug("save successful");
} catch (RuntimeException re) {
log.error("save failed", re);
throw re;
}
}



Oracle DB Trigger
..
..
BEGIN
IF INSERTING THEN
:NEW.db_created_by := NVL(:NEW.db_created_by, USER);
:NEW.db_created_on := NVL(:NEW.db_created_on, SYSDATE);
:NEW.version := 0;
ELSIF UPDATING THEN
:NEW.db_modified_by := USER;
:NEW.db_modified_on := SYSDATE;
:NEW.version := :OLD.version + 1;
END IF;
END;
/