StaleStateException on insert

eqbridges · **Posted:** Wed Feb 08, 2006 5:35 pm

This is saving a list of detached objects that were created in a separate session mixed with some related transient objects that need to be persisted. The list consists of a collection of ScenarioAnalysisResultImpl transient objects that each contain references to other detached objects (e.g. PortfolioResultImpl).

The error is thrown when the transient instances are being inserted. In the example below this is happening twice: once for a ScenarioAnalysisResultImpl instance, and once for a Result instance.

Feedback much appreciated.

Thanks

Hibernate version: 3.1.2

Name and version of the database you are using: Sybase 12.5 ASE

Mapping documents:

Code:

<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="citco.esd.risk.scenario.domain.result">
    <class name="ScenarioAnalysisResultsImpl" table="risk.dbo.execution_log">
        <id name="executionId" column="execution_id" type="long" unsaved-value="null">
            <generator class="hilo">
                <param name="table">risk.dbo.hibernate_unique_key</param>
                <param name="column">next_hi</param>
                <param name="max_lo">100</param>
            </generator>
        </id>
        <version name="version"/>
        <many-to-one class="citco.esd.risk.scenario.domain.config.PortfolioResultImpl" name="portfolioResult"
                     column="res_portfolio_id" not-null="true"/>
        <property name="valueDate" column="value_date" type="date" not-null="true"/>
        <property name="executionStart" column="execution_start" type="timestamp" not-null="true"/>
        <property name="executionEnd" column="execution_end" type="timestamp" not-null="true"/>
        <property name="executionStatus" column="execution_status" type="citco.esd.risk.scenario.domain.result.ExecutionStatus" not-null="true"/>
        <property name="executingUser" column="executing_user" type="string" not-null="true"/>
        <property name="executionOwner" column="execution_owner" type="int" not-null="false"/>
        <bag name="results" cascade="all,delete-orphan">
            <key column="execution_id"/>
            <one-to-many class="citco.esd.risk.scenario.domain.result.Result"/>
        </bag>
    </class>
</hibernate-mapping>

Code:

<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="citco.esd.risk.scenario.domain.result">
    <class name="Result" table="risk.dbo.execution_result">
        <id name="resultId" column="result_id" type="long" unsaved-value="null">
            <generator class="hilo">
                <param name="table">risk.dbo.hibernate_unique_key</param>
                <param name="column">next_hi</param>
                <param name="max_lo">100</param>
            </generator>
        </id>
        <version name="version"/>
        <property name="resultValue" column="result_value" type="java.math.BigDecimal" not-null="false"/>
        <property name="executionNotes" column="execution_notes" type="string" not-null="true"/>
        <property name="instrumentId" column="tid" type="int" not-null="true"/>
        <property name="executionStart" column="execution_start" type="timestamp"/>
        <property name="executionEnd" column="execution_end" type="timestamp"/>
        <many-to-one class="citco.esd.risk.scenario.domain.result.ScenarioAnalysisResultsImpl" name="executionLog"
                     column="execution_id" not-null="true"/>
        <set name="scenarioCases" table="risk.dbo.result_cases" cascade="all,delete-orphan">
          <key column="result_id"/>
          <many-to-many column="res_scenario_case_id" class="citco.esd.risk.scenario.domain.config.ScenarioCaseResultImpl"/>
        </set>
    </class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

    protected void storeResults(List resultList) {
        if(null != resultList && !resultList.isEmpty()) {
            if (log.isDebugEnabled()) {
                log.debug("Storing ["+resultList.size()+"] execution resultList.");
            }
            Session s = HibernateUtil.getSession();
            Transaction tx = s.beginTransaction();
            try {
                Iterator i = resultList.iterator();
                while(i.hasNext()) {
                    ScenarioAnalysisResults results = (ScenarioAnalysisResults) i.next();
                    s.save(results);
                }
                tx.commit();
            } catch (Throwable e) {
                log.error("Caught " + e.getClass().getName() + ". Message is: " + e.getMessage(), e);
                tx.rollback();
            } finally {
                HibernateUtil.closeSession();
            }
        } else {
            if (log.isDebugEnabled()) {
                log.debug("No execution resultList to store.");
            }
        }
    }

Full stack trace of any exception that occurs:
[2006-02-08 16:01:43] DEBUG AbstractFlushingEventListener - executing flush
[2006-02-08 16:01:43] DEBUG AbstractEntityPersister - Inserting entity: [citco.esd.risk.scenario.domain.result.ScenarioAnalysisResultsImpl#57974]
[2006-02-08 16:01:43] DEBUG AbstractEntityPersister - Version: 0
[2006-02-08 16:01:43] DEBUG AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
[2006-02-08 16:01:43] DEBUG SQL -
insert
into
risk.dbo.execution_log
(version, res_portfolio_id, value_date, execution_start, execution_end, execution_status, executing_user, execution_owner, execution_id)
values
(?, ?, ?, ?, ?, ?, ?, ?, ?)
[2006-02-08 16:01:43] DEBUG AbstractBatcher - preparing statement
[2006-02-08 16:01:43] DEBUG AbstractEntityPersister - Dehydrating entity: [citco.esd.risk.scenario.domain.result.ScenarioAnalysisResultsImpl#57974]
[2006-02-08 16:01:43] DEBUG IntegerType - binding '0' to parameter: 1
[2006-02-08 16:01:43] DEBUG VersionValue - version unsaved-value strategy UNDEFINED
[2006-02-08 16:01:43] DEBUG IdentifierValue - id unsaved-value: null
[2006-02-08 16:01:43] DEBUG LongType - binding '57469' to parameter: 2
[2006-02-08 16:01:43] DEBUG DateType - binding '30 November 2005' to parameter: 3
[2006-02-08 16:01:43] DEBUG TimestampType - binding '2006-02-08 16:01:13' to parameter: 4
[2006-02-08 16:01:43] DEBUG TimestampType - binding '2006-02-08 16:01:37' to parameter: 5
[2006-02-08 16:01:43] DEBUG StringType - binding 'DefaultExecutionContext' to parameter: 7
[2006-02-08 16:01:43] DEBUG IntegerType - binding null to parameter: 8
[2006-02-08 16:01:43] DEBUG LongType - binding '57974' to parameter: 9
[2006-02-08 16:01:43] DEBUG AbstractEntityPersister - Inserting entity: [citco.esd.risk.scenario.domain.result.Result#58075]
[2006-02-08 16:01:43] DEBUG AbstractEntityPersister - Version: 0
[2006-02-08 16:01:43] DEBUG AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[2006-02-08 16:01:43] DEBUG AbstractBatcher - closing statement
[2006-02-08 16:01:43] DEBUG AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
[2006-02-08 16:01:43] DEBUG SQL -
insert
into
risk.dbo.execution_result
(version, result_value, execution_notes, tid, execution_start, execution_end, execution_id, result_id)
values
(?, ?, ?, ?, ?, ?, ?, ?)
[2006-02-08 16:01:43] DEBUG AbstractBatcher - preparing statement
[2006-02-08 16:01:43] DEBUG AbstractEntityPersister - Dehydrating entity: [citco.esd.risk.scenario.domain.result.Result#58075]
[2006-02-08 16:01:43] DEBUG IntegerType - binding '0' to parameter: 1
[2006-02-08 16:01:43] DEBUG BigDecimalType - binding '0.5585850072749896' to parameter: 2
[2006-02-08 16:01:43] DEBUG StringType - binding 'success' to parameter: 3
[2006-02-08 16:01:43] DEBUG IntegerType - binding '500014174' to parameter: 4
[2006-02-08 16:01:43] DEBUG TimestampType - binding '2006-02-08 16:01:22' to parameter: 5
[2006-02-08 16:01:43] DEBUG TimestampType - binding '2006-02-08 16:01:27' to parameter: 6
[2006-02-08 16:01:43] DEBUG LongType - binding '57974' to parameter: 7
[2006-02-08 16:01:43] DEBUG LongType - binding '58075' to parameter: 8
[2006-02-08 16:01:43] ERROR AbstractFlushingEventListener - Could not synchronize database state with session
org.hibernate.StaleStateException: Unexpected row count: 0 expected: 1
at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:27)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2062)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2427)
at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:51)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:243)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:227)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:296)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1009)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:356)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at citco.esd.risk.scenario.main.AbstractExecutionContext.storeResults(AbstractExecutionContext.java:125)
at citco.esd.risk.scenario.main.DefaultExecutionContext.run(DefaultExecutionContext.java:82)
at citco.esd.risk.scenario.main.DefaultExecutionContext.main(DefaultExecutionContext.java:94)
[2006-02-08 16:01:43] ERROR AbstractExecutionContext - Caught org.hibernate.StaleStateException. Message is: Unexpected row count: 0 expected: 1
org.hibernate.StaleStateException: Unexpected row count: 0 expected: 1
at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:27)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2062)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2427)
at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:51)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:243)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:227)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:296)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1009)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:356)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at citco.esd.risk.scenario.main.AbstractExecutionContext.storeResults(AbstractExecutionContext.java:125)
at citco.esd.risk.scenario.main.DefaultExecutionContext.run(DefaultExecutionContext.java:82)
at citco.esd.risk.scenario.main.DefaultExecutionContext.main(DefaultExecutionContext.java:94)
[2006-02-08 16:01:43] DEBUG JDBCTransaction - rollback
[2006-02-08 16:01:43] DEBUG JDBCTransaction - rolled back JDBC Connection

zzyborg · **Posted:** Fri Feb 10, 2006 1:04 am

StaleStateException is thrown went some other transaction has modified the object in action . you can make use of versioning in hibernate to get around this

eqbridges · **Posted:** Fri Feb 10, 2006 8:08 am

Note from the mapping files that versioning is turned on.

abg1979 · **Joined:** Fri Sep 09, 2005 11:35 am **Posts:** 101

Have you tried to execute this query directly on the database?
Does it work there?

Are there any SQLExceptions also?

eqbridges · **Posted:** Fri Feb 10, 2006 12:02 pm

Yes, the inserts work as expected when run against the db from outside of hbm. Even tried doing it via a vanilla jdbc connection obtained from the session.

No sql exceptions, either.

I can provide more detail if necessary. I'm currently coding up a workaround right now using a plain jdbc connection and handwritten sql. I'd prefer not to do that of course.

Do the mappings seem correct?

steve · **Posted:** Fri Feb 10, 2006 11:35 pm

The execution may well in fact suceed, but what is being reported here is that the JDBC connection said that ZERO rows were affected by that insert statement while, obviously, Hibernate is expecting ONE.

eqbridges · **Posted:** Fri Feb 10, 2006 11:59 pm

Yes, indeed. As well, I would be expecting the insert to work as well. The question is *why* is it not working?

In pursuing it further, I *think* it may have to do with the property named "resultValue" in the Result class.

When executing it has a value that goes up to approx. 15-20 decimal places (depending on the calc that happens). This is causing an error when inserting to the database since the column has a specified precision of (20,10) -- i.e. 10 places. The problem is that hibernate is not reporting this error, and instead we're just getting zero rows back when it should be one.

damonf · **Joined:** Wed Dec 21, 2005 6:57 pm **Posts:** 70

This is basically an optimistic lock error.

As someone said: " ZERO rows were affected by that insert statement while, obviously, Hibernate is expecting ONE." The REASON it expects one is that it is doing an update with a where clause (with just an ID or both and ID and a version if you use versioning) and the where clause does not match -- so no rows were updated.

Your object has the guid/version combination of either an old, stale object, or of a deleted object.

eqbridges · **Posted:** Tue May 23, 2006 5:28 am

Please look again at the log files. This is doing an INSERT not an UPDATE. The number of rows affected (zero) is due to the insert not affecting any rows.

Indeed these are *new* objects, so to propose that they're stale is besides the point. They're not detached objects where the data is being updated, rather they're new objects where the new data is being created.

damonf · **Joined:** Wed Dec 21, 2005 6:57 pm **Posts:** 70

... also synchronizing disconnected pojos with session.update() on a new object can cause this. The resulting "INSERT" statement will update 0 rows. Use saveOrUpdate() in this case.

eqbridges · **Posted:** Tue May 23, 2006 6:49 pm

If you look at the code above once again, you'll see that it's doing a session.save() which should work correctly -- it shouldn't be doing an isUpdated check, no?