unique constraint error with increment generator, updating two database instances

Description

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

Environment

Hibernate 2.1.8, Oracle 9i, Tomcat

Assignee

Unassigned

Reporter

Dwight Harm

Labels

None

Feedback Requested

None

Feedback Requested By

None

backPortable

None

Suitable for new contributors

None

Pull Request

None

backportDecision

None

backportReEvaluate

None

Components

Affects versions

Priority

Major
Configure