net.sf.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update

Hibernate JIRA | Dwight Harm | 1 decade ago
  1. 0

    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

    Hibernate JIRA | 1 decade ago | Dwight Harm
    net.sf.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
  2. 0

    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

    Hibernate JIRA | 1 decade ago | Dwight Harm
    net.sf.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
  3. 0

    Trigger leads to ora-600 error from jdbc programme but sql in sql*plus works OK.

    Oracle Community | 1 decade ago | 68212
    java.sql.BatchUpdateException: ORA-00600: internal error code, arguments: [qmxarElemAt2], [0], [], [], [], [], [], [] ORA-04088: error during execution of trigger 'POCWIP.PNO_INSERT'
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    Update list with new element from persist object

    Stack Overflow | 2 years ago
    org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
  6. 0

    AbstractFlushingEventListener - Could not synchronize database state with session - Spring Forum

    spring.io | 1 year ago
    org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update

    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. java.sql.BatchUpdateException

      ORA-00001: unique constraint (TSI.SYS_C002728) violated

      at oracle.jdbc.dbaccess.DBError.throwBatchUpdateException()
    2. oracle.jdbc.dbaccess
      DBError.throwBatchUpdateException
      1. oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:459)
      1 frame
    3. Oracle jdbc
      OraclePreparedStatement.executeBatch
      1. oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:3907)
      1 frame
    4. c3p0:JDBC DataSources/Resource Pools
      FilterPreparedStatement.executeBatch
      1. com.mchange.v2.sql.filter.FilterPreparedStatement.executeBatch(FilterPreparedStatement.java:260)
      1 frame
    5. net.sf.hibernate
      JDBCTransaction.commit
      1. net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
      2. net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:128)
      3. net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:61)
      4. net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:58)
      5. net.sf.hibernate.impl.BatcherImpl.prepareBatchStatement(BatcherImpl.java:111)
      6. net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:454)
      7. net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:436)
      8. net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.java:37)
      9. net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2449)
      10. net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2435)
      11. net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2392)
      12. net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2261)
      13. net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
      13 frames
    6. com.xyz.promotion
      DeployQuartzJob.execute
      1. com.xyz.promotion.admin.deploy.OfferSynchronizer.synchronizeOffer(OfferSynchronizer.java:214)
      2. com.xyz.promotion.admin.deploy.OfferDeployer.execute(OfferDeployer.java:156)
      3. com.xyz.promotion.admin.deploy.DeployQuartzJob.execute(DeployQuartzJob.java:72)
      3 frames
    7. quartz
      SimpleThreadPool$WorkerThread.run
      1. org.quartz.core.JobRunShell.run(JobRunShell.java:191)
      2. org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:516)
      2 frames