Thread safe problem using hibernate new sequence generator

[fbn79]

Hi.
I'm developing a web application based on grails+hibernate+postgres

I want to use spring batch to import from a legacy flat files data in my database but I found a problem when tring to use org.hibernate.id.enhanced.SequenceStyleGenerator as id generator in my domains.

Randomly I gain a "duplicate key value violates unique constraint".

reading this post
https://forum.hibernate.org/viewtopic.php?p=2395315
i think that SequenceStyleGenerator is threadsafe ( grails use hibernate 3.3.1 ) so I think the problem is in spring batch.

I realized a small grails web application that reproduce the problem.
The test web application use hsqldb so no database is needed to test
http://fabianotaioli.maniacmansion.i...pfrombatch.rar

[Dave Syer]

Thanks for the sample. It would be more useful at this stage if you just posted the stacktrace from when the job fails.

(Please use [code][/code] tags to post code and stack traces.)

[fbn79]

Thank you for your quick reply.
This is the stacktrace ( using postgres as database )


Stack trace: first part

2009-12-19 20:42:36,104 [SimpleAsyncTaskExecutor-3] ERROR util.JDBCExceptionReporter  - L'operazione «batch» 2 insert into sub_domain (version, c1, c10, c2, c3, c4, c5, c6, c7, c8, c9, first_name, height, last_name, my_domain_class_id, id) values ('0', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', '0', 'ok', 'ok', '31', '124') è stata interrotta. Chiamare «getNextException» per scoprirne il motivo.
2009-12-19 20:42:36,104 [SimpleAsyncTaskExecutor-3] ERROR util.JDBCExceptionReporter  - ERROR: duplicate key value violates unique constraint "sub_domain_pkey"
2009-12-19 20:42:36,166 [SimpleAsyncTaskExecutor-3] ERROR def.AbstractFlushingEventListener  - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:94)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
        at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:114)
        at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:109)
        at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:244)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2252)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2688)
        at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79)
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
        at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
        at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027)
        at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:365)
        at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
        at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:655)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:732)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:701)
        at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:297)
        at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:67)
        at org.springframework.batch.repeat.support.TaskExecutorRepeatTemplate$ExecutingRunnable.run(TaskExecutorRepeatTemplate.java:230)
        at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.BatchUpdateException: L'operazione «batch» 2 insert into sub_domain (version, c1, c10, c2, c3, c4, c5, c6, c7, c8, c9, first_name, height, last_name, my_domain_class_id, id) values ('0', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', '0', 'ok', 'ok', '31', '124') è stata interrotta. Chiamare «getNextException» per scoprirne il motivo.
        at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2569)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1796)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2708)
        at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
        at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
        ... 21 more
2009-12-19 20:42:36,228 [SimpleAsyncTaskExecutor-3] ERROR tasklet.TaskletStep  - Rollback caused by fatal failure failed
org.springframework.transaction.IllegalTransactionStateException: Transaction is already completed - do not call commit or rollback more than once per transaction
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:795)
        at org.springframework.batch.core.step.tasklet.TaskletStep.rollback(TaskletStep.java:368)
        at org.springframework.batch.core.step.tasklet.TaskletStep.access$1000(TaskletStep.java:68)
        at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:309)
        at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:67)
        at org.springframework.batch.repeat.support.TaskExecutorRepeatTemplate$ExecutingRunnable.run(TaskExecutorRepeatTemplate.java:230)
        at java.lang.Thread.run(Thread.java:619)
2009-12-19 20:42:36,416 [SimpleAsyncTaskExecutor-6] ERROR util.JDBCExceptionReporter  - L'operazione «batch» 1 insert into sub_domain (version, c1, c10, c2, c3, c4, c5, c6, c7, c8, c9, first_name, height, last_name, my_domain_class_id, id) values ('0', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', '0', 'ok', 'ok', '51', '200') è stata interrotta. Chiamare «getNextException» per scoprirne il motivo.
2009-12-19 20:42:36,416 [SimpleAsyncTaskExecutor-6] ERROR util.JDBCExceptionReporter  - ERROR: duplicate key value violates unique constraint "sub_domain_pkey"
2009-12-19 20:42:36,478 [SimpleAsyncTaskExecutor-6] ERROR def.AbstractFlushingEventListener  - Could not synchronize database state with session

[fbn79]

stack trace : part 2

org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:94)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
        at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:114)
        at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:109)
        at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:244)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2252)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2688)
        at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79)
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
        at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
        at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027)
        at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:365)
        at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
        at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:655)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:732)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:701)
        at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:297)
        at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:67)
        at org.springframework.batch.repeat.support.TaskExecutorRepeatTemplate$ExecutingRunnable.run(TaskExecutorRepeatTemplate.java:230)
        at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.BatchUpdateException: L'operazione «batch» 1 insert into sub_domain (version, c1, c10, c2, c3, c4, c5, c6, c7, c8, c9, first_name, height, last_name, my_domain_class_id, id) values ('0', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', '0', 'ok', 'ok', '51', '200') è stata interrotta. Chiamare «getNextException» per scoprirne il motivo.
        at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2569)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1796)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2708)
        at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
        at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
        ... 21 more
2009-12-19 20:42:36,540 [SimpleAsyncTaskExecutor-6] ERROR tasklet.TaskletStep  - Rollback caused by fatal failure failed
org.springframework.transaction.IllegalTransactionStateException: Transaction is already completed - do not call commit or rollback more than once per transaction
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:795)
        at org.springframework.batch.core.step.tasklet.TaskletStep.rollback(TaskletStep.java:368)
        at org.springframework.batch.core.step.tasklet.TaskletStep.access$1000(TaskletStep.java:68)
        at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:309)
        at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:67)
        at org.springframework.batch.repeat.support.TaskExecutorRepeatTemplate$ExecutingRunnable.run(TaskExecutorRepeatTemplate.java:230)
        at java.lang.Thread.run(Thread.java:619)
2009-12-19 20:42:36,603 [SimpleAsyncTaskExecutor-1] ERROR step.AbstractStep  - Encountered an error executing the step: class org.springframework.batch.core.step.AbstractStep$FatalException: Fatal failure detected
org.springframework.batch.core.step.AbstractStep$FatalException: Fatal failure detected
        at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:303)
        at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:67)
        at org.springframework.batch.repeat.support.TaskExecutorRepeatTemplate$ExecutingRunnable.run(TaskExecutorRepeatTemplate.java:230)
        at java.lang.Thread.run(Thread.java:619)
Caused by: org.springframework.dao.DataIntegrityViolationException: Could not execute JDBC batch update; nested exception is org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
        at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:636)
        at org.springframework.orm.hibernate3.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:789)
        at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:663)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:732)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:701)
        at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:297)
        ... 3 more
Caused by: org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:94)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
        at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:114)
        at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:109)
        at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:244)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2252)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2688)
        at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79)
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
        at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
        at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027)
        at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:365)
        at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
        at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:655)
        ... 6 more
Caused by: java.sql.BatchUpdateException: L'operazione «batch» 1 insert into sub_domain (version, c1, c10, c2, c3, c4, c5, c6, c7, c8, c9, first_name, height, last_name, my_domain_class_id, id) values ('0', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', 'ok', '0', 'ok', 'ok', '4', '7') è stata interrotta. Chiamare «getNextException» per scoprirne il motivo.
        at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2569)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1796)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2708)
        at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
        at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
        ... 21 more

[fbn79]

This is the stack trace when using hsqld

2009-12-19 20:47:51,908 [SimpleAsyncTaskExecutor-1] ERROR step.AbstractStep  - Encountered an error executing the step: class org.springframework.batch.core.step.AbstractStep$FatalException: Fatal failure detected
org.springframework.batch.core.step.AbstractStep$FatalException: Fatal failure detected
        at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:303)
        at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:67)
        at org.springframework.batch.repeat.support.TaskExecutorRepeatTemplate$ExecutingRunnable.run(TaskExecutorRepeatTemplate.java:230)
        at java.lang.Thread.run(Thread.java:619)
Caused by: org.springframework.jdbc.UncategorizedSQLException: Hibernate flushing: Could not execute JDBC batch update; uncategorized SQLException for SQL [insert into sub_domain (version, c1, c10, c2, c3, c4, c5, c6, c7, c8, c9, first_name, height, last_name, my_domain_class_id, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; SQL state [null]; error code [0]; failed batch; nested exception is java.sql.BatchUpdateException: failed batch
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.orm.hibernate3.HibernateTransactionManager.convertJdbcAccessException(HibernateTransactionManager.java:801)
        at org.springframework.orm.hibernate3.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:787)
        at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:663)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:732)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:701)
        at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:297)
        ... 3 more
Caused by: java.sql.BatchUpdateException: failed batch
        at org.hsqldb.jdbc.jdbcStatement.executeBatch(Unknown Source)
        at org.hsqldb.jdbc.jdbcPreparedStatement.executeBatch(Unknown Source)
        at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
        at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
        at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:114)
        at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:109)
        at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:244)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2252)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2688)
        at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79)
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
        at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
        at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027)
        at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:365)
        at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
        at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:655)
        ... 6 more

[Dave Syer]

I wouldn't expect that id generator to work reliably in a concurrent application with HSQLDB (since there is no transaction isolation in that platform). Postgres I'm not so sure about. Are you using a HibernateTransactionManager, and did you set it up correctly with the session factory? I'm not 100% sure what Grails does for you, but I guess that should be kosher. If you can write a unit test that just uses Spring and Hibernate that would be helpful.

Bottom line though: it's almost certainly not a problem with Batch in my opinion.

[fbn79]

Hello,
Yes I'm using transactionManager auto configured by grails.
I based the test grails/batch application on this post

http://neuralmonkey.blogspot.com/200...f-current.html

adapted to batch 2.0 ( added the LineMapper).

I'm new to the world of spring and grails so is not easy and immediate for
me to build a test application in spring without grails.
If you want to take a look to the grails one the interesting parts are

/grails-app/conf/spring/myDomainClassResources.groovy
( all spring batch beans are declared here)

/src/groovy/MyDomainClassMapper.groovy (the lmapper)
/src/groovy/MyDomainClassItemWriter.groovy (the writer )

/grails-app/domain/* (my hibernate domains)

I have uploaded a postgres version of the test app
http://fabianotaioli.maniacmansion.i...ombatch_pg.rar

The app is configured to use the db
jdbcostgresql://localhost:5432/batch
user: batch
pass: batch

Thank you

[lkaczmarek]

hm... i think problem could be with sequence?

What happend then you add this code to domain class:

static mapping = {
id generator:'sequence', params:[sequence:'unique_name_id_sequence']
}


Łukasz Kaczmarek
http://www.draftsoft.pl
http://crm.draftsoft.pl

[fbn79]

With the traditional sequence id generator works without problem but you loose the benefits of the new advanced hibernate sequence generator.