[Hibernate-issues] [Hibernate-JIRA] Commented: (HB-1556) unique constraint error with increment generator, updating two database instances

[Christy P. (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HB-1556?page=comments#action_23350 ] 

Christy Pang commented on HB-1556:
----------------------------------

I am planning to upgrade to  Hibrenate 3.1.3 because of this issue, 

Are there any issues I should be aware of to upgrade from 2.1.8 to Hibernate 3.1.3?

Thanks
Christy

> unique constraint error with increment generator, updating two database instances
> ---------------------------------------------------------------------------------
>
>          Key: HB-1556
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HB-1556
>      Project: Hibernate2
>         Type: Bug

>   Components: core
>     Versions: 2.1.8
>  Environment: Hibernate 2.1.8, Oracle 9i, Tomcat
>     Reporter: Dwight Harm

>
>
> I'm updating two different instances of Oracle while running under Tomcat. The increment generator is using the highest value from the first instance when it generates a new key for the same table in the second instance. This results in a unique key constraint violation.
> Running a simplified test program in batch works, and Hibernate debug shows 2 calls that fetch the first free id (one for each db):
> id.IncrementGenerator::getNext(76): first free id: 123461
> But the real app, running under Tomcat, only shows one such trace. In this case, the first update, to db#1, runs under an http-processor thread; that generates the "first free id" trace. Later, a worker thread running a Quartz-scheduled task opens both db#1 and db#2, and adds a new "Offer" to db#2. No "first free id" trace appears. I've included a condensed version of the failing task's code below.
> Hibernate version: 2.1.8
> database: Oracle 9i
> Mapping documents:
> <hibernate-mapping package="com.xyz.admin.data">
> <class name="Offer" table="OFFER">
> <id name="id" type="java.lang.Long">
> <column name="OFFER_ID" sql-type="number" not-null="true"/>
> <generator class="increment"/>
> </id>
> Code from openSession() to failure:
> localSessionFactory = _hibernateEnvironments.getLocalSessionFactory();
> localSession = localSessionFactory.openSession();
> localTransaction = localSession.beginTransaction();
> localSession.update(_offerJob); // re-associate this with our session
> _offerJob.setMessage( "initializing" );
> targetSessionFactory = _hibernateEnvironments.getSessionFactory( _offerJob.getTargetEnvironmentName() );
> targetSession = targetSessionFactory.openSession();
> offers = _offerJob.getDeployableOffers();
> for (Iterator iter = offers.iterator(); iter.hasNext(); ) {
> DeployableOffer deployOffer = (DeployableOffer)iter.next();
> Offer sourceOffer = deployOffer.getOffer();
> Long sourceId = sourceOffer.getId();
> // it seemed necessary to do a 'get' here to avoid LazyInitialization errors later.
> sourceOffer = (Offer)localSession.get(Offer.class, sourceId);
> <calls method:>
> _targetSessionFactory = targetSession.getSessionFactory();
> Offer targetOffer = null;
> sourceOffer.setSynchronizationKey(syncKey);
> ClassMetadata offerMetadata = _targetSessionFactory.getClassMetadata( Offer.class );
> Transaction targetTransaction = targetSession.beginTransaction();
> targetOffer = (Offer)offerMetadata.instantiate(null);
> targetOffer.setSynchronizationKey(syncKey);
> /* ... update targetOffer fields... */
> targetOffer.setWebStatus(...);
> targetSession.saveOrUpdate( targetOffer );
> targetTransaction.commit();
> /* update of target offer fails here due to unique key constraint violation on Offer_id */
> stack trace:
> * ERROR 10:23:49.671 [Promotion Admin_Worker-2] deploy.OfferSynchronizer::synchronizeOffer(225): error synchronizing;
> net.sf.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
> at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:73)
> at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
> at net.sf.hibernate.impl.BatcherImpl.convert(BatcherImpl.java:328)
> at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:135)
> at net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:61)
> at net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:58)
> at net.sf.hibernate.impl.BatcherImpl.prepareBatchStatement(BatcherImpl.java:111)
> at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:454)
> at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:436)
> at net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.java:37)
> at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2449)
> at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2435)
> at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2392)
> at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2261)
> at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
> at com.xyz.promotion.admin.deploy.OfferSynchronizer.synchronizeOffer(OfferSynchronizer.java:214)
> at com.xyz.promotion.admin.deploy.OfferDeployer.execute(OfferDeployer.java:156)
> at com.xyz.promotion.admin.deploy.DeployQuartzJob.execute(DeployQuartzJob.java:72)
> at org.quartz.core.JobRunShell.run(JobRunShell.java:191)
> at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:516)
> Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (TSI.SYS_C002728) violated
> at oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:459)
> at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:3907)
> at com.mchange.v2.sql.filter.FilterPreparedStatement.executeBatch(FilterPreparedStatement.java:260)
> at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
> at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:128)
> ... 16 more
> Debug level Hibernate log excerpt:
> * DEBUG 12:32:34.968 [http-9980-Processor24] id.IncrementGenerator::getNext(62): fetching initial value: select max(OFFER_ID) from OFFER
> * DEBUG 12:32:34.984 [http-9980-Processor24] id.IncrementGenerator::getNext(76): first free id: 123462
> [the above occurs during creation of new Offer on db#1]
> * DEBUG 12:33:01.437 [http-9980-Processor24] id.SequenceGenerator::generate(76): Sequence identifier generated: 124
> * INFO 12:34:14.109 [Promotion Admin_Worker-1] deploy.DeployQuartzJob::execute(60): Deploy Job executing
> * INFO 12:34:14.234 [Promotion Admin_Worker-1] deploy.OfferDeployer::execute(151): processing offerid=123462
> * WARN 12:34:15.093 [Promotion Admin_Worker-1] util.JDBCExceptionReporter::logExceptions(57): SQL Error: 1, SQLState: 23000
> * ERROR 12:34:15.109 [Promotion Admin_Worker-1] util.JDBCExceptionReporter::logExceptions(58): ORA-00001: unique constraint (TSI.SYS_C002728) violated

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira